Skip to main content
theAuth has two separate storage decisions, and people often mix them up.
  1. The database holds users, sessions, agents, delegations and the audit log. It has to be durable. You choose it with database.provider.
  2. Secondary storage holds small, short-lived, high-churn values: rate-limit counters, device login codes. It only needs fast reads, writes and an atomic counter. You choose it with secondaryStorage.
The default for the second one is process memory, which is correct on a laptop and quietly wrong almost everywhere else. Most of the “it works locally” problems in Troubleshooting come from that default.

Start from where you deploy

The database, in one paragraph each

sqlite (sql.js). SQLite compiled to WebAssembly. No native build, runs on Node, Bun, Deno and edge runtimes. The whole database lives in memory and is never written to disk, so it only accepts url: ":memory:". A file URL throws THEAUTH_SQLITE_NOT_PERSISTENT at startup instead of silently losing your data on restart. Good for tests, CI and throwaway demos. For a database file use sqlite-native. sqlite-native (better-sqlite3). Real SQLite on disk, fast, one process at a time in practice. Needs a native build (install better-sqlite3 yourself). A good fit for a single VPS where you want zero database operations. Back up the file. postgres. The default recommendation for anything with more than one instance or any real traffic. Needs the pg package. Use a pooler on serverless. mysql. Not supported in this release. provider: "mysql" throws THEAUTH_MYSQL_UNSUPPORTED unless you pass experimental: true. See What is tested per database. libsql. SQLite served by libSQL: Turso, or a local file: URL. Needs @libsql/client. See Turso, Neon and Supabase. d1. Cloudflare D1 through a Worker binding. Use it when the app lives on Workers. Pass the binding instead of a URL. Setup snippets for each are on Database setup.

What is tested per database

All providers share one versioned schema (theauth_schema_migrations, see Database setup). What the repository tests on each:
Postgres works through the pg driver. We run the full test suite against Postgres 14 by hand before each release; it is not in CI yet. Timestamps are stored as integer epoch seconds, the same as SQLite, so use to_timestamp(created_at) when you query the tables yourself. The store contract also ran behind a transaction mode pooler (PgBouncer) and, through a bring your own handle, on Neon’s WebSocket driver against a local Postgres (see Turso, Neon and Supabase). Other Postgres drivers (postgres.js, PGlite) are not supported yet. MySQL is not supported in this release. provider: "mysql" throws unless you pass experimental: true, and even then the baseline schema does not apply on a real server. We will turn it on once the same suite passes on MySQL 8.4.

SQL Server, and why there is no kysely option yet

You cannot run theAuth on SQL Server today, and there is no database: { kysely, dialect } config. The reason is narrow. Core still talks to one Drizzle handle in about 700 places. Only two parts go through a store layer that another backend can implement: the single-use state (magic links, OTP guesses, refresh token claims, approvals, enrollment uses, the hourly budget) and the audit chain write path. Drizzle has no SQL Server driver, so an instance cannot boot on it, and a config option that accepted a Kysely handle but still needed a second, Drizzle-compatible connection to the same database would only mislead. What exists is internal and tested. A Kysely kernel (packages/core/src/db/store/kysely) implements the store contract for SQLite, Postgres, MySQL and SQL Server: conditional claims decided by the affected row count, guarded increments, atomic batches, interactive transactions and typed unique conflicts. A generator (packages/core/src/db/ddl/mssql.ts) writes SQL Server DDL for every core table from the Drizzle schema plus the indexes the migrations declare. Both are meant for the remaining aggregate ports, after which the option can be exposed. What was run, on this repository’s contract suites: The kernel contract covers each kernel primitive, value round trips, the single-use repositories under 24 parallel callers, and the audit repository: one test per hashed audit field (the hash is recomputed from what the backend returns), a gapless chain from parallel writers, and one chain from two processes. The instance contract is the full runStoreContract suite with the Kysely kernel taking over the single-use and audit writes while Drizzle serves everything else on the same database. Limits you should know about:
  • SQL Server DDL is generated for the current schema only. There is no migration ledger for it, so an existing table is never altered. Rerunning the statements is safe.
  • Text columns use a binary collation (Latin1_General_100_BIN2) because the server default ignores case. A text column that is part of an index has a bounded length: 191 characters for ids, and what is left of the 1700 byte index limit for other columns (850 characters at most). Values longer than that fail to insert.
  • A unique index over a nullable column is created as a filtered index (WHERE column IS NOT NULL), because SQL Server treats NULLs as equal in a unique index.
  • Trailing spaces are ignored in comparisons on SQL Server and on MySQL with the binary collation. Two tokens that differ only by trailing spaces collide there.
  • Reads and claims must go to the primary. A Kysely setup that routes reads to a replica breaks claim then read flows such as refresh token reuse detection.
  • The Azure SQL Edge image used for the run was retired by Microsoft in 2025. It was the one that runs on Apple silicon. The generated statements stay with features from SQL Server 2016 on (filtered indexes, OFFSET and FETCH, 1700 byte index keys), but only the SQL Server 2019 engine in the Edge image was run.

Secondary storage, in one paragraph each

The "database" backend creates one table, theauth_secondary_storage, and increments counters with a single guarded UPDATE, so it is exact on every supported database. Expired rows are removed lazily; run databaseStorage(db).purgeExpired() from a cron if you have a large keyspace. You can choose per feature. A common production setup on Workers is exact storage for device codes and soft storage for rate limits:
More detail, including the interface for a custom store, is on Secondary storage.

A few rules of thumb

  • If you are unsure, use Postgres plus secondaryStorage: 'database'. It has one moving part and works on every deployment shape except Workers.
  • Do not use memory for anything you would be upset to lose or to count twice: device codes and rate limits on a deployment with more than one instance.
  • Do not put the main database on KV. KV is only an option for secondary storage.
  • Moving from sqlite to Postgres later is a data copy. The table names are the same (theauth_*), so plan a one-off export and import, and run createTheAuth against the new database first so the tables exist.
  • The Prisma adapter reads and writes theAuth tables from code that already uses Prisma. It does not replace the choice above. See Prisma.
Using the Go library instead? Its backends are different, see Storage backends and Shared state for several replicas.

Database setup

Connection snippets for every provider.

Secondary storage

Interface, adapters and per-feature overrides.

Production checklist

Check storage and everything else before launch.

Troubleshooting

Serverless and edge gotchas.
Last modified on October 19, 2026