> ## Documentation Index
> Fetch the complete documentation index at: https://docs.theauth.dev/llms.txt
> Use this file to discover all available pages before exploring further.
# Database setup
> Configure theAuth with SQLite, native SQLite, Postgres, MySQL, or Cloudflare D1. Covers connection setup, migration, WAL mode for native SQLite, and skipping auto-migrations.
theAuth uses [Drizzle ORM](https://orm.drizzle.team) under the hood. You pick a provider and pass the connection URL; theAuth handles the rest.
## Choosing a provider
| Provider | Best for |
| - | - |
| `sqlite` | Local dev and small single-process deploys. Runs on `sql.js` (SQLite compiled to WebAssembly), no native build step |
| `sqlite-native` | Node.js servers that want the fastest SQLite. Uses `better-sqlite3`, which needs a native build |
| `postgres` | Production, high-concurrency, multi-tenant |
| `mysql` | Existing MySQL infrastructure |
| `d1` | Cloudflare Workers, using a D1 binding |
## Setup
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.
```typescript theme={"dark"}
import { createTheAuth } from '@glinr/theauth';
const theauth = await createTheAuth({
database: {
provider: 'sqlite',
url: './theauth.db',
},
});
```
For in-memory SQLite (tests and CI), use `:memory:` as the URL:
```typescript theme={"dark"}
const theauth = await createTheAuth({
database: {
provider: 'sqlite',
url: ':memory:',
},
});
```
`sql.js` keeps the database in memory. For a file path, theAuth loads the file at startup if it exists and rewrites the whole file after each write statement. That is fine for development and small apps, but it is a single-process setup: do not point several processes at the same file. theAuth turns on foreign keys (`PRAGMA foreign_keys = ON`). It does not enable WAL mode for this provider.
The `sqlite-native` provider uses `better-sqlite3`. Install it yourself, it is an optional peer dependency and needs C++ build tools:
```bash theme={"dark"}
npm install better-sqlite3
```
```typescript theme={"dark"}
import { createTheAuth } from '@glinr/theauth';
const theauth = await createTheAuth({
database: {
provider: 'sqlite-native',
url: './theauth.db',
},
});
```
theAuth enables WAL mode and foreign keys automatically for this provider:
```sql theme={"dark"}
PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;
```
Install the `pg` peer dependency:
```bash theme={"dark"}
npm install pg
npm install --save-dev @types/pg
```
```typescript theme={"dark"}
import { createTheAuth } from '@glinr/theauth';
const theauth = await createTheAuth({
database: {
provider: 'postgres',
url: process.env.DATABASE_URL!,
// postgresql://user:password@host:5432/dbname
},
});
```
theAuth uses `drizzle-orm/node-postgres` with a connection pool via `pg.Pool`. The `pg` package is loaded with a dynamic import, so it stays an optional peer dep.
Install the `mysql2` peer dependency:
```bash theme={"dark"}
npm install mysql2
```
```typescript theme={"dark"}
import { createTheAuth } from '@glinr/theauth';
const theauth = await createTheAuth({
database: {
provider: 'mysql',
url: process.env.DATABASE_URL!,
// mysql://user:password@host:3306/dbname
},
});
```
theAuth uses `drizzle-orm/mysql2` with a connection pool. `mysql2` is loaded via dynamic import and stays an optional peer dep.
Pass the D1 binding from your Worker environment. The `drizzle-orm/d1` driver is loaded with a dynamic import.
```typescript theme={"dark"}
import { createTheAuth } from '@glinr/theauth';
export default {
async fetch(request: Request, env: { DB: D1Database }) {
const theauth = await createTheAuth({
database: {
provider: 'd1',
binding: env.DB,
},
});
// ...
},
};
```
The D1 provider takes a `binding` instead of a `url`. Set `skipMigrations: true` if you create the tables yourself.
## Auto-migration
By default, theAuth calls `CREATE TABLE IF NOT EXISTS` at startup for the tables your config needs. This is safe to run on every start, but it only creates missing tables, it does not alter existing ones. There is no manual migration step for a new project.
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:
| Config | Tables created |
| - | - |
| `agents` (or `did`) | agents, permissions, delegation chains, agent cards, approval requests, trust scores, registration tokens, DIDs, ephemeral sessions, tenants, audit logs, cost and stream events, rate limits, budget policies, ReBAC |
| `auth.session` | sessions, trusted devices, login history, JWT refresh tokens and token families |
| `mcp` | MCP servers |
| `oauth()` plugin | OAuth accounts and states, plus OAuth client, token and code tables |
| `magicLink`, `emailOtp`, `totp`, `passkey`, `username`, `phone`, `org`, `sso`, `apiKeys` | The matching table(s), whether you use the config key or the plugin form (for example `plugins: [magicLink(...)]`) |
| `passwordReset`, `magicLink`, `emailOtp` | One-time tokens |
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`:
```typescript theme={"dark"}
database: {
provider: 'postgres',
url: process.env.DATABASE_URL!,
skipMigrations: true,
},
```
When `skipMigrations: true`, you are responsible for keeping the schema in sync. theAuth will fail at runtime if expected tables or columns are missing.
## Schema overview
The full set of tables theAuth can create is below. Which ones exist in your database depends on your config, see above.
| Table | Purpose |
| - | - |
| `theauth_secondary_storage` | Key/value rows used when `secondaryStorage: 'database'` (counters, device codes) |
| `theauth_users` | Human user identities (also holds ban, Stripe and Polar billing fields) |
| `theauth_tenants` | Multi-tenant isolation |
| `theauth_agents` | AI agent identities (the core entity) |
| `theauth_permissions` | Per-agent resource+action permissions with constraints |
| `theauth_delegation_chains` | Agent-to-agent delegation records |
| `theauth_audit_logs` | Log of every agent action |
| `theauth_rate_limits` | Per-agent call-rate counters |
| `theauth_mcp_servers` | Registered MCP servers |
| `theauth_sessions` | theAuth-managed human user sessions |
| `theauth_oauth_clients`, `theauth_oauth_access_tokens`, `theauth_oauth_authorization_codes` | OAuth 2.1 client registrations, issued tokens, and short-lived PKCE codes |
| `theauth_agent_cards` | A2A capability discovery cards |
| `theauth_agent_registration_tokens` | One-time tokens for [agent registration](/agent-registration-tokens) |
| `theauth_oauth_accounts`, `theauth_oauth_states` | Linked social accounts and in-flight OAuth state (`oauth()` plugin) |
| `theauth_approval_requests` | CIBA async approval flow records |
| `theauth_trust_scores` | Graduated autonomy trust scores per agent |
| `theauth_budget_policies` | Token and call budget caps per agent/user/tenant |
| `theauth_magic_links`, `theauth_email_otps`, `theauth_phone_verifications`, `theauth_totp` | Magic link, email OTP, phone OTP, and TOTP records |
| `theauth_username_accounts`, `theauth_passkey_credentials`, `theauth_passkey_challenges` | Username/password and passkey records |
| `theauth_organizations`, `theauth_org_members`, `theauth_org_invitations`, `theauth_org_roles`, `theauth_sso_connections`, `theauth_api_keys` | Organizations, SSO connections, and API keys |
| `theauth_trusted_devices`, `theauth_one_time_tokens`, `theauth_login_history`, `theauth_jwt_refresh_tokens`, `theauth_refresh_tokens`, `theauth_refresh_token_families` | Trusted devices, one-time tokens, login history, and refresh tokens |
| `theauth_ephemeral_sessions`, `theauth_agent_dids`, `theauth_cost_events`, `theauth_stream_events` | Ephemeral sessions, agent DIDs, cost attribution, and event streaming |
| `theauth_oidc_clients`, `theauth_oidc_auth_codes`, `theauth_oidc_refresh_tokens` | OIDC provider records |
| `theauth_rebac_resources`, `theauth_rebac_relationships` | ReBAC graph |
| `theauth_federation_instances`, `theauth_federation_tokens` | Federation records |
All table and column names use `snake_case`. IDs are `text`. In the SQLite schema, timestamps are stored as integers.
## Peer dependencies
| Provider | Required package |
| - | - |
| `sqlite` | `sql.js` (installed with core) |
| `sqlite-native` | `better-sqlite3` |
| `postgres` | `pg` |
| `mysql` | `mysql2` |
| `d1` | `drizzle-orm/d1` (a Cloudflare Workers binding, no extra install) |
theAuth uses dynamic imports for the SQLite, Postgres, and MySQL drivers so they remain optional. You will get a clear error message at startup if the required package is missing, for example:
```
TheAuth: provider "postgres" requires the "pg" package.
Install it with: npm install pg
```
## Testing with in-memory SQLite
Use `:memory:` for fast, isolated tests that need no setup or teardown:
```typescript theme={"dark"}
import { createTheAuth } from '@glinr/theauth';
import { describe, beforeEach, it } from 'vitest';
let theauth: Awaited>;
beforeEach(async () => {
theauth = await createTheAuth({
database: { provider: 'sqlite', url: ':memory:' },
agents: { enabled: true },
});
});
it('creates an agent', async () => {
const agent = await theauth.agent.create({
ownerId: 'user_1',
name: 'Test Agent',
type: 'autonomous',
permissions: [],
});
expect(agent.id).toBeDefined();
});
```
Each `createTheAuth()` call with `:memory:` gets a completely isolated database, so tests never share state.
## Related
Read theAuth tables through an existing PrismaClient.
Full createTheAuth() options including database and secrets.
In-memory mock server and factories for auth-dependent tests.
Provision agents and permissions as infrastructure-as-code.
This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.