Rolling Back - 🌲 Sonamu

Rollback migrations to restore the database to a previous state.

Rollback Methods

Sonamu UI

Rollback button on the Migrations tab Rolls back the most recent batch

runAction()

Call the Migrator API directly For scripts and automation

migrate status

Check status before rollback Verify batches

down function

Reverse operation in the migration file Determines the rollback result

There is no rollback CLI command. sonamu migrate provides exactly four subcommands: run, apply, generate, and status. Rollback is performed only from Sonamu UI or via Migrator.runAction("rollback", ...).

Basic Rollback

Rollback from Sonamu UI

Start the API dev server (pnpm dev).
Open the Migrations tab at http://localhost:34900/sonamu-ui.
Select the database connections to roll back.
Click Rollback and confirm in the modal.

A rollback reverts every migration in the most recent batch of the selected database.

Rollback from a Script

For CI or operational scripts, call Migrator directly.

src/scripts/rollback-migration.ts

import { Migrator, Sonamu } from "sonamu";

Sonamu.runScript(async () => {
  const migrator = new Migrator();
  await migrator.runAction("rollback", ["development"]);
});

pnpm tsx src/scripts/rollback-migration.ts

Check Current Status

pnpm sonamu migrate status

Output:

Executed migrations (Batch 1):
  ✅ 20251220143022_create__users.ts
  ✅ 20251220143100_create__posts.ts

Executed migrations (Batch 2):
  ✅ 20251220143200_alter_users_add1.ts  ← This batch gets rolled back

Pending migrations:
  ⏳ 20251220143300_alter_posts_add1.ts

Rollback Granularity

Batch-based Rollback

The unit of a rollback is a batch. A single rollback reverts every migration in the most recent batch.

# Before rollback
Batch 3 (most recent): 20251220150000_alter_users_add2.ts
                       20251220150100_alter_posts_add2.ts
Batch 2:               20251220143200_alter_users_add1.ts

# One rollback → both migrations in Batch 3 are rolled back together
Batch 2:               20251220143200_alter_users_add1.ts  ← kept

Reverting Multiple Batches

There is no way to roll back a single selected migration or several batches at once. To go further back, run the rollback once per batch, checking pnpm sonamu migrate status between runs.

Repeating rollbacks eventually returns the database to its initial schema (all tables dropped). Verify the impact of each run in production.

The down Function

Basic Structure

export async function down(knex: Knex): Promise<void> {
  // Perform reverse operation of up
}

CREATE TABLE Rollback

// up: Create table
export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable("users", (table) => {
    table.increments().primary();
    table.string("email", 255).notNullable();
  });
}

// down: Drop table
export async function down(knex: Knex): Promise<void> {
  return knex.schema.dropTable("users");
}

ALTER TABLE Rollback

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

// down: Drop column
export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    table.dropColumns("phone");
  });
}

FOREIGN KEY Rollback

// up: Add foreign key
export async function up(knex: Knex): Promise<void> {
  return knex.schema.alterTable("employees", (table) => {
    table.foreign("user_id").references("users.id").onUpdate("CASCADE").onDelete("CASCADE");
  });
}

// down: Drop foreign key
export async function down(knex: Knex): Promise<void> {
  return knex.schema.alterTable("employees", (table) => {
    table.dropForeign(["user_id"]);
  });
}

Rollback Scenarios

Scenario 1: Fix Incorrect Migration

# 1. Run migration
pnpm sonamu migrate run
✅ 20251220143200_alter_users_add1.ts

# 2. Discover problem (wrong column type)

# 3. Roll back from the Migrations tab in Sonamu UI

# 4. Fix migration file
vim src/migrations/20251220143200_alter_users_add1.ts

# 5. Run again
pnpm sonamu migrate run

Scenario 2: Production Emergency Rollback

The production database is often unreachable from wherever Sonamu UI runs, so run the rollback script with production environment variables.

# 1. Problem discovered after production deployment

# 2. Immediate rollback (script calling runAction("rollback", ["production"]))
NODE_ENV=production pnpm tsx src/scripts/rollback-migration.ts

# 3. Deploy previous application version

# 4. Fix problem and redeploy

When NODE_ENV is not local, only the connection whose name matches the environment is allowed as a target. Any other target is rejected with an error.

Scenario 3: Partial Rollback

# Current status
Batch 3: alter_users_add2.ts, alter_posts_add2.ts
Batch 2: alter_users_add1.ts
Batch 1: create__users.ts, create__posts.ts

# One rollback → only Batch 3 is rolled back (Batch 2, 1 kept)

# Result
Batch 2: alter_users_add1.ts  ← Retained
Batch 1: create__users.ts, create__posts.ts  ← Retained

Data Preservation

Data Loss on Rollback

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

// down: Drop column (data loss!)
export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    table.dropColumns("phone"); // ← All phone data deleted
  });
}

Data loss warning!Data cannot be recovered when columns/tables are dropped. Backup is required in production!

Safe Rollback Pattern

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

// Step 2: Data migration
await knex("users").update({
  new_phone: knex.raw("old_phone"),
});

// Step 3: Keep old_phone column (rollback preparation)
// → Delete later in separate migration

Non-reversible Cases

Data Transformation

// up: Type change
export async function up(knex: Knex): Promise<void> {
  await knex.raw(
    `ALTER TABLE users 
     ALTER COLUMN age TYPE integer 
     USING age::integer`,
  );
}

// down: Original data cannot be restored
export async function down(knex: Knex): Promise<void> {
  await knex.raw(
    `ALTER TABLE users 
     ALTER COLUMN age TYPE varchar(255)`,
  );
  // "25" → 25 → "25" (loss if original was "025")
}

Data Deletion

// up: Delete specific data
export async function up(knex: Knex): Promise<void> {
  await knex("users").where("is_deleted", true).delete();
}

// down: Deleted data cannot be restored
export async function down(knex: Knex): Promise<void> {
  // Cannot restore!
}

Rollback Strategies

1. Backup First

# PostgreSQL backup
pg_dump -U postgres -d mydb > backup_before_rollback.sql

# Rollback (Sonamu UI or the rollback script)

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

2. Staging Test

# Test rollback in staging
NODE_ENV=staging pnpm tsx src/scripts/rollback-migration.ts

# If no issues, production
NODE_ENV=production pnpm tsx src/scripts/rollback-migration.ts

3. Incremental Rollback

# Roll back once (most recent batch)

# Check status
pnpm sonamu migrate status

# Test application

# If everything looks fine, roll back the next batch

Error Handling

Rollback Failure

When a down function fails during rollback, an error like the following is raised. Error:

❌ Error rolling back 20251220143200_alter_users_add1.ts
   Error: column "phone" does not exist

Causes:

Migration was only partially applied
down function doesn’t match up
DB was modified manually

Solutions:

Manually verify DB state
Modify knex_migrations table
Fix down function and retry

Forced Rollback

-- Remove from knex_migrations
DELETE FROM knex_migrations
WHERE name = '20251220143200_alter_users_add1.ts';

-- Manually undo changes
ALTER TABLE users DROP COLUMN phone;

Rollback Logs

Checking the Result

runAction("rollback", ...) returns the batch number and the list of rolled back files per target connection.

const results = await migrator.runAction("rollback", ["development"]);
console.log(results);
// [{ connKey: "development", batchNo: 2, applied: ["20251220143200_alter_users_add1.ts"] }]

Sonamu UI shows the same information in the result modal.

Querying Rollback History

-- Check recently rolled back migrations
SELECT * FROM knex_migrations
WHERE batch = (SELECT MAX(batch) + 1 FROM knex_migrations);

Practical Tips

1. Verify down Function

// Test: up → down → up
test("migration reversibility", async () => {
  await migration.up(knex);
  await migration.down(knex);
  await migration.up(knex); // Should be runnable again
});

2. Document Rollback Plan

# Rollback Plan

## Migration: 20251220143200_alter_users_add1.ts

### Changes

- Add column: users.phone

### Rollback Impact

- Data loss: users.phone data deleted
- Estimated time: 5 seconds
- Downtime: None (nullable column)

### Rollback Steps

1. Roll back from the Migrations tab in Sonamu UI (or run the rollback script)
2. Deploy previous app version
3. Verify phone field not used

3. Auto-rollback Script

#!/bin/bash
# auto-rollback.sh

echo "Creating backup..."
pg_dump -U postgres -d mydb > backup.sql

echo "Rolling back..."
pnpm tsx src/scripts/rollback-migration.ts

if [ $? -eq 0 ]; then
  echo "✅ Rollback successful"
else
  echo "❌ Rollback failed, restoring backup..."
  psql -U postgres -d mydb < backup.sql
fi

Next Steps

Strategies

Safe migration strategies

Running Migrations

Apply with migrate run

Creating Migrations

Generate in Sonamu UI

How It Works

Understanding auto-generation

​Rollback Methods

Sonamu UI

runAction()

migrate status

down function

​Basic Rollback

​Rollback from Sonamu UI

​Rollback from a Script

​Check Current Status

​Rollback Granularity

​Batch-based Rollback

​Reverting Multiple Batches

​The down Function

​Basic Structure

​CREATE TABLE Rollback

​ALTER TABLE Rollback

​FOREIGN KEY Rollback

​Rollback Scenarios

​Scenario 1: Fix Incorrect Migration

​Scenario 2: Production Emergency Rollback

​Scenario 3: Partial Rollback

​Data Preservation

​Data Loss on Rollback

​Safe Rollback Pattern

​Non-reversible Cases

​Data Transformation

​Data Deletion

​Rollback Strategies

​1. Backup First

​2. Staging Test

​3. Incremental Rollback

​Error Handling

​Rollback Failure

​Forced Rollback

​Rollback Logs

​Checking the Result

​Querying Rollback History

​Practical Tips

​1. Verify down Function

​2. Document Rollback Plan

​3. Auto-rollback Script

​Next Steps

Strategies

Running Migrations

Creating Migrations

How It Works

Rollback Methods

Basic Rollback

Rollback from Sonamu UI

Rollback from a Script

Check Current Status

Rollback Granularity

Batch-based Rollback

Reverting Multiple Batches

The down Function

Basic Structure

CREATE TABLE Rollback

ALTER TABLE Rollback

FOREIGN KEY Rollback

Rollback Scenarios

Scenario 1: Fix Incorrect Migration

Scenario 2: Production Emergency Rollback

Scenario 3: Partial Rollback

Data Preservation

Data Loss on Rollback

Safe Rollback Pattern

Non-reversible Cases

Data Transformation

Data Deletion

Rollback Strategies

1. Backup First

2. Staging Test

3. Incremental Rollback

Error Handling

Rollback Failure

Forced Rollback

Rollback Logs

Checking the Result

Querying Rollback History

Practical Tips

1. Verify down Function

2. Document Rollback Plan

3. Auto-rollback Script

Next Steps