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. If you miss one, 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 up and, if you want to be able to undo it, down. 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. Leave down out only on a change you'll never roll back, because a rollback can't undo it. 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.

The plugin reads one folder, the dir under migrations, and its subfolders too. A relative dir is relative to root and has to point inside it, as the folder rules describe. With root: './src', './migrations' means src/migrations.

Every code file in that folder has to be a migration. If a file doesn't export up, the plugin throws PluginKyselyInvalidMigrationModule with the file's path, so keep helper files out of the folder. The plugin reads .ts, .tsx, .mts, .js, .jsx, and .mjs files. It skips type declarations, dotfiles, dot-folders, and node_modules.

Kysely compares the names of your migrations against the names recorded in its table to find the ones that haven't run. A migration's name is its file name without the folder or the extension, so 001-create-users.ts is recorded as 001-create-users. Moving that file into a subfolder keeps its name. Two files with the same name in different folders, like users/001-init.ts and guilds/001-init.ts, throw CoreDuplicateName with both paths.

Kysely sorts the pending migrations by name, by character code, before it runs them. 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. The options at the end say what it does change.

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

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();
    }
};

Both forms work in every file. If a file has a default export with an up function, the plugin reads that one and ignores the file's named exports.

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 is missing

The recorded name has no file extension, so 001-create-users.ts under seedcord dev and 001-create-users.js in your built bot match the same recorded name. The error comes up if you rename or delete a migration file after a database ran it.

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. A deploy that waits on the health path only sees your bot once its migrations have finished.

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. If the migration runs during startup, the new bot fails on the tables it expects. On a host that replaces your bot as soon as the new one starts, the old one is already gone by then. As a deploy step, the migration fails while the old bot keeps serving.

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

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

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

Under seedcord dev, saving a file in the migrations folder asks for a restart. The plugin ran the migrations when the bot started, so a hot reload would leave the database as it was.

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.

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.

nameComparator matters when two names differ in case, like B-seed and a-seed, and one run applied both within the same millisecond. Kysely sorted your files by character code, so it ran B-seed first. It reads the recorded names back with localeCompare, which puts a-seed first. The next start then throws corrupted migrations. Pass (a, b) => (a < b ? -1 : a > b ? 1 : 0) as nameComparator to read them back in the order they ran.