Creating Migrations - 🌲 Sonamu

Sonamu detects Entity changes and automatically generates Migration files.

Generation Workflow

Modify Entity

Edit JSON file Fields, types, indexes

Sonamu UI

Generate button Auto compare and generate

Review Migration

src/migrations/*.ts Review up/down

Execute

pnpm sonamu migrate Apply to DB

Generating in Sonamu UI

1. Modify Entity

First, modify the Entity JSON file:

// src/application/user/user.entity.json
{
  "id": "User",
  "table": "users",
  "props": [
    { "name": "id", "type": "integer" },
    { "name": "email", "type": "string", "length": 255 },
    { "name": "username", "type": "string", "length": 255 },
    { "name": "phone", "type": "string", "length": 20, "nullable": true } // ← New field added
  ]
}

2. Open Sonamu UI

Start the API dev server and open it in a browser. Sonamu UI is served from the API server’s /sonamu-ui path, without a separate port.

pnpm dev

# http://localhost:34900/sonamu-ui

3. Click Generate Button

Click the Generate button in the Entity tab.

4. Review Migration

Review the automatically generated Migration file:

// src/migrations/20251220143022_alter_users_add1.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.dropColumns("phone");
  });
}

Sonamu compares Entity with the current DB schema and generates migrations only for differences.

Generated Migration Types

New Table Creation

Adding Entity:

// src/application/post/post.entity.json
{
  "id": "Post",
  "table": "posts",
  "props": [
    { "name": "id", "type": "integer" },
    { "name": "title", "type": "string", "length": 200 },
    { "name": "content", "type": "string" }
  ]
}

Generated Migration:

// 20251220143100_create__posts.ts
export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable("posts", (table) => {
    table.increments().primary();
    table.string("title", 200).notNullable();
    table.text("content").notNullable();
  });
}

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

Adding Column

Entity Change:

{
  "props": [
    // ... existing fields
    { "name": "bio", "type": "string", "nullable": true } // ← Added
  ]
}

Generated Migration:

// 20251220143200_alter_users_add1.ts
export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    table.text("bio").nullable();
  });
}

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

Dropping Column

Entity Change:

{
  "props": [
    { "name": "id", "type": "integer" },
    { "name": "email", "type": "string" }
    // "old_field" removed
  ]
}

Generated Migration:

// 20251220143300_alter_users_drop1.ts
export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    table.dropColumns("old_field");
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    // Re-add on rollback (type info required)
    table.string("old_field", 100).nullable();
  });
}

Column deletion caution!Data cannot be recovered on rollback. Proceed carefully in production.

Changing Column Type

Entity Change:

{
  "name": "age",
  "type": "integer" // Changed from string to integer
}

Generated Migration:

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

export async function down(knex: Knex): Promise<void> {
  await knex.raw(`ALTER TABLE users ALTER COLUMN age TYPE varchar(255)`);
}

Adding Index

Entity Change:

{
  "indexes": [
    { "type": "unique", "column": "email" },
    { "type": "index", "column": "username" }, // ← Added
    {
      "type": "unique",
      "name": "users_email_active_unique",
      "columns": [{ "name": "email" }],
      "where": "deleted_at IS NULL"
    }
  ]
}

Generated Migration:

// 20251220143500_alter_users_index1.ts
export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable("users", (table) => {
    table.index(["username"], "users_username_index");
  });
  await knex.raw(
    `CREATE UNIQUE INDEX users_email_active_unique ON users (email) WHERE deleted_at IS NULL`,
  );
}

export async function down(knex: Knex): Promise<void> {
  await knex.raw(`DROP INDEX IF EXISTS users_email_active_unique`);
  await knex.schema.alterTable("users", (table) => {
    table.dropIndex(["username"], "users_username_index");
  });
}

where is a raw SQL predicate used directly as a PostgreSQL partial index condition. Do not combine it with user input — only declare stable column conditions. Even when PostgreSQL introspection returns the predicate wrapped in parentheses, Sonamu treats it as the same condition in migration diffs.

Adding Foreign Key

Entity Change:

{
  "props": [
    {
      "type": "relation",
      "name": "department",
      "with": "Department",
      "relationType": "BelongsTo"
    }
  ]
}

Generated Migration:

// 20251220143600_foreign__employees__department_id.ts
export async function up(knex: Knex): Promise<void> {
  return knex.schema.alterTable("employees", (table) => {
    table
      .foreign("department_id")
      .references("departments.id")
      .onUpdate("CASCADE")
      .onDelete("SET NULL");
  });
}

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

Composite Changes

Multiple changes result in multiple migrations: Entity Changes:

{
  "props": [
    { "name": "phone", "type": "string", "nullable": true }, // Added
    { "name": "bio", "type": "string", "nullable": true } // Added
  ],
  "indexes": [
    { "type": "index", "column": "phone" } // Added
  ]
}

Generated Migrations:

20251220143700_alter_users_add1.ts      (2 columns added)
20251220143701_alter_users_index1.ts    (index added)

Sonamu separates migrations by change type for easier management.

Migration Filename Convention

{timestamp}_{action}_{table_name}[_{number}].ts

timestamp: YYYYMMDDHHmmss
action: create, alter, foreign, drop
table_name: underscore-separated
number: add1, add2, etc. when multiple of the same action

Examples:

20251220143022_create__users.ts
20251220143100_alter_users_add1.ts
20251220143101_foreign__users__department_id.ts

Pre-generation Checklist

1. Verify DB Connection

# Check if DB is running
psql -U postgres -h localhost -p 5432 -d mydb

2. Check Existing Migration Status

# Check executed migrations
pnpm sonamu migrate status

3. Sync Entities

# Read Entity definitions and regenerate types/schemas (fails on invalid definitions)
pnpm sonamu sync

Manual Modification

You can manually modify auto-generated migrations:

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

    // Manual addition: set default value
    table.string("country_code", 3).notNullable().defaultTo("+82");
  });
}

Manual modification cautions: - Also modify the down function - May be overwritten on next Generate - Separate complex logic into separate migrations

Special Cases

Generated Columns

// Use raw SQL directly
export async function up(knex: Knex): Promise<void> {
  await knex.raw(
    `ALTER TABLE "users" 
     ADD COLUMN "full_name" varchar(510) 
     GENERATED ALWAYS AS (first_name || ' ' || last_name) 
     STORED`,
  );
}

Data Migration

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

  // 2. Migrate data
  await knex("users").update({
    status: knex.raw("CASE WHEN is_active THEN 'active' ELSE 'inactive' END"),
  });

  // 3. nullable → notNullable
  await knex.raw(`ALTER TABLE users ALTER COLUMN status SET NOT NULL`);
}

Conditional Execution

export async function up(knex: Knex): Promise<void> {
  const hasColumn = await knex.schema.hasColumn("users", "phone");

  if (!hasColumn) {
    await knex.schema.alterTable("users", (table) => {
      table.string("phone", 20).nullable();
    });
  }
}

Next Steps

Running Migrations

Apply with migrate run

Rolling Back

Undo changes

How It Works

Understanding auto-generation

Strategies

Safe migration practices

​Generation Workflow

Modify Entity

Sonamu UI

Review Migration

Execute

​Generating in Sonamu UI

​1. Modify Entity

​2. Open Sonamu UI

​3. Click Generate Button

​4. Review Migration

​Generated Migration Types

​New Table Creation

​Adding Column

​Dropping Column

​Changing Column Type

​Adding Index

​Adding Foreign Key

​Composite Changes

​Migration Filename Convention

​Pre-generation Checklist

​1. Verify DB Connection

​2. Check Existing Migration Status

​3. Sync Entities

​Manual Modification

​Special Cases

​Generated Columns

​Data Migration

​Conditional Execution

​Next Steps

Running Migrations

Rolling Back

How It Works

Strategies

Generation Workflow

Generating in Sonamu UI

1. Modify Entity

2. Open Sonamu UI

3. Click Generate Button

4. Review Migration

Generated Migration Types

New Table Creation

Adding Column

Dropping Column

Changing Column Type

Adding Index

Adding Foreign Key

Composite Changes

Migration Filename Convention

Pre-generation Checklist

1. Verify DB Connection

2. Check Existing Migration Status

3. Sync Entities

Manual Modification

Special Cases

Generated Columns

Data Migration

Conditional Execution

Next Steps