Running Migrations

pnpm sonamu migrate run

Batch 1 run: 3 migrations
✅ 20251220143022_create__users.ts
✅ 20251220143100_create__posts.ts
✅ 20251220143200_foreign__posts__user_id.ts

pnpm sonamu migrate status

Executed migrations:
  ✅ 20251220143022_create__users.ts
  ✅ 20251220143100_create__posts.ts

Pending migrations:
  ⏳ 20251220143200_alter_users_add1.ts
  ⏳ 20251220143300_alter_posts_add1.ts

1. 20251209160740_create__companies.ts
2. 20251209160741_create__departments.ts
3. 20251209160742_create__employees.ts
4. 20251209160750_foreign__departments__company_id.ts
5. 20251209160751_foreign__employees__user_id.ts

pnpm sonamu migrate up

Batch 1 run: 1 migration
✅ 20251220143022_create__users.ts

Batch 2 run: 1 migration
✅ 20251220143100_create__posts.ts

SELECT * FROM knex_migrations ORDER BY id;

# Batch 1: Run 2 at once
pnpm sonamu migrate run
✅ 20251220143022_create__users.ts     (batch 1)
✅ 20251220143100_create__posts.ts     (batch 1)

# Batch 2: Run 1 more later
pnpm sonamu migrate run
✅ 20251220143200_alter_users_add1.ts  (batch 2)

# .env.development
DATABASE_URL=postgresql://user:pass@localhost:5432/dev_db

pnpm sonamu migrate run

# .env.staging
DATABASE_URL=postgresql://user:pass@staging-db:5432/staging_db

NODE_ENV=staging pnpm sonamu migrate run

# .env.production
DATABASE_URL=postgresql://user:pass@prod-db:5432/prod_db

NODE_ENV=production pnpm sonamu migrate run

// 20251220143022_create__users.ts
export async function up(knex: Knex): Promise<void> {
  // This entire function is one transaction
  await knex.schema.createTable("users", (table) => {
    table.increments().primary();
    table.string("email", 255).notNullable();
  });
  
  // On error, the CREATE TABLE above is also rolled back
  await knex.schema.createTable("profiles", (table) => {
    table.increments().primary();
    table.integer("user_id").notNullable();
  });
}

// Configure in knexfile.ts
export default {
  migrations: {
    disableTransactions: true,  // Disable transactions
  },
};

pnpm sonamu migrate run

Batch 1 run: 3 migrations
✅ 20251220143022_create__users.ts
✅ 20251220143100_create__posts.ts
❌ 20251220143200_alter_users_add1.ts
   Error: column "phone" already exists

Batch 1 run: 2 migrations (1 successful, 1 failed)
✅ 20251220143022_create__users.ts     (committed)
❌ 20251220143100_create__posts.ts     (rolled back)

pnpm sonamu migrate run --verbose

Running migration: 20251220143022_create__users.ts
SQL: CREATE TABLE "users" (
  "id" serial primary key,
  "email" varchar(255) not null
)
✅ Completed in 45ms

Running migration: 20251220143100_create__posts.ts
SQL: CREATE TABLE "posts" (
  "id" serial primary key,
  "title" varchar(200) not null
)
✅ Completed in 32ms

Total time: 77ms

-- All execution history
SELECT * FROM knex_migrations 
ORDER BY migration_time DESC;

-- Recent batch
SELECT * FROM knex_migrations 
WHERE batch = (SELECT MAX(batch) FROM knex_migrations);

-- Check specific migration
SELECT * FROM knex_migrations 
WHERE name LIKE '%users%';

# .github/workflows/deploy.yml
name: Deploy

on:
  push:
    branches: [main]

jobs:
  migrate:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v3
      
      - name: Setup Node.js
        uses: actions/setup-node@v3
        with:
          node-version: '20'
      
      - name: Install dependencies
        run: pnpm install
      
      - name: Run migrations
        env:
          DATABASE_URL: ${{ secrets.DATABASE_URL }}
        run: pnpm sonamu migrate run

# Dockerfile
FROM node:20-alpine

WORKDIR /app
COPY package.json pnpm-lock.yaml ./
RUN pnpm install

COPY . .

# Run migrations
CMD ["pnpm", "sonamu", "migrate", "run"]

# 1. Blue environment (currently running)
DATABASE_URL=postgresql://blue-db pnpm start

# 2. Apply migration to Green environment
DATABASE_URL=postgresql://green-db pnpm sonamu migrate run

# 3. Test Green environment
DATABASE_URL=postgresql://green-db pnpm test

# 4. Switch traffic (Blue → Green)
# 5. Update Blue environment
DATABASE_URL=postgresql://blue-db pnpm sonamu migrate run

// Step 1: Add new column (nullable)
export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    table.string("new_field").nullable();  // ← nullable
  });
}

// Step 2: Deploy application (use new_field)

// Step 3: Data migration

// Step 4: Add NOT NULL constraint
export async function up(knex: Knex): Promise<void> {
  await knex.raw(
    `ALTER TABLE users ALTER COLUMN new_field SET NOT NULL`
  );
}

# Check status
pnpm sonamu migrate status

# Review pending migration
cat src/migrations/20251220143200_alter_users_add1.ts

# Test in staging
NODE_ENV=staging pnpm sonamu migrate run

# PostgreSQL backup
pg_dump -U postgres -d mydb > backup_$(date +%Y%m%d_%H%M%S).sql

# Run migration
pnpm sonamu migrate run

# Restore if issues occur
psql -U postgres -d mydb < backup_20251220_143000.sql

// Measure migration execution time
const startTime = Date.now();

await knex.migrate.latest();

const duration = Date.now() - startTime;
console.log(`Migration completed in ${duration}ms`);