SQLiteSupported

@zmdb/sqlite is the official SQLite vertical. The default @zmdb/core application includes it through @zmdb/core/sqlite; independently installed consumers can select @zmdb/sqlite directly.

Database-selection workflow#

The six official database packages use the same selection workflow. The package reference owns current install and peer ranges; the SQLite package README includes the standalone TypeScript setup and full capability table.

A hosted-service connection guide is a recipe using one of these owners or an explicitly supplied structural adapter. Protocol compatibility alone does not create another official package or transfer the recorded server qualification to that service.

Selecting it#

import { sqlite, sqliteDriver } from '@zmdb/sqlite';

const compiler = createQueryCompiler(sqlite);
const userRepo = defineRepository(users, sqliteDriver(db));

What it emits#

Column-specific value conversion#

Use the official adapter for node:sqlite execution, transaction pinning and statement handling:

import { DatabaseSync } from 'node:sqlite';
import { sqliteDriver } from '@zmdb/sqlite';

const db = new DatabaseSync('app.db');
export const driver = sqliteDriver(db);

SQLite stores booleans as integers, JSON as text and timestamps using the declared storage representation. Put any application-specific conversion beside the column through a custom type with toDb / fromDb; do not replace the official execution adapter with a SELECT-prefix parser. Conversion must be per column: changing every 0 or 1 to a boolean also changes ordinary integer data. The application closes db after its work.

Connection pragmas#

PRAGMA foreign_keys = ON;   -- off by default, per connection
PRAGMA journal_mode = WAL;  -- concurrent readers with a writer
PRAGMA busy_timeout = 5000; -- wait for the write lock instead of failing

foreign_keys being off by default is the one that bites: the constraint exists in your migration, and nothing enforces it. sqliteDriver(db) enables it when the adapter wraps the connection. A custom driver must still set it on every connection itself. WAL mode and the busy timeout remain application choices.

Types are advisory#

SQLite's declared column types are affinities, not constraints — a TEXT column will accept an integer. That means the database will not catch a type error the way Postgres would, so the type-level guarantees and the validators are doing more of the work here. It is a reason to validate rows coming from a SQLite database you did not write.

One writer#

SQLite serialises writes at the database level. WAL mode lets readers proceed during a write; contending writers must wait or handle SQLITE_BUSY. Choose the connection and deployment model around that locking behavior.

Why it is the best test database#

import { DatabaseSync } from 'node:sqlite';
import { diff, snapshot } from '@zmdb/migrations';
import { sqlite, sqliteDriver } from '@zmdb/sqlite';

export function freshDb() {
  const db = new DatabaseSync(':memory:');
  sqliteDriver(db);
  const before = { version: 1, tables: [], extensions: [] };
  const after = snapshot(allSchemas);
  const operations = diff(before, after, { dialect: sqlite });
  for (const operation of operations) db.exec(sqlite.migrations.emitUp(operation));
  return db;
}

node:sqlite is a built-in, so this adds no client dependency; separate :memory: connections give isolated databases. See Testing.

Connecting#

Local SQLite, Turso, SQLite Cloud, Cloudflare D1, Durable Objects, Bun, React Native.

---

See also: Connect: SQLite · Testing · Custom Types