bun-sqlx

Compile-time-checked raw SQL for Bun + PostgreSQL. Inspired by Rust's sqlx.

You write plain SQL strings. A prepare step validates them against your database via the PostgreSQL wire protocol and generates a TypeScript declaration file. Wrong column names, mismatched parameter types, stale queries after a migration — all become compile errors.

import { sql } from "bun-sqlx";

const rows = await sql(
  `SELECT id, name, role FROM users WHERE id = $1`,
  1n,
);
//      ^ bigint
//
// rows: { id: bigint; name: string; role: "admin" | "editor" | "viewer" }[]

Features

• Compile-time validation against a live PostgreSQL via Parse + Describe Statement (no query execution).
• Precise nullability inference through libpg-query: JOIN direction (LEFT/RIGHT/FULL), inner JOIN ... ON predicates, DML RETURNING, COALESCE, CASE, COUNT, expression propagation. Parameters become T | null when wrapped in COALESCE/NULLIF/IS [NOT] NULL/IS [NOT] DISTINCT FROM, or when bound to a nullable column in INSERT/UPDATE.
• WHERE narrowing: IS NOT NULL, equality chains, IN, LIKE, BETWEEN make columns non-null. Tracks AND/OR semantics.
• PostgreSQL enums generated as TypeScript literal unions (read + write side).
• Schema-aware jsonb via a BunSqlxJson global namespace and a config-driven column → type mapping. Works for both result columns and INSERT/UPDATE/WHERE parameters.
• Extension types out of the box: pgvector (vector, halfvec, sparsevec), hstore, citext, ltree/lquery/ltxtquery. Add your own through customTypes config.
• Domains resolve to their base TypeScript type (CREATE DOMAIN email AS text → string), including domains over extension types or other domains.
• Wide built-in type coverage: numeric, text, date/time, UUID, json/jsonb, network (inet/cidr/macaddr/macaddr8), bit strings, ranges/multiranges, geometric, money, tsvector/tsquery, xml — and the matching array variants.
• External SQL files via sql.file("queries/foo.sql", ...) — typed exactly like inline queries. Watch mode re-prepares on .sql edits too.
• One-row helpers: sql.one(...), sql.optional(...), sql.file.one(...), sql.file.optional(...), and the same chain on the tx callback — friendly with noUncheckedIndexedAccess: true. The scanner walks all of them.
• Array params for text[], int[], etc. are auto-serialised to PostgreSQL array literals ({a,b,c}) at runtime — no more string_to_array workaround.
• Typed transactions via sql.transaction(async tx => …) — the tx callback parameter is recognized by the scanner, so queries inside the block keep full type checking.
• Sourcemap-accurate error reporting: every prepare failure points to file:line:column of the originating sql(...) call site, with PG error code, position, and hint.
• Linear migrations with hash tampering detection.
• Runtime migrate() with PostgreSQL advisory lock, safe for multi-replica startup.
• Offline cache committed to your repo. CI verifies via prepare --check without a database.
• Schema snapshot + LLM manifest via schema dump / schema check: tables, columns, constraints, indexes, types, and function/procedure metadata are introspected from PostgreSQL.
• Shadow database validation via --shadow-url / SHADOW_DATABASE_URL: apply migrations to a throwaway DB, then prepare or introspect against it.
• Safe identifier quoting via sql.id(...), backed by the committed schema snapshot whitelist.
• Watch mode: ~15ms incremental re-prepare on file change.
• Cache pruning removes orphaned entries automatically (toggleable with --no-prune).

Install

bun add bun-sqlx

Setup

1. Configure the database URL

# .env
DATABASE_URL=postgres://user:password@localhost:5432/your_db
# Or with TLS against managed Postgres:
# DATABASE_URL=postgres://user:[email protected]:5432/your_db?sslmode=require

Supported sslmode values: disable, prefer (default — try TLS, fall back to plaintext), require (TLS or fail), verify-ca, verify-full. application_name and connect_timeout are also honored when provided as URL parameters.

2. Create a migration

bunx bun-sqlx migrate add init

Edit the created file (migrations/0001_init.up.sql):

CREATE TABLE users (
  id    BIGSERIAL PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  age   INT,
  bio   TEXT
);

Apply:

bunx bun-sqlx migrate run

3. Write your first query

// app.ts
import { sql } from "bun-sqlx";

const users = await sql(
  `SELECT id, name FROM users WHERE id = $1`,
  1n,
);

4. Prepare types

bunx bun-sqlx prepare

This generates bun-sqlx-env.d.ts next to your code. Add it to your tsconfig.json include if it isn't picked up automatically. (Pre-0.4.0 releases produced bun-sqlx.d.ts; delete the old file after upgrading. The new name avoids colliding with the package itself when baseUrl: "." is set.) Use --dts <path> to override the destination.

5. Dev loop with watch

bunx bun-sqlx prepare --watch

Save a .ts file, types regenerate in milliseconds, your editor picks up changes.

API

sql(query, ...params)

The typed query function. The first argument must be a string literal that exists in KnownQueries (populated by prepare).

const rows = await sql(`SELECT id FROM users WHERE name = $1`, "alice");
//                      ^ literal — checked at compile time

Unknown queries, wrong parameter types, and dynamic strings are compile errors. For genuinely dynamic SQL, use unsafe.

sql.file(path, ...params)

Load SQL from an external file. The path is resolved against the source file at scan time (so prepare can read it), and against process.cwd() at runtime (so the running process can read it). Both must point at the same content.

// queries/top_admins.sql
// SELECT id AS "id!", name AS "name!" FROM users WHERE role = $1 ORDER BY id LIMIT $2::int

import { sql } from "bun-sqlx";

const admins = await sql.file("queries/top_admins.sql", "admin", 5);
//                                                       ^ string  ^ number
// admins: { id: bigint; name: string }[]

File-backed queries are emitted into a separate KnownFileQueries interface; the path becomes the type key.

sql.one(query, ...params) and sql.optional(query, ...params)

Convenience wrappers for single-row queries. one throws if the row count is not exactly 1; optional returns null for 0 rows and throws on more than 1. They keep working under noUncheckedIndexedAccess: true without rows[0]! patterns.

const user = await sql.one(`SELECT id, name FROM users WHERE id = $1`, 1n);
// user: { id: bigint; name: string }

const maybe = await sql.optional(`SELECT id FROM users WHERE email = $1`, "x@y");
// maybe: { id: bigint } | null

Both forms also exist on sql.file (sql.file.one("queries/by_id.sql", ...)) and inside transactions (tx.one(...), tx.optional(...), tx.file.one(...), tx.file.optional(...)). The scanner recognizes every chain — these call sites are added to KnownQueries / KnownFileQueries just like a plain sql(...).

Array parameters

JavaScript arrays passed to text[], int[], uuid[], etc. are auto-encoded as PostgreSQL array literals before being sent. Strings containing commas, braces, quotes, or backslashes are escaped; null elements emit SQL NULL.

await sql("SELECT $1::text[] AS tags", ["alpha", "beta,gamma", "with \"quote\""]);
// → $1 sent as {alpha,"beta,gamma","with \"quote\""}

Encoding only kicks in when every element is a primitive (string / number / bigint / boolean / null). Arrays containing objects pass through unchanged — that's the path for jsonb columns whose value is a JSON array (attachments: BunSqlxJson.Attachment[]). If you need to store a primitive JS array as jsonb (rare), pass JSON.stringify(arr) explicitly. encodePgArrayLiteral(arr) is exported if you need the literal yourself for unsafe(...).

Empty arrays ([]) are passed straight through to Bun.SQL — the driver binds them as an empty PG array. If you need the literal "{}" instead (e.g. when concatenating into raw SQL), call encodePgArrayLiteral([]).

Parameter nullability

prepare infers param types as T | null when:

• $N appears inside COALESCE($N, …), NULLIF($N, …), IS [NOT] NULL, or IS [NOT] DISTINCT FROM — these patterns are only meaningful when the parameter can be null.
• $N is positionally bound in INSERT … VALUES (…, $N, …) or UPDATE … SET col = $N and the target column is nullable.

WHERE col = $N stays non-null even if col is nullable: col = NULL is always false in SQL, so passing null from the caller would be a bug. Use col IS NOT DISTINCT FROM $N (or an OR $N IS NULL clause) when you want NULL semantics.

sql.transaction(fn)

Wrap a function body in a database transaction. The callback receives a scoped tx that has the same typed () and .file() surface, but routes through the transaction's dedicated connection. The scanner recognises the callback parameter name and validates inner queries against KnownQueries.

import { sql } from "bun-sqlx";

const { userId, postId } = await sql.transaction(async (tx) => {
  const u = await tx(
    `INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id AS "id!"`,
    "Alice", "[email protected]",
  );
  const p = await tx(
    `INSERT INTO posts (user_id, title) VALUES ($1, $2) RETURNING id AS "id!"`,
    u[0].id, "Hello",
  );
  return { userId: u[0].id, postId: p[0].id };
});

If the callback throws, the transaction is rolled back. The return value of the callback becomes the return value of transaction.

unsafe(query, ...params)

Same runtime as sql but without type-checking. For dynamic SQL where compile-time validation isn't possible.

sql.id(...parts) / id(...parts)

Quote a dynamic identifier only if it exists in the generated schema snapshot. This is for the narrow cases where a table, column, function, type, index, or constraint name must be chosen dynamically.

import { unsafe, sql } from "bun-sqlx";

const orderBy = sql.id("users", "created_at");
await unsafe(`SELECT id, email FROM ${sql.id("users")} ORDER BY ${orderBy} DESC`);

The default snapshot path is .bun-sqlx/schema/schema.json. Override it at runtime with BUN_SQLX_SCHEMA_PATH. Pass schema-qualified identifiers as separate segments: sql.id("public", "users"), not sql.id("public.users").

migrate(options)

Apply pending migrations from application startup with a PostgreSQL advisory lock. Safe to call from multiple replicas.

import { migrate } from "bun-sqlx";

await migrate({ dir: "./migrations" });

Options:

type MigrateOptions = {
  dir?: string;
  databaseUrl?: string;
  log?: (msg: string) => void;
  lockKey?: number | bigint;     // overrides DEFAULT_MIGRATE_LOCK_KEY
  lockTimeoutMs?: number;        // pg_try_advisory_lock + polling; default: block
};

When lockTimeoutMs is set, acquisition uses pg_try_advisory_lock in a polling loop and throws if not obtained within the timeout — useful for CI / multi-replica startup to avoid an indefinitely-blocked pod.

getClient() / setClient() / close()

Low-level access to the underlying Bun.SQL instance, in case you need to manage the connection directly.

clearSqlFileCache()

Drops the in-memory cache used by sql.file(...). The cache invalidates automatically on file mtime change, so this is rarely needed manually.

Typed errors

import { NoRowsError, TooManyRowsError, PgError } from "bun-sqlx";

try {
  const u = await sql.one(`SELECT id FROM users WHERE id = $1`, 99);
} catch (e) {
  if (e instanceof NoRowsError) return null;
  if (e instanceof TooManyRowsError) console.error("ambiguous query, got", e.actual);
  if (e instanceof PgError) console.error("pg code:", e.code, "position:", e.position);
  throw e;
}

sql.one throws NoRowsError on 0 rows and TooManyRowsError (with .actual) on >1. PgError exposes .code, .position, .hint, .detail, .severity.

Transactions with options

sql.transaction(fn) and sql.transaction(opts, fn):

await sql.transaction({ isolation: "serializable", readOnly: true }, async (tx) => {
  return await tx(`SELECT id FROM accounts WHERE owner = $1`, ownerId);
});

Options: { isolation?: "read uncommitted" | "read committed" | "repeatable read" | "serializable"; readOnly?: boolean; deferrable?: boolean }. Applied via SET TRANSACTION immediately after BEGIN.

Namespace imports

In addition to import { sql } from "bun-sqlx", the scanner now recognises import * as ns from "bun-sqlx" and validates ns.sql(...), ns.sql.one(...), ns.sql.file(...), and ns.sql.transaction(...) exactly like the named-import form. Local re-declarations (const sql = ..., const { sql } = ...) correctly shadow the alias inside their scope.

CLI

bun-sqlx prepare [--check | --watch] [--root <dir>] [--dts <path>] [--no-prune] [--shadow-url <url>]
bun-sqlx migrate run [--lock-timeout <ms>] | info | revert | add <name> [--migrations <dir>]
bun-sqlx schema dump | check [--schema <path>] [--manifest <path>] [--no-manifest] [--shadow-url <url>]
bun-sqlx --version | --help

| Flag | Meaning | |-----------------------|--------------------------------------------------------------------------------------| | --check | Offline: verify cache matches sources, no database required. | | --watch | Persistent connection, re-prepare on file change. | | --root <dir> | Source/cache/migrations root (default: cwd). | | --dts <path> | Declarations output (default: <root>/bun-sqlx-env.d.ts). | | --no-prune | Keep orphaned cache entries instead of removing them. | | --migrations <dir> | Migrations directory (default: <root>/migrations). | | --lock-timeout <ms> | Advisory-lock acquisition timeout for migrate run / migrate revert. | | --shadow-url <url> | Apply migrations to this database, then prepare/introspect against it. | | --schema <path> | Schema snapshot path (default: <root>/.bun-sqlx/schema/schema.json). | | --manifest <path> | LLM schema manifest path (default: <root>/.bun-sqlx/schema/schema.md). | | --no-manifest | Skip writing the LLM schema manifest during schema dump. |

All flags accept both --flag value and --flag=value forms.

DATABASE_URL must be set for any command that touches the database, unless --shadow-url or SHADOW_DATABASE_URL is provided for that command. Supported URL search params: sslmode, application_name, connect_timeout.

Schema snapshot and manifest

schema dump introspects PostgreSQL and writes two generated files:

• .bun-sqlx/schema/schema.json — machine-readable contract for runtime identifier whitelisting and CI drift checks.
• .bun-sqlx/schema/schema.md — compact LLM-facing manifest with tables, columns, constraints, indexes, types, and functions.

schema check re-introspects the database and fails if the committed snapshot is stale. With --shadow-url, both prepare and schema dump/check first apply pending migrations to the shadow database, then use that database as the source of truth. In watch mode, pending shadow migrations are checked before every re-prepare; when a migration is applied, the prepare session is reopened so schema metadata is not reused across DDL changes.

Error output

When prepare fails, every diagnostic points back to the originating call site:

✗ src/users.ts:42:13 — describe failed: relation "userss" does not exist (pos 15, code 42P01)
    query: SELECT * FROM userss WHERE id = $1

Phases reported separately: describe failed, analyze failed, paramMap failed. PostgreSQL position, code, and hint are surfaced when present.

Configuration

bun-sqlx.config.ts at the project root is optional.

import type { BunSqlxConfig } from "bun-sqlx";

const config: BunSqlxConfig = {
  jsonbTypes: {
    "users.settings":     "BunSqlxJson.UserSettings",
    "posts.meta":         "BunSqlxJson.PostMeta",
    "posts.attachments":  "BunSqlxJson.Attachment",
  },
};

export default config;

Declare the referenced types anywhere in your project (.d.ts file is conventional):

// json-types.d.ts
declare global {
  namespace BunSqlxJson {
    type UserSettings = {
      theme: "light" | "dark";
      lang: string;
      notifications?: { email: boolean; push: boolean };
    };
    type PostMeta = { tags?: string[]; pinned?: boolean };
    type Attachment = { url: string; kind: "image" | "video" | "file"; sizeBytes: number };
  }
}
export {};

After re-running prepare, every jsonb column or parameter declared in jsonbTypes is checked against the corresponding TypeScript type.

Extension types and customTypes

bun-sqlx ships with a built-in registry that resolves popular PostgreSQL extension types automatically:

| pg_type.typname | TS type | Source extension | |-------------------|------------------------------------|-------------------| | vector | number[] | pgvector | | halfvec | number[] | pgvector | | sparsevec | string | pgvector | | hstore | Record<string, string \| null> | hstore | | citext | string | citext | | ltree | string | ltree | | lquery | string | ltree | | ltxtquery | string | ltree |

Add or override mappings via customTypes in bun-sqlx.config.ts. Keys are pg_type.typname values (the bare type name; namespacing isn't required):

import type { BunSqlxConfig } from "bun-sqlx";

const config: BunSqlxConfig = {
  customTypes: {
    vector: "Float32Array",         // override pgvector default
    geometry: "GeoJSON.Geometry",   // postgis (not built-in by design)
    myapp_color: "`#${string}`",    // your own CREATE TYPE base/domain
  },
};
export default config;

Domains resolve to their base type through pg_type.typbasetype. CREATE DOMAIN positive_int AS integer CHECK (VALUE > 0) → number, CREATE DOMAIN tagged AS hstore → Record<string, string | null>. Array variants of any registered scalar are also wired up automatically — vector[] → (number[])[].

Composite types (CREATE TYPE foo AS (a int, b text)) still resolve to unknown; see ROADMAP.

How nullability is inferred

A result column is non-null if all of the following hold:

1. The source column has a NOT NULL constraint (looked up via pg_attribute).
2. The source table isn't on the nullable side of an outer join.
3. Any wrapping expression is null-preserving — COALESCE with a non-null fallback, CASE with ELSE, COUNT(*), length(non_null), etc.

A column that doesn't satisfy the above is T | null. You can override:

• SELECT id AS "id!" → force non-null.
• SELECT id AS "id?" → force nullable.
• WHERE col IS NOT NULL / WHERE col = … / WHERE col IN (…) → narrows col to non-null in the result.

The runtime strips the !/? suffix from column keys so the row shape stays clean: { id: bigint }, not { "id!": bigint }.

CI workflow

Commit the generated bun-sqlx.d.ts and the .bun-sqlx/ cache directory to your repo. In CI:

- run: bun install
- run: bun-sqlx prepare --check   # fails if any query is missing from cache
- run: bun-sqlx schema check      # fails if the committed schema snapshot is stale
- run: tsc --noEmit               # fails if types are stale
- run: bun test

The prepare --check step runs without a database — your offline cache is the source of truth. schema check intentionally uses a live or shadow database because it verifies the committed schema contract against PostgreSQL.

Contributing

The project uses conventional commits, validated locally by cocogitto through lefthook hooks. Install both before contributing:

bun install                  # installs lefthook + wires git hooks
cargo install cocogitto      # or: brew install cocogitto

Releases are automated via release-please: pushes to main accumulate into a release PR that bumps package.json, writes CHANGELOG.md, and on merge tags the commit. The tag push fires the npm publish workflow.

Limitations

bun-sqlx is a young library. Known gaps:

• PostgreSQL only (no MySQL or SQLite).
• INSERT INTO t VALUES (...) without an explicit column list isn't parameter-mapped.
• SELECT * falls back to conservative nullability.
• Nested CTE references (CTE-b referencing CTE-a in the same WITH) and WITH RECURSIVE are not analysed transitively — at worst this produces extra T | null. Use AS "id!" overrides if needed.
• Composite types resolve to unknown. Domains and array types of registered types resolve correctly.
• Column names whose real name (not an alias) ends with ! or ? are not supported — the runtime strips those suffixes assuming an override. Use AS "alias" if you have such a column.
• Migrations run inside BEGIN/COMMIT. DDL that disallows transactions (CREATE INDEX CONCURRENTLY, VACUUM, REINDEX CONCURRENTLY, …) will fail; split such operations into separate migrations executed outside the runner.
• parseDatabaseUrl parses sslmode, application_name, and connect_timeout for the internal wire client (used by migrate run, prepare, and the runtime migrate() helper). The runtime sql() path delegates to Bun.SQL, which has its own TLS / connection-handling logic.
• connect_timeout covers the TCP-connect phase only; TLS handshake and SCRAM authentication have no timeout.
• sql.file(path) path is matched literally between scan time and runtime — they must agree on the working directory. Document a convention for your team (e.g. always run from the repo root).

See ROADMAP.md for what's planned.

Upgrading

Cache schema change (pre-1.0)

The .bun-sqlx/<fingerprint>.json entries dropped forceNonNull/forceNullable in favour of a single override?: "non-null" | "nullable" field. Cache files from the previous schema are rejected with a clear error pointing at the offending file. Delete .bun-sqlx/ and re-run bun-sqlx prepare against your database — there's no data loss, the cache is regenerated.

CI (prepare --check) will also fail loudly until the cache is regenerated; this is intentional so a stale schema can't silently emit incorrect .d.ts.

License

MIT.