> ## Documentation Index
> Fetch the complete documentation index at: https://sonamu.cartanova.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# migrate

> Managing database migrations

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.

```bash theme={null}
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.

```bash theme={null}
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

<Info>
  Sonamu analyzes Entity definitions and **automatically generates** necessary migrations. No need
  to manually write migration files.
</Info>

## Migration Generation

### Auto-generation

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

```typescript title="user.entity.ts" theme={null}
// Change Entity definition
const UserEntity = {
  properties: [
    {
      name: "email",
      type: "string",
      length: 255,
    },
    // Add new field
    {
      name: "phone",
      type: "string",
      length: 20,
      nullable: true,
    },
  ],
};
```

```bash theme={null}
# 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.

```typescript title="src/migrations/20240116_103045_add_phone_to_users.ts" theme={null}
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

```typescript title="sonamu.config.ts" theme={null}
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

```bash theme={null}
# 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

```typescript title="user.entity.ts" theme={null}
const UserEntity = {
  properties: [
    // Existing fields...
    {
      name: "profile_image",
      type: "string",
      nullable: true,
    },
  ],
};
```

### 2. Check Status

```bash theme={null}
pnpm migrate status
```

```
Prepared migrations:
  • 20240116_110230_add_profile_image_to_users
```

### 3. Apply Migration

```bash theme={null}
pnpm migrate run
```

```
✓ All migrations applied successfully!
```

### 4. Update Code

```typescript title="user.model.ts" theme={null}
class UserModelClass extends BaseModelClass {
  async updateProfileImage(userId: number, imageUrl: string) {
    await this.getPuri("w").table("users").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

```typescript title="src/scripts/migration-tools.ts" theme={null}
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

```typescript title="src/scripts/rollback-migration.ts" theme={null}
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**:

```bash theme={null}
pnpm tsx src/scripts/rollback-migration.ts
```

<Warning>
  **Data loss risk**: Rollback can delete tables or columns, so use **very carefully in
  production**.
</Warning>

### 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**:

```bash theme={null}
# 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**:

```bash theme={null}
# 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**

```typescript theme={null}
// 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**

```typescript theme={null}
// Adjust Entity file order to create users table first
// Then run pnpm migrate run
```

## Best Practices

### 1. Apply Frequently

```bash theme={null}
# Migrate frequently in small units
pnpm migrate run  # Daily
```

### 2. Test Before Production

```bash theme={null}
# Verify in test DB first
pnpm migrate status
pnpm migrate run

# Apply to production if no issues
```

### 3. Backup Required

```bash theme={null}
# 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

```typescript theme={null}
// ✅ 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

```bash theme={null}
# Commit migration files to Git
git add src/migrations/
git commit -m "Add phone field to users table"

# Share with team
git push
```

## Next Steps

<CardGroup cols={2}>
  <Card title="fixture" icon="database" href="/en/tools-and-cli/sonamu-cli/fixture">
    Manage test data
  </Card>

  <Card title="Entity" icon="cube" href="/en/core-concepts/entity">
    Learn more about Entity definitions
  </Card>
</CardGroup>