SQLite

This page documents only how the python-fastapi-sqlite target differs from python-fastapi-postgres. Everything not listed here — file tree, naming conventions, HTTP contract, OpenAPI, the FastAPI app/Docker app stage, extension points, limitations — is identical; read the PostgreSQL page for the full reference.

The database axis is a pluggable Dialect strategy (codegen/migration/Dialect.scala); it decides the SQLAlchemy column type, trigger shape, partial-index handling, and the connection / docker-compose wiring (DialectView). Selecting SQLite is one flag — no emitter or template changes.

sbt "cli/run compile --framework fastapi --db sqlite --ignore-verify --out /tmp/out fixtures/spec/url_shortener.spec"

Deltas from PostgreSQL

• File-backed, no service. The emitted docker-compose.yml has no database container and no migrations service dependency on one; the database is a local file. The app container is otherwise unchanged.
• Foreign keys. SQLite does not enforce foreign keys by default, so the emitted engine setup registers a connection listener issuing PRAGMA foreign_keys=ON.
• Column types. Postgres-specific column types are mapped to their SQLite equivalents by the Dialect; the canonical mapping is the emitter, which wins over this page if they disagree.
• Test generation. No difference. --with-tests emits the full conformance / property / stateful suite here exactly as for PostgreSQL — the suite is dialect-invariant (black-box HTTP, ORM-based admin reset) and is byte-identical across every fastapi dialect.
• Serial primary keys. A 64-bit serial PK maps to sa.Integer(), not sa.BigInteger(): SQLite autoincrements only the INTEGER PRIMARY KEY rowid alias (itself 64-bit), so a BIGINT PK would silently not autoincrement. This matches the raw-SQL renderer's Dialect.Sqlite.serialColumnDef.

CI gate

.github/workflows/python-build.yml runs the sqlite matrix leg: it emits the project --with-tests, runs uv sync --all-extras, and performs an Alembic upgrade head → downgrade base → upgrade head round-trip against a real file database (sqlite+aiosqlite) — proving the --with-tests project generates, installs, and migrates on SQLite. The emitted suite is dialect-invariant — byte-identical across every fastapi dialect, asserted deterministically by TestEmitTest — so SQLite gets exactly the suite PostgreSQL does. (End-to-end execution of the generated app/suite is exercised by the nightly mutation job; it needs --with-synthesis to fill non-CRUD bodies and is independent of the database axis.)

If this page and the emitted output disagree, the emitter wins — file an issue or PR to correct the doc.