# Migrations

Create and change your Postgres tables with Kysely migrations in the kysely plugin, and control when they run.

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.

```ts title="src/migrations/001-create-users.ts"
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](https://preview.seedcord.org/guide/plugins/kysely-schema/) describes the tables as they are once every migration has run.

## Where to put the files

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](https://preview.seedcord.org/guide/plugins/) 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`.

> **Warning**
>
> The plugin also skips a `.cjs` or `.cts` file without an error, so that migration never runs.

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](#the-rest-of-the-options) 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:

```ts title="src/migrations/002-add-username.ts"
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.

### When a previously executed migration is missing

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

```txt output
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.

## When they run

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](https://preview.seedcord.org/guide/build/server/#the-health-server) only sees your bot once its migrations have finished.

`onStartup` changes what runs at that point.

{/* prettier-ignore-start */}

| `onStartup`                                                                                                                  | what happens while the bot starts                                |
| ---------------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------- |
| left out, or `true`                                                                                                          | runs every pending migration                                     |
| `false`                                                                                                                      | runs nothing                                                     |
| a [`MigrationOptions`](https://seedcord.org/docs/packages/plugin-kysely-postgres/latest/interfaces/migration-options) object | runs to the `target`, or in the `direction` and `steps` you pass |

{/* prettier-ignore-end */}

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.

```ts
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.

## Running them yourself

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

{/* prettier-ignore-start */}

| method                                                                                                                                        | what it does                                                                                      |
| --------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------------- |
| [`migrate(options?)`](https://seedcord.org/docs/packages/plugin-kysely-postgres/latest/classes/kysely-postgres#migrate)                       | runs to the latest migration, or to the target or direction you pass                              |
| [`migrateUp(options?)`](https://seedcord.org/docs/packages/plugin-kysely-postgres/latest/classes/kysely-postgres#migrate-up)                  | runs one pending migration, or `steps` of them                                                    |
| [`migrateDown(options?)`](https://seedcord.org/docs/packages/plugin-kysely-postgres/latest/classes/kysely-postgres#migrate-down)              | reverts the last migration, or the last `steps` of them. It stops at a migration without a `down` |
| [`listMigrations()`](https://seedcord.org/docs/packages/plugin-kysely-postgres/latest/classes/kysely-postgres#list-migrations)                | every migration, run or pending                                                                   |
| [`listPendingMigrations()`](https://seedcord.org/docs/packages/plugin-kysely-postgres/latest/classes/kysely-postgres#list-pending-migrations) | the ones that haven't run yet                                                                     |

{/* prettier-ignore-end */}

`migrateUp` and `migrateDown` take a [`StepMigrationOptions`](https://seedcord.org/docs/packages/plugin-kysely-postgres/latest/interfaces/step-migration-options), 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`.

{/* prettier-ignore-start */}

| key         | what it accepts                                           |
| ----------- | --------------------------------------------------------- |
| `target`    | a migration name, or `NO_MIGRATIONS` to revert everything |
| `direction` | `'latest'`, `'up'`, or `'down'`. Defaults to `'latest'`   |
| `steps`     | how many to run under `'up'` or `'down'`. Defaults to 1   |

{/* prettier-ignore-end */}

`NO_MIGRATIONS` comes from `kysely/migration`.

```ts
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.

> **Warning**
>
> If a migration in that range has no `down`, Kysely skips it and keeps its row in the migrations table, then still rolls back every older one. The next start throws "corrupted migrations: expected previously executed migration", because the recorded list no longer starts with your first migration. Give every migration a `down` before you roll back to `NO_MIGRATIONS`.

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

## When a migration fails

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.

{/* prettier-ignore-start */}

| what went wrong                                                     | what you get                                                                      |
| ------------------------------------------------------------------- | --------------------------------------------------------------------------------- |
| a file in the folder that doesn't export `up`                       | `PluginKyselyInvalidMigrationModule`, with the file path                          |
| two files with the same name once the folder and extension are gone | `CoreDuplicateName`, with both file paths                                         |
| a migration that throws something other than an `Error`             | `PluginKyselyNonErrorFailure`                                                     |
| a `dir` that doesn't exist                                          | `CorePathUnreadable` under `seedcord dev`, `CoreDirectoryNotBuilt` in a built bot |

{/* prettier-ignore-end */}

## The rest of the options

{/* prettier-ignore-start */}

| option                     | what it sets                                                                                                                                                                           |
| -------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `allowUnorderedMigrations` | whether a new migration can sort before one that already ran. Defaults to `false`                                                                                                      |
| `migrationTableName`       | the table recording which migrations ran. Defaults to `kysely_migration`                                                                                                               |
| `migrationLockTableName`   | the table Kysely locks so two bots don't migrate at once. Defaults to `kysely_migration_lock`                                                                                          |
| `migrationTableSchema`     | the Postgres schema holding both of those tables                                                                                                                                       |
| `nameComparator`           | orders the recorded migrations that share a timestamp when Kysely reads them back from the database. Defaults to `localeCompare`. It never changes the order pending migrations run in |

{/* prettier-ignore-end */}

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