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

Inverse of DreamMigrationHelpers.encryptColumn: decrypt an encrypted_<column> column back to plaintext and rename it to column.

Every non-null value is decrypted with the same path the decorator's getter uses (InternalEncrypt.decryptColumn, which honors both the current and legacy encryption keys), then the column is renamed back. Called with the same table and column, this exactly reverses encryptColumn.

By default the column is left as text, because the original column type cannot be recovered from the encrypted state. Pass columnType to restore a specific type (e.g. 'integer'); the conversion runs ALTER COLUMN ... TYPE <columnType> USING <column>::<columnType>.

await DreamMigrationHelpers.decryptColumn(db, { table: 'users', column: 'phone' })

// restore the original column type as part of the inverse
await DreamMigrationHelpers.decryptColumn(db, { table: 'users', column: 'age', columnType: 'integer' })
Copy

The same per-row, batched, table-locking caveat as encryptColumn applies.

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).

Convert an existing plaintext column into the encrypted-backed form expected by the @Encrypted decorator.

This renames column to encrypted_<column>, widens it to text, and rewrites every non-null value with the AES-GCM ciphertext produced by the exact same code path the decorator's setter uses (InternalEncrypt.encryptColumn). After it runs, the column holds real ciphertext that the decorator's getter can decrypt — which is what a bare column rename does not do (renaming a plaintext column to encrypted_<column> and decorating the property leaves plaintext in the column, so the getter throws DecryptionError).

The encryption key and algorithm come from the application's encryption config (DreamApp encryption.columns.current); if encryption is not configured this throws MissingColumnEncryptionOpts. Null values are left null.

Intended for text/string columns (the common case is text -> encrypted text). The column is widened to text before reading, so values are encrypted as their text form; the inverse decryptColumn can restore a non-text type via its columnType option.

// plaintext `phone` column -> encrypted `encrypted_phone` text column
await DreamMigrationHelpers.encryptColumn(db, { table: 'users', column: 'phone' })
Copy

Rewrites rows one at a time (each value needs a fresh random IV computed in Node, so the rewrite cannot be a single SQL UPDATE), reading them in keyset batches of batchSize to bound memory. It still holds the table for the migration's duration; on very large tables do not use this helper — write your own batched / online migration.

Drop any index on the column first. Per-row updates pay index-maintenance cost on every write, and an index over ciphertext is useless anyway (encrypted values are not queryable). Remove the index before this migration and do not re-add it.

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 applies to both legacy serial/bigserial columns and modern GENERATED ... AS IDENTITY columns: identity columns are also backed by a {tablename}_id_seq sequence, so they need the same rename. It is skipped only 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.