# How to Run Database Migrations in Paperclip Development

> Learn to run database migrations in Paperclip development. Automate with dev server start or use pnpm db:migrate for local Docker or hosted PostgreSQL.

- Repository: [Paperclip/paperclip](https://github.com/paperclipai/paperclip)
- Tags: how-to-guide
- Published: 2026-08-14

---

**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.

```bash

# 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:

```bash

# 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:

```bash

# 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`](https://github.com/paperclipai/paperclip/blob/main/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:

```bash

# 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:

```bash

# 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`](https://github.com/paperclipai/paperclip/blob/main/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`](https://github.com/paperclipai/paperclip/blob/main/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.