sqler 1.2026.4.1

A lightweight, JSON-first micro-ORM for SQLite — Pydantic models as JSON documents, fluent queries, sync + async

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

Dataclass — zero dependencies, WASM compatible

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

msgspec — C-level JSON performance

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

Tour	Topics
01. Fundamentals	Models, CRUD, queries, aggregations
02. Relationships	References, hydration, cross-model queries
03. Safe Models	Optimistic locking, conflict resolution
04. Transactions	Atomic operations, rollback
05. Mixins	Timestamps, soft delete, lifecycle hooks
06. Advanced	Bulk ops, indexes, integrity, raw SQL
07. Export/Import	CSV, JSON, JSONL
08. Full-Text Search	FTS5, boolean queries, ranking
09. Change Tracking	Dirty checking, change detection
10. Database Ops	Health checks, stats, vacuum, logging
11. Metrics & Caching	Prometheus metrics, query caching, pools

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

All 11 tours

Tour	Topics
01. Fundamentals	Models, CRUD, queries, aggregations
02. Relationships	References, hydration, cross-model queries
03. Safe Models	Optimistic locking, conflict resolution
04. Transactions	Atomic operations, rollback
05. Mixins	Timestamps, soft delete, lifecycle hooks
06. Advanced	Bulk ops, indexes, integrity, raw SQL
07. Export/Import	CSV, JSON, JSONL
08. Full-Text Search	FTS5, boolean queries, ranking
09. Change Tracking	Dirty checking, partial updates, diff
10. Database Ops	Health checks, stats, vacuum, logging
11. Metrics & Caching	Prometheus metrics, query caching, pools

---

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

Operation	Result	Context
Bulk insert	84K rows/sec	bulk_upsert() at 10K rows
Index speedup	470x	9.4ms → 0.02ms with create_index()
Cache hit	7,000x	14ms → 0.002ms with @cached_query
FTS search	0.28ms	Sub-millisecond across all dataset sizes
Bulk vs single	5.2x	bulk_upsert vs save() loop at 10K
Lite/msgspec models	1.3x	Dataclass or msgspec variant vs Pydantic overhead

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

Resource	Description
API Reference	All 46 tested contracts (C01–C46)
Examples Cookbook	End-to-end scripts for every feature
Interactive Tours	11 marimo notebooks — run in browser
Benchmark Suite	uv run python -m benchmarks run — 22 scenarios
Changelog	Version history

---

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