How to Run Database Migrations in Paperclip Development

Paperclip automatically applies database migrations when you start the dev server with the embedded PostgreSQL mode, or you can run pnpm db:migrate explicitly for local Docker or hosted PostgreSQL setups.

Running database migrations in Paperclip depends on which database mode you're using for development. The project uses PostgreSQL with the Drizzle ORM, and the paperclipai/paperclip repository provides multiple paths depending on whether you want zero-config embedded mode or external database control.

Automatic Migrations in Embedded PostgreSQL Mode

By default, Paperclip uses an embedded PostgreSQL instance that requires no manual migration management.

When you run pnpm dev or pnpm dev:once without setting DATABASE_URL, Paperclip:

• Creates ~/.paperclip/instances/default/db/ if it doesn't exist
• Starts an embedded PostgreSQL process
• Automatically applies any pending migrations on first startup according to the migration journal

This mode is fully idempotent—restarting the server multiple times won't cause schema drift, as migrations are only applied if not yet recorded in the drizzle_migrations table.

# Start dev server with automatic embedded DB and migrations

pnpm dev

# Same, without file watching (good for CI or one-off runs)

pnpm dev:once

Manual Migrations for External PostgreSQL

When using local Docker PostgreSQL or hosted PostgreSQL (e.g., Supabase), you must run migrations explicitly.

Local Docker Setup

First configure your connection and start the database:

# Set DATABASE_URL as shown in .env.example

export DATABASE_URL=postgres://paperclip:paperclip@localhost:5432/paperclip

# Start PostgreSQL container

docker compose up -d

# Apply migrations manually

pnpm db:migrate

Hosted PostgreSQL Setup

For production-like environments or remote databases:

# Export connection string to DATABASE_URL or DATABASE_MIGRATION_URL for schema changes

export DATABASE_URL=postgresql://user:password@host.supabase.co:5432/paperclip

# Run migrations explicitly

pnpm db:migrate

The DATABASE_MIGRATION_URL variant in doc/DATABASE.md allows separate credentials with DDL permissions for schema changes.

Essential Migration Commands

Command	Purpose
pnpm dev	Starts API and UI with auto-migrations in embedded mode
pnpm dev:once	One-shot dev server with auto-migrations
pnpm db:generate	Compiles packages/db and generates new migration from Drizzle schema changes
pnpm db:migrate	Explicitly runs all unapplied migrations against current database
pnpm issue-references:backfill	Back-fills data after migration adds new reference tables

Generating New Migrations

When you modify the Drizzle schema in packages/db/src/schema/**, you must generate and apply migrations:

# 1. Generate migration file from updated schema

pnpm db:generate

# 2. Apply to current database

pnpm db:migrate

The pnpm db:generate command first compiles the packages/db workspace, then outputs SQL migration scripts to packages/db/src/migrations/*.sql.

Back-Filling Data After Migrations

Some migrations create new tables or indexes without populating them. Use the backfill command:

# Back-fill all issue references

pnpm issue-references:backfill

# Or limit to specific company

pnpm issue-references:backfill -- --company <company-id>

This is documented in doc/DATABASE.md as a post-migration step for reference table changes.

When to Run Migrations Manually

Even in embedded mode, explicit migration control is needed for:

• New schema changes — After pnpm db:generate, run pnpm db:migrate to materialize changes
• Database mode switches — Moving from embedded to Docker/hosted requires pnpm db:migrate with new DATABASE_URL
• Team synchronization — Pulling migration files from git may require explicit application

How Migrations Work Under the Hood

According to the Paperclip source code:

1. Drizzle migration journal — Tracks applied migrations in the drizzle_migrations table
2. Embedded startup flow — In README.md, the server detects missing DATABASE_URL, boots embedded PostgreSQL, ensures the paperclip database exists, and runs pending migrations automatically
3. Idempotent application — The migration system checks the journal before applying any SQL script from packages/db/src/migrations/*.sql

Summary

• Embedded mode (DATABASE_URL unset): migrations run automatically with pnpm dev or pnpm dev:once
• External databases: use pnpm db:migrate after configuring DATABASE_URL
• New schema changes: run pnpm db:generate then pnpm db:migrate
• Data back-fills: use pnpm issue-references:backfill after reference table migrations

Frequently Asked Questions

What happens if I forget to run migrations in embedded mode?

Nothing—migrations apply automatically when the dev server starts. The embedded PostgreSQL workflow is designed so you rarely interact with migrations directly.

Can I use the embedded database for production?

No. The embedded mode is development-only, storing data in ~/.paperclip/instances/default/db/. Production deployments require external PostgreSQL with explicit DATABASE_URL configuration.

What's the difference between pnpm dev and pnpm db:migrate?

pnpm dev starts the full development environment with automatic migrations in embedded mode. pnpm db:migrate is an explicit command that only runs migrations against whichever database is currently configured, useful for external PostgreSQL setups or after generating new migrations.

How do I know if my migrations are up to date?

Check the drizzle_migrations table in your database, which Drizzle maintains automatically. In embedded mode, restarting pnpm dev will apply any pending migrations and log the results. For external databases, pnpm db:migrate is idempotent and safe to run repeatedly—it only applies unrecorded migrations.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}