v1.2.2 — Production-Ready with 317 Tests & 79% Coverage

SQLite ORM
Using Pydantic Models

wsqlite is a production-grade SQLite ORM that uses Pydantic models for type-safe database operations. Full async/await support, connection pooling, and automatic schema synchronization.

View on GitHub Read the Docs
PyPI Version
Python Versions
License
Tests
Coverage
# Install in seconds
pip install wsqlite

# Define your model with Pydantic
from pydantic import BaseModel
from wsqlite import WSQLite

class User(BaseModel):
    id: int
    name: str
    email: str

db = WSQLite(User, "users.db")
db.insert(User(id=1, name="Alice", email="alice@example.com"))
users = db.get_all()
Introduction

What is wsqlite?

A production-grade SQLite ORM library engineered for modern Python applications. Built atop Pydantic's type validation system for type-safe database operations.

Type-Safe Database Operations

wsqlite leverages Pydantic models to define database schemas with full type safety. No SQL strings, no manual type conversions — just pure Python with automatic validation.

Perfect for data-intensive applications, microservices, and anywhere SQLite is the right tool for the job.

  • Type-Safe — Pydantic validation for all database operations
  • Async Support — Full async/await API for high-throughput scenarios
  • Connection Pooling — Built-in connection pool for performance
  • Auto Schema Sync — Models automatically sync with database
  • Production-Ready — 317 tests, 79% coverage, LTS release
1

Pydantic Model

Define schema with type hints

2

Auto Validation

Pydantic validates all data

3

SQLite Storage

Automatic table creation & sync

4

Type-Safe Results

Get back Pydantic models

Why wsqlite?

Problems wsqlite Solves

Raw SQLite and SQLAlchemy can be complex. wsqlite keeps it simple with Pydantic-powered type safety.

🐌

Verbose SQL Queries

Writing raw SQL strings is error-prone and hard to maintain. wsqlite uses Pydantic models instead.

🔓

Type Safety Issues

No compile-time checks for database operations. wsqlite validates everything with Pydantic.

🔧

Schema Management

Manual table creation and migrations. wsqlite auto-syncs models to database schema.

⚡

Performance

Connection overhead in async applications. wsqlite provides connection pooling out of the box.

Capabilities

Powerful Features

Everything you need for production-ready SQLite operations with clean Python code.

📝

Pydantic Models

Define database schemas using familiar Pydantic BaseModel. Full type validation and serialization.

View documentation
⚡

Full Async Support

Complete async/await API for high-throughput scenarios. Async connection pool included.

Async examples
🔄

Connection Pooling

Built-in connection pool with configurable size. AsyncConnectionPool for async workloads.

View pool docs
🔗

Auto Schema Sync

Models automatically sync with database structure. No manual migrations needed.

Schema sync docs
💾

Transaction Support

ACID-compliant transaction handling with context managers. Automatic rollback on errors.

Transaction examples
🔒

SQL Injection Protection

Query builder with parameterized queries. All inputs are properly escaped.

Query builder docs
⚠️

Exception Hierarchy

12 custom exception types for precise error handling. WSQLiteError, ValidationError, and more.

Exception docs
📊

Query Builder

Fluent SQL construction with JOINs, GROUP BY, HAVING, UNION support.

Query builder examples
🧪

Type Validators

Comprehensive type validation and serialization. Custom validators supported.

Validator docs
🚀

Benchmark Suite

Built-in benchmarks comparing vs sqlite3, sqlalchemy, and aiosqlite.

View benchmarks
🛠️

CLI Interface

Integrated command-line utilities for common database operations.

CLI docs
✅

Well Tested

317 tests with 79% code coverage. Stress testing suite included.

View test suite
API Reference

Core Classes & Functions

Complete API reference with all available classes, methods, and utilities.

from wsqlite import WSQLite

class WSQLite(Model: Type[BaseModel], db_path: str, ...):
  # Core Methods
  def insert(self, record: Model) -> Model
  def get_by_id(self, id: int) -> Optional[Model]
  def get_all(self) -> list[Model]
  def update(self, id: int, data: dict) -> Optional[Model]
  def delete(self, id: int) -> bool
  def count(self) -> int
  
  # Async Methods
  async def insert_async(self, record: Model)
  async def get_all_async(self) -> list[Model]
  
  # Query Methods
  def filter(self, **kwargs) -> list[Model]
  def paginate(self, page: int, per_page: int)

from wsqlite import ConnectionPool

class ConnectionPool:
  def __init__(
    db_path: str,
    pool_size: int = 5,
    timeout: float = 30.0
  ):
  
  def get_connection(self) -> Connection
  def return_connection(self, conn)
  def close(self)

class AsyncConnectionPool:
  # Same interface, async versions

from wsqlite import Transaction

class Transaction:
  # Context manager for transactions
  def __init__(db_path: str):
  
  # Usage:
with Transaction(db_path) as tx:
  tx.execute(sql, params)
  tx.commit() # or auto-commit on exit

class AsyncTransaction:
  # Async version with same interface

from wsqlite import QueryBuilder

class QueryBuilder:
  def select(self, *columns) -> self
  def where(self, condition: str, *params) -> self
  def join(self, table, on, how) -> self
  def group_by(self, *columns) -> self
  def having(self, condition) -> self
  def order_by(self, *columns) -> self
  def limit(self, n: int) -> self
  def execute(self) -> list

from wsqlite import TableSync

class TableSync:
  def __init__(db_path: str):
  
  def sync(self, model: Type[BaseModel]) -> bool
  def get_schema(self) -> dict
  def validate(self, model: Type[BaseModel]) -> bool

class AsyncTableSync:
  # Async version

from wsqlite import WSQLiteError

class WSQLiteError(Exception): # Base exception

class ValidationError(WSQLiteError):
class ConnectionError(WSQLiteError):
class PoolExhaustedError(WSQLiteError):
class DatabaseLockedError(WSQLiteError):
class TransactionError(WSQLiteError):
class QueryError(WSQLiteError):
class SQLInjectionError(WSQLiteError):
Get Started

Quick Start Guide

Start using wsqlite in minutes with these fundamental patterns.

Basic CRUD Operations

from pydantic import BaseModel
from wsqlite import WSQLite

class User(BaseModel):
  id: int
  name: str
  email: str

db = WSQLite(User, "users.db")

# Insert
user = db.insert(User(id=1, name="Alice", email="alice@example.com"))

# Query
all_users = db.get_all()
alice = db.get_by_id(1)

Async Operations

import asyncio
from wsqlite import WSQLite

async def main():
  db = WSQLite(User, "users.db")
  
  # Async insert
  await db.insert_async(User(id=2, name="Bob", email="bob@example.com"))
  
  # Async query
  users = await db.get_all_async()
  print(users)

asyncio.run(main())

Transactions

from wsqlite import Transaction

with Transaction("users.db") as tx:
  tx.execute("INSERT INTO users VALUES (?, ?, ?)", (3, "Charlie", "charlie@example.com"))
  tx.execute("INSERT INTO users VALUES (?, ?, ?)", (4, "Diana", "diana@example.com"))
  tx.commit() # Commits both or neither

Connection Pooling

from wsqlite import WSQLite, ConnectionPool

# With connection pooling
db = WSQLite(User, "users.db", pool_size=20)

# Or use pool directly
pool = ConnectionPool("users.db", pool_size=10)
conn = pool.get_connection()
Get Started

Installation

Install wsqlite in seconds and start building type-safe database operations.

PyPI (Recommended)

pip install wsqlite

From Source

# Clone the repository
git clone https://github.com/wisrovi/wsqlite
cd wsqlite
pip install -e .

Development Install

# Install with dev dependencies
pip install -e ".[dev]"
317
Tests Passing
79%
Code Coverage
12
Exception Types
3.9+
Python 3.9+
100%
Type Hints
MIT
License
About the Author

Meet the Creator

WS

William Steve Rodriguez Villamizar

AI Leader & Solutions Architect

ecaptureDtech

Bridging the gap between complex AI systems and practical business solutions. With expertise in distributed systems, automation, and enterprise architecture. Creator of multiple open-source Python libraries for data processing and security.

GitHub LinkedIn Portfolio

Ready to Build Type-Safe Apps?

Start creating production-ready SQLite applications with Pydantic-powered type safety.

Star on GitHub Read Full Documentation