Choosing a provider
Setup
- SQLite
- SQLite (native)
- Postgres
- MySQL
- Cloudflare D1
The
sqlite provider uses sql.js (SQLite compiled to WebAssembly), which ships with @glinr/theauth. It needs no native build and runs on Node.js, Bun, Deno, and edge runtimes. It is in-memory only: it never writes a database file, so use it for tests, CI and throwaway demos. For a database file that survives a restart, use the SQLite (native) tab (sqlite-native) or Postgres.For in-memory SQLite (tests and CI), use :memory: as the URL:sql.js keeps the database in memory and nothing is saved when the process exits. Passing a file path (for example url: './theauth.db') throws THEAUTH_SQLITE_NOT_PERSISTENT at startup, because the file would never be written and your data would disappear on restart. theAuth turns on foreign keys (PRAGMA foreign_keys = ON).Auto-migration
By default, theAuth migrates the database at startup: it creates the tables your config needs (CREATE TABLE IF NOT EXISTS) and applies any pending versioned migration, recording each one in the theauth_schema_migrations ledger (see Schema migrations). This is safe to run on every start, and there is no manual migration step for a new project or an upgrade.
Tables are created per feature, so a small config gives you a small schema. Users and the secondary storage table are always created. Everything else depends on the config:
If you add a feature later, restart once and the new tables appear. Add
agents: { enabled: true } if you use agents, since agent tables are not created without it. The @glinr/theauth-email plugin creates its own theauth_email_accounts table.
To disable this (e.g. when you manage migrations externally with Flyway, Liquibase, or drizzle-kit push), set skipMigrations: true:
Schema migrations
theAuth keeps a ledger table,theauth_schema_migrations, with one row per applied migration: id, name, checksum and applied_at. Migrations are numbered and append only:
On startup theAuth runs every migration that is not in the ledger yet, in order, and records it. Rules that make this safe on an existing database:
- A database from before the ledger is adopted, not rebuilt. If the tables exist but the ledger does not, theAuth probes the columns, treats
0000(and0001, when its columns are present) as already applied, records them, and runs the rest. Your rows are untouched. - Reruns are no-ops. Applied migrations are skipped. Statements tolerate “already exists”, so a half finished upgrade can be resumed.
- Applied migrations cannot change. The ledger stores a checksum of each migration’s SQL. If it no longer matches the code, startup fails with
SchemaChecksumErrorrather than guessing. - Features still decide which tables exist. A migration that touches the audit table only applies when your config creates it. Enable the feature later and the next start creates the table and applies the migrations that were waiting.
- The audit hash does not change.
decision_code,recorded_at_msandeventare not part of the hashed payload, so existing chains keep verifying.
--db <url> (or DATABASE_URL) picks the database; --config <file> loads your config module so only the tables your features need are considered. Add --json for machine readable output.
Using skipMigrations
WithskipMigrations: true theAuth never changes the schema, but it still reads the ledger and throws SchemaOutdatedError when migrations are pending. To keep the schema in step with the SDK:
- Generate the SQL for your dialect:
theauth db sql --dialect postgres > theauth-schema.sql. On a database that already has the 1.x tables, start at the first pending id instead, for example--from 0002. That output first creates the ledger and stamps the earlier migrations as applied. - Review it and run it with your own tooling (Flyway, Liquibase,
psql). Each migration ends with anINSERTinto the ledger, so theAuth sees it as applied. - Deploy the new SDK version. If anything is still pending the process fails at startup and names the ids.
packages/core/src/db/migrations/sql/<dialect>/<id>.sql. A test fails when they drift from the TypeScript migrations.
Schema overview
The full set of tables theAuth can create is below. Which ones exist in your database depends on your config, see above.Upgrading the audit table
Migrations0002 and 0003 add decision_code TEXT (with the index theauth_audit_logs_code) and recorded_at_ms (INTEGER on SQLite, BIGINT on Postgres, with the index theauth_audit_logs_order). Old rows keep a NULL code, and recorded_at_ms is backfilled from the second-resolution timestamp. Audit reads order by recorded_at_ms, so rows written in the same second keep their write order. Migration 0005 adds a nullable event TEXT column (with the index theauth_audit_logs_event). It backfills event from action for rows whose action is itself an event name (delegation.create, delegation.revoke, agent.cascade.revoked, approval.*, vault.read, audit.*), marks policy engine rows policy.evaluate, marks the remaining rows that have a decision code authorize, and leaves every other row NULL. None of these columns is covered by the audit hash, so existing hash chains keep verifying. If you use skipMigrations, see Using skipMigrations.
All table and column names use snake_case. IDs are text. Timestamps are integer epoch seconds on both SQLite and Postgres.
Peer dependencies
theAuth uses dynamic imports for the SQLite and Postgres drivers so they remain optional. You will get a clear error message at startup if the required package is missing, for example:
Testing with in-memory SQLite
Use:memory: for fast, isolated tests that need no setup or teardown:
createTheAuth() call with :memory: gets a completely isolated database, so tests never share state.
Related
Prisma adapter
Read theAuth tables through an existing PrismaClient.
Configuration
Full createTheAuth() options including database and secrets.
Test utilities
In-memory mock server and factories for auth-dependent tests.
Terraform
Provision agents and permissions as infrastructure-as-code.