Migrations

Your bot's tables have to exist on every database it uses, like your laptop, a teammate's machine, and production. If you create them by running CREATE TABLE yourself, each database needs the same statements in the same order. Miss one, and your bot fails at the first query that touches a table or column that database doesn't have.

A migration is a file that exports two functions. up makes one change to your database, like creating a table. down undoes that change, and Kysely only runs it when you roll the migration back. Kysely keeps a table in each database listing the migrations that database has already run. By default, the plugin runs the rest while your bot starts, before any service can query the tables.

import type { Kysely } from 'kysely';

export async function up(db: Kysely<any>): Promise<void> {
    await db.schema
        .createTable('users')
        .addColumn('id', 'serial', (col) => col.primaryKey())
        .addColumn('discord_id', 'text', (col) =>
            col.notNull().unique()
        )
        .addColumn('streak', 'integer', (col) =>
            col.notNull().defaultTo(0)
        )
        .execute();
}

export async function down(db: Kysely<any>): Promise<void> {
    await db.schema.dropTable('users').execute();
}

up creates the users table, while down drops it. Both take Kysely<any>, because each migration runs against whatever tables the migrations before it created. Your schema describes the tables as they are once every migration has run.

Kysely compares the names of your migrations against the names recorded in its table to find the ones that haven't run. The shape you give path under migrations sets each name, so changing the shape later can make a database's recorded migrations look missing. The last column shows the name Kysely records for a file called 001-create-users.ts.

Whichever shape you pick, path can be relative or absolute. A relative path like './src/migrations' resolves against the folder you start your bot from, so starting the bot from any other folder points the plugin at the wrong place. The samples on this page build path with resolve(import.meta.dirname, './migrations') instead. That resolves against the folder of bot.ts, so it stays correct wherever you start the bot.

The first sample exports up and down by name. A migration file can also default-export one object holding both functions:

import type { Kysely } from 'kysely';

export default {
    async up(db: Kysely<any>): Promise<void> {
        await db.schema
            .alterTable('users')
            .addColumn('username', 'text')
            .execute();
    },
    async down(db: Kysely<any>): Promise<void> {
        await db.schema
            .alterTable('users')
            .dropColumn('username')
            .execute();
    }
};

Only a directory path reads that form, because Kysely checks a file's default export before its named exports. If you list 002-add-username.ts in a file or array path, the plugin throws PluginKyselyInvalidMigrationModule, since it only looks for named up and down exports. Named exports work with all three shapes, so use them unless all your migrations are in one directory.

Kysely sorts every migration name by character code before it runs them, whichever shape you use. Start each file name with a number like 001- or a timestamp to set the order. Pad the number with zeros, since 10-add-streaks sorts before 9-create-users. nameComparator doesn't change that order, because Kysely sorts the names again after the plugin's own sort.

Kysely throws this error when its table lists a migration name that doesn't match any of your current files:

corrupted migrations: previously executed migration 001-create-users.ts is missing

With a file or an array path, a migration's name keeps its file extension, so 001-create-users.ts and 001-create-users.js are two different migrations. If a database ran 001-create-users.ts under seedcord dev, your built bot throws this error against that database, because its file is 001-create-users.js. If you switch a project from a directory to an array, the same error comes up, since 001-create-users becomes 001-create-users.ts.

With a directory path, Kysely records each migration's name without the extension, so the .ts and the .js file both match the same recorded name. Use a directory path, and keep using it once any database has run your migrations.

By default the plugin runs every pending migration while the bot starts. If a migration throws, startup stops. The plugin closes the pool, and your bot never reaches its handlers.

onStartup changes what runs at that point.

Set it to false when you run migrations as their own deploy step. A failing migration stops the bot either way, so what changes is when you find out. During startup, the deploy has already replaced your running bot, and the new one fails on the tables it expects. As a deploy step, the migration fails while the old bot keeps serving.

import { resolve } from 'node:path';

import { KyselyPostgres } from '@seedcord/plugin-kysely-postgres';

seedcord.attach('db', KyselyPostgres, {
    dir: resolve(import.meta.dirname, './services'),
    connectionString: 'postgres://localhost:5432/seedcord',
    migrations: {
        path: resolve(import.meta.dirname, './migrations'),
        onStartup: false
    }
});

With onStartup: false, this bot starts on whatever tables the database already has.

You can also run migrations from your own code, like an owner-only command that rolls back the last migration, or a script that migrates before your bot starts. The plugin has a method for each job. Once startup has connected, a handler calls them on this.core.db.

migrateUp and migrateDown take a StepMigrationOptions, whose only key is steps. listMigrations() returns Kysely's MigrationInfo objects. Each one's executedAt holds a Date once that migration has run, and undefined before.

migrate takes three optional keys. If you pass a target, migrate ignores direction and steps.

NO_MIGRATIONS comes from kysely/migration.

await db.migrate({ target: NO_MIGRATIONS });

That call runs every down in reverse order. Each one only undoes what you wrote in it, so your tables are gone once every down drops what its up created.

steps has to be a whole number of zero or more. A fraction, a negative number, NaN, or Infinity throws PluginKyselyInvalidStepCount. Zero runs nothing. If you pass any other direction, migrate throws PluginKyselyUnknownDirection.

If a migration throws an Error, you get that error unchanged, so a failing CREATE TABLE shows you Postgres's own message. The plugin throws its own codes for problems around the migrations.

If a file in a directory doesn't export up, Kysely skips it without an error.

allowUnorderedMigrations matters when two branches each add a migration. Say one branch merges 003-add-streaks after the database already ran 004-add-guilds. Unless you set it to true, Kysely throws a corrupted migrations error before running anything.