Use Prisma with ClickHouse Managed Postgres

Prisma 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, to create the database. You can also run the CREATE DATABASE statement in the SQL console.
On macOS: Docker, to run Prisma Migrate (see Run the migration)

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. 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 driver adapter, and dotenv:

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

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:

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

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.

Initialize Prisma

Run prisma init

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.

Add the CA certificate

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

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

Create a database

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

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

CREATE DATABASE

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:

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

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.

Point the Prisma CLI at the direct connection

Replace the contents of prisma7.config.ts:

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.

Define the schema and run the migration

Add a model

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

model Todo {
  id        Int      @id @default(autoincrement())
  title     String
  done      Boolean  @default(false)
  createdAt DateTime @default(now())
}

Run the migration

On Linux, run Prisma Migrate directly:

npx prisma migrate dev --name init

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

docker run --rm -it -v "$PWD":/app -w /app node:24 npx prisma migrate dev --name init

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

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.

Generate Prisma Client

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

Create the Prisma Client

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

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

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.

Run CRUD queries

Create script.ts:

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:

npx tsx script.ts

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

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:

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:

npx tsx server.ts

In a second terminal, add two todos:

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 in your browser to see them. The response looks like this:

[
  {
    "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.
`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: connection strings, PgBouncer, and TLS
Settings: tune Postgres and PgBouncer parameters
Read replicas: scale read-heavy workloads
Local development: develop against a local Postgres in Docker
Prisma Migrate in production: use prisma migrate deploy in your CI pipeline

Get started

Features

Guides

Pricing

ClickHouse Integration

Migrations

Monitoring

FAQ

Use Prisma with ClickHouse Managed Postgres

Prerequisites

Create a ClickHouse Managed Postgres service

Get your connection details

Set up the project

Initialize Prisma

Configure the connection

Define the schema and run the migration

Query the database

Verify in the SQL console

Troubleshooting

Next steps

​Prerequisites

​Create a ClickHouse Managed Postgres service

​Get your connection details

​Set up the project

​Initialize Prisma

​Configure the connection

​Define the schema and run the migration

​Query the database

​Verify in the SQL console

​Troubleshooting

​Next steps

Prerequisites

Create a ClickHouse Managed Postgres service

Get your connection details

Set up the project

Initialize Prisma

Configure the connection

Define the schema and run the migration

Query the database

Verify in the SQL console

Troubleshooting

Next steps