typed-sql

Appearance

SQLite example ​

The SQLite example uses the same structural query primitives as PostgreSQL and MySQL. It needs no container or third-party driver because the explicit adapter targets Node's built-in node:sqlite module.

AreaDemonstrated behavior
Read and writeDynamic queries, CTEs, cardinality helpers, inserts, updates, deletes, transactions
Reuse and batchingPrepared queries and a small transactional insert batch
Large resultsAsync iteration over the adapter's serialized statement queue
Validation and capability discoveryStandard Schema result validation and explicit unsupported capability flags

Define the database ​

The setup script recreates a local database from this strict SQLite schema:

sql
PRAGMA foreign_keys = ON;

CREATE TABLE account (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  status TEXT NOT NULL CHECK (status IN ('active', 'suspended'))
) STRICT;

CREATE TABLE project (
  id INTEGER PRIMARY KEY,
  owner_id INTEGER NOT NULL REFERENCES account(id),
  name TEXT NOT NULL,
  budget REAL
) STRICT;

INSERT INTO account (id, email, status) VALUES
  (1, 'alice@example.com', 'active'),
  (2, 'bob@example.com', 'suspended');

INSERT INTO project (id, owner_id, name, budget) VALUES
  (1, 1, 'Compiler', 12500.50);

The config selects the SQLite grammar, local schema provider, generated output, and matching runtime type policy:

ts
import { defineConfig } from "@typed-sql/core";
import { sqlite, typePolicy } from "@typed-sql/sqlite";
import { nodeSqlite } from "@typed-sql/sqlite/node-sqlite";

export const databasePath = new URL("./example.sqlite", import.meta.url);

export default defineConfig({
  dialect: sqlite({ typePolicy }),
  schema: {
    file: "generated/db/schema.json",
    provider: nodeSqlite({ path: databasePath, typePolicy }),
  },
  outDir: "generated/db",
  projects: ["tsconfig.json"],
  typePolicy,
});

Compose native SQL ​

SQLite's grammar analyzes the complete query after all conditional fragments are composed:

ts
import type { QueryParameters, QueryRow } from "@typed-sql/core";
import { sql } from "@typed-sql/sqlite";

export interface AccountFilters {
  readonly status?: string | null;
  readonly minimumId?: bigint | null;
}

export interface AccountSelect {
  readonly status: boolean;
  readonly projectBudget: boolean;
}

export function accounts<const Select extends AccountSelect>(filters: AccountFilters, select: Select) {
  return sql`
    SELECT
      account.id,
      account.email
      ${select.status ? sql.fragment`, account.status` : sql.empty}
      ${select.projectBudget ? sql.fragment`, project.budget` : sql.empty}
    FROM account
      ${select.projectBudget ? sql.fragment`LEFT JOIN project ON project.owner_id = account.id` : sql.empty}
    WHERE 1 = 1
      ${filters.status == null ? sql.empty : sql.fragment`AND account.status = ${filters.status}`}
      ${filters.minimumId == null ? sql.empty : sql.fragment`AND account.id >= ${filters.minimumId}`}
    ORDER BY account.id
  `;
}

export const activeAccounts = accounts({ status: "active", minimumId: 1n }, { status: true, projectBudget: true });

export const accountById = (accountId: bigint) => sql`
  SELECT account.id, account.email, account.status
  FROM account
  WHERE account.id = ${accountId}
`;

export const projectsByOwner = (ownerId: bigint) => sql`
  SELECT project.id, project.owner_id, project.name, project.budget
  FROM project
  WHERE project.owner_id = ${ownerId}
  ORDER BY project.id
`;

export const accountProjectSummary = sql`
  WITH project_totals AS (
    SELECT
      project.owner_id,
      COUNT(*) AS project_count,
      SUM(project.budget) AS total_budget
    FROM project
    GROUP BY project.owner_id
  )
  SELECT
    account.id,
    account.email,
    account.status,
    project_totals.project_count,
    project_totals.total_budget
  FROM account
  LEFT JOIN project_totals ON project_totals.owner_id = account.id
  ORDER BY account.id
`;

export type ActiveAccount = QueryRow<typeof activeAccounts>;
export type ActiveAccountParameters = QueryParameters<typeof activeAccounts>;

SQLite cannot derive an enum from a CHECK constraint, so status remains string. The selected budget column is number | null under this type policy.

Mutations and cardinality ​

ts
import { sql } from "@typed-sql/sqlite";

export interface NewAccount {
  readonly id: bigint;
  readonly email: string;
  readonly status: string;
}

export interface NewProject {
  readonly id: bigint;
  readonly ownerId: bigint;
  readonly name: string;
  readonly budget: number | null;
}

export const insertAccount = (account: NewAccount) => sql`
  INSERT INTO account (id, email, status)
  VALUES (${account.id}, ${account.email}, ${account.status})
  RETURNING id, email, status
`;

export const updateAccountStatus = (accountId: bigint, status: string) => sql`
  UPDATE account
  SET status = ${status}
  WHERE id = ${accountId}
  RETURNING id, email, status
`;

export const deleteAccount = (accountId: bigint) => sql`
  DELETE FROM account
  WHERE id = ${accountId}
  RETURNING id, email, status
`;

export const insertProject = (project: NewProject) => sql`
  INSERT INTO project (id, owner_id, name, budget)
  VALUES (${project.id}, ${project.ownerId}, ${project.name}, ${project.budget})
  RETURNING id, owner_id, name, budget
`;

export const deleteProjectsByOwner = (ownerId: bigint) => sql`
  DELETE FROM project
  WHERE owner_id = ${ownerId}
`;
ts
import type { SqliteDatabase } from "@typed-sql/sqlite/runtime";
import { accountById, activeAccounts } from "./queries.js";

export const listActiveAccounts = (database: SqliteDatabase) => database.all(activeAccounts);

export const requireAccount = (database: SqliteDatabase, accountId: bigint) => database.one(accountById(accountId));

export const findAccount = (database: SqliteDatabase, accountId: bigint) => database.maybeOne(accountById(accountId));
ts
import type { SqliteDatabase } from "@typed-sql/sqlite/runtime";
import { insertAccount, insertProject, type NewAccount, type NewProject } from "./mutations.js";

export function createAccountWithProject(
  database: SqliteDatabase,
  account: NewAccount,
  project: Omit<NewProject, "ownerId">,
) {
  return database.transaction(async (transaction) => {
    const insertedAccount = await transaction.one(insertAccount(account));
    const insertedProject = await transaction.one(insertProject({ ...project, ownerId: account.id }));
    return { account: insertedAccount, project: insertedProject };
  });
}

Prepared, batched, and streamed work ​

ts
import type { SqliteDatabase } from "@typed-sql/sqlite/runtime";
import { accountById, projectsByOwner } from "./queries.js";

export function prepareAccountQueries(database: SqliteDatabase) {
  return Object.freeze({
    accountById: database.prepare("example-account-by-id", accountById),
    projectsByOwner: database.prepare("example-projects-by-owner", projectsByOwner),
  });
}

SQLite does not pretend to offer a server bulk protocol. Small input sets can still use a transaction batch:

ts
import type { SqliteDatabase } from "@typed-sql/sqlite/runtime";
import { insertAccount, type NewAccount } from "./mutations.js";
import { accountById, projectsByOwner } from "./queries.js";

export async function loadAccountWorkspace(database: SqliteDatabase, accountId: bigint) {
  const [accounts, projects] = await database.batch([accountById(accountId), projectsByOwner(accountId)]);
  return { account: accounts[0], projects };
}

/**
 * SQLite has no typed-sql native bulk protocol. A small batch can still share one explicit
 * transaction; large ingestion should use an application-specific native strategy.
 */
export function insertSmallAccountBatch(database: SqliteDatabase, accounts: readonly NewAccount[]) {
  return database.transaction((transaction) => transaction.batch(accounts.map(insertAccount)));
}
ts
import type { QueryRow } from "@typed-sql/core";
import type { SqliteDatabase } from "@typed-sql/sqlite/runtime";
import { activeAccounts } from "./queries.js";

export function streamActiveAccounts(database: SqliteDatabase, batchSize = 100) {
  return database.stream(activeAccounts, { batchSize });
}

export async function collectActiveAccounts(database: SqliteDatabase) {
  const accounts: QueryRow<typeof activeAccounts>[] = [];
  for await (const account of streamActiveAccounts(database, 25)) accounts.push(account);
  return accounts;
}

export async function firstActiveAccount(database: SqliteDatabase) {
  for await (const account of streamActiveAccounts(database, 1)) return account;
  return undefined;
}

Validation and capability discovery ​

ts
import { sql } from "@typed-sql/sqlite";
import * as v from "valibot";

export const accountResult = v.object({
  id: v.bigint(),
  email: v.pipe(v.string(), v.email()),
  status: v.string(),
});

interface ValidatedAccount {
  readonly id: bigint;
  readonly email: string;
  readonly status: string;
}

export const validatedAccountById = (accountId: bigint) =>
  sql.validateResult(
    sql<ValidatedAccount>`
      SELECT account.id, account.email, account.status
      FROM account
      WHERE account.id = ${accountId}
    `,
    accountResult,
  );

The application can inspect the adapter contract instead of assuming that cancellation or deadlines exist:

ts
import type { SqliteDatabase } from "@typed-sql/sqlite/runtime";

export function sqliteExecutionCapabilities(database: SqliteDatabase) {
  return database.executionCapabilities;
}

Execute through node:sqlite ​

The adapter presents the same promise-shaped database contract even though the underlying built-in driver is synchronous:

ts
import { typePolicy } from "@typed-sql/sqlite";
import { createNodeSqliteDatabase } from "@typed-sql/sqlite/node-sqlite";
import { databasePath } from "../typed-sql.config.js";
import { loadAccountWorkspace } from "./batches.js";
import { sqliteExecutionCapabilities } from "./capabilities.js";
import { findAccount } from "./cardinality.js";
import { prepareAccountQueries } from "./prepared.js";
import { accountProjectSummary } from "./queries.js";
import { collectActiveAccounts } from "./streams.js";

export async function runSqliteExample() {
  const database = await createNodeSqliteDatabase({ path: databasePath, typePolicy });

  try {
    const prepared = prepareAccountQueries(database);
    const account = await findAccount(database, 1n);
    const preparedAccount = await database.maybeOne(prepared.accountById(2n));
    const workspace = await loadAccountWorkspace(database, 1n);
    const summary = await database.all(accountProjectSummary);
    const streamedAccounts = await collectActiveAccounts(database);
    return {
      account,
      preparedAccount,
      workspace,
      summary,
      streamedAccounts,
      executionCapabilities: sqliteExecutionCapabilities(database),
    };
  } finally {
    await database.close();
  }
}

The real Poku suite recreates the database, executes every documented path, checks driver-specific decoded values, and removes its mutation fixtures:

ts
import { mkdir, readFile, writeFile } from "node:fs/promises";
import { dirname, join, resolve } from "node:path";
import { fileURLToPath } from "node:url";
import {
  CONFORMANCE_VERSION,
  type ConformanceLiveAdapter,
  type ConformanceServerErrorClass,
  type ConformanceTypeNormalizer,
  createConformanceReport,
  createConformanceReproductionBundle,
  defineConformanceProbe,
  runLiveConformanceProbe,
  runStaticConformanceProbe,
  selectExpectedOutcome,
  serializeConformanceReport,
  serializeConformanceReproductionBundle,
} from "@typed-sql/conformance/v2";
import { sql, sqlite, typePolicy } from "@typed-sql/sqlite";
import { createNodeSqliteDatabase } from "@typed-sql/sqlite/node-sqlite";
import { describe, it, strict } from "poku";
import { insertSmallAccountBatch, loadAccountWorkspace } from "../src/batches.js";
import { sqliteExecutionCapabilities } from "../src/capabilities.js";
import { findAccount, listActiveAccounts, requireAccount } from "../src/cardinality.js";
import { deleteAccount, deleteProjectsByOwner, updateAccountStatus } from "../src/mutations.js";
import { prepareAccountQueries } from "../src/prepared.js";
import { accountProjectSummary } from "../src/queries.js";
import { collectActiveAccounts, firstActiveAccount } from "../src/streams.js";
import { createAccountWithProject } from "../src/transactions.js";
import { validatedAccountById } from "../src/validation.js";
import { databasePath } from "../typed-sql.config.js";

const database = await createNodeSqliteDatabase({ path: databasePath, typePolicy });
const packageDirectory = resolve(dirname(fileURLToPath(import.meta.url)), "..");
const workspaceDirectory = resolve(packageDirectory, "../..");
const generatedSnapshotPath = join(packageDirectory, "generated", "db", "schema.json");

function rowValue(row: unknown): Readonly<Record<string, unknown>> {
  if (row === null || typeof row !== "object") throw new TypeError("Expected a SQLite row object");
  return { ...row };
}

async function removeAccounts(ids: readonly bigint[]): Promise<void> {
  for (const id of ids) {
    await database.execute(deleteProjectsByOwner(id));
    await database.execute(deleteAccount(id));
  }
}

try {
  await describe("SQLite example against node:sqlite", async () => {
    await it("executes queries, cardinality, CTEs, prepared statements, batches, and streams", async () => {
      strict.strictEqual((await listActiveAccounts(database)).length, 1);
      strict.deepStrictEqual(rowValue(await requireAccount(database, 1n)), {
        id: 1n,
        email: "alice@example.com",
        status: "active",
      });
      strict.strictEqual(await findAccount(database, -1n), undefined);
      strict.strictEqual((await database.all(accountProjectSummary)).length, 2);

      const prepared = prepareAccountQueries(database);
      strict.strictEqual(prepared.accountById.statementName, "example-account-by-id");
      strict.deepStrictEqual(rowValue(await database.one(prepared.accountById(2n))), {
        id: 2n,
        email: "bob@example.com",
        status: "suspended",
      });
      strict.strictEqual((await loadAccountWorkspace(database, 1n)).projects.length, 1);
      strict.strictEqual((await collectActiveAccounts(database)).length, 1);
      strict.deepStrictEqual(rowValue((await firstActiveAccount(database))!), {
        id: 1n,
        email: "alice@example.com",
        status: "active",
        budget: 12500.5,
      });
      strict.deepStrictEqual(rowValue(await database.one(validatedAccountById(1n))), {
        id: 1n,
        email: "alice@example.com",
        status: "active",
      });
      strict.deepStrictEqual(sqliteExecutionCapabilities(database), { cancellation: false, deadlines: false });
    });

    await it("commits typed mutations atomically", async () => {
      await removeAccounts([9_001n]);
      const created = await createAccountWithProject(
        database,
        { id: 9_001n, email: "transaction.sqlite@example.com", status: "active" },
        { id: 9_001n, name: "SQLite transaction", budget: 42.5 },
      );
      strict.deepStrictEqual(rowValue(created.account), {
        id: 9_001n,
        email: "transaction.sqlite@example.com",
        status: "active",
      });
      strict.deepStrictEqual(rowValue(await database.one(updateAccountStatus(9_001n, "suspended"))), {
        id: 9_001n,
        email: "transaction.sqlite@example.com",
        status: "suspended",
      });
      await removeAccounts([9_001n]);
      strict.strictEqual(await findAccount(database, 9_001n), undefined);
    });

    await it("uses a transaction batch for small inserts without claiming a native bulk protocol", async () => {
      const ids = [9_101n, 9_102n] as const;
      await removeAccounts(ids);
      const results = await insertSmallAccountBatch(database, [
        { id: ids[0], email: "batch-one.sqlite@example.com", status: "active" },
        { id: ids[1], email: "batch-two.sqlite@example.com", status: "suspended" },
      ]);
      strict.strictEqual(results.length, 2);
      strict.deepStrictEqual(rowValue(await requireAccount(database, ids[1])), {
        id: ids[1],
        email: "batch-two.sqlite@example.com",
        status: "suspended",
      });
      await removeAccounts(ids);
    });

    await it("executes SQLite recursive compounds with positional members", async () => {
      const rows = await database.execute(sql`
        WITH RECURSIVE cnt(value) AS (
          SELECT ${1n}
          UNION ALL
          SELECT value + 1 FROM cnt WHERE value < ${3n}
        )
        SELECT value FROM cnt
      `);
      strict.deepStrictEqual(
        rows.map((row) => rowValue(row).value),
        [1n, 2n, 3n],
      );
    });

    await it("executes chained SQLite windows and explicit frames", async () => {
      const rows = await database.execute(sql`
        SELECT
          id,
          ROW_NUMBER() OVER ordered AS position,
          LAG(email, 1, 'missing') OVER ordered AS previous,
          SUM(id) OVER (ordered ROWS BETWEEN 1 PRECEDING AND CURRENT ROW EXCLUDE TIES) AS running_id
        FROM account
        WINDOW ordered AS (ORDER BY id)
        ORDER BY id
      `);
      strict.deepStrictEqual(rows.map(rowValue), [
        { id: 1n, position: 1n, previous: "missing", running_id: 1n },
        { id: 2n, position: 2n, previous: "alice@example.com", running_id: 3n },
      ]);
    });

    await it("executes SQLite UPSERT, RETURNING, and UPDATE FROM", async () => {
      await removeAccounts([9_201n]);
      const inserted = await database.execute(sql`
        INSERT INTO account (id, email, status)
        VALUES (${9_201n}, ${"upsert.sqlite@example.com"}, ${"active"})
        ON CONFLICT (id) DO UPDATE SET
          email = excluded.email,
          status = excluded.status
        RETURNING id, email, status
      `);
      strict.deepStrictEqual(rowValue(inserted[0]), {
        id: 9_201n,
        email: "upsert.sqlite@example.com",
        status: "active",
      });
      const updated = await database.execute(sql`
        UPDATE account
        SET status = source.status
        FROM account AS source
        WHERE account.id = ${9_201n} AND source.id = ${2n}
        RETURNING id, status
      `);
      strict.deepStrictEqual(rowValue(updated[0]), { id: 9_201n, status: "suspended" });
      await removeAccounts([9_201n]);
    });

    await it("records a redacted conformance v2 differential report", async () => {
      const dialect = sqlite();
      const snapshot = dialect.validateSnapshot(JSON.parse(await readFile(generatedSnapshotPath, "utf8")));
      if (snapshot.formatVersion !== 2) throw new TypeError("The SQLite conformance run requires snapshot v2");
      if (snapshot.metadata === undefined) throw new TypeError("The generated SQLite snapshot requires metadata");
      const query = sql`SELECT id FROM account WHERE id = ${1n}`;
      const versionRows = await database.execute(sql`SELECT sqlite_version() AS version`);
      const version = String(rowValue(versionRows[0]).version);
      const target = {
        grammar: "sqlite",
        grammarVersion: dialect.grammarVersion,
        databaseVersion: version,
      } as const;
      const probe = defineConformanceProbe({
        version: CONFORMANCE_VERSION,
        id: "sqlite.statement.select.live-bigint",
        featureId: "statement.select",
        grammar: "sqlite",
        targets: [target],
        source: "SELECT id FROM account WHERE id = ?",
        schemaFixture: "examples/sqlite/schema/catalog.snapshot.json",
        query,
        compilerSource:
          'import { sql } from "@typed-sql/sqlite";\nexport const query = sql`SELECT id FROM account WHERE id = ${1n}`;',
        live: { execute: true, maximumRows: 1 },
        expected: [
          {
            target: { grammarVersion: dialect.grammarVersion, databaseVersion: version },
            support: "conservative",
            rows: [
              {
                name: "id",
                tsType: "bigint",
                nullable: false,
                databaseType: "INTEGER",
                range: { start: 7, end: 9, line: 1, column: 8 },
              },
            ],
            parameters: [{ index: 1, tsType: "bigint", nullable: false, databaseType: "INTEGER" }],
            diagnostics: [],
            rendered: { text: "SELECT id FROM account WHERE id = ?", values: [1n] },
            compiled: { rowType: '{ "id": bigint; }', parameterType: "readonly [bigint]" },
            decodedRows: [{ id: 1n }],
            skips: {
              "lex-parse": "grammar-parser-private",
              prepare: "no-server-metadata",
              plan: "plan-format-unstable",
            },
          },
        ],
      });
      const requestedProbe = process.env.TYPED_SQL_CONFORMANCE_PROBE;
      if (requestedProbe !== undefined && requestedProbe !== probe.id) {
        throw new TypeError(`SQLite live suite does not contain requested probe ${requestedProbe}`);
      }
      const requestedDatabaseVersion = process.env.TYPED_SQL_CONFORMANCE_DATABASE_VERSION;
      if (requestedDatabaseVersion !== undefined && requestedDatabaseVersion !== version) {
        throw new TypeError(`Requested SQLite ${requestedDatabaseVersion}, connected to ${version}`);
      }
      const requestedFixtureGroup = process.env.TYPED_SQL_CONFORMANCE_FIXTURE_GROUP;
      if (requestedFixtureGroup !== undefined && requestedFixtureGroup !== "statement.select") {
        throw new TypeError(`SQLite live suite does not contain fixture group ${requestedFixtureGroup}`);
      }
      const adapter: ConformanceLiveAdapter = {
        grammar: "sqlite",
        driver: "node:sqlite",
        driverVersion: process.versions.node,
        async server() {
          return { version, capabilities: {} };
        },
        async prepare() {
          return { columns: [], parameters: [], unavailable: ["columns", "parameters", "nullability"] };
        },
        execute: async () => (await database.execute(query)).map(rowValue),
        classify(error): ConformanceServerErrorClass {
          const code = typeof error === "object" && error !== null && "code" in error ? String(error.code) : "";
          if (code.includes("ERROR")) return "semantic";
          if (code.includes("BUSY") || code.includes("LOCKED")) return "environment";
          return "semantic";
        },
        async cleanup() {},
        async close() {},
      };
      const normalizer: ConformanceTypeNormalizer = {
        column: (field) => ({
          name: field.name ?? "id",
          tsType: "bigint",
          nullable: field.nullable ?? false,
          databaseType: field.nativeType ?? "INTEGER",
        }),
        parameter: (field) => ({
          index: field.index,
          tsType: "bigint",
          nullable: field.nullable ?? false,
          databaseType: field.nativeType ?? "INTEGER",
        }),
      };
      const staticResult = runStaticConformanceProbe(probe, target, {
        dialect,
        snapshot,
        renderer: {
          placeholder: (index) => dialect.placeholder(index),
          quoteIdentifier: (identifier) => dialect.quoteIdentifier(identifier),
        },
      });
      const result = await runLiveConformanceProbe(probe, target, adapter, normalizer, staticResult);
      const report = createConformanceReport(
        "sqlite-live",
        {
          grammar: "sqlite",
          grammarVersion: dialect.grammarVersion,
          databaseVersion: version,
          driver: "node:sqlite",
          driverVersion: process.versions.node,
          runtime: "node",
          runtimeVersion: process.version,
          typescriptVersion: "7.0.2",
          schemaFingerprint: `sha256:${snapshot.metadata.schemaHash}`,
          capabilities: {},
        },
        [result],
      );
      const artifactDirectory = join(workspaceDirectory, "artifacts", "conformance");
      await mkdir(artifactDirectory, { recursive: true });
      const serialized = serializeConformanceReport(report);
      strict.ok(!serialized.includes("alice@example.com"));
      strict.ok(!serialized.includes("1n"));
      await writeFile(join(artifactDirectory, "sqlite.json"), serialized);
      if (result.status !== "pass") {
        const reproduction = createConformanceReproductionBundle(
          probe,
          target,
          report.environment,
          selectExpectedOutcome(probe, target),
          result,
        );
        await writeFile(
          join(artifactDirectory, "sqlite-reproduction.json"),
          serializeConformanceReproductionBundle(reproduction),
        );
      }
      strict.strictEqual(result.status, "pass", JSON.stringify(result, null, 2));
    });
  });
} finally {
  await removeAccounts([9_001n, 9_101n, 9_102n, 9_201n]).catch(() => undefined);
  await database.close();
}

Run it ​

From the repository root:

sh
pnpm --filter @typed-sql/example-sqlite generate
pnpm --filter @typed-sql/example-sqlite check
pnpm --filter @typed-sql/example-sqlite test
pnpm --filter @typed-sql/example-sqlite start
pnpm --filter @typed-sql/example-sqlite test:database

Both generate and start recreate the scoped examples/sqlite/example.sqlite file before use. The SQLite dialect guide describes its dynamic-typing, threading, and execution constraints.

Released under the MIT License.

Copyright © 2026 typed-sql contributors.