> ## Documentation Index
> Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Use Kysely with ClickHouse Managed Postgres

> Connect a TypeScript app to ClickHouse Managed Postgres with Kysely and node-postgres, using verified TLS, typed queries, and migrations

export const BetaBadge = ({link, galaxyTrack, galaxyEvent}) => {
  if (link) {
    return <a href={link} target="_blank" rel="noopener noreferrer" className="betaBadge" onClick={galaxyTrack && galaxyEvent ? galaxyOnClick(galaxyEvent) : undefined}>
                <span>Beta</span>
            </a>;
  }
  return <a href="https://clickhouse.com/docs/reference/settings/beta-and-experimental-features#beta-features" className="betaBadge">
            <span>Beta feature</span>
        </a>;
};

<BetaBadge link="https://clickhouse.com/cloud/postgres" galaxyTrack={true} galaxyEvent="docs.managed-postgres.guides-kysely-beta" />

[Kysely](https://kysely.dev) is a type-safe SQL query builder for TypeScript. In this guide, you connect Kysely to ClickHouse Managed Postgres through the [`pg`](https://node-postgres.com/) driver with verified TLS, create a table with Kysely migrations, and serve a small to-do API from Node.js.

<h2 id="prerequisites">
  Prerequisites
</h2>

* 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`](https://www.postgresql.org/download/), to create the database. You can also run the `CREATE DATABASE` statement in the [SQL console](/docs/integrations/connectors/sql-clients/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.

<h2 id="create-service">
  Create a ClickHouse Managed Postgres service
</h2>

In the ClickHouse Cloud console, click **New service** and select **Postgres**. The instance is ready in a few minutes. See the [quickstart](/docs/products/managed-postgres/quickstart) for a walkthrough.

<h2 id="connection-details">
  Get your connection details
</h2>

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](/docs/products/managed-postgres/connection#pgbouncer).

<h2 id="setup">
  Set up the project
</h2>

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

```bash theme={null}
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
```

<Tip>
  **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](#connection-config).

  ```bash theme={null}
  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
  ```
</Tip>

<h3 id="connection-config">
  Configure the connection
</h3>

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

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

```text theme={null}
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`:

```bash title=".env" theme={null}
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`.

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

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:

```json title="tsconfig.json" theme={null}
{
  "compilerOptions": {
    "target": "esnext",
    "module": "nodenext",
    "types": ["node"],
    "strict": true,
    "noEmit": true,
    "allowImportingTsExtensions": true,
    "erasableSyntaxOnly": true,
    "verbatimModuleSyntax": true,
    "skipLibCheck": true
  },
  "include": ["src", "migrations"]
}
```

<h2 id="database-interface">
  Define the database interface
</h2>

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

```ts title="src/db.mts" theme={null}
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.

<h2 id="migrations">
  Create and run a migration
</h2>

This guide uses Kysely's built-in `Migrator` with `FileMigrationProvider`, which is part of the core `kysely` package. The separate [`kysely-ctl`](https://github.com/kysely-org/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:

```ts title="migrations/2026-09-30_create_todo.mts" theme={null}
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`:

```ts title="src/migrate.mts" theme={null}
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:

```bash theme={null}
npm run migrate
```

```text theme={null}
Success: 2026-09-30_create_todo
```

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

<Tip>
  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:

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

  ```text theme={null}
  Migration failed: Error: unable to verify the first certificate; if the root CA is installed locally, try running Node.js with --use-system-ca
  ```
</Tip>

<h2 id="query">
  Query the database
</h2>

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:

```ts title="src/server.mts" theme={null}
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:

```bash theme={null}
npm run typecheck
```

<h2 id="verify">
  Verify
</h2>

Start the server:

```bash theme={null}
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:

```bash theme={null}
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](http://localhost:3000/todos) in your browser to list the remaining to-dos. The response looks like this:

```json theme={null}
[
  {
    "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 |

<h2 id="next-steps">
  Next steps
</h2>

* [Connection](/docs/products/managed-postgres/connection): connection strings, PgBouncer, and TLS.
* [Settings](/docs/products/managed-postgres/settings): tune PostgreSQL and PgBouncer parameters such as `max_connections`.
* [Read replicas](/docs/products/managed-postgres/read-replicas): scale read-heavy workloads.
* [Kysely documentation](https://kysely.dev/docs/intro): joins, transactions, and type generation.