Mikro ORM PostgreSQL Migration Generator

A MikroORM migration generator extension for automatically managing PostgreSQL updated_at column triggers and functions.

Why?

Unlike MySQL, PostgreSQL does not natively support the convenient ON UPDATE CURRENT_TIMESTAMP feature for automatically updating timestamp columns. Instead, the typical approach is to use database functions and triggers to achieve this behavior.

While MikroORM provides the onUpdate: () => new Date() option for automatically updating columns, this only works within ORM-managed code. For cases where you use query builders or raw SQL, it’s easy to miss handling the updated_at logic, leading to verbose and error-prone code.

This generator automatically creates the necessary migrations for PostgreSQL functions and triggers to keep your updated_at columns reliably updated, even outside the ORM.

Features

• Automatic migrations for updated_at columns: Generates PostgreSQL migration scripts for triggers and functions that update your timestamp columns.
• Custom column names: Support for columns other than updated_at.
• Custom types: Works with types other than timestamptz.
• Custom function and trigger names: Easily configure naming conventions.
• Up/Down migration scripts: Automatically create and drop functions/triggers as part of migration.
• Supports PostgreSQL versions 13 through 17.

Installation

# npm
npm install mikro-orm-pg-migration-generator @mikro-orm/migrations

# pnpm
pnpm add mikro-orm-pg-migration-generator @mikro-orm/migrations

Usage

Configure MikroORM to use the migration generator in your project:

import { defineConfig } from "@mikro-orm/postgresql";
import { createMigrationGeneratorClass } from "mikro-orm-pg-migration-generator";

export default defineConfig({
  // With default options
  migrations: { generator: createMigrationGeneratorClass() },

  // Or with customization
  migrations: {
    generator: createMigrationGeneratorClass({
      version: 17, // PostgreSQL parser version
      updatedAt: {
        // Custom column name matcher
        columnNameMatcher: ({ value }) => value.toLowerCase() === "update_date",
        // Custom column type matcher
        columnTypeMatcher: ({ value }) => value.toLowerCase() === "timestamp",
        // Custom function name
        functionNameFactory: (context) => "...",
        // Custom function up SQL
        functionUpSqlFactory: (context) => "...",
        // Custom function down SQL
        functionDownSqlFactory: (context) => "...",
        // Custom trigger name
        triggerNameFactory: (context) => "...",
        // Custom trigger up SQL
        triggerUpSqlFactory: (context) => "...",
        // Custom trigger down SQL
        triggerDownSqlFactory: (context) => "...",
      },
    }),
  },
});

Example: Generated Migration File

When calling mikro-orm migration:create for a users table with an updated_at column, the generated migration might look like:

// Migration20250928232237.ts
import { Migration } from "@mikro-orm/migrations";
export class Migration20250928232237 extends Migration {
  override async up(): Promise<void> {
    this.addSql(
      `create table "users" ( /* ... */ "updated_at" timestamptz not null default current_timestamp, /* ... */ );`
    );
    this
      .addSql(`create or replace function "fn_update_timestamp_users_updated_at"()
returns trigger as $$
begin
  new."updated_at" := current_timestamp;
  return new;
end;
$$ language plpgsql;`);
    this.addSql(`create trigger "tr_update_timestamp_users_updated_at"
before update on "users"
for each row
execute function "fn_update_timestamp_users_updated_at"();`);
  }

  override async down(): Promise<void> {
    this.addSql(
      `drop trigger if exists "tr_update_timestamp_users_updated_at" ON "users";`
    );
    this.addSql(
      `drop function if exists "fn_update_timestamp_users_updated_at"();`
    );
    this.addSql(`drop table if exists "users" cascade;`);
  }
}

Description

When you create a migration using mikro-orm migration:create for a table such as users with an updated_at column, the generator automatically creates the necessary function, and trigger for handling timestamp updates.

The up migration creates the trigger function, and trigger to update the timestamp column whenever a row is updated.

The down migration safely removes the trigger, and function, ensuring clean rollback.

This concise example shows how the generator integrates with MikroORM migrations for robust and automatic timestamp logic in your PostgreSQL schema.

How to Extend the Migration Generator

To add custom functionality—such as automatically formatting SQL with sql-formatter—extend the generator as follows:

# npm
npm install -D sql-formatter

# pnpm
pnpm add -D sql-formatter

import { createMigrationGeneratorClass } from "mikro-orm-pg-migration-generator";
import { format } from "sql-formatter";

const BaseMigrationGenerator = createMigrationGeneratorClass();

class CustomMigrationGenerator extends BaseMigrationGenerator {
  override createStatement(sql: string, padLeft: number): string {
    sql = format(sql, { language: "postgresql" });
    sql = `\n${sql}\n`;
    return super.createStatement(sql, padLeft);
  }
}

This example demonstrates how to wrap the migration generator and override methods—such as createStatement—to incorporate additional logic (e.g., SQL formatting). You can customize other behaviors the same way, making the generator flexible and easy to extend for advanced migration requirements.

License

MIT