Database Migrations

Database migrations (also known as schema migrations) are a way to manage incremental, reversible changes to relational database schemas. They act as version control for your database.

1. The Problem: Schema Drift

In software development, application code is versioned using Git. However, the database schema (tables, columns, indexes) also changes over time.

Scenario: Developer A adds a phone_number column to the Users table locally.
Deployment: If Developer A pushes the code but forgets to tell the Ops team to run the ALTER TABLE command on production, the app crashes.
Teamwork: Developer B pulls the code but their local database lacks the new column.

2. The Solution: Migrations

Migrations allow you to define changes to your database schema in code files. These files are committed to version control along with your application code.

How it Works

Migration Files: You create a file (e.g., 20231027_create_users_table.sql or .js) that defines the change.
Up and Down:
- Up: The logic to apply the change (e.g., CREATE TABLE).
- Down: The logic to revert the change (e.g., DROP TABLE).
Tracking Table: The migration tool creates a special table in your database (often called migrations or schema_version) to track which migration files have already been executed.
Execution: When you run the migration command (e.g., npm run migrate), the tool checks the tracking table and runs only the new migration files.

3. Example (SQL / Pseudo-code)

Migration File: 001_create_users.sql

-- UP
CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL
);

-- DOWN
DROP TABLE Users;

Migration File: 002_add_email.sql

-- UP
ALTER TABLE Users ADD COLUMN email VARCHAR(255);

-- DOWN
ALTER TABLE Users DROP COLUMN email;

4. Benefits

Consistency: Ensures all environments (Dev, Staging, Prod) have the exact same database structure.
Automation: Database changes are applied automatically during the CI/CD deployment process.
Rollbacks: If a deployment fails, you can run the "Down" migrations to revert the database to its previous state.

5. Common Tools

Language Agnostic: Flyway, Liquibase.
Node.js: Knex.js, TypeORM, Sequelize, Prisma.
Python: Alembic (SQLAlchemy), Django Migrations.
Ruby: Active Record Migrations (Rails).
Go: Golang-Migrate.

6. Best Practices

Never Edit Old Migrations: Once a migration has been committed and shared/deployed, treat it as immutable. If you need to fix something, create a new migration to fix it.
Backwards Compatibility: When deploying to production with zero downtime, ensure your database changes don't break the currently running version of the code (e.g., add a column as nullable first, populate it, then make it not null in a later deployment).

programming/database-basics programming/ci-cd-pipelines programming/git-version-control programming/devops

Created: March 17, 2026 • Last Modified: January 31, 2026

Docs Login