DEV Community

William Rodriguez
William Rodriguez

Posted on

The end of raw SQL strings: Pydantic v2 as your SQLite schema

#opensource

Why should you maintain separate Pydantic schemas for your FastAPI endpoints and SQLAlchemy models for your database? WSQLite bridges the gap with zero boilerplate and enterprise performance.

This is Day 01 of the WSQLite Open-Source Engineering Series.

The Reality of Modern Python Persistence

Maintaining dual schemas between validation layers and relational tables causes silent drift, type discrepancies, and tedious migration scripts. WSQLite introduces direct Pydantic v2 Native SQLite ORM Mapping.

Production Implementation 

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")

# Automatically creates table 'user' with proper SQLite schema
db = WSQLite(User, "app.db")

# Type-safe insert and query
db.insert(User(name="Alice", email="alice@example.com"))
user = db.get_by_field(email="alice@example.com")[0]
print(f"Found: {user.name} ({user.email})")
Enter fullscreen mode Exit fullscreen mode

Why Developers Love WSQLite

  • Direct Pydantic v2 Mapping: Define database tables using standard Pydantic models.
  • Auto-Sync Schema Migrations: Automatically checks and adds new columns on startup without manual SQL migration files.
  • Enterprise Concurrency: Thread-safe connection pool with WAL (Write-Ahead Logging) mode enabled by default (5,000+ inserts/sec).
  • Dual Engine: Full support for synchronous and asynchronous (async/await) execution.

Verification & Documentation

Author: William Steve Rodríguez Villamizar (Wisrovi)

Top comments (0)