Kysely and Postgres

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 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 (opens in a new tab) queries, so anything in Kysely's own docs applies as written.

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

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

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.

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 page explains the keys under migrations.

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.

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.

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

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.

init() connects, runs your migrations unless you turned that off, 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.

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

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, which a handler reads as this.core.db.connection once codegen has typed db.

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 you declare yourself.