@wess/atlas/db#

Functional query builder, schema definitions, changesets, and database drivers for Postgres and SQLite.

Entry points#

@wess/atlas/db/query exports query and schema APIs plus connection types without loading a driver. /db/postgres exports connect({ url, pool?, driver? }) and connectPostgres(url, pool?); /db/sqlite exports connect({ path, driver? }) and connectSqlite(path). Optional driver retains the matching literal value only. /db/changeset exports changeset validation. /db is the aggregate API.

Changeset validators use the structural ChangesetValidator safeParse contract, compatible with Zod, without a mandatory Zod runtime or type dependency.

Exports#

Query Builder#

• from(table: string | Schema, alias?: string) => Chainable - entry point for all queries
• raw(sql: string, ...values: any[]) => Fragment - raw SQL fragment with bind values. $n is the fragment's n-th value, so raw("a = $1 OR b = $1", x) binds x twice; the highest $n must equal the number of values or compiling throws
• isFragment(value: unknown) => boolean - type guard for Fragment
• createWhereBuilder() => WhereBuilder - standalone predicate builder

Chainable Methods#

Select: .select(...cols), .distinct(...cols), .where(cb), .join(t, on, alias?), .leftJoin(t, on, alias?), .innerJoin(t, on, alias?), .orderBy(col, dir?, nulls?), .groupBy(...cols), .having(cb), .limit(n), .offset(n), .returning(...cols)

String columns are checked at compile time. In q(col), orderBy, groupBy, distinct, and returning they must be an identifier, table.column, *, or table.*; pass raw() for anything else (orderBy(raw("lower(email)"))). select strings may also be expressions ("COUNT(*) as total", "u.name as author") but not contain ;, --, /* */, quotes, $ placeholders, or control characters. orderBy direction must be ASC/DESC and nulls FIRST/LAST (any case; emitted uppercase), otherwise it throws. Treat select strings and join on strings as trusted code, never request input.

Mutate: .insert(data), .insertMany(data[]), .insertFrom(cols, source), .update(data), .del(), .truncate(cascade?)

Advanced: .onConflict(spec), .cte(name, sub), .recursiveCte(name, sub)

Terminal: .toSql(dialect?) => SqlResult, .toQuery() => Query

Schema#

• defineSchema(table: string, columns: T) => Schema<T> - define a table schema
• column.serial() → number, .text() → string, .integer() → number, .bigint() → bigint, .real() → number, .boolean() → boolean, .timestamp() → Date, .json<T>() → T, .uuid() → string
• Column modifiers: .primaryKey(), .unique(), .nullable() (widens to T | null), .default(val) (JS value), .defaultRaw(sql) (SQL expression, e.g. "now()", emitted into DDL generated by migrate.diff), .ref(table, col)
• RowOf<typeof schema> extracts the row TypeScript type; nullable columns become T | null.

Changeset#

• changeset(schema, opts: { cast, required?, validate? }) => (data) => ChangesetResult

Drivers#

• connect(opts: ConnectOptions) => Connection
  • sqlite: { driver: "sqlite", path: string }
  • postgres: { driver: "postgres", url: string, pool?: number }

Key Types#

type Dialect = "postgres" | "sqlite"
type SqlResult = { text: string, values: readonly any[] }
type Schema<T> = { table: string, columns: T }
type Connection = { dialect: Dialect, execute(q: SqlResult): Promise<any[]>,
  one(q: SqlResult): Promise<any>, all(q: SqlResult): Promise<any[]>,
  transaction<T>(fn: (conn: Connection) => Promise<T>): Promise<T>, close(): Promise<void> }
type ChangesetResult<T> = { valid: boolean, changes: Partial<T>,
  errors: Record<string, string[]> }

Usage#

import { connect, defineSchema, column, from, changeset, type RowOf } from "@wess/atlas/db"
import { z } from "zod"

// 1. define schema — column types thread through to the row type.
const users = defineSchema("users", {
  id: column.serial().primaryKey(),         // number
  name: column.text(),                       // string
  email: column.text().unique(),             // string
  bio: column.text().nullable(),             // string | null
})

type User = RowOf<typeof users>
// { id: number; name: string; email: string; bio: string | null }

// 2. connect
const db = connect({ driver: "sqlite", path: ":memory:" })

// 3. build & run queries — return types are inferred end-to-end.
await db.execute(from(users).insert({ name: "Ada", email: "[email protected]" }))

const rows: User[] = await db.all(from(users))                 // User[]
const trimmed = await db.all(from(users).select("id", "name")) // { id: number; name: string }[]
const one = await db.one(from(users).where(q => q("id").equals(1))) // User | null

// `from(string)` still works for dynamic / cross-table queries; rows stay `any`.
const adminRows = await db.all<{ id: number }>(from("users").select("id"))

// 4. validate input
const validate = changeset(users, {
  cast: ["name", "email"] as const,
  required: ["name", "email"] as const,
  validate: { email: z.string().email() },
})
const result = validate({ name: "", email: "bad" })
// result.valid === false, result.errors contains field errors

Where Callback#

from("users").where(q => q("age").greaterThan(18))
from("users").where(q => q.or(q("role").equals("admin"), q("role").equals("mod")))
from("users").where(q => q.raw(raw("active = $1", true)))

Dependencies#

Sibling: none. External: none. Runtime: Bun.sql (postgres), bun:sqlite.

Nested tx.transaction(...) uses a savepoint on both drivers, so an inner throw rolls back only the inner block. Run nested calls one at a time; concurrent savepoints on one connection are unsupported.

SQLite caches up to 256 prepared statements per connection. Async transactions serialize with other operations on that connection; use the provided tx for every operation inside the callback. Nested tx.transaction calls use savepoints on both drivers (Postgres via Bun.sql savepoint); an inner failure rolls back only the inner scope. The nested Postgres test runs when ATLAS_TEST_POSTGRES_URL is set, otherwise it is skipped.

On Postgres, builder queries always run as a single prepared statement, even with no bound values, so text smuggled into a builder query cannot stack a second statement. Plain { text, values: [] } objects still use the simple protocol and may contain several statements (migrations rely on this); keep request input out of them.