Use Kysely with ClickHouse Managed Postgres

Kysely is a type-safe SQL query builder for TypeScript. In this guide, you connect Kysely to ClickHouse Managed Postgres through the pg driver with verified TLS, create a table with Kysely migrations, and serve a small to-do API from Node.js.

Prerequisites

Node.js 22.18 or later, which runs TypeScript files directly without a build step. The guide’s files use the .mts extension, so Node.js treats them as ES modules in both CommonJS and ES module projects.
A ClickHouse Cloud account.
psql, to create the database. You can also run the CREATE DATABASE statement in the SQL console.
curl to call the API.

This guide was tested with Node.js 24.21, Kysely 0.29.6, pg 8.23.1, TypeScript 7.0.2, and PostgreSQL 18.

Create a ClickHouse Managed Postgres service

In the ClickHouse Cloud console, click New service and select Postgres. The instance is ready in a few minutes. See the quickstart for a walkthrough.

Get your connection details

Click Connect in the left sidebar of your service. Keep Directly selected and turn on Use SSL: the url connection string now ends with sslmode=verify-full&sslrootcert=.... Click Download CA certificate to download your service’s CA certificate, named <service-name>-ca-certificate.pem, and copy the connection string. This guide connects directly to Postgres on port 5432, for two reasons:

A Node.js server is a long-lived process. The pg Pool already keeps a small set of connections open and reuses them, so a server-side pooler adds little.
Kysely’s Migrator holds a session-level advisory lock (pg_advisory_lock) while it runs migrations. PgBouncer runs in transaction pooling mode, so taking the lock, running the migrations, and releasing the lock can each land on a different backend connection. Through PgBouncer, the lock can stay held on a pooled connection, and later migration runs block waiting for it.

If you later run many app instances or serverless functions, you can point the app’s connection string at PgBouncer (port 6432, same host). Kysely with pg works in transaction pooling mode because pg uses unnamed prepared statements by default. Keep running migrations against port 5432. See PgBouncer connection pooling.

Set up the project

Create a project and install Kysely, the pg driver, and TypeScript:

mkdir kysely-todo && cd kysely-todo
npm init -y
npm install kysely pg
npm install --save-dev typescript @types/node @types/pg
npm pkg set scripts.migrate="node --env-file=.env src/migrate.mts" scripts.start="node --env-file=.env src/server.mts" scripts.typecheck="tsc"
mkdir src migrations

Adding to an existing app?Skip mkdir kysely-todo && cd kysely-todo and npm init -y. In your project, run the following commands, which add migrate and typecheck scripts but leave your start script alone, then continue with Configure the connection.

npm install kysely pg
npm install --save-dev typescript @types/node @types/pg
npm pkg set scripts.migrate="node --env-file=.env src/migrate.mts" scripts.typecheck="tsc"
mkdir -p src migrations

Configure the connection

Move the CA certificate you downloaded into your project directory and rename it to ca-certificate.pem. Then create a database for the app:

psql "postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:5432/postgres?sslmode=verify-full&sslrootcert=ca-certificate.pem" -c "CREATE DATABASE guide_kysely;"

CREATE DATABASE

Add the connection string you copied to a .env file, or to your existing .env. Replace <PASSWORD> with your password, the database name with guide_kysely, and set sslrootcert to ca-certificate.pem:

.env

DATABASE_URL="postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:5432/guide_kysely?sslmode=verify-full&sslrootcert=ca-certificate.pem"

pg reads sslmode and sslrootcert from the connection string: verify-full makes it check that the server certificate is signed by your service’s CA and matches the hostname. sslrootcert is resolved relative to the working directory, which is the project root when you use npm run.

Keep sslmode=verify-full in the URL. Without sslmode, pg connects without TLS at all. Don’t also pass an ssl object to Pool: when the URL contains sslmode, the URL’s settings replace your ssl object. If you need to load the CA from somewhere other than a file, remove sslmode and sslrootcert from the URL and pass ssl: { ca } to Pool instead.

Add a tsconfig.json so that tsc type-checks the same files that Node.js runs. If your app already has a tsconfig.json, merge these compilerOptions and the migrations folder into it:

tsconfig.json

{
  "compilerOptions": {
    "target": "esnext",
    "module": "nodenext",
    "types": ["node"],
    "strict": true,
    "noEmit": true,
    "allowImportingTsExtensions": true,
    "erasableSyntaxOnly": true,
    "verbatimModuleSyntax": true,
    "skipLibCheck": true
  },
  "include": ["src", "migrations"]
}

Define the database interface

Kysely types every query from an interface that describes your tables. Create src/db.mts with the interface and a Kysely instance that uses PostgresDialect with a pg Pool:

src/db.mts

import { Kysely, PostgresDialect, type Generated } from 'kysely'
import { Pool } from 'pg'

export interface TodoTable {
  id: Generated<number>
  title: string
  done: Generated<boolean>
  created_at: Generated<Date>
}

export interface Database {
  todo: TodoTable
}

export const db = new Kysely<Database>({
  dialect: new PostgresDialect({
    pool: new Pool({
      connectionString: process.env.DATABASE_URL,
      max: 10,
    }),
  }),
})

Generated marks columns that the database fills in, so they’re optional on insert.

Create and run a migration

This guide uses Kysely’s built-in Migrator with FileMigrationProvider, which is part of the core kysely package. The separate kysely-ctl CLI is optional and wraps the same Migrator. Create a migration file. Kysely runs migrations in alphanumeric order of their file names, so prefix them with a date:

migrations/2026-09-30_create_todo.mts

import { type Kysely, sql } from 'kysely'

export async function up(db: Kysely<any>): Promise<void> {
  await db.schema
    .createTable('todo')
    .addColumn('id', 'integer', (col) => col.primaryKey().generatedAlwaysAsIdentity())
    .addColumn('title', 'text', (col) => col.notNull())
    .addColumn('done', 'boolean', (col) => col.notNull().defaultTo(false))
    .addColumn('created_at', 'timestamptz', (col) => col.notNull().defaultTo(sql`now()`))
    .execute()
}

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

Create src/migrate.mts to apply all pending migrations. In Kysely 0.29, import Migrator and FileMigrationProvider from kysely/migration:

src/migrate.mts

import { promises as fs } from 'node:fs'
import path from 'node:path'
import { FileMigrationProvider, Migrator } from 'kysely/migration'
import { db } from './db.mts'

const migrator = new Migrator({
  db,
  provider: new FileMigrationProvider({
    fs,
    path,
    migrationFolder: path.join(import.meta.dirname, '../migrations'),
  }),
})

const { error, results } = await migrator.migrateToLatest()

for (const result of results ?? []) {
  console.log(`${result.status}: ${result.migrationName}`)
}

await db.destroy()

if (error) {
  console.error('Migration failed:', error)
  process.exit(1)
}

Run the migration:

npm run migrate

Success: 2026-09-30_create_todo

Kysely records applied migrations in the kysely_migration table, so running the command again does nothing.

To confirm that pg really verifies the certificate, run the migration without sslrootcert. Node.js doesn’t trust your service’s CA by default, so the connection fails:

DATABASE_URL="postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:5432/guide_kysely?sslmode=verify-full" npm run migrate

Migration failed: Error: unable to verify the first certificate; if the root CA is installed locally, try running Node.js with --use-system-ca

Query the database

Create src/server.mts, a small HTTP server that creates, reads, updates, and deletes to-dos with Kysely. In an existing app, you can use the same queries in your own route handlers:

src/server.mts

import { createServer } from 'node:http'
import { json } from 'node:stream/consumers'
import { db } from './db.mts'

const port = Number(process.env.PORT ?? 3000)

const server = createServer(async (req, res) => {
  const { pathname } = new URL(req.url ?? '/', 'http://localhost')
  const id = Number(pathname.match(/^\/todos\/(\d+)$/)?.[1])

  try {
    let result: unknown

    if (req.method === 'GET' && pathname === '/todos') {
      result = await db.selectFrom('todo').selectAll().orderBy('id').execute()
    } else if (req.method === 'POST' && pathname === '/todos') {
      const { title } = (await json(req)) as { title: string }
      result = await db.insertInto('todo').values({ title }).returningAll().executeTakeFirstOrThrow()
    } else if (req.method === 'PATCH' && id) {
      const { done } = (await json(req)) as { done: boolean }
      result = await db.updateTable('todo').set({ done }).where('id', '=', id).returningAll().executeTakeFirst()
    } else if (req.method === 'DELETE' && id) {
      result = await db.deleteFrom('todo').where('id', '=', id).returningAll().executeTakeFirst()
    } else {
      res.writeHead(404).end()
      return
    }

    res.writeHead(result ? 200 : 404, { 'content-type': 'application/json' })
    res.end(JSON.stringify(result ?? { error: 'not found' }, null, 2))
  } catch (err) {
    console.error(err)
    res.writeHead(500).end()
  }
})

server.listen(port, () => console.log(`Listening on http://localhost:${port}/todos`))

Because every query is checked against the Database interface, a misspelled table or column name, or an insert without the required title, fails type-checking. Check the project:

npm run typecheck

Verify

Start the server:

npm start

In an existing app, where npm start runs your own app, start the example server with node --env-file=.env src/server.mts instead. In another terminal, create, update, and delete a few to-dos:

curl -X POST localhost:3000/todos -d '{"title": "Create a Managed Postgres service"}'
curl -X POST localhost:3000/todos -d '{"title": "Connect Kysely with verify-full"}'
curl -X POST localhost:3000/todos -d '{"title": "Try PgBouncer later"}'
curl -X PATCH localhost:3000/todos/1 -d '{"done": true}'
curl -X DELETE localhost:3000/todos/3

Open http://localhost:3000/todos in your browser to list the remaining to-dos. The response looks like this:

[
  {
    "id": 1,
    "title": "Create a Managed Postgres service",
    "done": true,
    "created_at": "2026-09-30T16:47:31.081Z"
  },
  {
    "id": 2,
    "title": "Connect Kysely with verify-full",
    "done": false,
    "created_at": "2026-09-30T16:47:31.142Z"
  }
]

To see the same data in the console, open SQL console in the left sidebar of your service, expand guide_kysely and the public schema, and click the todo table. The kysely_migration and kysely_migration_lock tables that Kysely created are listed next to it:

id	title	done	created_at
1	Create a Managed Postgres service	true	2026-09-30 16:47:31.081942+00
2	Connect Kysely with verify-full	false	2026-09-30 16:47:31.142225+00

Next steps

Connection: connection strings, PgBouncer, and TLS.
Settings: tune PostgreSQL and PgBouncer parameters such as max_connections.
Read replicas: scale read-heavy workloads.
Kysely documentation: joins, transactions, and type generation.

Get started

Features

Guides

Pricing

ClickHouse Integration

Migrations

Monitoring

FAQ

Use Kysely with ClickHouse Managed Postgres

Prerequisites

Create a ClickHouse Managed Postgres service

Get your connection details

Set up the project

Configure the connection

Define the database interface

Create and run a migration

Query the database

Verify

Next steps

​Prerequisites

​Create a ClickHouse Managed Postgres service

​Get your connection details

​Set up the project

​Configure the connection

​Define the database interface

​Create and run a migration

​Query the database

​Verify

​Next steps

Prerequisites

Create a ClickHouse Managed Postgres service

Get your connection details

Set up the project

Configure the connection

Define the database interface

Create and run a migration

Query the database

Verify

Next steps