Class DreamMigrationHelpers

Unique indexes cannot be populated by the same value even within a transaction, but deferrable unique constraints can.

The Sortable decorator requires deferrable unique constraints rather than unique indexes.

Add a value to an enum.

Note that this always includes "IF NOT EXISTS", so is safe to re-run multiple times.

Create a database extension.

  // Add the case insensitive extension
  await DreamMigrationHelpers.createExtension(db, 'citext')

  // Add the pg trigram extension
  await DreamMigrationHelpers.createExtension(db, 'pg_trgm')
Copy

Create a gin index

Drop a constraint

Note that this always includes "IF NOT EXISTS", so is safe to re-run multiple times.

Drop a value from an enum and replace it with a different enum already present in the enum type (or optionally remove it from array columns).

Forces a new transaction boundary in migration execution.

When called in a migration file, this method ensures that any existing transaction is committed before this migration runs, and a new transaction is started before the migration in this file. This is essential for migrations that depend on previously committed changes.

Some database operations require that dependent changes be committed before they can be executed. For example, check constraints that reference enum values require those enum values to be committed to the database first.

// first migration file: Add enum value
export async function up(db: Kysely<any>): Promise<void> {
  await DreamMigrationHelpers.addEnumValue(db, {
    enumName: 'user_status',
    value: 'premium'
  })
}

// second migration file: Add check constraint that depends on the enum value
export async function up(db: Kysely<any>): Promise<void> {
  DreamMigrationHelpers.newTransaction() // Ensure enum value is committed first

  await db.schema
    .alterTable('users')
    .addCheckConstraint(
      'check_premium_users',
      sql`status = 'premium' OR credits < 100`
    )
    .execute()
}
Copy

Rename a table and its associated primary key index and sequence.

This method renames the table, its primary key index ({tablename}_pkey), and its primary key sequence ({tablename}_id_seq) to keep them in sync.

The sequence rename is skipped for tables with UUID primary keys (which have no associated sequence). The primary key index is always renamed since PostgreSQL does not automatically rename it when the table is renamed.