Database: D1

#Set up D1

D1 is Cloudflare's managed SQLite database (Cloudflare D1). To use it, add a database entry to your existing config:

import { defineConfig } from "goribu/server";

export default defineConfig({
  name: "my-app",
  compatibilityDate: "2026-09-01",
  database: { type: "d1", name: "my-app-db" }, // Add this
});

• Local database: pnpm dev uses a local database that is never uploaded. Don't forget to restart the dev server after changing the config.
• Production database: Deployment creates the named database the first time and reuses it afterwards.
• Already using Postgres? Connect your own database instead with Postgres. An app uses one or the other.
• Tables: Create tables with a migration before querying them. The tutorial walks through a complete example.

#Read rows

Write SQL with the req.d1 tagged template in a handler or middleware. Interpolated values are sent as SQL parameters, never pasted into the SQL text, so request input cannot inject SQL:

type Post = { id: number; title: string };

const post = await req.d1<Post>`
  SELECT id, title FROM posts WHERE id = ${req.params.id}
`.get();

const posts = await req.d1<Post>`
  SELECT id, title FROM posts ORDER BY id DESC
`.all();

const post = await req.d1`
  SELECT id, title FROM posts WHERE id = ${req.params.id}
`.get();

const posts = await req.d1`
  SELECT id, title FROM posts ORDER BY id DESC
`.all();

A query runs only when you call one of these methods. Awaiting the query without one throws an error so no need to worry about mistakes.

• Values: Pass strings, numbers, booleans (stored as 1 or 0), null, or binary data. Convert dates to strings and objects with JSON.stringify() first.
• Lists: An array expands to one parameter per item, so IN (${ids}) works. See empty lists.

#Write rows

Validate input, write with .run() and turn a constraint failure into a field error:

export const POST: Handler = async (req, res) => {
  const { title } = await req.body();
  if (typeof title !== "string" || !title.trim()) {
    return res.invalid({ title: "Enter a title." });
  }

  try {
    const { lastRowId } = await req.d1`
      INSERT INTO posts (title) VALUES (${title.trim()})
    `.run();

    return res.redirect(`/posts/${lastRowId}`);
  } catch (error) {
    // posts.title has a UNIQUE constraint.
    if (error.constraint === "unique") {
      return res.invalid({ title: "A post with this title already exists." });
    }
    throw error;
  }
};

export const POST = async (req, res) => {
  const { title } = await req.body();
  if (typeof title !== "string" || !title.trim()) {
    return res.invalid({ title: "Enter a title." });
  }

  try {
    const { lastRowId } = await req.d1`
      INSERT INTO posts (title) VALUES (${title.trim()})
    `.run();

    return res.redirect(`/posts/${lastRowId}`);
  } catch (error) {
    // posts.title has a UNIQUE constraint.
    if (error.constraint === "unique") {
      return res.invalid({ title: "A post with this title already exists." });
    }
    throw error;
  }
};

• Constraint errors: For a SQLite constraint failure, the error has constraint ("unique", "notNull", "foreignKey", or "check") and, when SQLite reports them, table, columns, or constraintName. Rethrow other errors so they reach your error page.

#Atomic batches

Use req.d1.atomic() when several writes must all succeed or all fail. Pass the queries without .run():

await req.d1.atomic([
  req.d1`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${fromId}`,
  req.d1`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${toId}`,
]);

await req.d1.atomic([
  req.d1`UPDATE accounts SET balance = balance - ${amount} WHERE id = ${fromId}`,
  req.d1`UPDATE accounts SET balance = balance + ${amount} WHERE id = ${toId}`,
]);

• Result: One entry per query, each with rows, changes, and lastRowId.
• Limitation: The whole batch is sent at once. You cannot read a result in JavaScript and decide the next query inside the same batch. You have to express the condition in SQL instead.

#Replication and freshness

New databases get read replicas around the world, so reads are fast for distant visitors. This is handled for you automatically and without having to repeat the same code in each new app. Replicas can lag slightly behind the primary database, so Goribu gives you ways to handle that for you:

const account = await req.d1.fresh<Account>`
  SELECT id, balance FROM accounts WHERE id = ${accountId}
`.get();

const account = await req.d1.fresh`
  SELECT id, balance FROM accounts WHERE id = ${accountId}
`.get();

• How the default works: Goribu remembers the visitor's position in the database in a cookie and makes their next requests read at least that recent data.
• Public is not caching: req.d1.public only chooses where the query reads from. It does not make the HTTP response cacheable.
• Turning replication off: Set replication: false in the database config. All three query styles then read the primary database and no cookie is set.
• Existing databases: Without a replication setting, deployment leaves an existing database's replication as it is. Set true or false to change it on the next deploy.

#Migrations

Migrations are numbered SQL files that change your schema the same way locally and in production. Create the next one:

pnpm exec goribu migrate:new create posts

Write your SQL below the two comment lines it starts with. The whole file is the migration:

-- create-posts
-- Migrations are forward-only. To undo a change create a new migration.

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL UNIQUE
);

Apply pending migrations to the local database and check what has run:

pnpm exec goribu migrate
pnpm exec goribu migrate:status

• Production: When you deploy, Goribu applies pending migrations before uploading the Worker, automatically. Add --remote to migrate or migrate:status to act on production directly.
• History is fixed: Never edit, remove, or reorder a migration that has run. Goribu detects it and stops. Restore the original file and then fix things in a new migration.
• Failures: Each migration runs as one batch with its tracking record, so a failed migration changes nothing and can be retried. Separate files are separate batches.
• Empty files: A file with only comments, like a fresh one, is refused, so it can't be recorded before you write its SQL.
• Forward-only: There is no rollback command. See Undo a change.
• Seed data: Put local test data in migrations/seed.sql and run pnpm exec goribu migrate:seed after migrate. It runs the file as-is each time, so write it to be rerun safely, for example with INSERT OR IGNORE. It never runs against production.

INSERT OR IGNORE INTO posts (id, title) VALUES
  (1, 'Hello Goribu'),
  (2, 'Second post');

• Live apps: Migrations and the Worker upload are separate steps, so old code briefly runs against the new schema. Add schema first, deploy code that uses it and remove old columns in a later release. See Deploys.

Undo a change

Removing something from the schema is a change too, so it gets its own migration and runs locally and in production like any other:

pnpm exec goribu migrate:new drop posts-subtitle

-- drop-posts-subtitle
-- Migrations are forward-only; undo a change with a new migration.

DROP INDEX IF EXISTS posts_subtitle_idx;
ALTER TABLE posts DROP COLUMN subtitle;

• Tables: DROP TABLE posts; removes the table and its rows.
• Columns: SQLite won't drop a column that is indexed, unique, part of the primary key, or used by a foreign key, view or trigger. Drop the index or view first, as above.
• Rows: To delete data rather than schema, run SQL directly, such as pnpm exec goribu d1 "DELETE FROM posts WHERE id = 2". See Inspect data.
• Older files: Migrations created before 0.9.4 have -- migrate:up and -- migrate:down markers. They keep working, but only the SQL between the markers runs, never the down section. Leave them as they are: editing an applied file, even its down section, stops further migrations.
• Upgrade together: Goribu 0.9.3 can't read migrations created by 0.9.4, so update every machine and CI job that runs migrations at the same time.

To start your local database over while you're still shaping a migration, stop pnpm dev and run:

rm -rf .wrangler/state/v3/d1
pnpm exec goribu migrate
pnpm exec goribu migrate:seed

This deletes only local D1 data, not local KV or R2 files; clear those with pnpm exec goribu local:clear kv and local:clear r2. Skip migrate:seed if you have no seed.sql. Never rewrite a migration that has already run in production; add a new one instead.

#Inspect data

Run SQL from the terminal with goribu d1, passing the SQL as one quoted argument:

pnpm exec goribu d1 "SELECT id, title FROM posts ORDER BY id DESC LIMIT 5"
pnpm exec goribu d1 --remote "SELECT count(*) FROM posts"

• Local: Uses the same database as pnpm dev and needs no Cloudflare login.
• Production: With --remote, a single SELECT runs directly. Anything else asks you to type the database name first, and is refused outright in CI, scripts, and coding agents.
• Not recorded: Changes made this way are not tracked. Use a migration for production data changes you need to repeat.

#Dynamic SQL

Most queries that vary can stay a tagged template, with every value still a parameter.

Optional filters

Bind null to mean "no filter". undefined is rejected, so convert missing values with ?? null:

const status = req.query.get("status") ?? null;

const posts = await req.d1<Post>`
  SELECT id, title FROM posts
  WHERE (${status} IS NULL OR status = ${status})
  ORDER BY created_at DESC
`.all();

const status = req.query.get("status") ?? null;

const posts = await req.d1`
  SELECT id, title FROM posts
  WHERE (${status} IS NULL OR status = ${status})
  ORDER BY created_at DESC
`.all();

Lists

An empty array is rejected, because IN () is not valid SQL and Goribu will not guess whether you meant "nothing" or "everything". Decide before querying:

if (ids.length === 0) return res.json([]);

const posts = await req.d1<Post>`
  SELECT id, title FROM posts WHERE id IN (${ids})
`.all();

if (ids.length === 0) return res.json([]);

const posts = await req.d1`
  SELECT id, title FROM posts WHERE id IN (${ids})
`.all();

Ordering

Choose between fixed orders with CASE:

const sort = req.query.get("sort") === "title" ? "title" : "newest";

const posts = await req.d1<Post>`
  SELECT id, title FROM posts
  ORDER BY CASE WHEN ${sort} = 'title' THEN title END,
           created_at DESC
`.all();

const sort = req.query.get("sort") === "title" ? "title" : "newest";

const posts = await req.d1`
  SELECT id, title FROM posts
  ORDER BY CASE WHEN ${sort} = 'title' THEN title END,
           created_at DESC
`.all();

Building SQL text

When the SQL itself must change, build it from fragments your code wrote and pass it to req.d1.unsafe(text, params) with a ? for each value. Never put request input in the SQL text. unsafe() does not make it safe.

import type { D1Value } from "goribu/server";

const conditions: string[] = [];
const params: D1Value[] = [];

if (status) {
  conditions.push("status = ?");
  params.push(status);
}

if (authorId) {
  conditions.push("author_id = ?");
  params.push(authorId);
}

const where = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";

const posts = await req.d1
  .unsafe<Post>(`SELECT id, title FROM posts ${where} LIMIT ?`, [...params, 20])
  .all();

const conditions = [];
const params = [];

if (status) {
  conditions.push("status = ?");
  params.push(status);
}

if (authorId) {
  conditions.push("author_id = ?");
  params.push(authorId);
}

const where = conditions.length > 0 ? `WHERE ${conditions.join(" AND ")}` : "";

const posts = await req.d1
  .unsafe(`SELECT id, title FROM posts ${where} LIMIT ?`, [...params, 20])
  .all();

• No nesting: Tagged templates cannot be nested or joined together; use unsafe() for that.
• Arrays: unsafe() does not expand arrays. Write one ? per item yourself.

#Text search and case

SQLite's LIKE, lower(), and upper() only handle case for ASCII letters, so comparison with strings like these:'Ελλάδα' LIKE 'ελλάδα' is false. For case- and accent-insensitive search in any language, store a normalized copy of the text and search it with input normalized the same way:

const normalize = (text: string) =>
  text.normalize("NFD").replace(/\p{M}/gu, "").toLocaleLowerCase();

// Write: keep search_text next to the original title.
await req.d1`
  INSERT INTO posts (title, search_text) VALUES (${title}, ${normalize(title)})
`.run();

// Read: escape LIKE wildcards in the visitor's input with "!".
const term = normalize(query).replace(/[!%_]/g, "!$&");

const posts = await req.d1<Post>`
  SELECT id, title FROM posts WHERE search_text LIKE ${`%${term}%`} ESCAPE '!'
`.all();

const normalize = (text) =>
  text.normalize("NFD").replace(/\p{M}/gu, "").toLocaleLowerCase();

// Write: keep search_text next to the original title.
await req.d1`
  INSERT INTO posts (title, search_text) VALUES (${title}, ${normalize(title)})
`.run();

// Read: escape LIKE wildcards in the visitor's input with "!".
const term = normalize(query).replace(/[!%_]/g, "!$&");

const posts = await req.d1`
  SELECT id, title FROM posts WHERE search_text LIKE ${`%${term}%`} ESCAPE '!'
`.all();

#Raw access

For libraries that need Cloudflare's D1 API directly, such as a query builder:

• req.d1.session: The same session req.d1 uses, so the library keeps the read-your-own-writes behavior. Prefer this.
• req.d1.fresh.session: A session that starts at the primary database.
• req.d1.binding: The plain Cloudflare binding, with no session handling.