migrate - 🌲 Sonamu

The pnpm migrate command safely manages database schema changes. It automatically generates migrations based on Entity definitions and can apply them consistently across multiple databases.

Basic Concept

Migration is a system for version controlling database schema changes:

Entity-based: Auto-generate migrations from Entity definitions
Sequential execution: Apply migrations in creation order
Multi-DB support: Manage multiple databases simultaneously
Auto-detection: Automatically detect Entity changes and generate code

Commands

status - Check Status

Check the current migration status.

pnpm migrate status

Output example:

Development Master: ✓ Up to date (v20240115_143022)
Testing: ⚠ 2 pending migrations
Production: ✓ Up to date (v20240115_143022)

Prepared migrations:
  • 20240116_101530_add_user_profile
  • 20240116_102045_create_posts_table

Status types:

✓ Up to date: All migrations applied
⚠ N pending: N migrations not yet applied
❌ Error: Database connection failed

run - Run Migrations

Apply all pending migrations.

pnpm migrate run

Execution process:

Running migrations...

Development Master:
  ✓ 20240116_101530_add_user_profile (0.2s)
  ✓ 20240116_102045_create_posts_table (0.3s)

Testing:
  ✓ 20240116_101530_add_user_profile (0.2s)
  ✓ 20240116_102045_create_posts_table (0.3s)

All migrations completed successfully!

Auto-generation and application:

Detect Entity changes
Auto-generate migration code
Apply sequentially to all target DBs

Sonamu analyzes Entity definitions and automatically generates necessary migrations. No need to manually write migration files.

Migration Generation

Auto-generation

When you modify an Entity, Sonamu automatically generates a migration.

user.entity.ts

// Change Entity definition
const UserEntity = {
  properties: [
    {
      name: "email",
      type: "string",
      length: 255,
    },
    // Add new field
    {
      name: "phone",
      type: "string",
      length: 20,
      nullable: true,
    },
  ],
};

# Check status
pnpm migrate status

# Output:
# Prepared migrations:
#   • 20240116_103045_add_phone_to_users

# Apply
pnpm migrate run

Migration Files

Generated migration files are stored in the src/migrations/ directory.

src/migrations/20240116_103045_add_phone_to_users.ts

import type { Knex } from "knex";

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

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

Structure:

up(): Apply migration (create tables, add columns, etc.)
down(): Rollback migration (revert changes)

Supported Changes

Changes that Sonamu auto-detects and generates migrations for:

Change Type	Description	Example
Table creation	Add new Entity	Create `PostEntity`
Column addition	Add new property	Add `phone` field
Column modification	Change type/length	`varchar(100)` → `varchar(255)`
Column deletion	Remove property	Delete `deprecated_field`
Index addition	Define index	`indexes` array
Foreign key addition	Define relationship	`belongsTo` relation

Multi-Database Management

Sonamu can manage multiple databases simultaneously.

Database Configuration

sonamu.config.ts

export default {
  database: {
    // Development environment - master
    development_master: {
      client: "mysql2",
      connection: {
        host: "localhost",
        database: "myapp_dev",
        user: "root",
        password: "password",
      },
    },
    // Development environment - slave (read-only)
    development_slave: {
      // Slaves are automatically ignored
    },
    // Test environment
    test: {
      client: "mysql2",
      connection: {
        host: "localhost",
        database: "myapp_test",
        user: "root",
        password: "password",
      },
    },
    // Production environment
    production: {
      client: "mysql2",
      connection: {
        host: "prod-db.example.com",
        database: "myapp_prod",
        user: "root",
        password: process.env.DB_PASSWORD,
      },
    },
  },
};

Migration targets:

Databases ending with _master
All databases not ending with _slave

Batch Application

# Apply to all databases
pnpm migrate run

# Output:
# Development Master: ✓ 2 migrations applied
# Testing: ✓ 2 migrations applied
# Production: ✓ 2 migrations applied

Practical Workflow

1. Change Entity

user.entity.ts

const UserEntity = {
  properties: [
    // Existing fields...
    {
      name: "profile_image",
      type: "string",
      nullable: true,
    },
  ],
};

2. Check Status

pnpm migrate status

Prepared migrations:
  • 20240116_110230_add_profile_image_to_users

3. Apply Migration

pnpm migrate run

✓ All migrations applied successfully!

4. Update Code

user.model.ts

class UserModelClass extends BaseModelClass {
  async updateProfileImage(userId: number, imageUrl: string) {
    await this.getPuri("w").update({ profile_image: imageUrl }).where("id", userId);
  }
}

Advanced Usage (Programmatic)

Advanced features not provided via CLI commands can be implemented using the Migrator class directly.

Using Migrator Class

src/scripts/migration-tools.ts

import { Migrator } from "sonamu";

const migrator = new Migrator();

// Rollback (last batch)
await migrator.runAction("rollback", ["development_master"]);

// Select specific databases only
await migrator.runAction("apply", ["test"]);

// Check status (programmatic)
const status = await migrator.getStatus();
console.log(status);

Rollback Script

src/scripts/rollback-migration.ts

import { Migrator, Sonamu } from "sonamu";

Sonamu.runScript(async () => {
  const migrator = new Migrator();

  console.log("Rolling back last migration batch...");
  await migrator.runAction("rollback", ["development_master", "test"]);
  console.log("Rollback completed!");
});

Run:

pnpm tsx src/scripts/rollback-migration.ts

Data loss risk: Rollback can delete tables or columns, so use very carefully in production.

Available Actions

Actions supported by Migrator.runAction():

Action	Description	Use Case
`apply`	Apply migrations	Apply new schema changes
`rollback`	Rollback last batch	Revert mistaken migrations

Troubleshooting

Migration Conflict

Problem: Multiple developers create migrations simultaneously

Error: Migration conflict detected

Solution:

# 1. Get latest code
git pull

# 2. Check migration status
pnpm migrate status

# 3. Regenerate migrations if needed
pnpm migrate run

Database Connection Failed

Problem: DB connection failed

Error: connect ECONNREFUSED 127.0.0.1:3306

Solution:

# 1. Check MySQL is running
mysql -u root -p -e "SHOW DATABASES;"

# 2. Check connection info
cat sonamu.config.ts

# 3. Create database
mysql -u root -p -e "CREATE DATABASE myapp_dev;"

Migration Order Error

Problem: Applied in wrong order

Error: Foreign key constraint violation

Solution: When there are foreign key references, referenced tables must be created first. Fix method 1: Programmatic rollback and reapply

// Rollback
const migrator = new Migrator();
await migrator.runAction("rollback", ["development_master"]);

// Modify migration filename (adjust timestamp)
// 20240116_110230_*.ts → 20240116_110231_*.ts

// Reapply
await migrator.runAction("apply", ["development_master"]);

Fix method 2: Adjust Entity definition order

// Adjust Entity file order to create users table first
// Then run pnpm migrate run

Best Practices

1. Apply Frequently

# Migrate frequently in small units
pnpm migrate run  # Daily

2. Test Before Production

# Verify in test DB first
pnpm migrate status
pnpm migrate run

# Apply to production if no issues

3. Backup Required

# Backup before production migration
mysqldump -u root -p myapp_prod > backup_$(date +%Y%m%d).sql

# Apply migration
pnpm migrate run

4. Consider Rollback Possibility

// ✅ Rollback possible - down() function properly defined
export async function up(knex: Knex) {
  await knex.schema.alterTable("users", (table) => {
    table.string("phone").nullable();
  });
}

export async function down(knex: Knex) {
  await knex.schema.alterTable("users", (table) => {
    table.dropColumn("phone");
  });
}

5. Version Control with Git

# Commit migration files to Git
git add src/migrations/
git commit -m "Add phone field to users table"

# Share with team
git push

Get Started

Core Concepts

Database

API Development

Frontend Integration

Testing

Advanced Features

Tools & CLI

Configuration

API Reference

Troubleshooting

FAQ

​Basic Concept

​Commands

​status - Check Status

​run - Run Migrations

​Migration Generation

​Auto-generation

​Migration Files

​Supported Changes

​Multi-Database Management

​Database Configuration

​Batch Application

​Practical Workflow

​1. Change Entity

​2. Check Status

​3. Apply Migration

​4. Update Code

​Advanced Usage (Programmatic)

​Using Migrator Class

​Rollback Script

​Available Actions

​Troubleshooting

​Migration Conflict

​Database Connection Failed

​Migration Order Error

​Best Practices

​1. Apply Frequently

​2. Test Before Production

​3. Backup Required

​4. Consider Rollback Possibility

​5. Version Control with Git

​Next Steps

fixture

Entity

Basic Concept

Commands

status - Check Status

run - Run Migrations

Migration Generation

Auto-generation

Migration Files

Supported Changes

Multi-Database Management

Database Configuration

Batch Application

Practical Workflow

1. Change Entity

2. Check Status

3. Apply Migration

4. Update Code

Advanced Usage (Programmatic)

Using Migrator Class

Rollback Script

Available Actions

Troubleshooting

Migration Conflict

Database Connection Failed

Migration Order Error

Best Practices

1. Apply Frequently

2. Test Before Production

3. Backup Required

4. Consider Rollback Possibility

5. Version Control with Git

Next Steps