Skip to content

F17.4 — SQLite engine and embedded migrations

State Closed
Parent E17
Iteration Release 8
Start Date 2026-10-31
Closed Date 2026-10-31
Predecessors —
Source Owner request, 2026-10-21

Stories: 4 Closed

Story State Depends on
S17.4.1 — Approve the alembic dependency Closed (2026-10-31) —
S17.4.2 — Build create_sqlite_engine Closed (2026-10-31) —
S17.4.3 — Build run_migrations Closed (2026-10-31) —
S17.4.4 — Document both Closed (2026-10-31) —

A local-first application keeps its operational state in one SQLite file, sets the same connection pragmas by hand and runs its Alembic migrations from inside the process at startup. Apollo wrote both, and this feature added them to rn-forge-sqlalchemy.

Outcome: create_sqlite_engine and run_migrations (behind the migrations extra) shipped on 2026-10-31. They are documented in the rn-forge-sqlalchemy usage guide (docs/guides/usage.md, SQLite engine and Embedded migrations).

Context

Apollo does not wait for it; it keeps its own engine and migration code on today's pins and adopts these helpers after release and a pin bump.

Async only, like the rest of the package. rn-forge-sqlalchemy works through AsyncSession only, and an application behind FastAPI should too. Apollo's synchronous engine serves only its threaded job worker. Its beets library is not SQLAlchemy at all: beets keeps its own sqlite3 connection behind F17.2's actor. The rebuild moves the worker and its routers to AsyncSession, so no synchronous engine is needed, and none is offered.

A dependency outside this repository. An application generated by kiln can take rn-forge-sqlalchemy only once kiln's rn-forge-deps rule admits it for python-web-app. Apollo's rebuild plan raises that as a kiln follow-up; kiln already pins rn-forge-sqlalchemy-v0.2.0. Adoption waits on it.

Design

create_sqlite_engine lives in rn_forge.sqlalchemy.sqlite, re-exported from the facade, and never creates tables because schema is the migrations' job. run_migrations lives in rn_forge.sqlalchemy.migrate behind the migrations extra and is not re-exported (ADR-0003). It blocks, because Alembic's async env.py template calls asyncio.run(), which cannot run inside a running event loop; an application calls it through asyncio.to_thread from its lifespan. Prior art is Apollo's SQLite session module and its embedded migration runner.

S17.4.1 — Approve the alembic dependency

State: Closed (2026-10-31)

Acceptance: the owner approves alembic as the only dependency of a new rn-forge-sqlalchemy[migrations] extra.

S17.4.2 — Build create_sqlite_engine

State: Closed (2026-10-31)

Acceptance:

  • create_sqlite_engine behaves as under Design, with its tests passing.
  • It is exported from rn_forge.sqlalchemy.
  • uv run pyright and uv run lint-imports pass.

S17.4.3 — Build run_migrations

State: Closed (2026-10-31)

Acceptance:

  • rn_forge.sqlalchemy.migrate.run_migrations behaves as under Design, with its tests passing under the migrations extra.
  • rn_forge.sqlalchemy imports and works without the extra, and migrate is not in its facade.

S17.4.4 — Document both

State: Closed (2026-10-31)

Acceptance: the package's guide covers engine setup and embedded migrations: migrations run from application startup through asyncio.to_thread, not from the engine helper, with the async env.py, render_as_batch and package-resource script_location shown. The package site builds with --strict.

Acceptance

set -euo pipefail
absent() { local rc=0; rg -q --hidden --glob '!.git' "$@" </dev/null || rc=$?; [ "$rc" -eq 1 ]; }
cd packages/rn-forge-sqlalchemy
grep -q '^migrations = \["alembic' pyproject.toml            # S17.4.1 S17.4.3
uv run pytest -q tests                                       # S17.4.2 S17.4.3
absent 'migrate' src/rn_forge/sqlalchemy/__init__.py         # S17.4.3
uv run pyright && uv run lint-imports                        # S17.4.2
uv run --group docs mkdocs build --strict                    # S17.4.4

Considered and rejected

  • A synchronous engine helper. The package is async throughout, and its upsert and keyset take an AsyncSession, so a sync engine could not use them. AsyncEngine.sync_engine is no substitute: it runs on the async driver and raises MissingGreenlet outside SQLAlchemy's greenlet context, so it serves only as an event target.
  • Making run_migrations a coroutine. Alembic's command API is synchronous; a coroutine wrapper would hide the thread hop that the async env.py needs anyway.
  • Creating tables with metadata.create_all. It would bypass the migration history the application relies on.