migrations

Under the hood, Psychic leverages kysely to run our 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()
}

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

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

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.

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

NODE_ENV=test yarn psy db:drop
NODE_ENV=test yarn psy db:create
NODE_ENV=test yarn psy db:migrate
NODE_ENV=test yarn psy db:rollback
NODE_ENV=test yarn 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.