Migrations

Olympia uses @orphnet/d1-eloquent for every D1 schema change. There is no wrangler d1 migrations directory, no raw SQL scattered through repos — every entity has one migration file, every migration runs against a binding the ORM resolves itself.

One file per entity

olympia-api-hono/
└── database/
    └── migrations/
        ├── 20251001_120000_create_rooms.ts
        ├── 20251001_120100_create_guest_sessions.ts
        ├── 20251001_120200_create_replay_index.ts
        ├── 20251001_120300_create_banned_ip_hashes.ts
        └── 20251115_140000_add_replay_version_to_replay_index.ts

Filenames are timestamped to fix ordering. The _migrations table tracks which have been applied; running migrate twice is a no-op.

A migration is a single defineMigration call exporting an up and a down:

import { defineMigration } from '@orphnet/d1-eloquent'

export default defineMigration({
  up: async ({ schema }) => {
    await schema.createTable('rooms', (t) => {
      t.id()                               // TEXT/UUID primary key
      t.text('game').notNullable()         // 'chess' | 'pool' | 'poker' | 'snake'
      t.text('code').notNullable().unique() // 6-char room code
      t.text('state').notNullable()        // 'lobby' | 'live' | 'completed' | 'expired'
      t.integer('idle_at').notNullable()   // unix seconds; cron sweeper key
      t.timestamps()
      t.softDeletes()
    })
    await schema.createIndex('rooms_idle_idx', 'rooms', ['idle_at'])
  },
  down: async ({ schema }) => {
    await schema.dropTable('rooms')
  },
})

t.id() produces a TEXT column with a CHECK ensuring valid UUIDs. Foreign keys use t.text('user_id').references('users', 'id').onDelete('cascade'). Never autoincrement, never integer PKs — see d1-eloquent safety rules.

Running migrations

Every project, every time:

bunx d1-eloquent migrate            # apply pending
bunx d1-eloquent status             # show applied + pending
bunx d1-eloquent migrate:rollback   # roll back the last batch
bunx d1-eloquent migrate:fresh      # drop everything, re-run, FK-safe

The CLI resolves the D1 binding name (DB) from wrangler.jsonc automatically. Don't pass --db, --local, or --remote — they aren't accepted.

Running in CI

A typical GitHub Actions step:

- name: Migrate D1 (production)
  run: bunx d1-eloquent migrate
  env:
    CLOUDFLARE_ACCOUNT_ID: ${{ secrets.CLOUDFLARE_ACCOUNT_ID }}
    CLOUDFLARE_API_TOKEN: ${{ secrets.CLOUDFLARE_API_TOKEN }}

The CLI auto-detects whether it's running locally (against the wrangler --local D1 file) or remotely (when CLOUDFLARE_* env vars are present). No extra flags.

For destructive steps in CI — migrate:fresh, migrate:rollback — gate behind an explicit if: github.event_name == 'workflow_dispatch' step so PR runs can't trash production.

Safety rules (recap)

• IDs are TEXT/string (UUIDs) — never integer autoincrement.
• If softDeletes=true is set on the model, ensure deleted_at exists on the table.
• Don't add deleted_at to pivot tables (causes ambiguous-column errors on joins).
• Use upsert: true instead of INSERT OR REPLACE for FK-safe writes.

Full reference: see the @orphnet/d1-eloquent README.