Database Migrations Application Code

As applications evolve, so do their data requirements. Database migrations provide a structured way to evolve

your database schema alongside your application code. They're essential for maintaining data integrity while

allowing your application to grow and change over time.

In this lesson, we'll explore how to implement and manage database migrations directly within your application code,

using popular frameworks and tools across different programming ecosystems. You'll learn about migration strategies,

best practices, and patterns for safely evolving your database schema without data loss or downtime.

Database migrations are programmatic changes to a database schema that transform it from one state to another.

Think of them as version control for your database structure—just as Git tracks changes to your code,

migrations track changes to your database schema.

Migration Scripts

: Code that defines the changes to be applied

Migration Registry/History

: Tracks which migrations have been applied

Migration Runner

: Executes the migrations in the correct order

Rollback Functionality

: Ability to undo migrations when needed

Migrations typically involve operations like:

Creating, altering, or dropping tables

Adding, modifying, or removing columns

Creating or changing indexes

Adding constraints or foreign keys

Seeding data or transforming existing data

Different programming languages and frameworks have their own migration tools, but they all share similar principles.

Let's explore some popular migration frameworks across major ecosystems:

: SQL query builder with migration support

: ORM with migrations through CLI

: TypeScript ORM with migration generation

Prisma Migrate

: Declarative migrations from schema

node-pg-migrate

: PostgreSQL-focused migration tool

: SQLAlchemy's migration companion

Django Migrations

: Built into Django framework

SQLAlchemy-Migrate

: Older migration tool for SQLAlchemy

Yoyo-Migrations

: Standalone Python migration tool

Peewee-Migrate

: Migrations for Peewee ORM

Laravel Migrations

: Built into Laravel framework

: PHP database migrations framework

Doctrine Migrations

: For Doctrine ORM

: Java-based database migration tool

: Open source database change management

Let's explore how to implement migrations using two popular Node.js tools: Knex.js and Prisma.

Knex.js is a SQL query builder that provides a flexible migration system. Here's how to use it:

1. Set up Knex.js
2. Initialize migrations
3. Write migration
4. Run migrations

🔍 Knex.js Migration Pattern

Note how each migration has both an

function (to apply changes) and a

function

(to revert changes). This pattern is common across many migration frameworks and enables bidirectional migrations.

1# Install Knex and database driver
2npm install knex pg
3npm install -g knex  # Install CLI globally (optional)

1knex init  # Creates knexfile.js
2knex migrate:make create_users_table

1// migrations/20251020120000_create_users_table.js
2exports.up = function(knex) {
3  return knex.schema.createTable('users', table => {
4    table.increments('id').primary();
5    table.string('username').notNullable().unique();
6    table.string('email').notNullable().unique();
7    table.string('password_hash').notNullable();
8    table.boolean('is_active').defaultTo(true);
9    table.timestamps(true, true);
10  });
11};
12
13exports.down = function(knex) {
14  return knex.schema.dropTable('users');
15};

1knex migrate:latest  # Apply all pending migrations
2knex migrate:rollback  # Rollback the last batch of migrations

Prisma takes a different approach to migrations with a declarative schema-first approach:

1. Set up Prisma
2. Define your schema
3. Create and apply migration

🔍 Prisma's Declarative Approach

With Prisma, you define your target schema, and Prisma generates the necessary migration

scripts to transition from your current schema to the target. This is different from the

imperative approach used by tools like Knex, where you explicitly define the changes.

1npm install prisma --save-dev
2npx prisma init

1// prisma/schema.prisma
2generator client {
3  provider = "prisma-client-js"
4}
5
6datasource db {
7  provider = "postgresql"
8  url      = env("DATABASE_URL")
9}
10
11model User {
12  id          Int       @id @default(autoincrement())
13  username    String    @unique
14  email       String    @unique
15  passwordHash String    @map("password_hash")
16  isActive    Boolean   @default(true) @map("is_active")
17  createdAt   DateTime  @default(now()) @map("created_at")
18  updatedAt   DateTime  @updatedAt @map("updated_at")
19  posts       Post[]
20
21  @@map("users")
22}
23
24model Post {
25  id        Int       @id @default(autoincrement())
26  title     String
27  content   String?
28  published Boolean   @default(false)
29  authorId  Int       @map("author_id")
30  author    User      @relation(fields: [authorId], references: [id])
31  createdAt DateTime  @default(now()) @map("created_at")
32  updatedAt DateTime  @updatedAt @map("updated_at")
33
34  @@map("posts")
35}

1npx prisma migrate dev --name init  # Creates and applies migration

Python has several mature migration frameworks. Let's look at two popular options: Alembic (used with SQLAlchemy) and Django Migrations.

Alembic is the migration tool designed to work with SQLAlchemy, Python's most popular ORM:

1. Set up Alembic
2. Configure Alembic
3. Create a migration
4. Review the generated migration
5. Apply migration

1pip install alembic sqlalchemy psycopg2-binary
2alembic init migrations

1# alembic/env.py
2from sqlalchemy import engine_from_config, pool
3from alembic import context
4import os
5import sys
6
7sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))
8
9from myapp.models import Base  # Import your SQLAlchemy models
10
11target_metadata = Base.metadata  # Point to your SQLAlchemy models
12
13# ... rest of Alembic configuration ...

1alembic revision --autogenerate -m "Create users table"

1# alembic/versions/a1b2c3d4e5f6_create_users_table.py
2"""Create users table
3
4Revision ID: a1b2c3d4e5f6
5Revises:
6Create Date: 2025-10-20 12:00:00
7
8"""
9from alembic import op
10import sqlalchemy as sa
11
12# revision identifiers
13revision = 'a1b2c3d4e5f6'
14down_revision = None
15branch_labels = None
16depends_on = None
17
18def upgrade():
19    # ### commands auto generated by Alembic - please adjust! ###
20    op.create_table('users',
21        sa.Column('id', sa.Integer(), nullable=False),
22        sa.Column('username', sa.String(), nullable=False),
23        sa.Column('email', sa.String(), nullable=False),
24        sa.Column('password_hash', sa.String(), nullable=False),
25        sa.Column('is_active', sa.Boolean(), nullable=True),
26        sa.Column('created_at', sa.DateTime(), nullable=True),
27        sa.Column('updated_at', sa.DateTime(), nullable=True),
28        sa.PrimaryKeyConstraint('id'),
29        sa.UniqueConstraint('email'),
30        sa.UniqueConstraint('username')
31    )
32    # ### end Alembic commands ###
33
34def downgrade():
35    # ### commands auto generated by Alembic - please adjust! ###
36    op.drop_table('users')
37    # ### end Alembic commands ###

1alembic upgrade head  # Apply all migrations
2alembic downgrade -1  # Rollback one migration

Django has a built-in migration system that integrates seamlessly with its ORM:

1. Define your model
2. Create migration
3. Apply migration

Django's ORM-Integrated Approach

Django's migration system is tightly integrated with its ORM. You define your models in Python,

and Django can automatically generate and apply the necessary migrations. This is similar to Prisma's approach

but with Django's powerful ORM backing it.

1# myapp/models.py
2from django.db import models
3
4class User(models.Model):
5    username = models.CharField(max_length=150, unique=True)
6    email = models.EmailField(unique=True)
7    password_hash = models.CharField(max_length=255)
8    is_active = models.BooleanField(default=True)
9    created_at = models.DateTimeField(auto_now_add=True)
10    updated_at = models.DateTimeField(auto_now=True)
11
12    class Meta:
13        db_table = 'users'

1python manage.py makemigrations

1python manage.py migrate

Real-world database migrations often require careful planning, especially in production environments where data integrity

and application availability are critical. Here are some advanced patterns and strategies:

Strategies for migrating schemas without taking the application offline:

Expand & Contract Pattern (also called Blue-Green)

Multiple deployment phases with backward compatibility

Using database views to abstract schema changes

Temporary dual schema support

Patterns for safely migrating data between schemas:

Dual Writing

: Write to both old and new schemas

Dual Reading

: Read from both schemas, compare results

: Migrate existing data in batches

: Test changes with production traffic copies

One of the most powerful patterns for safe migrations is the Expand & Contract pattern (also called Blue-Green):

Phase 1: Expand

Add new columns/tables without removing old ones

Update application to write to both old and new schemas

Continue reading from old schema only

Deploy this change to production

Phase 2: Transition

Update application to read from new schema

Continue writing to both schemas

Backfill any missing data to new schema

Deploy this change to production

Phase 3: Contract

Update application to only write to new schema

Read only from new schema

Deploy this change to production

Eventually remove old schema elements

1// Example of dual writing in Node.js (Phase 1 of Expand & Contract)
2async function createUser(userData) {
3  // Write to new schema
4  const user = await db.users.create({
5    data: {
6      username: userData.username,
7      email: userData.email,
8      passwordHash: userData.passwordHash,
9      // New field
10      fullName: userData.fullName
11    }
12  });
13
14  // Also write to old schema (if using a separate table or service)
15  await db.legacyUsers.create({
16    data: {
17      username: userData.username,
18      email: userData.email,
19      password_hash: userData.passwordHash,
20      // Old schema doesn't have fullName
21    }
22  });
23
24  return user;
25}

To ensure successful migrations, follow these best practices:

Small, Focused Migrations

Break large changes into smaller, more manageable pieces.

Always Have Rollback Plans

Ensure every migration has a tested rollback procedure.

Test in Staging First

Never run untested migrations directly in production.

Schedule Appropriately

Run complex migrations during low-traffic periods.

Idempotent Migrations

Migrations should be safely repeatable without side effects.

Avoid Data Loss Operations

Add columns before removing; make renamed columns nullable first.

Use Transactions When Possible

Wrap migrations in transactions to ensure atomicity.

Monitor Performance

Watch for locks or performance impacts during migrations.

Modern applications integrate migrations into their CI/CD pipeline:

Example CI/CD Workflow for Migrations:

Validation Phase

Lint migration files, check for common mistakes

Test Phase

Apply migrations to test database, run schema tests

Staging Deployment

Apply migrations to staging, run integration tests

Production Deployment

Apply migrations to production with monitoring

Rollback Verification

Verify rollback procedures in staging environment

1# Example GitHub Actions workflow for migrations
2name: Database Migration Workflow
3
4on:
5  push:
6    branches: [ main ]
7    paths:
8      - 'migrations/**'
9      - 'prisma/schema.prisma'
10
11jobs:
12  validate:
13    runs-on: ubuntu-latest
14    steps:
15      - uses: actions/checkout@v3
16      - name: Set up Node.js
17        uses: actions/setup-node@v3
18        with:
19          node-version: '20'
20      - name: Validate migrations
21        run: npm run validate-migrations
22
23  test-migrations:
24    needs: validate
25    runs-on: ubuntu-latest
26    services:
27      postgres:
28        image: postgres:16
29        env:
30          POSTGRES_USER: test
31          POSTGRES_PASSWORD: test
32          POSTGRES_DB: test_db
33        ports:
34          - 5432:5432
35    steps:
36      - uses: actions/checkout@v3
37      - name: Set up Node.js
38        uses: actions/setup-node@v3
39        with:
40          node-version: '20'
41      - name: Install dependencies
42        run: npm ci
43      - name: Run migrations
44        run: npm run migrate
45        env:
46          DATABASE_URL: postgresql://test:test@localhost:5432/test_db
47      - name: Run tests
48        run: npm run test:migrations
49
50  deploy-staging:
51    needs: test-migrations
52    runs-on: ubuntu-latest
53    steps:
54      - uses: actions/checkout@v3
55      - name: Deploy to Staging
56        run: npm run deploy:staging
57
58  # Production deployment would follow similar pattern
59  # but with manual approval step

Even with careful planning, you may encounter challenges during database migrations. Here are some common issues and their solutions:

Long-running migrations locking tables

Use batching for data migrations; add columns with DEFAULT or NULL first, then update data in chunks

Failed migrations leaving database in inconsistent state

Use transactions when possible; implement checkpoints in long migrations

Schema conflicts between branches

Implement migration dependency tracking; use linear migration history

Production data issues not discovered in testing

Use production data samples in test environments; implement data validation before migrations

Deployment coordination with schema changes

Use the Expand & Contract pattern; coordinate code and schema changes carefully

⚠️ Common Mistake: Irreversible Changes

One of the most common migration mistakes is making irreversible changes like dropping columns or tables

without proper backups or transition periods. Always ensure you have:

Recent backups before running destructive migrations

A tested rollback strategy for every migration

A phased approach for major schema changes

Let's walk through a complete example of a migration lifecycle, from planning to execution, using Prisma as our migration tool:

We have an existing app with users, and we need to add user profiles with additional information.

Step 1: Current Schema

Step 2: Planning the Change

We want to add user profiles with bio, avatar URL, and social links. We have options:

Add fields directly to User model (simpler but less flexible)

Create a separate Profile model with a relation to User (more flexible, better separation of concerns)

We'll choose option 2 for better data organization.

Step 3: Updated Schema

Step 4: Create the Migration

Step 5: Review the Generated Migration

Step 6: Testing the Migration

Before deploying to production, we should test:

Apply migration to development/staging environment

Verify application can read/write to new schema

Test rollback procedure

Check for performance issues

Step 7: Application Code Updates

Step 8: Deploy to Production

Now that everything is tested, we can deploy to production:

Back up the production database

Apply migrations during a maintenance window (if needed)

Deploy updated application code

Monitor for any issues after deployment

Step 9: Post-Deployment

After successful deployment, we should:

Monitor database performance

Check error rates in application

Consider backfilling profiles for existing users if needed

Document the schema changes for other developers

1// Current schema.prisma
2model User {
3  id          Int       @id @default(autoincrement())
4  email       String    @unique
5  username    String    @unique
6  passwordHash String
7  createdAt   DateTime  @default(now()) @map("created_at")
8  updatedAt   DateTime  @updatedAt @map("updated_at")
9
10  @@map("users")
11}

1// Updated schema.prisma
2model User {
3  id          Int       @id @default(autoincrement())
4  email       String    @unique
5  username    String    @unique
6  passwordHash String
7  createdAt   DateTime  @default(now()) @map("created_at")
8  updatedAt   DateTime  @updatedAt @map("updated_at")
9  profile     Profile?
10
11  @@map("users")
12}
13
14model Profile {
15  id          Int       @id @default(autoincrement())
16  userId      Int       @unique @map("user_id")
17  bio         String?   @db.Text
18  avatarUrl   String?   @map("avatar_url")
19  location    String?
20  socialLinks Json?     @map("social_links")
21  createdAt   DateTime  @default(now()) @map("created_at")
22  updatedAt   DateTime  @updatedAt @map("updated_at")
23  user        User      @relation(fields: [userId], references: [id])
24
25  @@map("profiles")
26}

1npx prisma migrate dev --name add_user_profiles

1-- Migration: 20251020120000_add_user_profiles
2-- Generated at: 2025-10-20T12:00:00.000Z
3
4-- Create profiles table
5CREATE TABLE "profiles" (
6    "id" SERIAL NOT NULL,
7    "user_id" INTEGER NOT NULL,
8    "bio" TEXT,
9    "avatar_url" TEXT,
10    "location" TEXT,
11    "social_links" JSONB,
12    "created_at" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
13    "updated_at" TIMESTAMP(3) NOT NULL,
14
15    CONSTRAINT "profiles_pkey" PRIMARY KEY ("id")
16);
17
18-- Add unique constraint for one-to-one relationship
19CREATE UNIQUE INDEX "profiles_user_id_key" ON "profiles"("user_id");
20
21-- Add foreign key constraint
22ALTER TABLE "profiles" ADD CONSTRAINT "profiles_user_id_fkey"
23FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

1// Updated user service with profile support
2import { PrismaClient } from '@prisma/client';
3const prisma = new PrismaClient();
4
5export async function createUser(userData) {
6  return prisma.user.create({
7    data: {
8      email: userData.email,
9      username: userData.username,
10      passwordHash: userData.passwordHash,
11      // Create empty profile simultaneously
12      profile: {
13        create: {}
14      }
15    },
16    include: {
17      profile: true
18    }
19  });
20}
21
22export async function updateProfile(userId, profileData) {
23  return prisma.profile.update({
24    where: { userId },
25    data: {
26      bio: profileData.bio,
27      avatarUrl: profileData.avatarUrl,
28      location: profileData.location,
29      socialLinks: profileData.socialLinks
30    }
31  });
32}
33
34export async function getUserWithProfile(userId) {
35  return prisma.user.findUnique({
36    where: { id: userId },
37    include: { profile: true }
38  });
39}

Database migrations are a critical part of application development that bridge the gap between code changes and data evolution.

By implementing migrations directly in your application code using tools like Prisma, Knex.js, Alembic, or Django Migrations,

you gain version control, repeatability, and safety when evolving your database schema.

Remember these key principles for successful migrations:

Use migration frameworks that integrate with your application stack

Keep migrations small and focused

Always have a tested rollback strategy

Use patterns like Expand & Contract for zero-downtime migrations

Integrate migrations into your CI/CD pipeline

Test thoroughly in staging environments before production

By following these practices, you'll be able to confidently evolve your database schema alongside your application code,

ensuring data integrity and application reliability throughout your project's lifecycle.

Prisma Migration Documentation

Knex.js Migration Guide

Django Migrations in Depth

Alembic Documentation

Zero-Downtime Database Migrations

Best Practices for Schema Changes and Data Migrations