Database

@silgi/db is SQL through one small API on every preset: Postgres, MySQL, SQLite or Cloudflare D1, through the drivers Bun and Node already have, the platform's own database, or a client your app brings. There is no query builder and no schema diff. Queries are tagged templates, schema changes are SQL files, and row types are read from those files.

bun add @silgi/db

Add it to modules and set its options under the db key. Its code is server only: importing @silgi/db/server from client code fails the build and names the importer.

import { defineConfig } from "silgi";

export default defineConfig({
  framework: "react",
  modules: ["@silgi/db"],
  runtimeConfig: { databaseUrl: "" },
  db: {
    migrate: "start",
    connections: {
      default: {
        connector: "bun-sql",
        url: { runtimeConfig: "databaseUrl" },
        dev: { connector: "bun-sqlite", path: ".data/dev.sqlite" },
      },
      analytics: { connector: "client", dialect: "postgres", client: "./server/db/analytics-client.ts" },
    },
  },
});

Connections

Each key of db.connections is a connection; default is the one useDatabase() returns. db.migrate and the connections sit apart, so a connection can have any name that holds A-Z, a-z, 0-9 and _.

static and every preset's prerender run on Bun while the site builds. A connector a preset cannot run fails the build: Database "default" uses the "bun-sqlite" connector, which the "node" preset cannot run (it runs on: bun, static).

• url of bun-sql is read from DATABASE_URL (and Bun.SQL's other names) when you leave it out. A literal URL with a password logs a build warning; read it from runtime config instead: url: { runtimeConfig: "databaseUrl" }, with SILGI_DATABASE_URL set where the server runs. The dialect comes from the scheme of a literal URL, else it is "postgres"; set it when the URL comes from runtime config and is not Postgres.
• path of the SQLite connectors is relative to the working directory the server runs in, its folder is created, and ":memory:" works. Both SQLite connectors wait up to 5 seconds for another process's write lock instead of failing with database is locked.
• With no default connection, the module gives you the preset's own database: bun-sqlite at .data/db.sqlite on bun, static and under silgi dev, node-sqlite at the same path on node, and d1 with the binding DB on cloudflare. On vercel and netlify the build fails, because their file systems do not keep files: @silgi/db needs db.connections.default on the vercel preset: its file system does not keep files. Use .data/ only for development; it is in the .gitignore of silgi init.
• A d1 connection needs a d1_databases entry with its binding in your wrangler.json, or an id in cloudflare.bindings.<NAME>.id or SILGI_CF_<NAME>_ID, which the build writes for you (Deploy). Without either the build fails and names the binding.

A connection opens at its first query, once per process (once per isolate on Workers), and the server closes every open connection when it drains.

Queries

useDatabase(name?) returns the connection. Every ${value} in a tagged query is a bound parameter, $1 … $n on Postgres and ? on SQLite and MySQL; there is no way to splice text into a tagged query.

import { createError, defineHandler } from "silgi/server";
import { useDatabase, type Row } from "@silgi/db/server";

export const listTodos = defineHandler({
  route: "/api/todos",
  method: "GET",
  handler: () => useDatabase().sql<Row<"todos">>`select * from todos where done = ${0} order by id`,
});

export const finish = defineHandler({
  route: "/api/todos/:id/finish",
  method: "POST",
  async handler({ id }: { id: string }) {
    const db = useDatabase();
    return db.transaction(async (tx) => {
      const todo = await tx.first<Row<"todos">>`select * from todos where id = ${Number(id)}`;
      if (!todo) throw createError({ status: 404, message: "Todo not found" });
      const { changes } = await tx.run`update todos set done = 1 where id = ${todo.id}`;
      await tx.run`insert into audit (todo_id, action) values (${todo.id}, ${"finish"})`;
      return { changes };
    });
  },
});

export const reset = defineHandler({
  route: "/api/todos/reset",
  method: "POST",
  async handler() {
    const db = useDatabase();
    const [, count] = await db.batch([db.run`delete from audit`, db.first<{ n: number }>`select count(*) as n from todos`]);
    return { todos: count?.n ?? 0 };
  },
});

A query is lazy: it runs when first awaited, once, so batch can take queries that have not run. batch is D1's batch (one round trip) on d1 and a transaction elsewhere; a failed query rolls back all of them.

Inside transaction(fn) use the tx argument, never db: db.sql there throws db.sql was called inside db.transaction; use the tx argument, and a nested transaction throws too, since on SQLite the first would wait for its own lock forever. A tx kept after its transaction ended rejects. On Postgres a failed statement aborts the transaction; if your fn catches that error, transaction rejects with transaction was rolled back: a statement in it failed, …, so let the error end the function or run the statement in tx.batch. d1 has no interactive transactions: transaction throws transaction is not available on the d1 connector: D1 has no interactive transactions; use batch([...]).

Values and results

undefined, NaN, a plain object and a function are refused with a TypeError before the query is sent, with the number of the value: db.sql value 2 is undefined; pass null. Bind JSON.stringify(value) for a JSON column.

Reads are the connector's: SQLite integers are number (and lose precision above 2^53), Postgres bigint and numeric are string and timestamps are Date, MySQL BIGINT is a number inside the 32-bit range and a string outside it, and DECIMAL is a string.

With tracing on, each query is a db.query <connection> span with db.system.name and db.query.text (the text with placeholders; values are never recorded).

Row types

silgi typegen and silgi build read each connection's migrations in order and write .silgi/types/db.d.ts, the same file the sample below shows. Row<"todos"> is then the type of a row, and useDatabase completes the connection names:

declare module "@silgi/db" {
  interface SilgiDatabases {
    default: {
      dialect: "sqlite";
      tables: {
        audit: { todo_id: number; action: string };
        todos: { id: number; title: string; done: number; created_at: string };
      };
    };
  }
}

export {};

A column without NOT NULL or PRIMARY KEY gets | null. The types follow the connection's dialect: Postgres bigint is string, MySQL datetime is Date, and an unknown type is unknown with one typegen warning that names the file, table and column. Typegen reads CREATE TABLE, ALTER TABLE … ADD, DROP COLUMN, RENAME TO, RENAME COLUMN and DROP TABLE and ignores other statements. The types are a reading of the migrations, not of the database: a table created another way has no type, and nothing infers the result of a query. You name the row type. The API reference lists the type table for every dialect.

Migrations

A migration is one SQL file applied once, in number order, and recorded. For the default connection the files are server/db/migrations/<number>_<name>.sql; for another connection, server/db/migrations/<connection>/. A layer's folders join the app's. Files are plain SQL in the dialect of the connection; this one is for SQLite:

create table todos (
  id integer primary key,
  title text not null,
  done integer not null default 0,
  created_at text not null default (datetime('now'))
);

The name matches 0001_init.sql (four digits or more, then a-z, 0-9 and _). Each file runs in its own transaction (on MySQL statement by statement, because MySQL commits DDL as it goes) and is recorded in _silgi_migrations with a SHA-256 checksum. A run holds a lock, so two instances that start together apply each file once: an exclusive write lock on SQLite, an advisory lock on Postgres, GET_LOCK on MySQL. A lock that is not obtained in 60 seconds fails the run.

A failed file stops the run and keeps the earlier ones: Migration 0002_add_tags.sql failed: <driver message>. Silgi refuses to run when it would leave two databases different:

• an applied file that changed: Migration 0001_init.sql changed after it was applied;
• a pending file numbered below the highest applied one: Migration 0002_mid.sql is numbered below 0003_add.sql, which is already applied; renumber it after 0003;
• a pending file with the content of an applied one whose file is gone: Migration 0003_add.sql was applied as 0002_add.sql; give it its old name back.

An applied file whose file is gone is only a warning, since a rollback deploy has fewer files.

When migrations run

db.migrate decides:

"start" on another preset fails the build and tells you to use "build" or to run silgi db migrate --prod in the deploy step. Under silgi dev a change to an applied file shows the checksum error in the terminal and the overlay.

D1. Wrangler owns D1 migrations. The build copies a d1 connection's files to .output/migrations/<connection>/ and sets migrations_dir on its d1_databases entry of .output/wrangler.json; the deploy step runs wrangler d1 migrations apply <database> --remote --config .output/wrangler.json. migrate: "build" or "start" with a d1 connection fails the build.

Before the prerender of any build, the files are also applied to each connection's dev connection, so a prerendered page finds its tables.

silgi db

silgi db new add_tags
silgi db status
silgi db migrate
silgi db migrate --connection analytics --prod

--connection <name> picks one connection. Without --prod a command uses the dev connection (a connection's dev, else the connection under the dev config); with --prod it uses the connection a build uses, with the production config and .env.production. On a d1 connection migrate and status print db.connections.default uses d1; run wrangler d1 migrations apply or list and exit 0.

The same run is the task db:migrate, payload { connection?: string }: await runTask("db:migrate"), silgi task run db:migrate --prod or tasks.http reach it, under the same locks, whatever db.migrate says. See Tasks.

The development connection

A connection's dev replaces it under silgi dev and in the prerender of every preset, as devStorage does for a mount: a Postgres app develops on a SQLite file, and a D1 app prerenders without Cloudflare. The build still bundles the connection's own connector. The default connection in the config at the top has one: Postgres through bun-sql in production, a SQLite file on your machine.

dev has no dev of its own and must be bun-sql, bun-sqlite, node-sqlite or client, because it runs on Bun. Migrations, silgi db and useDatabase() under silgi dev all use it. Row types follow the configured connection's dialect, not the dev one, so a Postgres connection with a SQLite dev types bigint as string while SQLite returns a number there.

Without dev, silgi dev runs the connection itself; a d1 connection fails at its first query and tells you to set db.connections.default.dev to a bun-sqlite connection.

Bring your own driver

The client connector is for Postgres or MySQL off Bun, or any driver you prefer. You name a file; the file adapts its driver to DatabaseClient, so Silgi depends on none of them. The config holds only the path (config is data), and the build bundles the file into a chunk loaded at the connection's first query.

interface DatabaseClient {
  query(text: string, params: unknown[]): Promise<{ rows: Record<string, unknown>[]; changes?: number; lastInsertId?: number | string }>;
  reserve?(): Promise<{ query: DatabaseClient["query"]; release(error?: Error): void | Promise<void> }>;
  end?(): Promise<void>;
}

query gets the text with the dialect's placeholders and the values in order. transaction, batch and migrations need reserve, because a BEGIN sent to a pool lands on any connection. release is passed the error that left the connection in an unknown state, so the pool drops it. The factory runs once per process, and close() calls end.

pg (Postgres). When query runs several statements pg resolves to a list, so the sample reads the last result:

import { Pool } from "pg";
import { defineDatabaseClient } from "@silgi/db/server";

export default defineDatabaseClient(() => {
  const pool = new Pool({ connectionString: process.env.ANALYTICS_URL });
  const on = (q: Pick<Pool, "query">) => async (text: string, params: unknown[]) => {
    const result = await q.query(text, params);
    const last = Array.isArray(result) ? result.at(-1) : result;
    return { rows: last?.rows ?? [], changes: last?.rowCount ?? 0 };
  };
  return {
    query: on(pool),
    async reserve() {
      const connection = await pool.connect();
      return { query: on(connection), release: (error?: Error) => connection.release(error) };
    },
    end: () => pool.end(),
  };
});

mysql2 (MySQL). Bound values go through execute, a prepared statement, because query escapes values into the text on the client. Text without values goes through query, since START TRANSACTION and SAVEPOINT cannot be prepared:

import { createPool, type Pool, type ResultSetHeader, type RowDataPacket, type ExecuteValues } from "mysql2/promise";
import { defineDatabaseClient } from "@silgi/db/server";

const bound = (value: unknown) => (value instanceof Uint8Array ? Buffer.from(value.buffer, value.byteOffset, value.byteLength) : typeof value === "bigint" ? String(value) : value);

const on = (q: Pick<Pool, "query" | "execute">) => async (text: string, params: unknown[]) => {
  const [result] = params.length ? await q.execute<RowDataPacket[] | ResultSetHeader>(text, params.map(bound) as ExecuteValues[]) : await q.query<RowDataPacket[] | ResultSetHeader>(text);
  return Array.isArray(result) ? { rows: result } : { rows: [], changes: result.affectedRows, lastInsertId: result.insertId };
};

export default defineDatabaseClient(() => {
  const pool = createPool({ uri: process.env.SHOP_URL, supportBigNumbers: true });
  return {
    query: on(pool),
    async reserve() {
      const connection = await pool.getConnection();
      return { query: on(connection), release: (error?: Error) => (error ? connection.destroy() : connection.release()) };
    },
    end: () => pool.end(),
  };
});

The analytics connection of the config at the top is this file with dialect: "postgres". The pg and mysql2 packages are yours: add them to your app's dependencies. A missing file fails the build (Cannot find database client "./server/db/x.ts" for db.connections.analytics) and a file that does not export defineDatabaseClient(…) fails at the first query.

Recipe: Drizzle

Drizzle's schema diff is not part of @silgi/db, and @silgi/db does not depend on drizzle-orm: both use your driver. Create the pool in one file, let a client file adapt it for Silgi's migrations, row types and tasks, and hand the same pool to Drizzle for queries.

import { Pool } from "pg";

export const pool = new Pool({ connectionString: process.env.DATABASE_URL });

import { defineDatabaseClient } from "@silgi/db/server";
import { pool } from "./drizzle-pool.ts";

const on = (q: Pick<typeof pool, "query">) => async (text: string, params: unknown[]) => {
  const result = await q.query(text, params);
  const last = Array.isArray(result) ? result.at(-1) : result;
  return { rows: last?.rows ?? [], changes: last?.rowCount ?? 0 };
};

export default defineDatabaseClient(() => ({
  query: on(pool),
  async reserve() {
    const connection = await pool.connect();
    return { query: on(connection), release: (error?: Error) => connection.release(error) };
  },
  end: () => pool.end(),
}));

import { defineConfig } from "silgi";

export default defineConfig({
  framework: "react",
  modules: ["@silgi/db"],
  db: { connections: { default: { connector: "client", dialect: "postgres", client: "./server/db/client.ts" } } },
});

import { eq } from "drizzle-orm";
import { drizzle } from "drizzle-orm/node-postgres";
import { boolean, integer, pgTable, text } from "drizzle-orm/pg-core";
import { defineHandler } from "silgi/server";
import { pool } from "./drizzle-pool.ts";

export const todos = pgTable("todos", {
  id: integer("id").primaryKey(),
  title: text("title").notNull(),
  done: boolean("done").notNull().default(false),
});

const orm = drizzle({ client: pool });

export const openTodos = defineHandler({
  route: "/api/todos",
  method: "GET",
  handler: () => orm.select().from(todos).where(eq(todos.done, false)),
});

Migrations stay SQL files in server/db/migrations/, and Drizzle's table definition is kept in step with them by hand. Row<"todos"> and the Drizzle table describe the same table; the migration is the source of both.

Recipe: remote data in development

To develop against the data of a deployed database, point the dev connection at it in a mode of its own and keep the credentials in a file that is not .env. --mode remote picks the $env.remote block of the config, and --dotenv .env.remote reads one more file; it fills variables that are not set yet and does not override .env, .env.<mode> or the shell (Environment).

D1 has an HTTP API, so a client file can reach a D1 database from a machine without Wrangler bindings. The remote client is the dev of the d1 connection only in the remote block; a plain silgi dev keeps its SQLite file:

import { defineConfig } from "silgi";

export default defineConfig({
  framework: "react",
  modules: ["@silgi/db"],
  db: {
    connections: {
      default: { connector: "d1", binding: "DB", dev: { connector: "bun-sqlite", path: ".data/dev.sqlite" } },
    },
  },
  $env: {
    remote: {
      db: {
        migrate: false,
        connections: {
          default: { connector: "d1", binding: "DB", dev: { connector: "client", dialect: "sqlite", client: "./server/db/d1-http-client.ts" } },
        },
      },
    },
  },
});

import { defineDatabaseClient } from "@silgi/db/server";

interface D1Answer {
  success: boolean;
  errors: Array<{ message: string }>;
  result: Array<{ results?: Record<string, unknown>[]; meta: { changes: number; last_row_id: number } }>;
}

export default defineDatabaseClient(() => {
  const { CF_ACCOUNT_ID, CF_D1_ID, CF_API_TOKEN } = process.env;
  if (!CF_ACCOUNT_ID || !CF_D1_ID || !CF_API_TOKEN) throw new Error("Set CF_ACCOUNT_ID, CF_D1_ID and CF_API_TOKEN");
  const url = `https://api.cloudflare.com/client/v4/accounts/${CF_ACCOUNT_ID}/d1/database/${CF_D1_ID}/query`;
  return {
    async query(text, params) {
      const response = await fetch(url, {
        method: "POST",
        headers: { authorization: `Bearer ${CF_API_TOKEN}`, "content-type": "application/json" },
        body: JSON.stringify({ sql: text, params }),
      });
      const answer = (await response.json()) as D1Answer;
      if (!answer.success) throw new Error(answer.errors.map((error) => error.message).join("; ") || `D1 answered ${response.status}`);
      const last = answer.result.at(-1);
      return { rows: last?.results ?? [], changes: last?.meta.changes ?? 0, lastInsertId: last?.meta.last_row_id };
    },
  };
});

cat > .env.remote <<'ENV'
CF_ACCOUNT_ID=...
CF_D1_ID=...
CF_API_TOKEN=...
ENV
silgi dev --mode remote --dotenv .env.remote

Cloudflare's reference describes params as strings; check what your queries bind. This client has no reserve, so transaction, batch and migrations are refused on it (transaction needs a client with reserve(); …). Migrations would also fail the start of silgi dev with migrations need a client with reserve(), so the remote block sets migrate: false; migrate the real database with Wrangler. Use the remote mode with silgi dev only: a dev connection receives its connection's migration files before the prerender of every build, whatever db.migrate says, so a build with this client as dev fails when the app has migration files. Remote data is real data: use a token that can read, and use a copy when you write.

Storage on a connection

The db storage driver keeps a storage mount in a table of a connection, so sessions and caches can live in your database. Core has no postgres or cloudflare-d1 storage driver: this is it.

import { defineConfig } from "silgi";

export default defineConfig({
  framework: "react",
  modules: ["@silgi/db"],
  db: { connections: { default: { connector: "bun-sql", url: { runtimeConfig: "databaseUrl" } } } },
  runtimeConfig: { databaseUrl: "" },
  storage: {
    sessions: { driver: "db", connection: "default", table: "silgi_storage" },
  },
});

Rows are (key, value, expires, mtime) in silgi_storage, or the table you name, created at first use. Mounts share a table without sharing rows. It runs wherever the connection's connector runs, and a mount whose connection is not in db.connections fails the build. increment is atomic on every dialect. Do not call storage on the same connection inside db.transaction: on Postgres and MySQL it runs outside that transaction, and on SQLite it throws instead of waiting forever.