Data access
How business code reaches the database: repositories only, one contract over SQLite, D1 and Postgres, what a transaction means on each, and why migrations are kept per dialect.
Kernlift runs on SQLite, Cloudflare D1 and Postgres from the same business code. All database
access goes through src/server/db/repositories/*, and everything that
differs between databases stays in the schema, migration and adapter layers underneath. To switch
databases, see Use Postgres.
Repositories are the only door
Server functions, services and API routes call repository functions. They do not import Drizzle
tables, write SQL or branch on a dialect. A repository gets its database through
withRepositoryDialect, which passes it three values:
| Value | What it is |
|---|---|
db | The Drizzle database of the active adapter |
schema | The table definitions for that database: schema/postgres.ts or schema/sqlite.ts |
dialect | "postgres", "sqlite" or "d1" |
Both schema files define the same tables with the same names and columns, so a repository writes one
query against schema.billingOrders and it runs on either. Where a column's storage differs, most
often a timestamp, the repository converts at that one point by checking dialect. Agent tables
follow the same pattern in agent-sqlite.ts and agent-postgres.ts.
Two schema families, five adapters
The adapter determines how the app connects. The schema family determines the SQL it runs.
| Adapter | Schema family | Runs on | Connection |
|---|---|---|---|
sqlite | SQLite | Node | A local file through better-sqlite3 |
turso | SQLite | Node | libSQL with DATABASE_URL and DATABASE_AUTH_TOKEN |
d1 | SQLite | Cloudflare Workers | The EDGE_DB binding |
postgres | Postgres | Node | DATABASE_URL, one client per process |
hyperdrive | Postgres | Cloudflare Workers | The HYPERDRIVE binding, one client per request |
DATABASE_ADAPTER selects one and defaults to DATABASE_DIALECT. You can register your own for
either family. See Write a custom database adapter.
A Worker may not reuse a socket that was opened while handling another request. The hyperdrive
adapter therefore creates its Postgres client inside a per-request scope. The Worker entry wraps each
request and each scheduled event in runInRequestScope, and the client lives as long as that scope.
Hyperdrive pools the real connections behind it. On Node there is no scope and the postgres
adapter keeps one client.
What a transaction means on each database
Repositories are written once, with async transaction callbacks:
db.transaction(async (tx) => { ... }). What happens underneath depends on the database.
| Database | Behaviour |
|---|---|
| Postgres, Hyperdrive, Turso | The driver runs the callback as an interactive transaction |
SQLite (better-sqlite3) | The driver is synchronous and rejects an async callback, so the repository facade runs it as a real transaction on the same connection: BEGIN IMMEDIATE, then COMMIT, or ROLLBACK with the original error. Transactions in one process run one at a time, in order, and a nested call joins the open one |
| D1 | No interactive transactions. The repository context reports d1, and code that would open a transaction runs the same statements one by one instead |
On SQLite a transaction callback should do database work only and stay short, because other queued work in the process can run between its statements. On D1 a multi-step write is not atomic, so it must be safe to repeat or to stop halfway. For the same reason the Agent, which needs transactions for its runs, does not use D1.
Where one statement is enough, the code uses one. The scheduled-jobs lock is a single conditional update and the fulfilment claim is a single insert under a unique key, so both behave the same on all three databases.
Migrations are kept per dialect
There are two migration chains, one per schema family, with the same file names:
| Chain | Used by | Applied with |
|---|---|---|
drizzle/migrations/sqlite | SQLite, Turso, D1 | pnpm db:migrate; pnpm db:migrate:d1:local and pnpm db:migrate:d1:remote for D1 |
drizzle/migrations/postgres | Postgres, Hyperdrive | pnpm db:migrate, or pnpm db:migrate:runtime in a production image |
The chains are maintained by hand. A schema change means editing both schema files and adding a
migration with the same name to both chains. pnpm db:generate:sqlite and
pnpm db:generate:postgres produce a first draft for each. pnpm validate:migrations then checks
that the two chains agree: the journals and the files match, each chain replays onto an empty database, each
--> statement-breakpoint segment holds one statement, and both dialects end with the same tables.
pnpm db:migrate wraps the migration tool to report the one state it otherwise fails on silently:
a database whose tables exist but whose migration bookkeeping is empty, because something other than
the chain created it.
Files
Three validators cover this layer: validate:db-adapters (the registry, the bindings and the server
entry), validate:migrations (the two chains) and validate:sqlite-transactions (commit, rollback,
ordering and nesting on the default SQLite adapter).