Migrations

Task-oriented instructions for creating, running, and managing database schema changes.

How to create a migration

Use roost make:migration to generate a timestamped migration file in database/migrations/.

roost make:migration create_posts_table
roost make:migration add_status_to_posts
roost make:migration drop_legacy_table

The generator creates a file with empty up() and down() methods. Write the forward change in up() using this.db.run() and its inverse in down().

export default {
  async up(db: D1Database): Promise<void> {
    await db.prepare(`
      CREATE TABLE posts (
        id       INTEGER PRIMARY KEY AUTOINCREMENT,
        title    TEXT    NOT NULL,
        slug     TEXT    NOT NULL UNIQUE,
        body     TEXT    NOT NULL,
        author_id INTEGER NOT NULL REFERENCES users(id),
        status   TEXT    NOT NULL DEFAULT 'draft',
        created_at TEXT  NOT NULL DEFAULT (datetime('now')),
        updated_at TEXT  NOT NULL DEFAULT (datetime('now'))
      )
    `).run();
  },

  async down(db: D1Database): Promise<void> {
    await db.prepare('DROP TABLE IF EXISTS posts').run();
  },
};

How to run pending migrations

Run roost migrate to apply all migrations that haven't been recorded in the database yet.

roost migrate

The CLI prints each migration as it runs. Migrations are recorded in a migrations table so they only run once. To run against a remote (production) D1 database, use the wrangler CLI directly:

wrangler d1 migrations apply my-app-db --remote

How to rollback migrations

Use roost migrate:rollback to undo the most recently applied batch.

# Rollback the last batch
roost migrate:rollback

Always implement down() in migrations you may need to roll back. Migrations without a down() cannot be reversed by the CLI.

How to reset and re-run all migrations

Use roost migrate:reset to roll back every migration and re-run them all. This is destructive — only use it in development.

# Rollback everything and re-migrate from scratch
roost migrate:reset

How to define columns

D1 uses SQLite syntax. Common column types and patterns:

-- Text and numbers
id       INTEGER PRIMARY KEY AUTOINCREMENT
name     TEXT    NOT NULL
email    TEXT    NOT NULL UNIQUE
score    REAL    NOT NULL DEFAULT 0.0
count    INTEGER NOT NULL DEFAULT 0
active   INTEGER NOT NULL DEFAULT 1  -- boolean: 0 or 1

-- JSON (stored as text, query with json_extract)
metadata TEXT

-- Timestamps (stored as ISO 8601 text)
created_at TEXT NOT NULL DEFAULT (datetime('now'))
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
deleted_at TEXT                          -- nullable for soft deletes
published_at TEXT                        -- nullable optional datetime

-- Foreign keys
author_id INTEGER NOT NULL REFERENCES users(id)
org_id    INTEGER REFERENCES organizations(id) ON DELETE SET NULL

How to add indexes and foreign keys

Add indexes in the same migration or a separate one. Name indexes consistently: idx_{table}_{column}.

export default {
  async up(db: D1Database): Promise<void> {
    // Composite index for common query patterns
    await db.prepare(
      'CREATE INDEX idx_posts_author_status ON posts (author_id, status)'
    ).run();

    // Unique index
    await db.prepare(
      'CREATE UNIQUE INDEX idx_posts_slug ON posts (slug)'
    ).run();

    // Foreign key constraints are declared inline with REFERENCES.
    // SQLite requires PRAGMA foreign_keys = ON at connection time to enforce them.
  },

  async down(db: D1Database): Promise<void> {
    await db.prepare('DROP INDEX IF EXISTS idx_posts_author_status').run();
    await db.prepare('DROP INDEX IF EXISTS idx_posts_slug').run();
  },
};

Related: ORM guides for model definitions, @roostjs/orm reference for the full Model and QueryBuilder API.