# DB Defaults

> Use when adding local data persistence to a Python project — caching, relational storage (simple, complex/OLTP, or analytical/OLAP), document storage, full-text search, vector/embedding search, background job queues, cron/scheduled tasks, pub/sub/event streaming, graph data, blob storage, or time-series data — and no explicit "evaluate the best DB for this" request was made. Gives the default technology per category, chosen for permissive licensing, pytest-local testability with no docker/cloud, and low-boilerplate LLM-agent-friendly APIs, so picks stay consistent across projects instead of drifting session to session.

- Skill: `theodoread/db-defaults` (Agent Skill, multi-file: 16 files)
- Install (CLI): `npx skillmds@latest add theodoread/db-defaults`
- Raw SKILL.md: https://api.skillmd.com/api/skills/theodoread/db-defaults/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: theodoread (https://skillmd.com/u/theodoread)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/theodoread/db-defaults

---


# Default storage tech per use case

Personal, Python-first, local-first projects. Applies when starting a storage/caching need without
an explicit request to analyze alternatives — pick from this table, don't re-litigate from scratch
each session. Deviating is fine when a category genuinely doesn't fit or scale requirements outgrow
the default (see each category's "Escalate to" line) — the point is to stop a fresh session/model
from silently picking something different for no reason, not to forbid judgment calls.

**Selection criteria for every entry below**: MIT/Apache-2.0/BSD-style permissive license only;
popular and actively maintained (verified via real GitHub/PyPI activity, not vibes); testable fully
inside a plain `pytest` run — in-memory or `tmp_path`-scoped, no Docker/cloud account/CI services;
low-boilerplate API a coding agent can use correctly without much ceremony or indirection. Picks
favor the best-fit tool per concern over minimizing the number of technologies in a project — don't
force a consolidation the categories below don't call for.

**Cash in the testability criterion: don't mock these.** The whole point of picking a store the
suite can start and stop is that tests run the real one, so a double here throws away what the
choice bought. `python-conventions`, "Don't double anything the suite can run for real", states the
rule and the boundary.

**Security is deliberately not a selection factor.** Every default here is chosen for local,
personal-scale use — nothing on this page should be treated as a production/multi-tenant/internet-
facing recommendation. Each category's "Escalate to" line is the pick for that situation instead.

## In-process ephemeral state

- Snippet: [`references/snippets/in-process-state.py`](references/snippets/in-process-state.py)
- Default: plain stdlib (`threading.Lock`, `time.monotonic`, a dict) — no library
- Why: no persistence needed; a library would be pure overhead for "hold one number, guarded by one
  lock"
- Escalate to: n/a — once it needs to survive a restart, it's the Cache or Relational category
  instead

## Cache (TTL / eviction)

- Snippet: [`references/snippets/cache.py`](references/snippets/cache.py)
- Default: `diskcache` (Apache-2.0, pure Python)
- Why: disk-backed, handles TTL/eviction so you don't hand-roll it; trivial pytest `tmp_path`
  fixture
- Escalate to: Redis — once multiple processes/machines need to share one cache

## Relational — simple (few tables, KV-shaped)

- Snippet: [`references/snippets/relational-simple.py`](references/snippets/relational-simple.py)
- Default: stdlib `sqlite3`, raw SQL
- Why: zero dependency, no ORM ceremony for a shape this small; `:memory:` or `tmp_path` in pytest
- Escalate to: Postgres

## Relational — complex / OLTP (many tables, real joins, migrations, frequent writes)

- Snippet: [`references/snippets/relational-oltp.py`](references/snippets/relational-oltp.py)
- Default: `sqlalchemy` + `alembic` (both MIT)
- Why: real Engine/Session/declarative-model ceremony, but it buys migration and relationship safety
  that nothing lighter offers for a genuinely transactional multi-table shape
- **Define the column vocabulary once**, via the declarative base's `type_annotation_map` for plain
  types and `Annotated` aliases for the ones needing per-column arguments. Every `Mapped[Decimal]`
  in every model then resolves the same way, so no column opts out by being written from memory in
  another file. This is also the honest answer to "why the ORM rather than Core" — Core gives you
  the same SQL with the vocabulary spelled out at each column instead.
- Escalate to: Postgres
- **The SQLite tier silently corrupts `Decimal` and drops `tzinfo`**, and both are invisible on the
  Postgres you escalate to — which matters here specifically, because "testable fully inside a plain
  `pytest` run" is what puts SQLite under every project following this entry. Measured against
  SQLAlchemy 2.0.52:
  - `Numeric` round-trips a `Decimal` through a float, with no warning:
    `1234567890123456789.000000001` comes back `1234567890123456768.0000000000`. The mechanism is in
    the library — `engine/default.py` sets `supports_native_decimal = False` and the SQLite dialect
    does not override it, so `sqltypes.py` takes the branch commented "DBAPI returns floats,
    convert", quantized to scale 10. A `TypeDecorator` storing the exact string is the fix.
  - A timezone-aware datetime comes back naive. Not a SQLite limitation: `dialects/sqlite/base.py`'s
    `DATETIME.bind_processor` never reads `tzinfo` at all.
  - The trap is how they hide. A first probe of the `Decimal` case _passes_ on a ten-digit value,
    which survives a float intact — so a project whose fixtures use realistic amounts concludes
    `Numeric` is fine and ships. Probe with a value wide enough to fail.
- **Pass `sqlite_strict=True`** in `__table_args__` so the test dialect stops being laxer than the
  Postgres it stands in for — the failure mode a test-only dialect invites. It is **not** a free
  extra keyword, and the snippet shows the whole shape: STRICT accepts only
  `INT`/`INTEGER`/`REAL`/`TEXT`/`BLOB`/`ANY`, so `create_all` fails outright on `NUMERIC(38, 10)`,
  on `DATETIME`, and on the `VARCHAR(64)` that a plain `String(64)` renders (all three measured on
  2.0.52). `String(64).with_variant(Text(), "sqlite")` keeps the real bound on Postgres, and the
  `Decimal`/`datetime` `TypeDecorator`s above are what make the other two columns legal. Adopting
  STRICT and closing the two traps are therefore the same piece of work, which is the argument for
  doing both at once rather than neither.
- **Alembic's SQLite batch mode is a data-integrity footgun.** On SQLite, `batch_alter_table`
  implements a change by recreating the table, which **drops that table's triggers** — and Alembic
  has no trigger awareness anywhere (a grep for "trigger" across the package returns one unrelated
  comment). It also skips a `CheckConstraint` that is **both reflected and unnamed**, per
  `batch.py`'s own `TODO`. Two mitigations, and either is valid: **name every constraint**, or
  decide that the SQLite tier never migrates at all and is always created from current metadata.

## Analytical / OLAP (read/aggregate-heavy queries over structured data)

- Snippet: [`references/snippets/analytical-olap.py`](references/snippets/analytical-olap.py)
- Default: `duckdb` (MIT — IP held by a nonprofit foundation specifically to keep it MIT "in
  perpetuity")
- Why: `duckdb.connect(":memory:")` gives native SQL over a plain connection, zero ORM ceremony —
  much lower-boilerplate than SQLAlchemy for this shape, and genuinely fast at joins/aggregation.
  Single-writer (fine for personal-scale local use); a multi-writer "Quack" protocol shipped May
  2026 but is still new
- Escalate to: Postgres+DuckDB extension, or MotherDuck

## Document store (schemaless / semi-structured JSON)

- Snippet: [`references/snippets/document-store.py`](references/snippets/document-store.py)
- Default: `tinydb` (MIT, pure Python)
- Why: real dict-like API (`db.insert({...})`, `db.search(Query().field == x)`) — no SQL anywhere;
  `MemoryStorage()` or `tmp_path` for pytest. Maintainer calls it "maintenance mode"
  (feature-complete and stable) — still gets bugfix releases, not abandoned
- Alternative: `sqlitedict` (Apache-2.0) for pure key→object storage with no field queries needed —
  flag its multi-year commit gap before reaching for it
- Escalate to: MongoDB

## Full-text search

- Snippet: [`references/snippets/full-text-search.py`](references/snippets/full-text-search.py)
- Default: SQLite `FTS5` virtual tables — ships with stdlib `sqlite3`, zero new dependency, built-in
  `bm25()` ranking
- Alternative: `bm25s` (MIT) for a standalone RAG-style scorer decoupled from a datastore; `tantivy`
  (MIT, Python bindings to the Rust `tantivy` search engine — `pip install tantivy`, not
  `tantivy-py`, which is a different, stale package) for heavier Lucene-like search once FTS5's
  feature set is genuinely too thin
- Avoid: `Whoosh` — long-unmaintained
- Escalate to: Elasticsearch or Meilisearch (both need a running server, so are excluded as local
  defaults)

## Vector / embedding similarity search

- Snippet: [`references/snippets/vector-search.py`](references/snippets/vector-search.py)
- Default: `qdrant-client` (Apache-2.0)
- Why: `QdrantClient(":memory:")` or `QdrantClient(path=tmp_path)` needs no server at all, and
  escalating later is a literal constructor-arg swap (`url=...`/cloud creds) — same class, same
  methods, verified against Qdrant's own docs. Handles vectors + metadata + persistence in one API
- Alternative: `chromadb` (Apache-2.0) — far bigger community/mindshare, arguably an even simpler
  first-touch API (`add()`/`query()`); reasonable pick if onboarding ease matters more than a
  verified no-rewrite escalation path
- Escalate to: Qdrant Cloud (client code unchanged) or Pinecone/Weaviate

## Background job / message queue

- Snippet: [`references/snippets/job-queue.py`](references/snippets/job-queue.py)
- Default: `huey` (MIT)
- Why: `SqliteHuey(..., immediate=True)` runs `@huey.task()` functions synchronously in-process — no
  consumer subprocess, no orchestration, a plain pytest test just calls the function directly. Small
  decorator-based API, 15-year track record, near-zero open-issue backlog
- Escalate to: Celery/RQ/Dramatiq — all need a real broker (Redis/RabbitMQ), which is the point once
  you need multiple worker processes/machines

## Cron / scheduled recurring tasks

- Snippet: [`references/snippets/cron-scheduler.py`](references/snippets/cron-scheduler.py)
- Default: `apscheduler` 3.x (MIT)
- Why: `SQLAlchemyJobStore` pointed at a `sqlite:///` URL persists schedules across restarts, no
  external broker, usable purely as a queue if scheduling isn't even needed. Genuine bus-factor risk
  to know about, not a reason to avoid it outright: single dominant maintainer (1,134 commits vs.
  next-highest contributor's 6), ~2-year-old unreviewed PRs, and v4 has been in alpha since 2020
  with no stable release — 3.x is still the only practical choice and remains actively patched
  (releases as recently as Jun 2026)
- Alternative: `schedule` (MIT) — zero-dependency, trivial in-process API, but no persistence (a
  restart loses all schedules) and itself hasn't been pushed since May 2024; a lightweight escape
  hatch for pure in-process cases, not a governance-driven replacement
- Escalate to: a managed scheduler (e.g. cloud cron) once recurring jobs need to survive beyond one
  machine

## Pub/sub / event streaming

- Snippet: [`references/snippets/pubsub.py`](references/snippets/pubsub.py)
- Default: `blinker` (MIT) — in-process signal/observer dispatch (`signal.connect(receiver)`,
  `signal.send(sender)`)
- Why: the same library Flask itself uses for signals; zero ceremony, fans out to multiple
  subscribers in-process, no server, trivially testable
- Escalate to: NATS (Apache-2.0) — genuinely different tool, not a bigger version of this one: needs
  a separately-provisioned `nats-server` process, and no official/maintained Python pytest-fixture
  package exists for local testing (the two community attempts are both abandoned), so it doesn't
  qualify as a local default here even though it's a strong product once you're actually running a
  server

## Graph data

- Snippet: [`references/snippets/graph-data.py`](references/snippets/graph-data.py)
- Default: `ladybug` (MIT, `pip install ladybug`) — embedded, Cypher query language, no server
- Why: this is the actively-maintained continuation of Kuzu, forked by the community 3 days after
  Kuzu's Oct 2025 archival (Apple acquisition) — same underlying engine, new stewardship, not a
  rewrite. Daily commits, MIT license, named active maintainers. Still under a year old as a project
  name, so treat as promising-but-young rather than as battle-tested as, say, SQLite
- Lightweight alternative: for relationship modeling that doesn't need real graph-traversal
  performance or a query language, a plain `edges(src, dst, relation)` table in your relational
  store is still simpler — reach for `ladybug` specifically when you need actual graph algorithms/
  traversal at more than toy scale. Pair either with `networkx` (BSD-3-Clause) for in-memory graph
  algorithms over already-loaded data
- Escalate to: Neo4j Aura, Amazon Neptune

## Blob / object storage

- Snippet: [`references/snippets/blob-storage.py`](references/snippets/blob-storage.py)
- Default: plain `pathlib.Path` file writes — no library
- Why: zero dependency, trivial `tmp_path` testing; adding an abstraction for cloud storage you
  don't have a concrete plan for yet is exactly the case YAGNI is for
- Escalate to: `fsspec` (BSD-3-Clause) + `s3fs`/`gcsfs` once cloud storage is an actual plan, not a
  maybe — same `fs.open()`/`fs.ls()` calls, only the protocol string changes

## Time-series data

- Snippet: [`references/snippets/time-series.py`](references/snippets/time-series.py)
- Default: `duckdb` (MIT) — same tool as the Analytical/OLAP category above
- Why: DuckDB's window functions, `time_bucket`, gap-filling, and ASOF joins are genuinely strong
  for time-series analysis and meaningfully cut boilerplate versus hand-rolling the same queries in
  raw SQL — the better tool for this concern specifically, independent of whether a project also
  needs it for OLAP work
- Lightweight alternative: a plain SQLite table with a timestamp-indexed column, if the need is
  truly just "store timestamped rows and filter by range," with no resampling/windowing
- Escalate to: InfluxDB, TimescaleDB

## Editing this skill

This file is _copied_ into `~/.agents/skills/db-defaults` at install time, never symlinked, so
**editing the deployed copy is local drift** — the exact thing this skill exists to prevent, and it
reaches no other machine. Edit the source in the repo this was installed from, push, and re-run
`skills add <that source> --global --skill db-defaults` to refresh every project's copy. If you
installed it from someone else's repo rather than your own fork, the source is theirs: open an issue
or a pull request there instead.

## Starter snippets

`references/snippets/` has one real, ruff-clean, self-contained Python file per category above —
each a verified `pip install` command in its docstring plus a working `test_*` function showing the
pytest-local pattern, directly copy-pasteable rather than a tutorial to adapt. Each category's
"Snippet:" line above links straight to its own file, so there's no need to open the others. This is
also where two real naming traps caught while writing them are documented at the point they matter:
`full-text-search.py` (the `tantivy` vs `tantivy-py` PyPI mixup) and `cron-scheduler.py` (the
nonexistent `SQLiteJobStore` class name).

## Full rationale

See [`references/rationale.md`](references/rationale.md) — the GitHub/PyPI evidence, the options
that were considered and rejected per category, and the reasoning behind every branch/alternative
above.