Designing a D1 schema (tables & migrations)

A schema is the blueprint of your database — the list of tables, the columns in each table, and the rules that keep the data clean. In this guide we model a tiny blog: users who write posts. You will learn primary keys, foreign keys, indexes, and the NOT NULL / UNIQUE rules, then manage every future change with migrations.

D1 runs on SQLite, so this is plain, standard SQL. The big idea: design the tables thoughtfully up front, then never hand-edit the live database — instead, write a small migration file for each change so your schema has a clear, versioned history.

One user can write many posts, and every post belongs to exactly one user. That is a one-to-many relationship. We link them with a foreign key: posts.user_id points back to users.id.

These are the building blocks you will use in almost every table you ever design.

Apps grow: you will need a new column, a new table, or a new index. A migration is a small, numbered SQL file (0001_..., 0002_...) that records exactly one change. Wrangler keeps track of which files have already run, so applying them is repeatable on every environment.

The golden rule: once a migration has been applied to a shared or production database, never edit that file. To fix or change something, write a new migration. This keeps the history honest and lets teammates catch up by simply running 'migrations apply'.

Now wire it all together: create the database, bind it to a Worker, define the schema in a migration, apply it, add a second migration to evolve the schema, and finally query across both tables with a JOIN.

1. Create the database

   Wrangler prints a database_id — copy it for the next step.

   bashcontent_copyCopy

   npx wrangler d1 create blog-db

2. Bind it to your Worker

   Add this to wrangler.jsonc so your Worker reaches the database as env.DB.

   jsoncontent_copyCopy

   {
     "d1_databases": [
       {
         "binding": "DB",
         "database_name": "blog-db",
         "database_id": "<paste-your-id-here>"
       }
     ]
   }

3. Create the first migration

   This makes a migrations/ folder and an empty 0001_create_users_and_posts.sql file.

   bashcontent_copyCopy

   npx wrangler d1 migrations create blog-db create_users_and_posts

4. Write the schema SQL

   Open the generated file and define both tables with their keys, constraints, and indexes.

   sqlcontent_copyCopy

   -- migrations/0001_create_users_and_posts.sql
   -- Enforce foreign keys (SQLite leaves this off by default)
   PRAGMA foreign_keys = ON;
   
   CREATE TABLE users (
     id         INTEGER PRIMARY KEY,
     email      TEXT NOT NULL UNIQUE,
     name       TEXT NOT NULL,
     created_at TEXT NOT NULL DEFAULT (datetime('now'))
   );
   
   CREATE TABLE posts (
     id         INTEGER PRIMARY KEY,
     user_id    INTEGER NOT NULL,
     title      TEXT NOT NULL,
     body       TEXT,
     status     TEXT NOT NULL DEFAULT 'draft',
     created_at TEXT NOT NULL DEFAULT (datetime('now')),
     FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
   );
   
   -- Index the columns we filter and join on
   CREATE INDEX idx_posts_user_id ON posts (user_id);
   CREATE INDEX idx_posts_status  ON posts (status);

5. Apply locally, then remotely

   Test against the local copy first; once it works, apply to the real cloud database.

   bashcontent_copyCopy

   # Build the schema on the local test DB
   npx wrangler d1 migrations apply blog-db --local
   
   # See what is still pending on production
   npx wrangler d1 migrations list blog-db --remote
   
   # Apply to the real cloud database
   npx wrangler d1 migrations apply blog-db --remote

6. Evolve the schema later

   Need a new column? Create a second migration instead of editing the first — then apply it the same way.

   bashcontent_copyCopy

   npx wrangler d1 migrations create blog-db add_published_at_to_posts
   
   # In migrations/0002_add_published_at_to_posts.sql:
   #   ALTER TABLE posts ADD COLUMN published_at TEXT;
   
   npx wrangler d1 migrations apply blog-db --local
   npx wrangler d1 migrations apply blog-db --remote

7. Query across tables from a Worker

   prepare() builds a safe query, bind() fills the ? placeholder (blocking SQL injection), and the JOIN pulls each post together with its author.

   jscontent_copyCopy

   export default {
     async fetch(request, env) {
       const url = new URL(request.url);
       const userId = url.searchParams.get("user_id") ?? "1";
   
       // Join each post to its author, newest first.
       const { results } = await env.DB
         .prepare(
           `SELECT posts.id, posts.title, posts.status, users.name AS author
            FROM posts
            JOIN users ON users.id = posts.user_id
            WHERE posts.user_id = ?
            ORDER BY posts.created_at DESC`
         )
         .bind(userId)
         .all();
   
       return Response.json(results);
     },
   };

8. Deploy

   Publish the Worker — your schema-backed API is now live.

   bashcontent_copyCopy

   npx wrangler deploy

Seed some data to test

npx wrangler d1 execute blog-db --remote --command "INSERT INTO users (email, name) VALUES ('mei@example.com', 'Mei');"

npx wrangler d1 execute blog-db --remote --command "INSERT INTO posts (user_id, title, status) VALUES (1, 'Hello D1', 'live');"

• Turn on foreign keys with PRAGMA foreign_keys = ON; SQLite ignores FK rules otherwise.
• Add an index on every foreign key and any column you frequently filter or sort by.
• SQLite's ALTER TABLE can add columns and rename, but cannot drop a column easily — plan columns thoughtfully.
• Prefer NOT NULL with a DEFAULT so existing rows stay valid when you add a column.
• Test every migration with --local before --remote; the local copy is fast and disposable.
• Keep the migrations/ folder in Git so the whole schema history travels with your code.