Running Migrations - 🌲 Sonamu

How to apply generated Migration files to the database.

Execution Commands

migrate run

Run all migrations Update DB to latest

migrate status

Check execution status Pending list

migrate apply

Run against selected databases

migrate generate

Generate prepared migration files

sonamu migrate has exactly four subcommands: run, apply, generate, and status. Rollback is not a CLI command — see Rolling Back.

Basic Execution

Run All Migrations

pnpm sonamu migrate run

Execution Result:

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

migrate run executes all pending migrations in order.

Check Execution Status

pnpm sonamu migrate status

Example Output:

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

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

Execution Order

Migrations are executed in filename timestamp order:

20251209160740_create__companies.ts
20251209160741_create__departments.ts
20251209160742_create__employees.ts
20251209160750_foreign__departments__company_id.ts
20251209160751_foreign__employees__user_id.ts

Targeting Specific Databases

migrate apply

migrate run applies migrations only to the database matching the current NODE_ENV (test and fixture when NODE_ENV=test). To choose the targets yourself, use migrate apply.

pnpm sonamu migrate apply

A target selection prompt appears.

? Please input #targets ›
◯ Development
◯ Staging
◯ Production
◯ Fixture
◯ Test

When NODE_ENV is not local, only the connection whose name matches the environment is allowed. Choosing any other target fails with Migration targets are not allowed in NODE_ENV=....

There is no command to run a single migration at a time. The execution unit is always all pending migrations.

Batch System

What is a Batch?

A group of migrations executed together:

SELECT * FROM knex_migrations ORDER BY id;

id	name	batch	migration_time
1	20251220143022_create__users.ts	1	2025-12-20 14:30:22
2	20251220143100_create__posts.ts	1	2025-12-20 14:30:22
3	20251220143200_alter_users_add1.ts	2	2025-12-20 15:00:00

Role of Batches

Rollback unit: Migrations in the same batch are rolled back together
Execution group: Running migrate run once puts them in the same batch

# 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)

Environment-specific Execution

Development Environment

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

pnpm sonamu migrate run

Staging Environment

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

NODE_ENV=staging pnpm sonamu migrate run

Production Environment

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

NODE_ENV=production pnpm sonamu migrate run

Before running in production: 1. Test in staging first 2. Confirm backup is complete 3. Plan for downtime 4. Prepare rollback plan

Transactions

Automatic Transactions

Each migration runs in a separate transaction:

// 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();
  });
}

Disabling Transactions

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

Some DDL operations may not support transactions.

Error Handling

Error During Execution

pnpm sonamu migrate run

Error Occurred:

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

Handling:

Fix the migration file
Retry execution

Partial Failure

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

When a migration fails: - Only that migration is rolled back - Previous migrations are retained - Not recorded in knex_migrations table

Checking Execution Logs

Execution Result

migrate run prints the batch number and the list of applied files per target connection. There is no option for SQL-level verbose logging, so use the knex_migrations queries below or your database server logs to inspect the executed SQL.

Querying Execution History

-- 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%';

CI/CD Integration

GitHub Actions

# .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

Docker

# Dockerfile
FROM node:20-alpine

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

COPY . .

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

Minimizing Downtime

Blue-Green Deployment

# 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

Maintaining Compatibility

// 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`);
}

Practical Tips

1. Pre-execution Checks

# 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

2. Backup

# 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

3. Monitoring

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

await knex.migrate.latest();

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

Next Steps

Rolling Back

Undo from Sonamu UI

Strategies

Safe migration practices

Creating Migrations

Generate in Sonamu UI

How It Works

Understanding auto-generation

​Execution Commands

migrate run

migrate status

migrate apply

migrate generate

​Basic Execution

​Run All Migrations

​Check Execution Status

​Execution Order

​Targeting Specific Databases

​migrate apply

​Batch System

​What is a Batch?

​Role of Batches

​Environment-specific Execution

​Development Environment

​Staging Environment

​Production Environment

​Transactions

​Automatic Transactions

​Disabling Transactions

​Error Handling

​Error During Execution

​Partial Failure

​Checking Execution Logs

​Execution Result

​Querying Execution History

​CI/CD Integration

​GitHub Actions

​Docker

​Minimizing Downtime

​Blue-Green Deployment

​Maintaining Compatibility

​Practical Tips

​1. Pre-execution Checks

​2. Backup

​3. Monitoring

​Next Steps

Rolling Back

Strategies

Creating Migrations

How It Works

Execution Commands

Basic Execution

Run All Migrations

Check Execution Status

Execution Order

Targeting Specific Databases

migrate apply

Batch System

What is a Batch?

Role of Batches

Environment-specific Execution

Development Environment

Staging Environment

Production Environment

Transactions

Automatic Transactions

Disabling Transactions

Error Handling

Error During Execution

Partial Failure

Checking Execution Logs

Execution Result

Querying Execution History

CI/CD Integration

GitHub Actions

Docker

Minimizing Downtime

Blue-Green Deployment

Maintaining Compatibility

Practical Tips

1. Pre-execution Checks

2. Backup

3. Monitoring

Next Steps