Documentation

Migrations

When to use pnpm db:generate, pnpm db:migrate, and pnpm db:studio in Motoko Base.

Motoko Base uses Drizzle Kit to manage database migrations. Schema is defined in TypeScript (src/lib/db/schema/); Drizzle generates SQL migration files and applies them to your Postgres database.

All three commands are defined in package.json:

"db:generate": "drizzle-kit generate",
"db:migrate": "drizzle-kit migrate",
"db:studio": "drizzle-kit studio"

Configuration lives in drizzle.config.ts:

export default defineConfig({
  schema: "./src/lib/db/schema/schema.ts",
  out: "./drizzle/migrations",
  dialect: "postgresql",
  dbCredentials: {
    url: process.env.DATABASE_URL!,
  },
});

DATABASE_URL must be set in .env before running any command. For Supabase, use the direct connection (port 5432) if migrations fail through the transaction pooler.

`pnpm db:generate`

Generates a new migration file from differences between your TypeScript schema and the last migration snapshot.

When to use

After adding or editing a table in src/lib/db/schema/
After adding columns, indexes, foreign keys, or constraints
After exporting a new schema file from schema.ts

When not to use

To apply migrations — use db:migrate instead
To browse data — use db:studio instead
On a fresh clone with no schema changes — there is nothing new to generate

What it does

Reads src/lib/db/schema/schema.ts and all exported tables
Compares against the latest snapshot in drizzle/migrations/
Creates a new folder under drizzle/migrations/ with:
- migration.sql — the SQL to run
- snapshot.json — schema state for the next diff

Example

You added a notes table to the schema:

pnpm db:generate

Output (example):

drizzle/migrations/20260824120000_some_name/migration.sql

Review the generated SQL before migrating. Drizzle Kit is accurate for most changes, but always check destructive operations (drops, renames).

`pnpm db:migrate`

Applies pending migrations to the database connected via DATABASE_URL.

When to use

After pnpm db:generate — to apply your new migration
After pulling from git when a teammate (or upstream) added migrations
On a fresh database — to create all tables from scratch
During local setup — right after cloning (see Installation)

When not to use

When you changed the schema but have not run db:generate yet — generate first
To inspect data — use db:studio

What it does

Connects to Postgres using DATABASE_URL
Runs any migration SQL files not yet recorded in the database migration journal
Updates the journal so the same migration is not applied twice

Example

pnpm db:migrate

Run this after every schema change workflow:

pnpm db:generate && pnpm db:migrate

On CI or production deploy, run pnpm db:migrate as part of your release step (after env vars are available).

Troubleshooting

Problem	Fix
DDL fails via pooler	Point `DATABASE_URL` at Supabase direct connection (port `5432`), migrate, switch back
`DATABASE_URL` not set	Add it to `.env`; Drizzle Kit loads via `dotenv/config`
Migration already applied	Normal on re-run — Drizzle skips completed migrations
Generated SQL looks wrong	Edit schema, delete the bad migration folder, re-run `db:generate` (only before applying)

`pnpm db:studio`

Opens Drizzle Studio — a local web UI to browse tables, view rows, and run ad-hoc queries.

When to use

Inspect data during development (users, links, files, sessions)
Debug whether a migration created the expected columns
Verify test sign-ups wrote the correct rows
Explore unfamiliar tables after pulling new migrations

When not to use

To change schema — edit TypeScript schema files and run db:generate + db:migrate
In production as a primary admin tool — use Supabase Dashboard or a dedicated admin for production data
As a substitute for proper seed scripts — Studio is for inspection, not repeatable setup

What it does

Starts a local server (Drizzle Kit prints the URL, typically https://local.drizzle.studio). It connects using DATABASE_URL from .env.

Example

pnpm db:studio

Keep the terminal open while using Studio. Stop with Ctrl+C when done.

Typical workflows

First-time local setup

cp .env.example .env
# fill in DATABASE_URL
pnpm db:migrate
pnpm dev

No db:generate needed — migrations already ship with the repo.

You changed the schema

# 1. Edit src/lib/db/schema/*.ts and export from schema.ts
pnpm db:generate
pnpm db:migrate
pnpm db:studio   # optional — verify the new table

You pulled new migrations from git

pnpm db:migrate

You want to inspect data without changing anything

pnpm db:studio

Migration files in the repo

Applied migrations live in drizzle/migrations/. Each folder contains:

migration.sql — SQL executed by db:migrate
snapshot.json — internal schema snapshot for the next db:generate diff

Commit migration files to git. Teammates and production deploys rely on the same ordered SQL history.

Do not hand-edit applied migrations in production. If you need to fix a mistake before anyone has migrated, delete the unapplied migration folder and regenerate. If already applied, create a new corrective migration.

Next steps

Adding a New Table — Full tutorial: schema → export → generate → migrate → queries.

Database — Architecture overview, schema conventions, and query patterns.