The Quickstart gave you a Worker that boots. It has no data, which means it is not yet an app. This page adds a table, migrates it, and reads it back through a route — the first thing in the project that is actually yours.
Four steps, and the third one is where most of the interesting rules live.
1. Give the Worker a database
A scaffolded apps/api/wrangler.jsonc declares "d1_databases": [] in every stanza, because nothing has asked for one. Add a binding named DB, at the top level and in each environment block:
"d1_databases": [
{ "binding": "DB", "database_name": "my-backend-dev-db", "database_id": "" }
]database_id stays empty for now. On dev nothing reads it — the local runtime keys on the binding name — and pithy provision --env staging fills it in for the environments that need a real one.
Two Workers that both declare DB are backed by one database. That is the whole sharing rule, and it is why the binding name is the decision rather than an implementation detail. A Worker that wants its own declares a different name.
2. Define the table
A table is a Zod object. Not a hand-written row interface beside a CREATE TABLE — one definition, from which the Kysely types are derived.
Create apps/api/src/data/note.ts:
import { SQLiteDate } from "@pithy-sh/core/src/data/codecs";
import { z } from "zod";
export const Note = z
.object({
id: z.string().describe("Primary key. A random UUID, so notes cannot be enumerated from a URL."),
body: z.string().min(1).max(4096).describe("The note's text, as the author typed it."),
createdAt: SQLiteDate.describe("When it was written. Ms-epoch in SQLite; a Date in app code."),
})
.describe("One note.");
export type Note = z.output<typeof Note>;Three conventions are visible there, and each one is load-bearing.
Every field carries a .describe(), and so does the object. The schemas are the object model’s documentation — they feed the self-documenting config, the CLI, and agent tooling. A field without a description is incomplete.
The const and the inferred type share a name. Note is both. There is no NoteSchema, ever.
Every conversion between JavaScript and SQLite goes through a codec. SQLiteDate stores a Date as a ms-epoch integer and hands you a Date back. SQLiteBoolean and sqliteJson(schema) are the other two you will reach for. A raw 0/1, a manual new Date(), or a JSON.stringify in query code is the smell this rule exists to remove.
3. Write the migration
Migrations are Kysely’s model — TypeScript up and down, with a down that is tested — rather than raw .sql files, and rather than wrangler’s own D1 migrations.
Create apps/api/src/migrations/0001_init.ts:
import type { Kysely } from "kysely";
import type { Migration } from "kysely/migration";
export const app_0001_init: Migration = {
up: async (db: Kysely<unknown>): Promise<void> => {
await db.schema
.createTable("notes")
.addColumn("id", "text", (c) => c.primaryKey())
.addColumn("body", "text", (c) => c.notNull())
.addColumn("createdAt", "integer", (c) => c.notNull())
.execute();
await db.schema.createIndex("notesCreatedAtIdx").on("notes").column("createdAt").execute();
},
down: async (db: Kysely<unknown>): Promise<void> => {
await db.schema.dropIndex("notesCreatedAtIdx").ifExists().execute();
await db.schema.dropTable("notes").ifExists().execute();
},
};Identifiers are written in camelCase and the runner emits snake_case SQL — CamelCasePlugin is installed on every Kysely instance the kit builds, so createdAt becomes the column created_at and your query code never types an underscore.
Every drop is ifExists(). D1 has no transactional DDL, and Kysely deletes the ledger row only after down resolves — so a down that dies halfway leaves what it already dropped gone and the migration still recorded as applied. Without ifExists(), re-running it throws on the first statement forever.
4. Register the database, and read it back
Both halves go into apps/api/pithy.config.ts, on the app capability you already own:
import { app_0001_init } from "./src/migrations/0001_init";
import { Note } from "./src/data/note";
const app = defineCapability({
name: "app",
requiredBindings: [{ type: "d1", name: "DB" }],
databases: {
app: {
binding: "DB",
tables: { notes: Note },
migrationOrder: 2000,
migrations: { "0001_init": app_0001_init },
},
},
routes: (a) => {
a.get("/notes", async (c) => {
const rows = await c.var.db.app
.selectFrom("notes")
.select(["id", "body", "createdAt"])
.orderBy("createdAt", "desc")
.limit(20)
.execute();
return c.json({ notes: rows });
});
},
});migrationOrder sorts your migrations against every capability’s within the same database. The kit’s own capabilities occupy the low numbers and the next free one is 1400; the ceiling is 9999. Pick something high — 2000 is what the Pithy dashboard uses for its own app — so a capability you add next year still lands before your tables rather than after them. The number is unique within its database and stable forever: renumbering renames the composed migration keys, which makes Kysely read applied migrations as unapplied and run them again.
requiredBindings is validated on the first request, and it fails with the binding’s name. A Worker deployed without DB says so immediately rather than throwing somewhere inside a query an hour later.
c.var.db.app is the typed Kysely instance for the database you named app. Its types come from the tables map, so a column you did not declare is a compile error rather than a runtime surprise.
Run it
pithy migrate --env dev
pithy dev--env dev runs locally through Miniflare against .wrangler/state, the same store wrangler dev reads, so what you migrate is what the Worker sees. It is idempotent — a second run with nothing pending prints Nothing to migrate. and changes nothing.
curl http://localhost:8787/notesAn empty list, which is correct: nothing has written a row yet. pithy seed is how you get a cast of fixtures from the same schemas that define the tables.
When it goes wrong
A migration run needs a project name. Set name in the root pithy.config.ts. Migrate stamps the database with the project that owns it and refuses one belonging to somebody else, and it will not guess a name — a guessed name stamps one value and checks a different one next run, which locks a project out of its own database.
A migration the database applied that nothing declares any more. That is what deleting a migration file leaves behind, and Kysely treats it as a corrupted chain. On dev, delete .wrangler/state and run again. On a deployed environment there are real rows, so restore the migration or remove its pithy_migrations row deliberately.