SQLite

The SQLite adapter stores Better IAM in a single file on local disk through the native better-sqlite3 driver and Kysely. Use it for development, automated tests, and single-host deployments with a persistent disk. When several hosts must share the data, or writes are sustained, use PostgreSQL; when you want SQLite semantics over the network, use libSQL.

Installation

npm i better-iam

import { betterIam } from 'better-iam';
import { sqliteAdapter } from 'better-iam/adapter-sqlite';

export const iam = betterIam({
  database: sqliteAdapter({ filename: './iam.db' }),
  secret: process.env.BETTER_IAM_SECRET!,
  baseURL: process.env.BETTER_IAM_BASE_URL!,
});

npx better-iam migrate --config better-iam.config.mjs

npx better-iam doctor --config better-iam.config.mjs

Usage

Choose durability

Committed IAM writes, such as a revoked session, must survive a crash. The defaults keep them durable:

import { sqliteAdapter } from 'better-iam/adapter-sqlite';

// A database on a network share, where WAL's shared memory is not available.
export const database = sqliteAdapter({
  filename: '/mnt/shared/iam.db',
  journalMode: 'delete',
  durability: 'full',
});

journalMode is ignored for :memory:.

Share one file between processes

Your application, background workers, and CLI commands can open the same file:

• Within one process, the adapter serializes every operation on a file through an in-process lock keyed by the file's canonical path (symbolic links, relative paths, and Windows short names all resolve to one lock, and the lock is shared even when two copies of the adapter package are loaded).
• Across processes, each transaction starts with BEGIN IMMEDIATE, taking SQLite's writer lock before any read. Another process waiting for that lock retries for up to busyTimeoutMs and then fails with STORAGE_BUSY, which can be retried.

Raise busyTimeoutMs when long CLI commands, such as store-export or a large sweep, run beside a busy application. Opening a second adapter on a file inside a transaction that already holds it, for example copying a database onto itself, fails with DATABASE_IN_USE instead of waiting forever.

Configure from environment variables only

The CLI and configFromEnv() from better-iam/cli create a SQLite adapter when BETTER_IAM_DATABASE_URL is sqlite:PATH, a path ending in .db, .sqlite, or .sqlite3, or :memory:. sqlite: with no path opens an in-memory database.

export BETTER_IAM_DATABASE_URL=sqlite:./iam.db
export BETTER_IAM_SECRET=paste-a-generated-secret
npx better-iam migrate

better-iam init --database sqlite writes a configuration whose adapter reads BETTER_IAM_DATABASE, falling back to ./better-iam.db.

Close the database

iam.store.close() releases the file. Close it at the end of scripts and tests, especially on Windows, where an open handle prevents deleting the file.

import { iam } from '../iam';

try {
  console.log(await iam.store.describe?.());
} finally {
  await iam.store.close();
}

Options

Invalid values throw INVALID_CONFIG when the adapter is created.

Performance

• Prepared statements are cached. The adapter reuses up to 256 prepared statements by SQL text. SQLite's compile time grows with the number of indexes a write maintains, so caching keeps inserts cheap.
• Lookups use partial expression indexes, one per field in INDEXED_FIELDS, so a session token lookup stays fast as the sessions collection grows. See Schema.
• One writer at a time. SQLite has a single writer per file. For sustained concurrent writes from several hosts, move to PostgreSQL with store-copy.

SQLite is not suited to serverless hosts or containers without a persistent volume: the file must survive restarts and every instance must see the same file on the same host.

Testing

:memory: gives each adapter its own private database that disappears when it is closed, which makes it the fastest way to test code that uses Better IAM. Two adapters created with :memory: never share data, so create one per test (or per test file) and close it afterwards.

import { afterEach, beforeEach, expect, it } from 'vitest';
import { betterIam, type BetterIam } from 'better-iam';
import { sqliteAdapter } from 'better-iam/adapter-sqlite';

let iam: BetterIam;

beforeEach(async () => {
  iam = betterIam({
    database: sqliteAdapter({ filename: ':memory:' }),
    secret: 'test-only-secret-with-at-least-32-characters',
    baseURL: 'http://localhost:3000',
  });
  await iam.initialize(); // creates the schema in the new in-memory database
});

afterEach(async () => {
  await iam.store.close();
});

it('creates the root tenant once', async () => {
  const { tenant } = await iam.bootstrap({
    email: 'root@example.com',
    name: 'Root',
    password: 'a long root password',
  });
  expect(tenant.type).toBe('root');
  await expect(
    iam.bootstrap({ email: 'other@example.com', name: 'Other', password: 'another long password' }),
  ).rejects.toMatchObject({ code: 'ALREADY_INITIALIZED' });
});

The SQLite, libSQL, and PostgreSQL adapters pass the same conformance suite (@better-iam/core/conformance), so behavior you test on :memory: holds on PostgreSQL. For more patterns, see Test your integration.

Errors

Next steps