# Kysely and Postgres

Connect a gateway or http bot to Postgres through Kysely with the kysely plugin. Covers its options, where the pool comes from, creating the database, startup and shutdown, and the Kysely instance.

A bot on Postgres usually creates a `pg` pool in `bot.ts`, runs a migration script before it starts, and calls `pool.end()` on shutdown. A handler can import that pool before the tables exist. If you forget `pool.end()`, the connections stay open after the bot stops. [`@seedcord/plugin-kysely-postgres`](https://docs.seedcord.org/packages/plugin-kysely-postgres/latest) opens the pool, runs your migrations, and loads your service classes from a directory while the bot starts, then closes the pool when it stops. Your queries are ordinary [Kysely](https://kysely.dev/docs/intro) queries, so anything in Kysely's own docs applies as written.

```sh
pnpm add @seedcord/plugin-kysely-postgres kysely pg
```

```ts title="src/bot.ts"
import { resolve } from 'node:path';

import { Seedcord } from '@seedcord/gateway';
import { KyselyPostgres } from '@seedcord/plugin-kysely-postgres';
import { GatewayIntentBits } from 'discord.js';

export const seedcord = new Seedcord({
    bot: {
        clientOptions: { intents: [GatewayIntentBits.Guilds] },
        interactions: {
            path: resolve(import.meta.dirname, './handlers')
        },
        commands: {
            path: resolve(import.meta.dirname, './commands')
        },
        events: { path: null }
    },
    subscribers: { path: null }
}).attach('db', KyselyPostgres, {
    dir: resolve(import.meta.dirname, './services'),
    connectionString: 'postgres://localhost:5432/seedcord',
    migrations: {
        path: resolve(import.meta.dirname, './migrations')
    }
});

export default seedcord;
```

This sample builds a gateway bot. The same `attach` call works on an http `Seedcord` from `@seedcord/http`. An edge bot can't [attach plugins](/plugins) at all.

`dir` and `migrations` are the two required options. The sample also passes `connectionString`. Without one, `pg` reads `PGHOST`, `PGPORT`, `PGUSER`, `PGPASSWORD`, and `PGDATABASE` from the environment.

## The options

{/* prettier-ignore-start */}

| option             | required | what it sets                                                                           |
| ------------------ | -------- | -------------------------------------------------------------------------------------- |
| `dir`              | yes      | the directory the plugin scans for service classes                                     |
| `migrations`       | yes      | where your migration files are and when they run                                       |
| `pool`             | no       | a `pg` pool to reuse, or the config to build one from                                  |
| `connectionString` | no       | the Postgres URL                                                                       |
| `onConnectSQL`     | no       | statements to run on every new connection                                              |
| `forceInsecureSSL` | no       | turns off certificate verification on `ssl`                                            |
| `kysely`           | no       | any Kysely config except the dialect, which is Postgres here                           |
| `timeout`          | no       | how long closing the pool may take during shutdown, in milliseconds. Defaults to 10000 |

{/* prettier-ignore-end */}

If you pass a config object as `pool`, `connectionString` replaces its `connectionString` key and `forceInsecureSSL` replaces its `ssl` key. Set `forceInsecureSSL` when your database's certificate doesn't verify, like a self-signed one, because `pg` refuses that connection otherwise. It sets `ssl` to `{ rejectUnauthorized: false }`, which turns certificate checks off entirely. The connection then no longer proves which server it reached. It also turns TLS on, so `pg` can't connect to a local Postgres that doesn't have TLS set up.

> **Warning**
>
> If your connection string has an `ssl` or `sslmode` parameter, like the `?sslmode=require` many hosted databases add, `pg` takes its TLS settings from the string and ignores `forceInsecureSSL`. Change the parameter to `sslmode=no-verify` to turn certificate checks off from the string itself.

A Postgres setting like the session time zone only applies to the connection that ran it. `onConnectSQL` runs your statements on every connection the pool opens, including the ones it adds later. The plugin doesn't wait for them to finish. `pg` still runs the first statement before your query, though with several statements, your query can run before the later ones. If a statement fails, the plugin logs the error and skips the statements after it, without throwing.

The [migrations](/plugins/kysely-migrations) page explains the keys under `migrations`.

## Where the connection comes from

Most bots only need a URL. You'll pass more when you tune the pool, or when other code in your app already has a pool. The plugin checks `pool` to pick between three setups.

**A connection string on its own** is the shortest setup, which the first sample uses. The plugin builds the pool for you.

**A `PoolConfig` object** sets `pg`'s own pool options, like `max` and `idleTimeoutMillis`.

```ts
seedcord.attach('db', KyselyPostgres, {
    dir: resolve(import.meta.dirname, './services'),
    pool: {
        connectionString: 'postgres://localhost:5432/seedcord',
        max: 20
    },
    onConnectSQL: ["set time zone 'UTC'"],
    migrations: {
        path: resolve(import.meta.dirname, './migrations')
    }
});
```

That pool holds up to 20 connections, up from `pg`'s default of 10. Each new connection runs `set time zone 'UTC'` first.

**A `Pool` you built yourself** fits when other code in your app already queries through that pool. The plugin uses it as you built it, so it ignores `connectionString` and `forceInsecureSSL`. It also skips creating the database, which the next section covers.

```ts
import { resolve } from 'node:path';

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

const pool = new Pool({
    connectionString: 'postgres://localhost:5432/seedcord'
});

seedcord.attach('db', KyselyPostgres, {
    dir: resolve(import.meta.dirname, './services'),
    pool,
    migrations: {
        path: resolve(import.meta.dirname, './migrations')
    }
});
```

> **Warning**
>
> Shutdown always closes the pool, including the `pool` you passed in. Any other code using it can't query once the bot has shut down.

## The plugin creates the database if it's missing

On a new machine or a fresh deploy, the database in your URL often doesn't exist yet, so Postgres refuses the connection. The plugin creates that database first.

Before it opens your pool, the plugin connects to the `postgres` database with the same host and credentials. It takes the target name from `database` on your pool config, or from the path of your connection string, so `postgres://localhost:5432/seedcord` targets `seedcord`. When that database doesn't exist, the plugin runs `CREATE DATABASE` for it.

Two bots starting at once can both try to create it. The second one gets Postgres's duplicate-database error, which the plugin ignores.

The plugin skips the check for:

* a `Pool` you built yourself, since it never gets a config to build that first connection from
* a target of `postgres` itself
* a config without a database name, including one that leaves the name to `PGDATABASE`
* a connection string that doesn't parse as a URL

If anything else fails here, like a database user without permission to create databases or a Postgres server that isn't running, the plugin throws `PluginKyselyBootstrapFailed`.

## Startup and shutdown

`init()` connects, runs your migrations unless you [turned that off](/plugins/kysely-migrations), then loads every service class from `dir`. If any of those steps fails, `init()` closes the pool itself before it rethrows the error, since seedcord doesn't call `dispose()` for a plugin whose `init()` failed. A bot whose migration fails never reaches its handlers.

If the pool can't connect after that check, `init()` throws `PluginKyselyConnectionFailed`. Its message includes the database name when the plugin could find one, and pg's own error is in its `cause`.

> **Warning**
>
> The migrations directory has to exist before the first start. The plugin treats a missing path as a single migration file, so importing it throws Node's `ERR_MODULE_NOT_FOUND`. An empty directory works.

Shutdown runs fewer steps. `dispose()` clears the service registry, then closes the pool. If closing the pool fails, `dispose()` throws `PluginKyselyDisconnectFailed`.

## The Kysely instance

[Services](/plugins/kysely-services) hold the queries for one table each. A query that joins tables, or a transaction that writes to two of them, uses the Kysely instance directly. That instance is [`connection`](https://docs.seedcord.org/packages/plugin-kysely-postgres/latest/kysely-postgres#connection), which a handler reads as `this.core.db.connection` once [codegen has typed `db`](/plugins/typing).

`connection` is only set after the pool connects. If you read it before then, you get `undefined`, even though its type says it's always set.

TypeScript checks the table and column names in those queries against a [schema](/plugins/kysely-schema) you declare yourself.