migrations

Migrations are generated implicitly by the model and controller generators or explicitly using the migration generator.

Under the hood, Psychic leverages Kysely to run migrations. Whenever a new model is generated, a migration is automatically generated to pair with the model, as we have seen for the User model generated in the authentication example:

import { Kysely, sql } from 'kysely'

// eslint-disable-next-line @typescript-eslint/no-explicit-any
export async function up(db: Kysely<any>): Promise<void> {
  await db.schema.createType('user_roles_enum').asEnum(['App', 'Admin']).execute()

  await db.schema
    .createTable('users')
    .addColumn('id', 'bigserial', col => col.primaryKey())
    .addColumn('email', 'varchar(255)')
    .addColumn('password_digest', 'varchar(255)')
    .addColumn('user_role', sql`user_roles_enum`)
    .addColumn('created_at', 'timestamp', col => col.notNull())
    .addColumn('updated_at', 'timestamp', col => col.notNull())
    .execute()
}

// eslint-disable-next-line @typescript-eslint/no-explicit-any
export async function down(db: Kysely<any>): Promise<void> {
  await db.schema.dropTable('users').execute()
  await db.schema.dropType('user_roles_enum').execute()
}

Always generate, never hand-write

Migrations are always created by the migration, model, resource, or sti-child generators — never written from scratch. The generators produce the boilerplate and timestamp the filename correctly; hand-edit the generated file for anything the column shorthand can't express, such as check constraints, enum alterations, or a custom backfill.

Editing a migration file

Never modify a migration file that has already been merged into main. Production data already reflects the original migration's effect; rewriting its history would skip the new behavior entirely on machines that already recorded the original as applied. Any change to a merged migration's behavior must be expressed as a new migration instead.

A migration that is still on a feature branch — not yet merged — may be freely edited in place. There's no production data to protect, so add the correct constraints (NOT NULL, etc.) directly rather than writing a backfill for data that doesn't exist yet.

If you edit an unmerged migration after it's already been applied locally, run db:reset to bring the database back in sync — db:migrate won't re-run a migration that's already recorded as applied, so the edit has no effect until the database is rebuilt from scratch. Don't chain a follow-up migration to "fix" an unmerged one; edit the original and reset. This is especially important after hand-editing raw Kysely DDL such as check constraints or partial indexes — TypeScript can compile while Postgres rejects the generated SQL, so a fresh db:reset is the verification step.

Running migrations

To run this migration, we can use the migrate CLI command, like so:

NODE_ENV=development pnpm psy db:migrate
NODE_ENV=test pnpm psy db:migrate

See migrate/rollback for the full set of database commands and how NODE_ENV affects type syncing.

Adding columns to an existing table

For schema changes that just add columns — or a foreign key — to an existing table, g:migration accepts the same column shorthand as g:model and g:resource, including Model:belongs_to and :optional. Hand-edit the generated migration only when the change isn't expressible as shorthand.

NOT NULL columns and defaults

When a NOT NULL column has a single obvious domain default ('normal', 'pending', false, 0), encode it at the migration layer with col.defaultTo(value).notNull(). This lets callers omit the field without a runtime failure, and it removes the temptation to add a silent default in controller code that drifts from "default if missing" into "default if unrecognized." If the default is genuinely caller-dependent, leave the column NOT NULL with no default — a 400/500 on a missing value is preferable to silently coercing it.

A NOT NULL column with no default fails the moment the migration runs against a populated table, since Postgres has nothing to put in the existing rows. When the default is caller-dependent (so a permanent default doesn't fit), add the column with a temporary default, then drop the default in a separate statement in the same migration:

await db.schema
  .alterTable('conversation_threads')
  .addColumn('channel', sql`conversation_thread_channels_enum`, col => col.notNull().defaultTo('web'))
  .execute()

// existing rows are now backfilled with 'web'; drop the default so new rows
// must set the value intentionally in application code
await db.schema
  .alterTable('conversation_threads')
  .alterColumn('channel', col => col.dropDefault())
  .execute()

The temporary default backfills existing rows; dropping it afterward keeps the "no silent default in caller code" guarantee for everything written from here on. If a permanent default genuinely fits the domain, keep it and skip the drop. The generator's column shorthand omits the default by design — it can't know whether the table already has rows — so this is expected generate-then-edit territory, not a generator gap.

Supported column types

The following column types are supported out of the box (though it isn't guaranteed that the TypeScript types will be well defined for anything beyond text, character varying (string in Dream generators), uuid, integer, numeric (decimal in Dream generators), bigint, bigserial, serial, boolean, date, timestamp (datetime in Dream generators), json, jsonb and array versions of these types):

bigint	bigserial	bit	bit varying
boolean	box	bytea	character
character varying	cidr	circle	date
double	inet	integer	interval
json	jsonb	line	lseg
macaddr	macaddr8	money	numeric
path	pg_lsn	pg_snapshot	point
polygon	real	smallint	smallserial
serial	text	time	time
timestamp	tsquery	tsvector	txid_snapshot
uuid	xml

Dream migrations will allow whatever data types are supported by postgres. The complete list can be found at https://www.postgresql.org/docs/current/datatype.html.

Writing migrations

Primary key patterns

// UUID with uuidv7 (recommended)
.addColumn('id', 'uuid', col => col.primaryKey().defaultTo(sql`uuidv7()`))

// bigint identity (auto-incrementing)
.addColumn('id', 'bigint', col => col.primaryKey().generatedByDefaultAsIdentity())

JSON

Reaching for json or jsonb should be a stop-and-reconsider moment, not a default. These column types carry no schema, so Dream's column-type inference, validation surface, and Psychic's model-derived OpenAPI request/response shapes all short-circuit at that field — the column becomes an untyped blob from the framework's perspective, and every consumer has to re-derive the shape by hand.

Before adding one, work through whether the data is really shaped like an associated model. Repeated keys with their own lifecycle (created, updated, or deleted independently, queried by value, validated per-row) almost always want their own table with a HasMany / BelongsTo. A bounded set of attributes that always travels with the parent row usually wants real columns. Reach for jsonb only when the data is genuinely schemaless or polymorphic in a way no relational shape captures — a third-party webhook payload stored verbatim, opaque per-tenant configuration, an audit snapshot — and write down in the migration why the relational alternative was rejected.

.addColumn('metadata', 'jsonb', col => col.notNull().defaultTo(sql`'{}'::jsonb`))
.addColumn('config', 'jsonb')  // Optional — null is the initial state
.addColumn('settings', 'json')  // Prefer jsonb

Foreign keys

// Basic FK
.addColumn('user_id', 'uuid', col =>
  col.references('users.id').onDelete('restrict').notNull()
)

// Optional FK
.addColumn('approver_id', 'uuid', col =>
  col.references('users.id').onDelete('set null')
)

// Unique FK (one-to-one)
.addColumn('user_id', 'uuid', col =>
  col.references('users.id').onDelete('restrict').notNull().unique()
)

Delete behaviors: 'restrict', 'cascade', 'no action', 'set null'.

Self-referential FKs: the generator names the column after the model — Room:belongs_to:optional on Room produces room_id. For a clearer name like parent_id, use the aliased belongs_to shorthand: Room@parent:belongs_to:optional produces parent_id and a parent: Room association. Hand-edit the migration only when the alias shorthand can't express what you need.

Always create an index for a foreign key column:

await db.schema.createIndex('posts_user_id').on('posts').column('user_id').execute()

Indexes

// Single column
await db.schema
  .createIndex('places_host_id')
  .on('places')
  .column('host_id')
  .execute()

// Multi-column, unique
await db.schema
  .createIndex('localized_texts_localizable_for_locale')
  .on('localized_texts')
  .columns(['localizable_type', 'localizable_id', 'locale'])
  .unique()
  .execute()

createGinIndex is a migration helper for full-text/similarity search indexes — see migration helpers.

Column modifiers

col.primaryKey()              // Primary key
col.notNull()                 // NOT NULL constraint
col.unique()                  // Unique constraint
col.defaultTo(value)          // Default value
col.defaultTo(sql`...`)       // SQL expression default
col.references('table.id')    // Foreign key
col.onDelete('restrict')      // Delete behavior
col.check(sql`...`)           // Check constraint

Alter table

// Add column
await db.schema
  .alterTable('rooms')
  .addColumn('bed_types', sql`bed_types_enum[]`, col => col.notNull().defaultTo('{}'))
  .execute()

// Drop column
await db.schema
  .alterTable('rooms')
  .dropColumn('bed_types')
  .execute()

// Add check constraint (for STI)
await db.schema
  .alterTable('rooms')
  .addCheckConstraint(
    'rooms_not_null_bath_or_shower_style',
    sql`type != 'Bathroom' OR bath_or_shower_style IS NOT NULL`,
  )
  .execute()

Fixed migration-time DDL literals belong in the emitted SQL, not in bound parameters. When a check constraint contains static constants, inline the controlled literal in the DDL expression rather than parameterizing it — parameterized DDL can pass TypeScript but fail when Postgres prepares the statement.

For partial index predicates, Kysely may require the raw predicate to be typed as SQL boolean:

import { sql, type SqlBool } from 'kysely'

await db.schema
  .createIndex('places_active_idx')
  .on('places')
  .column('host_id')
  .where(sql<SqlBool>`deleted_at IS NULL`)
  .execute()

Dropping a column

In a rolling deploy, a dropColumn migration finishes running while containers built from the previous image are still serving requests — and those containers still name the dropped column in whatever SQL leftJoinPreload/leftJoinLoad generate for that association, so every joined read of that model fails with a 42703 column "..." does not exist error until the last old container drains. A spec suite runs against a single schema and can't catch this.

Drop a column across two deploys using the model's ignoredColumns getter:

export default class Place extends ApplicationModel {
  public override get ignoredColumns() {
    return ['legacyNightlyRateCents'] as const
  }

  // ...
}

Deploy 1: remove every code reference to the column, declare it in ignoredColumns, and run psy sync. The column is omitted from the generated types, so it disappears from Place.columns() and every select list derived from it, and any lingering reference becomes a type error. Deploy 2: ship the dropColumn migration. No image carrying the ignoredColumns declaration ever names the column, so the drop is safe on every code path — and rolling back to a declaration-carrying image stays safe afterward.

Two preconditions:

• ignoredColumns is consumed by sync, not at runtime. A model that declares it without a regenerated types file isn't protected, so run psy sync and commit the regenerated types as part of deploy 1.
• The column must be nullable or carry a database default before deploy 1 ships. An image built with the declaration never puts the column in an INSERT column list, so a NOT NULL column with no default fails every create.

See also the deploy-time summary of this two-deploy rule in server deployment.

Enum types

// Create
await db.schema
  .createType('place_styles_enum')
  .asEnum(['cottage', 'cabin', 'treehouse', 'tent', 'cave'])
  .execute()

// Use in table
.addColumn('style', sql`place_styles_enum`, col => col.notNull())

// Array column
await db.schema
  .createType('bed_types_enum')
  .asEnum(['twin', 'bunk', 'queen', 'king', 'cot', 'sofabed'])
  .execute()

.addColumn('bed_types', sql`bed_types_enum[]`, col => col.notNull().defaultTo('{}'))

Array columns (enum arrays, text[], integer[], etc.) work seamlessly with Dream — set and read them as regular arrays. In-place mutations (.push(), etc.) are not detected by dirty tracking, since the check compares array identity, not contents. Always reassign the whole array to trigger an update:

const kitchen = await Kitchen.findOrFail(id)

// Creates a new array, so dirty tracking sees the change:
await kitchen.update({ appliances: [...kitchen.appliances, 'dishwasher'] })

Adding, dropping, and renaming enum values — including the two-migration pattern PostgreSQL requires for renaming a value in use — are covered in migration helpers.

Extensions

// Case-insensitive text
await DreamMigrationHelpers.createExtension(db, 'citext')

// Trigram similarity for fuzzy search
await DreamMigrationHelpers.createExtension(db, 'pg_trgm')

See migration helpers for createExtension options and the common extensions (pg_trgm, citext, uuid-ossp).

Polymorphic association columns

A polymorphic BelongsTo needs a type column (enum) and an ID column:

await db.schema
  .createType('localized_text_localizable_types_enum')
  .asEnum(['Host', 'Place', 'Room'])
  .execute()

await db.schema
  .createTable('localized_texts')
  .addColumn('id', 'uuid', col => col.primaryKey().defaultTo(sql`uuidv7()`))
  .addColumn('localizable_type', sql`localized_text_localizable_types_enum`, col => col.notNull())
  .addColumn('localizable_id', 'uuid', col => col.notNull())
  .addColumn('locale', sql`locales_enum`, col => col.notNull())
  .addColumn('title', 'varchar')
  .addColumn('created_at', 'timestamp', col => col.notNull())
  .addColumn('updated_at', 'timestamp', col => col.notNull())
  .execute()

// Index for polymorphic lookup
await db.schema
  .createIndex('localized_texts_localizable_for_locale')
  .on('localized_texts')
  .columns(['localizable_type', 'localizable_id', 'locale'])
  .unique()
  .execute()

STI columns

Single Table Inheritance uses a type column (varchar, stores the child class name) plus child-specific columns backed by check constraints:

// Base table with type column
await db.schema
  .createTable('rooms')
  .addColumn('id', 'uuid', col => col.primaryKey().defaultTo(sql`uuidv7()`))
  .addColumn('type', sql`room_types_enum`, col => col.notNull())
  .addColumn('position', 'integer', col => col.notNull())
  .addColumn('place_id', 'uuid', col => col.references('places.id').onDelete('restrict').notNull())
  .addColumn('created_at', 'timestamp', col => col.notNull())
  .addColumn('updated_at', 'timestamp', col => col.notNull())
  .execute()

// Child-specific column with a check constraint
await db.schema
  .alterTable('rooms')
  .addColumn('bath_or_shower_style', sql`bath_styles_enum`)
  .execute()

await db.schema
  .alterTable('rooms')
  .addCheckConstraint(
    'rooms_not_null_bath_or_shower_style',
    sql`type != 'Bathroom' OR bath_or_shower_style IS NOT NULL`,
  )
  .execute()

Soft delete column

.addColumn('deleted_at', 'timestamp')  // nullable, no default needed

See @SoftDelete for the decorator this column backs.

Sortable column

// Position column
.addColumn('position', 'integer', col => col.notNull())

// Deferrable unique constraint (separate migration or same) — required for @Sortable
await DreamMigrationHelpers.addDeferrableUniqueConstraint(db, 'room_position_constraint', {
  table: 'rooms',
  columns: ['place_id', 'position'],
})

See addDeferrableUniqueConstraint in migration helpers.

Raw Kysely data queries in migrations

A migration's db handle is the same Kysely instance Dream builds everywhere else, with CamelCasePlugin applied — write table and column identifiers in camelCase, and Kysely translates them to the real snake_case columns on the way out and camelCases every result key on the way back, unconditionally. This is easy to get wrong in a migration file because the rest of the file — DDL, DreamMigrationHelpers calls — is written in snake_case, so a raw data query reads like it should be too:

export async function up(db: Kysely<any>): Promise<void> {
  const rooms = await db
    .selectFrom('rooms')
    .select(['id', 'hostId'])
    .execute()

  for (const room of rooms) {
    await db
      .updateTable('bookings')
      .set({ hostId: room.hostId })
      .where('roomId', '=', room.id)
      .execute()
  }
}

Forcing a new transaction in migrations

Dream runs all pending migrations in a single transaction by default. If a migration file contains the literal string DreamMigrationHelpers.dropEnumValue or DreamMigrationHelpers.newTransaction(), Dream runs that file in its own transaction, separate from the migrations before and after it:

export async function up(db: Kysely<any>): Promise<void> {
  DreamMigrationHelpers.newTransaction()
  await db.schema.alterTable('places')
  // ...
}

This detection is a naive string search on the file contents, not AST analysis — see the caveat in migration helpers about renaming the DreamMigrationHelpers import.

Along with migrations, Psychic provides additional cli commands for governing migrations:

NODE_ENV=test pnpm psy db:drop
NODE_ENV=test pnpm psy db:create
NODE_ENV=test pnpm psy db:migrate
NODE_ENV=test pnpm psy db:rollback
NODE_ENV=test pnpm psy db:reset # drop, create, migrate

Dream runs Kysely migrations with allowUnorderedMigrations: true, which facilitates collaboration by large teams (see https://github.com/kysely-org/kysely/issues/697 for the rationale). Keysely's migration documentation may be a helpful reference. Of particular importance is the following guarantee:

The migration methods use a lock on the database level and parallel calls are executed serially. This means that you can safely call migrateToLatest and other migration methods from multiple server instances simultaneously and the migrations are guaranteed to only be executed once. The locks are also automatically released if the migration process crashes or the connection to the database fails.

Also, since Dream currently only supports Postgres, and Kysely runs all migrations in a transaction on Postgres, Dream migrations are run in a transaction. This means that if multiple migration files are being migrated, and the last one fails, all of them will be rolled back. This is a powerful guarantee since it always leaves the database in a known state: If a final migration file adds a unique index that cannot be applied to the database due to existing records, then the code can safely be rolled back to the previous version while a fix is implemented, and the database schema will be in the state that that previous code version defined and is known to work with.

Migration Helpers

Dream provides migration helpers to simplify common database operations.