Database Migrations¶

// Without migrations - nightmare scenario
// Dev 1: adds column locally
ALTER TABLE users ADD COLUMN phone VARCHAR(20)

// Dev 2: never got the memo
// Production: has no phone column
// App crashes: "column users.phone does not exist"

// With migrations - everything in version control
// 001_add_phone_to_users.sql is committed , reviewed , deployed

// schema.prisma - change schema
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  phone     String?  // new field
  role      Role     @default(USER)
  createdAt DateTime @default(now())
}

// Generate migration
npx prisma migrate dev --name add_phone_field

// Apply to production
npx prisma migrate deploy

// Reset for development
npx prisma migrate reset

// Generated SQL migration - review before applying
-- CreateMigration
ALTER TABLE "public"."User" ADD COLUMN "phone" TEXT;

/*
  Warnings:
  - A unique constraint covering the columns `[phone]` on table `User`
    will be added. If there are existing duplicate values, this will fail.
*/

-- AlterIndex
CREATE UNIQUE INDEX "User_phone_key" ON "User"("phone");

// Create a migration
npx knex migrate:make create_users_table

// File: migrations/20250101000000_create_users_table.js
exports.up = function(knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id').primary()
    table.string('email').notNull().unique()
    table.string('name')
    table.enu('role', ['user', 'admin']).defaultTo('user')
    table.timestamp('created_at').defaultTo(knex.fn.now())
  })
}

exports.down = function(knex) {
  return knex.schema.dropTableIfExists('users')
}

// Adding a column
npx knex migrate:make add_phone_to_users

exports.up = function(knex) {
  return knex.schema.table('users', (table) => {
    table.string('phone')
    table.string('address')
  })
}

exports.down = function(knex) {
  return knex.schema.table('users', (table) => {
    table.dropColumn('phone')
    table.dropColumn('address')
  })
}

// Run migrations
npx knex migrate:latest

// Rollback
npx knex migrate:rollback

// Check status
npx knex migrate:status

// Install
npm install node-pg-migrate pg

// package.json script
"migrate": "node-pg-migrate up",
"migrate:down": "node-pg-migrate down"

// Run with DATABASE_URL
DATABASE_URL=postgres://localhost/mydb npm run migrate

// migrations/001_add-users-table.js
exports.up = (pgm) => {
  pgm.createTable('users', {
    id: { type: 'serial', primaryKey: true },
    email: { type: 'varchar(255)', notNull: true, unique: true },
    name: { type: 'varchar(255)' },
    role: { type: 'varchar(20)', default: 'user' },
    created_at: { type: 'timestamp', default: pgm.func('now()') }
  })
}

exports.down = (pgm) => {
  pgm.dropTable('users')
}

// migrations/002_add-phone-column.js
exports.up = (pgm) => {
  pgm.addColumn('users', {
    phone: { type: 'varchar(20)' }
  })
}

exports.down = (pgm) => {
  pgm.dropColumn('users', 'phone')
}

// Prisma seed
// prisma/seed.js
const { PrismaClient } = require('@prisma/client')
const prisma = new PrismaClient()

async function main() {
  // Create admin user
  const admin = await prisma.user.upsert({
    where: { email: 'admin@example.com' },
    update: {},
    create: {
      email: 'admin@example.com',
      name: 'Admin',
      role: 'ADMIN'
    }
  })

  // Create sample posts
  const posts = await Promise.all([
    prisma.post.create({
      data: { title: 'Welcome', content: 'First post', authorId: admin.id }
    }),
    prisma.post.create({
      data: { title: 'Tutorial', content: 'How to use this app', authorId: admin.id }
    })
  ])

  console.log('Seeded:', { admin, posts })
}

main()
  .catch(e => { console.error(e); process.exit(1) })
  .finally(() => prisma.$disconnect())

// Knex seed
// seeds/01_users.js
exports.seed = async function(knex) {
  // Delete existing entries
  await knex('users').del()

  // Insert seed data
  await knex('users').insert([
    { email: 'admin@example.com', name: 'Admin', role: 'admin' },
    { email: 'user1@example.com', name: 'Mahmoud', role: 'user' },
    { email: 'user2@example.com', name: 'Ali', role: 'user' }
  ])
}

// Run seeds
npx knex seed:run

# .github/workflows/deploy.yml
jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      - uses: actions/setup-node@v3

      - name: Install dependencies
        run: npm ci

      - name: Run database migrations
        run: npx prisma migrate deploy
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

      - name: Run seed (dev only)
        if: github.ref == 'refs/heads/develop'
        run: npx prisma db seed
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}

# Docker entrypoint - run migrations before starting app
#!/bin/sh
# entrypoint.sh
echo "Running database migrations..."
npx prisma migrate deploy

echo "Starting application..."
exec node dist/index.js

// Strategy 1: Sequential rollback (Knex)
npx knex migrate:down    # rollback last migration
npx knex migrate:down    # rollback one more
npx knex migrate:down    # keep rolling back until stable

// Strategy 2: Rollback to specific point
npx knex migrate:down 20250101000000  # rollback to this timestamp

// Strategy 3: Create a fix migration instead
// 003_fix_broken_column.js
exports.up = (pgm) => {
  // Add the column we forgot
  pgm.addColumn('users', {
    phone: { type: 'varchar(20)' }
  })
}

// Strategy 4: Blue-green deploy with backward-compatible schema
// Always make migrations backward-compatible for 1 deploy cycle
// Step 1: Add column (app still works without it)
// Step 2: Deploy new code that uses column
// Step 3: Remove old column (next deploy)

// 1. One change per migration
// BAD: 003_add_phone_and_address_and_role.sql
// GOOD: 003_add_phone.sql
// GOOD: 004_add_address.sql
// GOOD: 005_add_role.sql

// 2. Never edit existing migrations - create new ones
// BAD: Editing 001_create_users.sql after it was applied
// GOOD: 006_fix_users_email_constraint.sql

// 3. Test down migrations before deploying up
exports.down = (pgm) => {
  // If this throws , you can't rollback
  pgm.dropColumn('users', 'phone')
}

// 4. Back up production before running migrations
// pg_dump mydb > backup_$(date +%Y%m%d).sql

// 5. Run migrations during maintenance windows for large datasets
// ALTER TABLE on a 10M-row table locks the table for minutes