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.
• 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() as Record<string, unknown>;
  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

Fill in its SQL. Keep both markers and leave the down section empty when the change cannot be safely reversed.

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

-- migrate:down
DROP TABLE posts;

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.
• Rollback: pnpm exec goribu migrate:down runs the down SQL of the latest migration. Read it first as it may delete data. It mostly exists as a tool in dev and should not be used in production.
• 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.

#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.