• Home
  • Writing Migrations

Writing Migrations

Write forward and rollback SQL migrations

Each migration is a timestamped directory with forward and rollback SQL.

prisma/migrations/
└── 20240101000000_add_users/
    ├── migration.sql
    └── down.sql

The directory name must match <numeric-id>_<name>. IDs must be unique.

Forward SQL

Put only forward changes in migration.sql.

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_created_at ON users(created_at);

Rollback SQL

Put the inverse operation in down.sql.

DROP INDEX IF EXISTS idx_users_created_at;
DROP TABLE IF EXISTS users;

Rollback fails before changing history when down.sql is missing or empty. This prevents an irreversible migration from being marked as rolled back.

Common Patterns

Add Columns

migration.sql:

ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

down.sql:

ALTER TABLE users DROP COLUMN last_login;
ALTER TABLE users DROP COLUMN is_active;

Data Migration

migration.sql:

INSERT INTO roles (name) VALUES ('admin'), ('user'), ('guest');

down.sql:

DELETE FROM roles WHERE name IN ('admin', 'user', 'guest');

Rename a Column

migration.sql:

ALTER TABLE users RENAME COLUMN old_email TO email;

down.sql:

ALTER TABLE users RENAME COLUMN email TO old_email;

Legacy Combined Files

Older releases stored both directions in one migration.sql with these markers:

  • -- Migration: Up
  • -- Migration: Down

The library still reads that format so existing projects can roll back.

Do not run prisma migrate deploy while combined files remain. Native Prisma treats the whole file as forward SQL and can execute the rollback section. The prisma-migrations deploy wrapper detects these files and stops first.

Convert a legacy migration by keeping the Up section in migration.sql, moving the Down section to down.sql, and removing both markers.

Transactions

Each migration runs in a Prisma transaction. DDL differs by provider:

  • PostgreSQL and SQLite can roll back most schema changes.
  • MySQL implicitly commits many DDL statements.

MySQL can leave partial schema changes after a failure. Inspect the database before retrying. Test both directions against the production provider.

Applied Migrations

Do not edit an applied migration.sql. Its checksum is stored in _prisma_migrations, and later commands reject modified history. Create a new migration for corrective changes.

Commands

npx prisma-migrations create add_users
npx prisma-migrations up
npx prisma-migrations down
npx prisma-migrations status

Next Steps