Writing Migrations: Safe Schema Changes

Create a free account to save your progress

Earn XP, track streaks, and sync your dashboard across devices.

Lesson

You know why migrations exist, now let's look at how to actually write good ones. A well-crafted migrationWhat is migration?A versioned script that changes your database structure (add a column, create a table) so every developer and server stays in sync. is small, reversible, and safe to run in production at any time. A poorly written one can take down your app or destroy data you can never get back.

MigrationWhat is migration?A versioned script that changes your database structure (add a column, create a table) so every developer and server stays in sync. file naming

The filename is not just a label, it controls the order migrations run. Tools sort files alphabetically, so timestamps at the front guarantee the right sequence.

20240115120000_create_users_table.sql
└─────┬─────┘ └──────────┬──────────┘
  Timestamp           Description
 (YYYYMMDDHHMMSS)

If two developers create migrations at the same second, you will get a conflict. The solution is simple: the second developer renames their file with a slightly later timestamp before committing.

The file format

Every migration file follows the same pattern, a comment header, then the up block, then the down block:

-- Migration: Add avatar column to users
-- Created: 2024-01-15

-- Up Migration
ALTER TABLE users ADD COLUMN avatar TEXT;

-- Down Migration
ALTER TABLE users DROP COLUMN avatar;

Principles of a good migrationWhat is migration?A versioned script that changes your database structure (add a column, create a table) so every developer and server stays in sync.

Keep it small and focused

One migration should do one logical thing. If you need to add a column and create a new table, write two separate migrations. This makes rollbacks surgical, you can undo one change without undoing the other.

-- Bad: Multiple unrelated changes in one migration
ALTER TABLE users ADD COLUMN avatar TEXT;
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
ALTER TABLE comments ADD COLUMN likes INTEGER;

-- Good: One migration per change
-- 001_add_avatar_to_users.sql
ALTER TABLE users ADD COLUMN avatar TEXT;

-- 002_create_posts_table.sql
CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);

-- 003_add_likes_to_comments.sql
ALTER TABLE comments ADD COLUMN likes INTEGER;

Always write the down migration

It is tempting to skip the rollbackWhat is rollback?Undoing a database migration or deployment to restore the previous state when something goes wrong. script when you are confident the migration is correct. Resist that temptation. You will not always be the one running the rollback, and "I forgot to write it" is not an acceptable reason to be stuck.

-- Good: reversible
-- Up
ALTER TABLE users ADD COLUMN bio TEXT;
-- Down
ALTER TABLE users DROP COLUMN bio;

-- Bad: the down migration causes more damage than the up
-- Up
DROP TABLE old_users;
-- Down
-- ??? You cannot recreate data that was deleted

Make changes safe for a running app

Dropping a column while your app code still reads from it will cause immediate crashes. The safe pattern is to make changes in stages:

-- Bad: drops column while app might still use it
ALTER TABLE users DROP COLUMN old_field;

-- Good: make it nullable first (app won't crash if the value is missing)
ALTER TABLE users ALTER COLUMN old_field DROP NOT NULL;
-- Then deploy new code that stops using old_field
-- Then drop the column in a later migration
ALTER TABLE users DROP COLUMN old_field;

Common migrationWhat is migration?A versioned script that changes your database structure (add a column, create a table) so every developer and server stays in sync. patterns

Pattern	SQL example
Add a column with a default	`ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active' NOT NULL;`
Rename a column (PostgreSQL)	`ALTER TABLE users RENAME COLUMN email TO email_address;`
Add a foreign key	`ALTER TABLE posts ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);`
Add an index	`CREATE INDEX idx_users_email ON users(email);`
Seed reference data	`INSERT INTO roles (name) VALUES ('admin'), ('user');`

Changing a column type

This one needs extra care because you may need to convert existing data:

-- PostgreSQL: cast existing values during the change
ALTER TABLE users
  ALTER COLUMN age TYPE INTEGER USING age::INTEGER;

-- SQLite: no direct type change, so recreate the table
CREATE TABLE users_new (
  id INTEGER PRIMARY KEY,
  age INTEGER  -- previously TEXT
);
INSERT INTO users_new SELECT id, CAST(age AS INTEGER) FROM users;
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;

Data migrations, transforming existing rows

Sometimes you need to move data around, not just change the schemaWhat is schema?A formal definition of the structure your data must follow - which fields exist, what types they have, and which are required.. Keep data migrations in their own file, separate from schema changes, so each part can be rolled back independently.

-- Split full_name into first_name and last_name

-- Up
-- Step 1: add the new columns
ALTER TABLE users ADD COLUMN first_name TEXT;
ALTER TABLE users ADD COLUMN last_name TEXT;

-- Step 2: backfill from existing data
UPDATE users
SET
  first_name = SPLIT_PART(full_name, ' ', 1),
  last_name  = SPLIT_PART(full_name, ' ', 2)
WHERE full_name IS NOT NULL;

-- Step 3: drop the old column goes in a SEPARATE migration
-- after you have deployed and confirmed the new columns work

-- Down
ALTER TABLE users DROP COLUMN first_name;
ALTER TABLE users DROP COLUMN last_name;

Do not drop full_name in the same migration that adds the new columns. Deploy and monitor first. If something is wrong, rolling back only the data migration is much safer than rolling back both at once.

Using Knex.js

Knex is a popular Node.js query builderWhat is query builder?A library that lets you construct SQL queries by chaining method calls in code, giving more control than an ORM but less boilerplate than raw SQL. that includes a full migrationWhat is migration?A versioned script that changes your database structure (add a column, create a table) so every developer and server stays in sync. system.

# Create a new migration file
npx knex migrate:make create_users_table
# Creates: migrations/20240115120000_create_users_table.js

// migrations/20240115120000_create_users_table.js

exports.up = async function(knex) {
  await knex.schema.createTable('users', table => {
    table.increments('id').primary();
    table.string('email', 255).notNullable().unique();
    table.string('username', 50).notNullable().unique();
    table.text('bio').nullable();
    table.timestamps(true, true); // adds created_at and updated_at
  });
};

exports.down = async function(knex) {
  await knex.schema.dropTable('users');
};

npx knex migrate:latest    # Run pending migrations
npx knex migrate:rollback  # Undo last batch
npx knex migrate:status    # See what has run

Using Prisma Migrate

Prisma takes a different approach: you edit a schemaWhat is schema?A formal definition of the structure your data must follow - which fields exist, what types they have, and which are required. file and Prisma generates the SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. migrationWhat is migration?A versioned script that changes your database structure (add a column, create a table) so every developer and server stays in sync. for you.

# 1. Edit prisma/schema.prisma to add or change a model
# 2. Generate and apply the migration
npx prisma migrate dev --name create_users_table

Prisma creates a file like migrations/20240115120000_create_users_table/migration.sql with the SQL it derived from your schema diffWhat is diff?A comparison showing exactly which lines were added, removed, or changed between two versions of code.. You still commitWhat is commit?A permanent snapshot of your staged changes saved in Git's history, identified by a unique hash and accompanied by a message describing what changed. this file to git, it is your migration history.

Testing a migrationWhat is migration?A versioned script that changes your database structure (add a column, create a table) so every developer and server stays in sync. before you commitWhat is commit?A permanent snapshot of your staged changes saved in Git's history, identified by a unique hash and accompanied by a message describing what changed.

# 1. Apply it
npm run migrate

# 2. Inspect the result
psql -d myapp -c "\d users"

# 3. Run your app and check nothing is broken
npm run dev

# 4. Test the rollback
npm run migrate:rollback

# 5. Confirm the schema reverted
psql -d myapp -c "\d users"

# 6. Re-apply and commit
npm run migrate
git add migrations/
git commit -m "Add avatar column to users"

Quick reference

Tool	Create	Run	Rollback	Status
Knex	`npx knex migrate:make <name>`	`npx knex migrate:latest`	`npx knex migrate:rollback`	`npx knex migrate:status`
Prisma	`npx prisma migrate dev --name <name>`	(same command)	`npx prisma migrate reset`	`npx prisma migrate status`
Raw SQL	Create file manually	`npm run migrate`	`npm run migrate:rollback`	`npm run migrate:status`

Done

Complete & Next