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

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-prisma-beta" />

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

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

* 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](/docs/integrations/connectors/sql-clients/sql-console).
* On macOS: [Docker](https://docs.docker.com/get-started/get-docker/), to run Prisma Migrate (see [Run the migration](#migrate))

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

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

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

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).
</Tip>

<h2 id="init">
  Initialize Prisma
</h2>

<Steps>
  <Step title="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.
  </Step>

  <Step title="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.
  </Step>
</Steps>

<h2 id="configure">
  Configure the connection
</h2>

<Steps>
  <Step title="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
    ```
  </Step>

  <Step title="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.
    </Warning>
  </Step>

  <Step title="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).
  </Step>
</Steps>

<h2 id="migrate">
  Define the schema and run the migration
</h2>

<Steps>
  <Step title="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())
    }
    ```
  </Step>

  <Step title="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.
    </Warning>

    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.
    ```
  </Step>

  <Step title="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.
  </Step>
</Steps>

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

<Steps>
  <Step title="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.
    </Tip>
  </Step>

  <Step title="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
    ```
  </Step>

  <Step title="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"
      }
    ]
    ```
  </Step>
</Steps>

<h2 id="verify">
  Verify in the SQL console
</h2>

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 |

<h2 id="troubleshooting">
  Troubleshooting
</h2>

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

<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 Postgres and PgBouncer parameters
* [Read replicas](/docs/products/managed-postgres/read-replicas): scale read-heavy workloads
* [Local development](/docs/products/managed-postgres/local-development): 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