SQLite

@typesql/sqlite runs your queries on node:sqlite (DatabaseSync), better-sqlite3 or bun:sqlite. They share one synchronous interface, so the adapter has no dependencies.

npm install @typesql/core @typesql/sqlite

Add better-sqlite3 if you use it; node:sqlite and bun:sqlite are built in.

Open an example in the TypeScript Playground to hover the types and edit the SQL, with nothing to install.

Create sql

Pass your database to createSql:

import { DatabaseSync } from "node:sqlite";
import { createSql } from "@typesql/sqlite";

const sql = createSql<DB>(new DatabaseSync("app.db"));
const posts = await sql(
  "SELECT id, title FROM posts WHERE author_id = $1",
  1,
).execute();

The same call takes a better-sqlite3 Database or a bun:sqlite Database. The statement text is sent as written: the adapter prepares it on each execution and binds $1, $2, … by name, so result column names match what TypeSQL infers. A query from sql.prepare is prepared once, on its first execution, and reused.

Values are bound as the driver expects: sql.json(value) as JSON text, true / false as 1 / 0. A Date throws, because SQLite has no date type; pass date.toISOString() or a number.

The drivers bind a JavaScript number differently: node:sqlite and better-sqlite3 bind it as REAL, bun:sqlite binds an integral one as INTEGER. Column affinity hides this when you store values, but not in expressions: 7 / $1 with 2 is 3.5 on the first two and 3 on bun. Pass a bigint (or CAST($1 AS INTEGER)) where integer arithmetic matters.

Run queries

The execute* methods work as with every adapter. executeCount() returns the number of changed rows for INSERT, UPDATE and DELETE, or the number of rows for statements that return rows.

Attach .withPlugin(new CasePlugin()) (imported from @typesql/core) for camelCase row keys.

Integers as bigint

Pass { bigint: true } to read integers as bigint (node:sqlite's setReadBigInts, better-sqlite3's and bun:sqlite's safeIntegers). Rows are then typed with BigIntTypeMap. This works the same on all three drivers.

Without it, integers beyond Number.MAX_SAFE_INTEGER differ by driver: node:sqlite throws, while better-sqlite3 and bun:sqlite silently lose precision. Use { bigint: true } if your data can hold such values (random() usually does).

Transactions

sql.transaction(fn) runs BEGIN / COMMIT / ROLLBACK on the database, and nested calls use savepoints. A database is a single connection, so top-level transactions on it run one after another; a query you start outside fn while a transaction is open runs inside it. See Transactions.

Types

The SQLite drivers return numbers, strings, null and Uint8Array (better-sqlite3: Buffer, a subclass), which is what DefaultTypeMap of @typesql/core/sqlite describes. See SQLite for how declared types map to TypeScript.

Generate the schema

typesql introspect --dialect sqlite, from @typesql/cli, reads a database file and writes its schema, with each column's declared type, nullability, default, position and INTEGER PRIMARY KEY, and the generated and hidden columns:

npm install -D @typesql/cli
npx typesql introspect --dialect sqlite --db app.db --out src/db/schema.ts

// Generated by `typesql introspect` from SQLite 3.53.4. Do not edit.
import type { Col, View } from "@typesql/core/sqlite";

export interface DB {
  active_users: View<{
    id: Col<"integer", { nullable: true; position: 0 }>;
    email: Col<"text", { nullable: true; position: 1 }>;
  }>;
  users: {
    id: Col<"integer", { hasDefault: true; position: 0; rowid: true }>;
    email: Col<"text", { position: 1 }>;
    name: Col<"varchar", { nullable: true; position: 2 }>;
    created_at: Col<"datetime", { hasDefault: true; position: 3 }>;
  };
}

--dialect sqlite can be left out when --db is given.

Option	What it does
--db <file>	the database file, opened read-only
--out <file>	output file; default stdout
--exclude <pattern>	relation to leave out, where * matches anything; repeat it or comma-separate
--type-name <name>	name of the interface; default DB
--import-from <module>	where Col and View are imported from; default @typesql/core/sqlite

The command opens the file with node:sqlite, so it needs Node.js 22.13 or later. SQLite doesn't record whether a view's column can be NULL, so view columns are nullable; FTS shadow tables and sqlite_* tables are left out. From code, @typesql/sqlite/introspect exports introspect(db, { exclude }), which takes a node:sqlite, better-sqlite3 or bun:sqlite database, and generateSource(result, { typeName, importFrom }).

Migrations

migrate(db, m) runs a migrations() builder's statements in one transaction. Build it with migrations from @typesql/core/sqlite.