wsqlite 🚀

SQLite ORM using Pydantic models - Simple, Type-Safe, and High-Performance.

wsqlite is a high-level Python ORM library that provides a clean, type-safe interface for SQLite database operations. By leveraging Pydantic v2, it ensures data integrity and offers a modern development experience with automatic schema synchronization and full async support.

📖 Table of Contents

• 🌟 Key Features
• 🤔 Why wsqlite?
• 📊 Performance
• 📦 Installation
• 🚀 Quick Start
• 🛠️ SQL Constraints Reference
• 🎯 Advanced Functionality
• 📁 Project Structure
• 🧪 Testing & Quality
• 📝 License & Author

🌟 Key Features

• 🕵️ Enterprise Forensic Automation - Ghost table (_forensic_audit_log) audit trail for all data mutations (INSERT, UPDATE, DELETE).

• 👁️ Database Views (@view) - Declarative SQLite View definition with topological depends_on=[...] DDL ordering and read-only ORM querying.

• 🗂️ Multi-Table Manager & Registry - Multi-model registration with dictionary indexing app[User] and dynamic attribute dispatch app.user.

• 🔗 Pydantic Integration - Define your database schema using standard Pydantic v2 models.

• 🔄 Auto Schema Sync - Tables are created and synchronized automatically when your models change.

• ⚡ Connection Pooling - High-performance, thread-safe connection pool with WAL mode enabled by default.

• 📝 Intuitive CRUD - Simple methods for insert, get, update, and delete.

• 🔍 Smart Migration - Automatically adds new columns when detected in the model.

• 🔒 Advanced Constraints - Native support for Primary Keys, UNIQUE (single & composite), NOT NULL, and Foreign Keys.

• 🛡️ Type Safety - Full type hints and runtime validation powered by Pydantic.

• ⚡ Async Ready - Full async/await support for high-performance applications.

• 🔨 Query Builders - Safe, fluent API for complex queries with JOINs, GROUP BY, and more.

• 🔄 Versioned Migrations - Robust system for schema versioning and upgrades/rollbacks.

🛠️ Key Technologies & Libraries Used

• Python 3.9+ - Primary programming language
• Pydantic v2 - Modern schema definition and data validation
• sqlite3 & aiosqlite - SQLite connectors for synchronous and asynchronous execution
• loguru - High-performance structured logging framework
• click - Command-line interface builder
• pytest & pytest-cov - Automated unit testing and coverage evaluation

🤔 Why wsqlite?

Most Python ORMs are either too heavy (SQLAlchemy) or too basic (raw sqlite3). wsqlite fills the gap by providing:

1. Zero Boilerplate: No need to write CREATE TABLE statements or manual migrations for new columns.
2. Developer Experience: Use the same Pydantic models for your API (FastAPI) and your Database.
3. Safety First: SQL Injection protection and strict type validation out of the box.
4. Performance: Optimized connection pooling and WAL mode for concurrent applications.

📊 Performance at a Glance

🏆 wSQLite Benchmark Results:
   - Throughput: ~5,000+ insertions/second
   - Latency: ~0.2ms average
   - Footprint: <10MB memory overhead

📦 Installation

pip install wsqlite

For development or benchmarking:

pip install -e ".[dev,benchmark,stress]"

🚀 Quick Start in 60 Seconds

Define and Insert

from typing import Optional
from pydantic import BaseModel, Field
from wsqlite import WSQLite

class User(BaseModel):
    id: Optional[int] = Field(None, description="primary autoincrement")
    name: str
    email: str = Field(..., description="unique")

# Initializing creates the table automatically
db = WSQLite(User, "app.db")

# Type-safe insertion
db.insert(User(name="John Doe", email="john@example.com"))

# Easy querying
users = db.get_all()
john = db.get_by_field(email="john@example.com")

Async Operations

async def main():
    await db.insert_async(User(name="Jane Doe", email="jane@example.com"))
    users = await db.get_all_async()

🛠️ SQL Constraints Reference

wsqlite uses the description parameter of Pydantic's Field to define SQL constraints in a declarative way.

Example Model with all Constraints

class Employee(BaseModel):
    # Auto-incrementing Primary Key
    id: Optional[int] = Field(None, description="primary autoincrement")

    # Required and Unique
    employee_code: str = Field(..., description="unique not null")

    # Composite Unique (unique together: department + position)
    department: str = Field(..., description="unique:job_role")
    position: str = Field(..., description="unique:job_role")

    # Foreign Key
    office_id: int = Field(..., description="references:office.id")

🎯 Advanced Functionality

Composite Unique Constraints

class Profile(BaseModel):
    username: str = Field(..., description="unique:account")
    provider: str = Field(..., description="unique:account")
# (username, provider) must be unique together

Foreign Keys

class Post(BaseModel):
    title: str
    author_id: int = Field(..., description="references:user.id")

Powerful Query Builder

from wsqlite.builders import QueryBuilder

results = (
    QueryBuilder("users")
    .select("id", "name")
    .join("orders", "users.id = orders.user_id", "LEFT")
    .where("status", "=", "active")
    .group_by("users.id")
    .having("COUNT(orders.id)", ">", 5)
    .execute(conn)
)

📁 Project Structure

• src/wsqlite/: Core library logic.
• examples/: Comprehensive usage examples (CRUD, Transactions, Relationships, etc.).
• test/: Full unit and integration test suite.
• benchmark/: Performance testing tools.

🧪 Testing & Quality

We maintain high standards with extensive testing:

# Run unit tests
pytest

# Run stress tests
python -m stress_test.run --scenario concurrent --records 100000

📈 Version History

• v1.2.4 - 337+ tests, 78% coverage. Improved migrations and query builder.
• v1.2.2 - Added Composite Uniques, Foreign Keys, and Autoid support. Fixed transaction bugs.
• v1.2.0 - 90%+ test coverage, async connection pool.
• v1.1.0 - Connection pooling, advanced query builder, migrations.

📝 License & Author

Distributed under the MIT License. Created with ❤️ by William Steve Rodriguez Villamizar.

• 📧 wisrovi.rodriguez@gmail.com
• 🔗 GitHub | LinkedIn