Database

This project uses Drizzle ORM with Cloudflare D1 (SQLite-based). Drizzle provides type-safe queries while D1 gives us a serverless SQL database at the edge.

Architecture

graph TB
    subgraph Local
        DEV[bun dev] --> MF[MiniFlare D1]
        MF --> LOCAL_DB[(Local SQLite)]
    end

    subgraph Staging
        STG_WORKER[affirm-staging Worker] --> STG_D1[D1 Binding: DB]
        STG_D1 --> STG_DB[(affirm-staging D1)]
    end

    subgraph Production
        PROD_WORKER[affirm Worker] --> PROD_D1[D1 Binding: DB]
        PROD_D1 --> PROD_DB[(affirm D1)]
    end

    DEV -.->|"db:migrate"| LOCAL_DB
    CI_STG[staging branch push] -.->|"db:migrate:staging"| STG_DB
    CI_PROD[main branch push] -.->|"db:migrate:prod"| PROD_DB

Loading

Migration Lifecycle

flowchart TB
    A[Edit schema.ts] --> B[db:generate]
    B --> C[.sql migration file]
    C --> D[db:migrate]
    D --> E{Test locally}
    E -->|Pass| F[Push to staging branch]
    F --> G[CI: db:migrate:staging]
    G --> H{Test staging}
    H -->|Pass| I[PR to main]
    I --> J[CI: db:migrate:prod]

Loading

Database Commands

Command	What it does
bun run db:generate	Compare schema to snapshots, produce .sql migration
bun run db:migrate	Apply migrations to local MiniFlare D1
bun run db:migrate:staging	Apply migrations to staging D1 (remote)
bun run db:migrate:prod	Apply migrations to production D1 (remote)
bun run db:push:staging	Push schema directly to staging (no migration file)
bun run db:push:prod	Push schema directly to production (no migration file)
bun run db:studio:staging	Open Drizzle Studio for staging database
bun run db:studio:prod	Open Drizzle Studio for production database

Warning

db:push:* bypasses the migration system and pushes schema changes directly. Use it for prototyping only — never in production workflows. Always use db:generate + db:migrate:* for production changes.

Creating a Migration

1. Edit the schema in server/database/schema.ts

2. Generate the migration SQL:

   bun run db:generate

3. Review the generated .sql file in server/database/migrations/

4. Apply locally and test:

   bun run db:migrate
   bun dev

5. Commit the migration file alongside the schema change

Using the Database in Server Routes

useDB(event) is auto-imported in all server routes — no import statement needed:

// server/api/users.get.ts
export default defineEventHandler(async (event) => {
  const db = useDB(event);

  // Relational query API
  const allUsers = await db.query.users.findMany();

  // Or SQL-like builder
  // import { users } from '../database/schema';
  // const allUsers = await db.select().from(users);

  return allUsers;
});

Drizzle Studio

Drizzle Studio provides a web UI to browse and edit your database. Requires .env credentials.

Using the secrets repo, dump secrets.yaml out to your terminal.

sops decrypt secrets.yaml

It will look something like this:

affirm:
    cloudflare:
        account_id: x
        d1:
            database_ids:
                production: x
                staging: x
            api_token: x

Then copy .env.example to .env. Edit `.env and fill in the variables from the secrets:

# environment variables go here. this file is also used as .dev.vars(.example).
# fetch these values from [`tecapps/secrets`](https://github.com/tecapps/secrets)
# and save them to .env before running the app or using any tooling.
# .env is gitignored.

CLOUDFLARE_ACCOUNT_ID=
CLOUDFLARE_PRODUCTION_DATABASE_ID=
CLOUDFLARE_STAGING_DATABASE_ID=
CLOUDFLARE_D1_TOKEN=

Once you have these values filled in, you can use the Drizzle studio.

# Browse staging data
bun run db:studio:staging

# Browse production data
bun run db:studio:prod

Troubleshooting

"No migrations to apply" — The migration may already be applied. Check the d1_migrations table in the target database.

"env.DB is undefined" — Ensure nitro-cloudflare-dev is in nuxt.config.ts modules. This module provides D1 bindings during local development.

Type errors on event.context.cloudflare — Run bun run types to regenerate worker-configuration.d.ts.

drizzle-kit studio/push fails — These commands use the HTTP API and need CLOUDFLARE_ACCOUNT_ID, CLOUDFLARE_DATABASE_ID, and CLOUDFLARE_D1_TOKEN in .env.

GitHub Secrets

The CI/CD workflows require these secrets in the GitHub repository settings:

Secret	Purpose
CLOUDFLARE_API_TOKEN	Wrangler authentication for deploys and migrations
CLOUDFLARE_ACCOUNT_ID	Cloudflare account identifier

These are used by both the staging.yaml and ci.yaml (deploy-production) workflows.