Create Database Migration

Create Database Migration

Generate timestamped Knex migrations following project conventions.

Quick Start

1. Create migration: pnpm --filter api knex:new-migration {description}
   • Example: pnpm --filter api knex:new-migration add-column-email-to-users-table
   • Creates timestamped file in apps/api/src/shared-kernel/adapters/sql-knex/migrations/
2. Implement up() and down() functions in the generated file
3. Update apps/api/src/shared-kernel/adapters/sql-knex/tableTypes.d.ts if schema changes
4. Run: pnpm --filter api knex:migrate-latest

File Naming

Format: {YYYYMMDDHHmmss}_{verb}-{description}.ts

Use kebab-case OR snake_case consistently within a single filename.

Migration Templates

Create Table

import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.createTable("table_name", (table) => {
    table.uuid("id").primary();
    table.string("name").notNullable();
    table.uuid("related_id").references("id").inTable("other_table");
    table.timestamp("created_at").notNullable();
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.dropTableIfExists("table_name");
}

Add Column

import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.table("table_name", (table) => {
    table.string("column_name");  // nullable by default
    // OR: table.boolean("flag").defaultTo(false);
    // OR: table.string("required_col").notNullable();
  });
}

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

Drop Column

import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.table("table_name", (table) => {
    table.dropColumn("column_name");
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.table("table_name", (table) => {
    table.string("column_name").nullable();
  });
  // Optionally restore data if recoverable
}

Rename Column

import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex.schema.table("table_name", (table) => {
    table.renameColumn("old_name", "new_name");
  });
}

export async function down(knex: Knex): Promise<void> {
  await knex.schema.table("table_name", (table) => {
    table.renameColumn("new_name", "old_name");
  });
}

Data Migration

import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  await knex("table_name")
    .whereIn("column", ["old_value1", "old_value2"])
    .update({ column: "new_value" });
}

export async function down(knex: Knex): Promise<void> {
  await knex("table_name")
    .where("column", "new_value")
    .update({ column: "old_value1" });  // Best effort
}

Complex Data Migration (JSON columns)

import type { Knex } from "knex";

export async function up(knex: Knex): Promise<void> {
  const rows = await knex("table_name")
    .select("id", "json_column")
    .whereRaw(`json_column::json->>'field' IS NOT NULL`);

  for (const row of rows) {
    const data = row.json_column as Record<string, unknown>;
    const updated = { ...data, newField: transformValue(data.oldField) };
    delete (updated as Record<string, unknown>).oldField;

    await knex("table_name").update({ json_column: updated }).where({ id: row.id });
  }
}

export async function down(): void {
  return;  // Data migration not reversible
}

Column Types Reference

tableTypes.d.ts Updates

After schema changes, update apps/api/src/shared-kernel/adapters/sql-knex/tableTypes.d.ts:

// Add new type for new table
type SqlNewTable = {
  id: string;
  name: string;
  created_at: Date;
  optional_col: string | null;  // nullable columns use | null
};

// Register in Tables interface
declare module "knex/types/tables" {
  interface Tables {
    new_table: SqlNewTable;  // table_name: SqlType
  }
}

Rules:

• Use snake_case for column names (matches DB)
• Use | null for nullable columns (not ?:)
• Use Date for timestamps (Knex converts)
• Register table in Tables interface

Checklist

1. Migration created with pnpm --filter api knex:new-migration {description}
2. up() implements forward migration
3. down() reverses migration (or returns void if not possible)
4. tableTypes.d.ts updated for schema changes
5. Migration tested: pnpm --filter api knex:migrate-latest
6. Rollback tested: pnpm --filter api knex:migrate-rollback