> ## 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 Prisma with ClickHouse Managed Postgres

> Connect a TypeScript app to ClickHouse Managed Postgres with Prisma ORM, run migrations with Prisma Migrate, and query through PgBouncer with verified TLS

[Beta](https://clickhouse.com/cloud/postgres)

[Prisma ORM](https://www.prisma.io/orm) is a TypeScript ORM with a declarative schema, a migration tool (Prisma Migrate), and a generated, type-safe client. In this guide, you connect Prisma to ClickHouse Managed Postgres, create a table with a migration, and query it from a script and a small HTTP endpoint.

This guide uses Prisma ORM 7 (tested with `7.10.0`), the current stable release.

## Prerequisites

* Node.js 20.19, 22.12, or 24 and later
* A ClickHouse Managed Postgres service
* [`psql`](https://www.postgresql.org/download/), to create the database. You can also run the `CREATE DATABASE` statement in the [SQL console](https://clickhouse.com/docs/integrations/connectors/sql-clients/sql-console.md).
* On macOS: [Docker](https://docs.docker.com/get-started/get-docker/), to run Prisma Migrate (see [Run the migration](#migrate))

## 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](https://clickhouse.com/docs/products/managed-postgres/quickstart.md) for a walkthrough.

## Get your connection details

Click **Connect** in the left sidebar of your service. The modal shows your username, password, server, and port, plus ready-made connection strings.

This guide uses two connections, because Prisma has two components that talk to the database:

* **Your application (Prisma Client)** connects **via PgBouncer** on port `6432`. PgBouncer pools connections, so many app processes or serverless instances can share a small number of Postgres backends.
* **The Prisma CLI (Prisma Migrate)** connects **directly** on port `5432`. Prisma Migrate takes a session-level advisory lock while it runs. In PgBouncer's transaction pooling mode, that lock can stay held on a pooled backend that then serves other clients, and later migrations block on it.

At the top of the modal, switch between **Directly** and **via PgBouncer** to see each port. The server and credentials are the same for both connections; only the port changes.

With **via PgBouncer** selected, click **Download CA certificate**. With **Directly** selected, turn on **Use SSL** first to show the button. The file is named `<service-name>-ca-certificate.pem`. You also find it under **Settings → CA Certificate**. The certificate is unique to your instance, and you use it to connect with `sslmode=verify-full`, which checks that you're talking to your own server.

## Set up the project

Create a TypeScript project and install the Prisma CLI, Prisma Client, the [node-postgres](https://node-postgres.com/) driver adapter, and `dotenv`:

```bash theme={null}
mkdir hello-prisma
cd hello-prisma
npm init -y
npm pkg set type=module
npm install typescript tsx @types/node @types/pg --save-dev
npm install prisma@7 --save-dev
npm install @prisma/client@7 @prisma/adapter-pg pg dotenv
```

> [!NOTE]
>
> Install `prisma@7` and `@prisma/client@7` explicitly. At the time of writing, the npm `latest` tag of `prisma` points to a Prisma 8 release candidate, which uses a different setup from this guide.

Create a `tsconfig.json`:

```json theme={null}
{
  "compilerOptions": {
    "module": "ESNext",
    "moduleResolution": "bundler",
    "target": "ES2023",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "types": ["node"]
  }
}
```

> [!TIP]
>
> **Adding to an existing app?**
>
> Skip `mkdir`, `npm init`, and the `tsconfig.json`. In your project, run `npm install tsx @types/pg prisma@7 --save-dev` and `npm install @prisma/client@7 @prisma/adapter-pg pg dotenv`, then continue with [Initialize Prisma](#init).

## Initialize Prisma

**1. Run prisma init**

```bash theme={null}
npx prisma init --datasource-provider postgresql --output ../generated/prisma
```

This creates `prisma/schema.prisma` and the Prisma config file `prisma7.config.ts`, adds a placeholder `DATABASE_URL` to `.env` and the generated client to `.gitignore` (creating either file if needed), and adds Prisma skills for AI coding agents. Prisma 7.10 and later name the config file `prisma7.config.ts` so that it doesn't clash with the Prisma 8 format.

**2. Add the CA certificate**

Move the CA certificate you downloaded into the project root and rename it to `ca-certificate.pem`:

```bash theme={null}
mv ~/Downloads/<service-name>-ca-certificate.pem ca-certificate.pem
```

Relative certificate paths are resolved from the directory you run commands in, so run all commands in this guide from the project root.

## Configure the connection

**1. Create a database**

Create a database for the app. Replace `<PASSWORD>` and the host with the values from the **Connect** modal:

```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_prisma;"
```

```text theme={null}
CREATE DATABASE
```

**2. Set the connection URLs**

In `.env`, replace the placeholder `DATABASE_URL` that `prisma init` added with these two connection URLs, which both point at the `guide_prisma` database. Use the password from the Connect modal:

```bash theme={null}
# Prisma Client (your app): via PgBouncer, port 6432
DATABASE_URL="postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:6432/guide_prisma?sslmode=verify-full&sslrootcert=ca-certificate.pem"

# Prisma CLI (migrations): direct connection, port 5432
DIRECT_URL="postgresql://postgres:<PASSWORD>@your-instance.pg.clickhouse.cloud:5432/guide_prisma?sslmode=require&sslaccept=strict&sslcert=ca-certificate.pem"
```

The two URLs use different TLS parameters because they're read by different drivers:

* **`DATABASE_URL`** is read by node-postgres through `@prisma/adapter-pg`. It supports the standard `sslmode=verify-full&sslrootcert=...` parameters, the same ones shown in the Connect modal.
* **`DIRECT_URL`** is read by the Prisma CLI's schema engine, which has its own parameters: `sslcert` is the path to the CA certificate, and `sslaccept=strict` turns on certificate and hostname verification.

> [!WARNING]
>
> Don't use `sslmode=verify-full&sslrootcert=...` in the URL for the Prisma CLI. The schema engine ignores `sslrootcert` and, without `sslaccept=strict`, doesn't verify the server certificate at all, so the connection is encrypted but not authenticated.

**3. Point the Prisma CLI at the direct connection**

Replace the contents of `prisma7.config.ts`:

```typescript theme={null}
import "dotenv/config";
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
  },
  datasource: {
    url: env("DIRECT_URL"),
  },
});
```

In Prisma 7, the URL in the config file is used only by the CLI. Prisma Client gets its connection from the driver adapter, which you configure in [Query the database](#query).

## Define the schema and run the migration

**1. Add a model**

Add a `Todo` model to the end of `prisma/schema.prisma`:

```prisma theme={null}
model Todo {
  id        Int      @id @default(autoincrement())
  title     String
  done      Boolean  @default(false)
  createdAt DateTime @default(now())
}
```

**2. Run the migration**

On Linux, run Prisma Migrate directly:

```bash theme={null}
npx prisma migrate dev --name init
```

On macOS, run the same command in a Linux container:

```bash theme={null}
docker run --rm -it -v "$PWD":/app -w /app node:24 npx prisma migrate dev --name init
```

> [!WARNING]
>
> ClickHouse Managed Postgres accepts TLS 1.3 only. On macOS, the Prisma CLI's schema engine uses the system TLS library, which can't connect with these settings. It fails with `P1011: Error opening a TLS connection: One or more parameters passed to a function were not valid`, or with `bad protocol version` if you remove `sslcert` ([prisma/orm#29600](https://github.com/prisma/orm/issues/29600)). This affects only CLI commands that connect to the database, such as `prisma migrate` and `prisma db pull`. Prisma Client isn't affected, because it connects through node-postgres. Running the CLI in a Linux container, or in your Linux-based CI pipeline, works around the issue.

Prisma creates the SQL migration in `prisma/migrations/` and applies it:

```text theme={null}
Applying migration `20260930165244_init`

The following migration(s) have been created and applied from new schema changes:

prisma/migrations/
  └─ 20260930165244_init/
    └─ migration.sql

Your database is now in sync with your schema.
```

**3. Generate Prisma Client**

```bash theme={null}
npx prisma generate
```

The client is generated into `generated/prisma`. This command doesn't connect to the database, so you can run it on any platform.

## Query the database

**1. Create the Prisma Client**

Create `lib/prisma.ts`. The `PrismaPg` adapter connects with `DATABASE_URL`, through PgBouncer:

```typescript theme={null}
import "dotenv/config";
import { PrismaPg } from "@prisma/adapter-pg";
import { PrismaClient } from "../generated/prisma/client";

const adapter = new PrismaPg({ connectionString: process.env.DATABASE_URL });

export const prisma = new PrismaClient({ adapter });
```

> [!TIP]
>
> **No PgBouncer settings needed**
>
> `@prisma/adapter-pg` sends each query as an unnamed prepared statement and never runs `DEALLOCATE`, which works with PgBouncer's transaction pooling mode. It handles hundreds of distinct queries running concurrently across pooled backends without errors. The `pgbouncer=true` URL parameter from older Prisma versions isn't needed, and has no effect with the driver adapter.

**2. Run CRUD queries**

Create `script.ts`:

```typescript theme={null}
import { prisma } from "./lib/prisma";

async function main() {
  // Create
  const todo = await prisma.todo.create({
    data: { title: "Connect Prisma to ClickHouse Managed Postgres" },
  });
  console.log("Created:", todo);

  // Read
  const open = await prisma.todo.findMany({ where: { done: false } });
  console.log("Open todos:", open.length);

  // Update
  const updated = await prisma.todo.update({
    where: { id: todo.id },
    data: { done: true },
  });
  console.log("Updated:", updated);

  // Delete
  await prisma.todo.delete({ where: { id: todo.id } });
  console.log("Deleted todo", todo.id);
}

main().finally(() => prisma.$disconnect());
```

Run it:

```bash theme={null}
npx tsx script.ts
```

```text theme={null}
Created: {
  id: 1,
  title: 'Connect Prisma to ClickHouse Managed Postgres',
  done: false,
  createdAt: 2026-09-30T16:52:57.116Z
}
Open todos: 1
Updated: {
  id: 1,
  title: 'Connect Prisma to ClickHouse Managed Postgres',
  done: true,
  createdAt: 2026-09-30T16:52:57.116Z
}
Deleted todo 1
```

**3. Serve the data over HTTP**

Create `server.ts`, a minimal HTTP server that creates todos with `POST /todos` and lists them on any `GET` request:

```typescript theme={null}
import { createServer } from "node:http";
import { prisma } from "./lib/prisma";

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

createServer(async (req, res) => {
  if (req.method === "POST" && req.url === "/todos") {
    let body = "";
    for await (const chunk of req) body += chunk;
    const todo = await prisma.todo.create({ data: { title: JSON.parse(body).title } });
    res.writeHead(201, { "Content-Type": "application/json" });
    res.end(JSON.stringify(todo));
    return;
  }

  const todos = await prisma.todo.findMany({ orderBy: { id: "asc" } });
  res.writeHead(200, { "Content-Type": "application/json" });
  res.end(JSON.stringify(todos, null, 2));
}).listen(port, () => console.log(`Listening on http://localhost:${port}`));
```

Start the server:

```bash theme={null}
npx tsx server.ts
```

In a second terminal, add two todos:

```bash theme={null}
curl -X POST localhost:3000/todos -H "Content-Type: application/json" -d '{"title": "Try Prisma with ClickHouse Managed Postgres"}'
curl -X POST localhost:3000/todos -H "Content-Type: application/json" -d '{"title": "Add a read replica"}'
```

Open [http://localhost:3000/todos](http://localhost:3000/todos) in your browser to see them. The response looks like this:

```json theme={null}
[
  {
    "id": 2,
    "title": "Try Prisma with ClickHouse Managed Postgres",
    "done": false,
    "createdAt": "2026-09-30T16:53:06.098Z"
  },
  {
    "id": 3,
    "title": "Add a read replica",
    "done": false,
    "createdAt": "2026-09-30T16:53:06.184Z"
  }
]
```

## Verify in the SQL console

In the ClickHouse Cloud console, open **SQL console** for your service, expand your database and the `public` schema, and select the `Todo` table. You also see the `_prisma_migrations` table, where Prisma Migrate records applied migrations.

The `Todo` table contains the two rows you added through the HTTP endpoint:

| id | title | done | createdAt |
| - | - | - | - |
| 2 | Try Prisma with ClickHouse Managed Postgres | false | 2026-09-30 16:53:06.098 |
| 3 | Add a read replica | false | 2026-09-30 16:53:06.184 |

## Troubleshooting

| Error | Cause and fix |
| - | - |
| `P1011: Error opening a TLS connection: One or more parameters passed to a function were not valid` or `bad protocol version` | The Prisma CLI is running on macOS. Run it in a Linux container, as shown in [Run the migration](#migrate). |
| `P1011: ... certificate verify failed` | The Prisma CLI can't verify the server. Check that `sslcert` in `DIRECT_URL` points to your instance's CA certificate. |
| `P1011: ... cert file not found` | The `sslcert` path in `DIRECT_URL` is wrong. Run the CLI from the project root, or use an absolute path. |
| `unable to verify the first certificate` | Prisma Client can't verify the server. Check that `sslrootcert` in `DATABASE_URL` points to your instance's CA certificate. |
| `ENOENT: no such file or directory` | The `sslrootcert` path in `DATABASE_URL` is wrong. Run your app from the project root, or use an absolute path. |

## Next steps

* [Connection](https://clickhouse.com/docs/products/managed-postgres/connection.md): connection strings, PgBouncer, and TLS
* [Settings](https://clickhouse.com/docs/products/managed-postgres/settings.md): tune Postgres and PgBouncer parameters
* [Read replicas](https://clickhouse.com/docs/products/managed-postgres/read-replicas.md): scale read-heavy workloads
* [Local development](https://clickhouse.com/docs/products/managed-postgres/local-development.md): develop against a local Postgres in Docker
* [Prisma Migrate in production](https://www.prisma.io/docs/orm/prisma-migrate/workflows/development-and-production): use `prisma migrate deploy` in your CI pipeline