SQLer

English | 日本語はこちら

A lightweight, JSON-first micro-ORM for SQLite. Define models with Pydantic, dataclasses, or msgspec — persist them as JSON documents, query with a fluent API. Sync and async.

Install

pip install sqler

Python 3.12+ · SQLite with JSON1 (bundled on most platforms).

Quick Start

from sqler import SQLerDB, SQLerModel
from sqler.query import SQLerField as F

class User(SQLerModel):
    name: str
    age: int
    tags: list[str] = []

db = SQLerDB.in_memory()
User.set_db(db)

User(name="Alice", age=30, tags=["admin"]).save()
User(name="Bob", age=25, tags=["user"]).save()

admins = User.query().filter(F("tags").contains("admin")).all()
assert admins[0].name == "Alice"

young = User.query().filter(F("age") < 28).order_by("name").all()
assert young[0].name == "Bob"

Three Model Backends, One API

from dataclasses import dataclass
from sqler import SQLerDB
from sqler.models.lite import SQLerLiteModel

@dataclass
class User(SQLerLiteModel):
    __tablename__ = "users"
    name: str = ""
    age: int = 0

db = SQLerDB.in_memory()
User.set_db(db)
User(name="Alice", age=30).save()

from sqler import SQLerDB
from sqler.models.msgspec import SQLerMsgspecModel

class User(SQLerMsgspecModel):
    __tablename__ = "users"
    name: str = ""
    age: int = 0

db = SQLerDB.in_memory()
User.set_db(db)
User(name="Alice", age=30).save()

All three backends share the same query API (filter, order_by, save_many, etc.), safe model variants with optimistic locking, and sync + async support.

See It in Action

git clone https://github.com/gabu-quest/sqler.git && cd sqler
uv run python demo.py

Interactive Tour

Learn sqler hands-on with marimo notebooks — run and modify code directly in your browser.

Launch Interactive Tour →

Run in browser (Lite models — no install needed):

Run locally (full Pydantic features — all 11 tours):

git clone https://github.com/gabu-quest/sqler.git && cd sqler
uv sync --dev
uv run marimo edit examples/tour_01_fundamentals.py

Why sqler?

SQLite is the most deployed database on earth, and JSON1 turns it into a document store. sqler bridges that gap: define models with your preferred backend (Pydantic, dataclasses, or msgspec), get document-style flexibility with a fluent query builder that reaches into nested JSON, and real data integrity (optimistic locking, referential policies, transactions) — all in a single file, zero-config database.

Performance

Real numbers from the benchmark suite (22 scenarios, Python 3.12, SQLite 3.50):

Honest Limitations

• Single writer — SQLite's architecture. Use save_many() / bulk_upsert and transactions to batch writes.
• JSON overhead — Fields are extracted via json_extract(), not native columns. Indexes close the gap.
• No JOINs — Relationships use reference hydration, not SQL JOINs. Fine for typical document patterns.
• Memory-bound — Large result sets materialize in Python. Use paginate() and count() to limit.

Run uv run python -m benchmarks run --scale medium --storage both to generate your own report.

Features

Core

• Three model backends — all with the same API:
  • Pydantic (SQLerModel) — full validation, rich serialization
  • Dataclass (SQLerLiteModel) — zero dependencies, WASM/Pyodide compatible
  • msgspec (SQLerMsgspecModel) — C-level JSON performance, Struct-based
• Fluent query builder: filter, exclude, order_by, paginate, contains, isin, between
• F() operator for nested fields: F("x")["y"], F(["items"]).any().where(...)
• Sync and async APIs with matching semantics

Safety & Integrity

• SQLerSafeModel with optimistic locking (_version + StaleVersionError)
• Referential integrity: cascade, restrict, set_null delete policies
• Transaction-aware saves — model.save() inside with db.transaction(): rolls back properly
• save_many() / asave_many() for fast bulk creation (multi-row INSERT with auto-chunking)
• bulk_upsert for efficient batch writes

Developer Experience

• FTS5 full-text search with ranking and highlights
• Query caching with TTL and LRU eviction
• Connection pooling, schema migrations, metrics collection
• Export/import: CSV, JSON, JSONL (sync + async)
• 11 interactive marimo tour notebooks
• Query logging, debug(), explain_query_plan()

When to Use sqler

Good fit:

• Fast prototyping with real persistence
• SQLite as embedded app state (Electron, CLI tools, mobile)
• JSON flexibility + data integrity in one package
• Pick your model backend: Pydantic (validation), dataclasses (lightweight), msgspec (speed)
• Browser/WASM apps via Pyodide (dataclass backend)

Consider alternatives:

• Need multi-database support → SQLAlchemy
• Purely relational SQLite workflows → sqlite-utils
• Complex JOINs at scale → a full ORM with proper relational modeling
• Write-heavy under contention → PostgreSQL

Showcase

Safe Models — Optimistic Locking

from sqler import SQLerDB, SQLerSafeModel, StaleVersionError

class Account(SQLerSafeModel):
    owner: str
    balance: int

db = SQLerDB.in_memory()
Account.set_db(db)

acc = Account(owner="Ada", balance=100).save()  # _version == 0
acc.balance = 120
acc.save()                                       # _version == 1

# Another process changes the row...
# Your next save detects the conflict:
try:
    acc.balance = 130
    acc.save()
except StaleVersionError:
    acc.refresh()  # reload from DB, then decide

Full tour → · API reference →

Relationships — Hydration & Cross-Filtering

from sqler import SQLerDB, SQLerModel

class Address(SQLerModel):
    city: str
    country: str

class User(SQLerModel):
    name: str
    address: Address | None = None

db = SQLerDB.in_memory()
Address.set_db(db)
User.set_db(db)

home = Address(city="Kyoto", country="JP").save()
user = User(name="Alice", address=home).save()

loaded = User.from_id(user._id)
assert loaded.address.city == "Kyoto"  # auto-hydrated

kyoto_users = User.query().filter(
    User.ref("address").field("city") == "Kyoto"
).all()
assert kyoto_users[0].name == "Alice"

Full tour → · API reference →

Full-Text Search

from sqler import SQLerDB, SQLerModel, FTSIndex

class Article(SQLerModel):
    title: str
    content: str

db = SQLerDB.in_memory()
Article.set_db(db)

Article(title="Python Tips", content="Learn Python fast").save()
Article(title="SQLite Guide", content="Master SQLite queries").save()

fts = FTSIndex(Article, fields=["title", "content"])
fts.create(db)
fts.rebuild()

results = fts.search("Python")        # instant — 0.28ms at any scale
ranked = fts.search_ranked("Python")   # with relevance scores

Full tour → · API reference →

Documentation

Testing

uv run pytest -q                                       # unit tests
uv run pytest -q -m perf                               # performance benchmarks
uv run python -m benchmarks run --scale small           # full benchmark suite
uv run python -m benchmarks plot                        # generate charts

Contributing

uv run ruff format .                                   # format
uv run ruff check .                                    # lint
uv run pytest -q --cov=src --cov-report=term-missing   # test with coverage

License

MIT