Writing Migrations

Learn how to write effective database migrations with Prisma Migrations

This guide covers how to write migrations using SQL with up/down sections.

Migration File Structure

Each migration is stored in its own directory with a migration.sql file:

prisma/migrations/
└── [timestamp]_migration_name/
    └── migration.sql

The timestamp format is the same as Prisma’s default: 20240101000000_migration_name

Basic Migration Format

Each SQL file contains two sections separated by marker comments:

-- Migration: Up
-- This section runs when you do: npx prisma-migrations up
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

-- Migration: Down
-- This section runs when you do: npx prisma-migrations down
DROP TABLE IF EXISTS users;

Important: The marker comments must be exactly:

-- Migration: Up
-- Migration: Down

Common Migration Patterns

Creating Tables

-- Migration: Up
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  author_id INTEGER REFERENCES users(id),
  published BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_posts_author_id ON posts(author_id);
CREATE INDEX idx_posts_published ON posts(published);

-- Migration: Down
DROP TABLE IF EXISTS posts;

Adding Columns

-- Migration: Up
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT true;

-- Migration: Down
ALTER TABLE users DROP COLUMN last_login;
ALTER TABLE users DROP COLUMN is_active;

Modifying Columns

-- Migration: Up
ALTER TABLE users ALTER COLUMN email TYPE TEXT;
ALTER TABLE users ALTER COLUMN name SET NOT NULL;
ALTER TABLE users ALTER COLUMN role SET DEFAULT 'member';

-- Migration: Down
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);
ALTER TABLE users ALTER COLUMN name DROP NOT NULL;
ALTER TABLE users ALTER COLUMN role DROP DEFAULT;

Data Migrations

-- Migration: Up
INSERT INTO roles (name, permissions) VALUES
  ('admin', '{"all": true}'),
  ('user', '{"read": true}'),
  ('guest', '{"read": true, "write": false}');

-- Migration: Down
DELETE FROM roles WHERE name IN ('admin', 'user', 'guest');

Creating Indexes

-- Migration: Up
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE UNIQUE INDEX idx_users_username ON users(username);

-- Migration: Down
DROP INDEX IF EXISTS idx_users_email;
DROP INDEX IF EXISTS idx_users_created_at;
DROP INDEX IF EXISTS idx_users_username;

Renaming Columns

-- Migration: Up
ALTER TABLE users RENAME COLUMN old_email TO email;

-- Migration: Down
ALTER TABLE users RENAME COLUMN email TO old_email;

Best Practices

1. Always Write Down Migrations

Every Up section should have a corresponding Down that reverses it:

-- ✓ Good
-- Migration: Up
CREATE TABLE foo (id SERIAL PRIMARY KEY);

-- Migration: Down
DROP TABLE IF EXISTS foo;

-- ✗ Bad - Empty down section
-- Migration: Up
CREATE TABLE foo (id SERIAL PRIMARY KEY);

-- Migration: Down
-- Nothing here!

2. Use Descriptive Migration Names

# ✓ Good
npx prisma-migrations create add_user_email_verification
npx prisma-migrations create add_posts_published_at_column

# ✗ Bad
npx prisma-migrations create update
npx prisma-migrations create changes

3. Keep Migrations Focused

Each migration should do one logical thing:

# ✓ Good - Separate migrations
npx prisma-migrations create add_users_table
npx prisma-migrations create add_posts_table
npx prisma-migrations create add_users_last_login

# ✗ Bad - Too much in one migration
npx prisma-migrations create add_all_tables_and_columns

4. Test Both Directions

Always test both up and down:

# Test up
npx prisma-migrations up

# Verify changes
psql -d mydb -c "\d users"

# Test down
npx prisma-migrations down

# Verify rollback worked
psql -d mydb -c "\d users"

# Test up again
npx prisma-migrations up

5. Use IF EXISTS/IF NOT EXISTS

Make migrations more resilient:

-- Migration: Up
CREATE TABLE IF NOT EXISTS users (
  id SERIAL PRIMARY KEY
);

-- Migration: Down
DROP TABLE IF EXISTS users;

Multi-Step Data Migrations

When you need to transform data, break it into steps:

-- Migration: Up
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);

-- Step 2: Populate from existing data
UPDATE users SET full_name = first_name || ' ' || last_name;

-- Step 3: Make it required
ALTER TABLE users ALTER COLUMN full_name SET NOT NULL;

-- Step 4: Drop old columns
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

-- Migration: Down
-- Reverse all steps
ALTER TABLE users ADD COLUMN first_name VARCHAR(255);
ALTER TABLE users ADD COLUMN last_name VARCHAR(255);

UPDATE users
SET first_name = split_part(full_name, ' ', 1),
    last_name = split_part(full_name, ' ', 2);

ALTER TABLE users DROP COLUMN full_name;

Database-Specific Features

PostgreSQL

JSON columns:

-- Migration: Up
ALTER TABLE users ADD COLUMN metadata JSONB DEFAULT '{}';
CREATE INDEX idx_users_metadata ON users USING GIN(metadata);

-- Migration: Down
DROP INDEX IF EXISTS idx_users_metadata;
ALTER TABLE users DROP COLUMN metadata;

Arrays:

-- Migration: Up
ALTER TABLE users ADD COLUMN tags TEXT[] DEFAULT ARRAY[]::TEXT[];

-- Migration: Down
ALTER TABLE users DROP COLUMN tags;

Full-text search:

-- Migration: Up
ALTER TABLE posts ADD COLUMN search_vector TSVECTOR;
CREATE INDEX idx_posts_search ON posts USING GIN(search_vector);

UPDATE posts SET search_vector = to_tsvector('english', title || ' ' || content);

-- Migration: Down
DROP INDEX IF EXISTS idx_posts_search;
ALTER TABLE posts DROP COLUMN search_vector;

MySQL

JSON columns:

-- Migration: Up
ALTER TABLE users ADD COLUMN metadata JSON;

-- Migration: Down
ALTER TABLE users DROP COLUMN metadata;

Fulltext indexes:

-- Migration: Up
ALTER TABLE posts ADD FULLTEXT INDEX idx_posts_content (content);

-- Migration: Down
DROP INDEX idx_posts_content ON posts;

SQLite

Adding columns (SQLite limitations):

-- Migration: Up
ALTER TABLE users ADD COLUMN email TEXT;

-- Migration: Down
-- Note: SQLite doesn't support DROP COLUMN easily
-- You'd need to recreate the table without the column
CREATE TABLE users_backup AS SELECT id, name FROM users;
DROP TABLE users;
ALTER TABLE users_backup RENAME TO users;

Handling Schema and Data Together

Sometimes you need to mix schema changes with data migrations:

-- Migration: Up
-- 1. Add new column (nullable first)
ALTER TABLE users ADD COLUMN status VARCHAR(50);

-- 2. Set default for existing rows
UPDATE users SET status = 'active' WHERE is_active = true;
UPDATE users SET status = 'inactive' WHERE is_active = false;

-- 3. Make it required
ALTER TABLE users ALTER COLUMN status SET NOT NULL;

-- 4. Drop old column
ALTER TABLE users DROP COLUMN is_active;

-- Migration: Down
ALTER TABLE users ADD COLUMN is_active BOOLEAN;
UPDATE users SET is_active = (status = 'active');
ALTER TABLE users DROP COLUMN status;

Troubleshooting

Migration Failed Mid-Way

If a migration fails partway through:

Check your database state manually
Fix the SQL in the migration file
Either:
- Clean up partial changes manually and re-run
- Write a new migration to continue from current state

Testing Rollbacks

Always test your down migrations in a staging environment first:

# In staging
npx prisma-migrations up
# Test the app
npx prisma-migrations down
# Verify rollback worked
npx prisma-migrations up
# Test again

Syntax Errors

If you get SQL syntax errors:

Check your SQL syntax for your specific database (PostgreSQL, MySQL, SQLite)
Test the SQL directly in your database client first
Make sure marker comments are exact: -- Migration: Up and -- Migration: Down

Reversing Complex Migrations

Some operations are hard to reverse:

Dropping columns (data loss)
Complex data transformations

For these, consider:

Backing up data before dropping
Documenting manual steps in comments
Testing extensively in staging

Next Steps

Installation & Setup - Get started with Prisma Migrations
CLI Commands - Learn all available commands
Code Labs - Step-by-step tutorials