Skip to main content
Sonamu automatically generates database migrations based on Entity definitions.

​
Migration Overview

Auto Generation

Entity β†’ MigrationNo manual writing needed

Version Control

Timestamp-basedSequential execution guaranteed

Up/Down

Apply and rollbackBidirectional support

Knex-based

Standard Knex APICompatibility guaranteed

​
Migration Generation Flow

​
From Entity to Migration

​
1. Entity Definition

// 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": "role", "type": "enum", "id": "UserRole" }
  ]
}

​
2. Auto-Generated Migration

// 20251209160747_create__users.ts
import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable("users", (table) => {
    table.increments().primary();
    table.string("email", 255).notNullable();
    table.string("username", 255).notNullable();
    table.text("role").notNullable();
    
    table.unique(["email"], "users_email_unique");
  });
}

export async function down(knex: Knex): Promise<void> {
  return knex.schema.dropTable("users");
}
Migration filename: {timestamp}_{action}_{table_name}.ts
  • Timestamp: YYYYMMDDHHmmss format
  • Action: create, alter, foreign, drop, etc.
  • Table name: underscore-separated

​
Migration File Structure

​
up Function - Apply Changes

export async function up(knex: Knex): Promise<void> {
  // Apply changes to database
  await knex.schema.createTable("users", (table) => {
    // Table definition
  });
}

​
down Function - Rollback Changes

export async function down(knex: Knex): Promise<void> {
  // Undo changes
  return knex.schema.dropTable("users");
}
The down function is required!You must define the reverse operation of up for rollbacks.

​
Generated Migration Types

​
CREATE TABLE

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

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

​
ALTER TABLE - Add Column

// 20251211150026_alter_departments_add1.ts
export async function up(knex: Knex): Promise<void> {
  await knex.schema.alterTable("departments", (table) => {
    table.string("code", 10).notNullable();
  });
}

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

​
ALTER TABLE - Drop Column

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

export async function down(knex: Knex): Promise<void> {
  await knex.schema.alterTable("projects", (table) => {
    // Rollback: re-add column
    table.string("old_field", 100).nullable();
  });
}

​
Add FOREIGN KEY

// 20251209160751_foreign__employees__user_id_department_id.ts
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");
    
    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(["user_id"]);
    table.dropForeign(["department_id"]);
  });
}

​
Database Comparison

Sonamu detects differences by comparing the current DB schema with Entities:

​
Comparison Targets

  1. Tables: existence, name
  2. Columns: type, length, nullable, default
  3. Indexes: unique, index, composite indexes
  4. Foreign Keys: referenced table, onUpdate, onDelete
  5. Constraints: CHECK, DEFAULT, etc.

​
Difference Detection

// Entity: email length 100 β†’ 255 changed
{
  "name": "email",
  "type": "string",
  "length": 255  // Changed
}

// Generated Migration
export async function up(knex: Knex): Promise<void> {
  await knex.raw(
    `ALTER TABLE users ALTER COLUMN email TYPE varchar(255)`
  );
}

​
Migration Execution Order

​
1. Timestamp-based Sorting

20251209160740_create__companies.ts       (1st)
20251209160741_create__departments.ts     (2nd)
20251209160742_create__employees.ts       (3rd)
20251209160750_foreign__departments.ts    (4th)
20251209160751_foreign__employees.ts      (5th)

​
2. Dependency Consideration

// Correct order
1. CREATE TABLE companies
2. CREATE TABLE departments (has company_id but no FK yet)
3. CREATE TABLE employees
4. ADD FOREIGN KEY departments.company_id β†’ companies.id
5. ADD FOREIGN KEY employees.department_id β†’ departments.id
Foreign keys are generated as separate migrations!CREATE TABLE only creates columns; foreign key constraints are added later in separate migrations.

​
Execution Tracking

​
knex_migrations Table

Knex tracks executed migrations: 
SELECT * FROM knex_migrations;
idnamebatchmigration_time
120251209160740_create__companies.ts12025-12-09 16:10:00
220251209160741_create__departments.ts12025-12-09 16:10:00
320251209160742_create__employees.ts12025-12-09 16:10:00
  • batch: Group executed together (rollback unit)
  • migration_time: Execution time

​
Entity Type to Migration Mapping

​
Basic Types

Entity TypeDB TypeMigration
stringvarchar(n)table.string(name, length)
integerintegertable.integer(name)
booleanbooleantable.boolean(name)
datetimestamptztable.timestamp(name, { useTz: true })
jsonjsonbtable.jsonb(name)
uuiduuidtable.uuid(name)

​
Relation Types

// Entity
{
  "type": "relation",
  "name": "user",
  "with": "User",
  "relationType": "BelongsTo"
}

// Migration
table.integer("user_id").nullable();

// Foreign Key (separate migration)
table.foreign("user_id")
  .references("users.id")
  .onUpdate("CASCADE")
  .onDelete("SET NULL");

​
Indexes

// Entity
{
  "indexes": [
    { "type": "unique", "column": "email" },
    { "type": "index", "columns": ["username", "created_at"] }
  ]
}

// Migration
table.unique(["email"], "users_email_unique");
table.index(["username", "created_at"], "users_username_created_at_index");

​
Advanced Features

​
Generated Columns

// 20251211150026_alter_departments_add1.ts
export async function up(knex: Knex): Promise<void> {
  await knex.raw(
    `ALTER TABLE "departments" 
     ADD COLUMN "code" varchar(10) 
     GENERATED ALWAYS AS ('DEP-' || LPAD(id::text, 3, '0')) 
     STORED NOT NULL`
  );
}

​
Full-text Search Index

export async function up(knex: Knex): Promise<void> {
  // tsvector column
  await knex.raw(
    `ALTER TABLE "posts" 
     ADD COLUMN "search_vector" tsvector 
     GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || content)) 
     STORED`
  );
  
  // GIN index
  await knex.raw(
    `CREATE INDEX posts_search_vector_idx 
     ON posts USING GIN(search_vector)`
  );
}

​
Vector Search Index

export async function up(knex: Knex): Promise<void> {
  // vector column
  await knex.raw(`ALTER TABLE "documents" ADD COLUMN "embedding" vector(1536)`);
  
  // HNSW index
  await knex.raw(
    `CREATE INDEX documents_embedding_hnsw_idx 
     ON documents USING hnsw (embedding vector_cosine_ops)`
  );
}

​
Next Steps

Creating Migrations

Generate in Sonamu UI

Running Migrations

migrate run command

Rolling Back

Undo changes

Entity Definition

Understanding Entity structure