Prisma

Prisma connects to DB9 using the standard PostgreSQL provider over pgwire. No special configuration is needed — Prisma’s binary protocol, connection pooling, and query builder work out of the box.

Full Prisma compatibility

DB9 passes every test in its Prisma compatibility suite, covering CRUD, transactions, relations, advanced SQL, and vector operations. No custom adapter is needed at runtime.

Prisma’s schema tooling is the weak spot on DB9: on models with @default(autoincrement()) ids, only the first prisma db push against an empty schema is safe. Later runs fail — or, for a table name with upper-case letters, report success while breaking the id default. See A second db push fails on autoincrement() ids before you plan your migration workflow.

Prerequisites

• A DB9 database (create one)
• Node.js 18+
• Prisma 5.7 through 6.x (the commands below install 6.x)

Create a DB9 Database

Terminal

db9 create --name prisma-app

db9 create prints the connection string, with the admin password, once — copy it from the Connection String: line. It has this shape:

Output

postgresql://YOUR_TENANT_ID.admin:YOUR_PASSWORD@pg.d0000.db9.io:5432/postgres

YOUR_TENANT_ID is the tenant ID prefix in the generated connection string. Copy the username from the Connection String: line printed by db9 create or from db9 db connect. The examples below use pg.db9.io:5433, which reaches the same server; either host and port works. db9 db status prisma-app shows the connection string without the password; if you lose it, db9 db reset-password prisma-app sets a new password and prints a new connection string.

Project Setup

Terminal

mkdir prisma-db9 && cd prisma-db9
npm init -y
npm install @prisma/client@6
npm install -D prisma@6 typescript @types/node tsx
npx prisma init

Install Prisma 6, not latest

Keep the @6 in both commands. An unpinned npm install -D prisma currently resolves to an 8.0 release candidate whose CLI has no db push, db pull or generate command (No command registered for db push), and Prisma 7 no longer accepts the url field in the datasource block this guide uses.

Set the connection string in .env:

Use database name “postgres”

The database name is always postgres. If Prisma generates a connection string with a different database name, update it manually. This page uses port 5433; 5432 also works.

Terminal

DATABASE_URL="postgresql://YOUR_TENANT_ID.admin:YOUR_PASSWORD@pg.db9.io:5433/postgres"

Schema Definition

Edit prisma/schema.prisma:

prisma/schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String?
  metadata  Json?
  createdAt DateTime @default(now()) @map("created_at")
  posts     Post[]

  @@map("users")
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  authorId  Int      @map("author_id")
  author    User     @relation(fields: [authorId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now()) @map("created_at")

  @@map("posts")
}

Create Tables

prisma db push works for the initial creation against an empty schema:

Terminal

npx prisma db push

With this schema’s autoincrement() ids it will not work a second time — see A second db push fails — and prisma migrate dev needs a shadow database DB9 will not create under the name Prisma picks (see P3014), then fails on its second migration for the same reason. Because of that, raw SQL is the schema path that keeps working on DB9:

setup.ts

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function setup() {
  await prisma.$executeRawUnsafe(`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) UNIQUE NOT NULL,
      name VARCHAR(100),
      metadata JSONB,
      created_at TIMESTAMPTZ DEFAULT now()
    )
  `);

  await prisma.$executeRawUnsafe(`
    CREATE TABLE IF NOT EXISTS posts (
      id SERIAL PRIMARY KEY,
      title VARCHAR(500) NOT NULL,
      content TEXT,
      published BOOLEAN DEFAULT false,
      author_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
      created_at TIMESTAMPTZ DEFAULT now()
    )
  `);

  console.log('Tables created');
  await prisma.$disconnect();
}

setup();

Then generate the Prisma client:

Terminal

npx prisma generate
npx tsx setup.ts

CRUD Operations

TypeScript

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

// Create
const user = await prisma.user.create({
  data: {
    email: 'alice@example.com',
    name: 'Alice',
    metadata: { role: 'admin', level: 3 },
  },
});

// Create with relation
const post = await prisma.post.create({
  data: {
    title: 'Getting Started with DB9',
    content: 'DB9 is a serverless PostgreSQL-compatible database.',
    authorId: user.id,
  },
});

// Read with filter
const admins = await prisma.user.findMany({
  where: { metadata: { path: ['role'], equals: 'admin' } },
  include: { posts: true },
});

// Update
await prisma.user.update({
  where: { id: user.id },
  data: { name: 'Alice Updated' },
});

// Upsert
await prisma.user.upsert({
  where: { email: 'alice@example.com' },
  update: { name: 'Alice V2' },
  create: { email: 'alice@example.com', name: 'Alice V2' },
});

// Delete (cascades to posts)
await prisma.user.delete({ where: { id: user.id } });

Transactions

Prisma supports interactive transactions with all PostgreSQL isolation levels:

TypeScript

// Interactive transaction
const result = await prisma.$transaction(async (tx) => {
  const user = await tx.user.create({
    data: { email: 'bob@example.com', name: 'Bob' },
  });
  const post = await tx.post.create({
    data: { title: 'First Post', authorId: user.id },
  });
  return { user, post };
});

// With isolation level
await prisma.$transaction(
  async (tx) => {
    // Operations here run at REPEATABLE READ
    const count = await tx.user.count();
    return count;
  },
  { isolationLevel: 'RepeatableRead' }
);

Supported isolation levels: ReadCommitted, RepeatableRead, Serializable (runs as REPEATABLE READ on DB9).

Raw SQL for Advanced Features

Some PostgreSQL features require raw SQL via $queryRaw:

Window functions

TypeScript

const ranked = await prisma.$queryRaw`
  SELECT name, email,
    ROW_NUMBER() OVER (ORDER BY created_at) AS row_num,
    RANK() OVER (ORDER BY name) AS name_rank
  FROM users
`;

CTEs and recursive queries

TypeScript

const hierarchy = await prisma.$queryRaw`
  WITH RECURSIVE tree AS (
    SELECT id, name, 0 AS depth
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, t.depth + 1
    FROM categories c JOIN tree t ON c.parent_id = t.id
    WHERE t.depth < 10
  )
  SELECT * FROM tree ORDER BY depth, name
`;

DISTINCT ON

TypeScript

const latest = await prisma.$queryRaw`
  SELECT DISTINCT ON (author_id) *
  FROM posts
  ORDER BY author_id, created_at DESC
`;

Vector similarity search

TypeScript

// Create vector table and index
await prisma.$executeRawUnsafe(`
  CREATE EXTENSION IF NOT EXISTS vector;
  CREATE TABLE IF NOT EXISTS documents (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding VECTOR(1024)
  );
  -- HNSW index building works over both transports. The search below uses
  -- the index: its metric matches the opclass and it has a LIMIT and no
  -- WHERE clause. IVFFlat is unavailable, so USING hnsw.
  CREATE INDEX IF NOT EXISTS idx_docs ON documents
    USING hnsw (embedding vector_cosine_ops);
`);

// Search
const results = await prisma.$queryRaw`
  SELECT content,
    embedding <=> ${queryVector}::vector AS distance
  FROM documents
  ORDER BY distance
  LIMIT 5
`;

Schema Changes

prisma db push cannot apply a change once tables whose models use autoincrement() ids exist (see A second db push fails), so apply changes as raw SQL:

TypeScript

// Add a column
await prisma.$executeRawUnsafe(`
  ALTER TABLE users ADD COLUMN IF NOT EXISTS bio TEXT
`);

// Add an index
await prisma.$executeRawUnsafe(`
  CREATE INDEX IF NOT EXISTS idx_users_email ON users (email)
`);

// Re-generate the Prisma client after schema changes
// npx prisma generate

For teams, track migrations as numbered SQL files and apply them in order.

Production Notes

• Connection pooling: Prisma manages its own connection pool. DB9 supports multiple concurrent connections per tenant.
• Binary protocol: Prisma uses the PostgreSQL binary wire protocol via pg driver. This is fully supported.
• Prepared statements: Prisma uses prepared statements by default. These work correctly with DB9’s extended query protocol.
• Connection string: Use the format postgresql://<tenant_id>.admin:<password>@pg.db9.io:5433/postgres, using the tenant ID prefix from the generated connection string.

Troubleshooting

prisma migrate dev fails with P3014

Error: P3014
Prisma Migrate could not create the shadow database.

Original error:
ERROR: invalid database name: prisma_migrate_shadow_db_66aa9e7c-6eaa-4d7f-b34b-04a4191e1c59

prisma migrate dev creates a temporary shadow database to diff migrations against. This is not a permission problem, whatever Prisma’s own hint says — the admin role is a superuser and CREATE DATABASE works. The failure is a name restriction: DB9 rejects any database name containing a hyphen, dot or space, and Prisma names its shadow database prisma_migrate_shadow_db_<uuid>, where the UUID contains hyphens. (Quoting itself is fine — "select" and "RvMixedCase" both create.)

SQL

CREATE DATABASE prisma_migrate_shadow_db_4562bb13_2613_43a5_90a5_f401c95c3564;  -- ok
CREATE DATABASE "prisma_migrate_shadow_db_4562bb13-2613-43a5-90a5-f401c95c3564";
ERROR:  invalid database name: prisma_migrate_shadow_db_4562bb13-2613-43a5-90a5-f401c95c3564
-- 42602; any hyphen, dot or space does this

You can work around the first migration by pre-creating an underscore-named database (CREATE DATABASE prisma_shadow;) and pointing shadowDatabaseUrl at it. Set it in prisma.config.ts, the file npx prisma init generates: while that file exists, a shadowDatabaseUrl in the datasource block of schema.prisma has no effect and migrate dev still ends at P3014.

prisma.config.ts

import "dotenv/config";
import { defineConfig, env } from "prisma/config";

export default defineConfig({
  schema: "prisma/schema.prisma",
  migrations: {
    path: "prisma/migrations",
  },
  engine: "classic",
  datasource: {
    url: env("DATABASE_URL"),
    shadowDatabaseUrl: env("SHADOW_DATABASE_URL"), // DATABASE_URL with /prisma_shadow in place of /postgres
  },
});

That gets migrate dev through an initial migration against an empty schema. It does not survive a second one, for the reason in the next section.

prisma generate works and needs no database connection at all.

A second db push fails on autoincrement() ids

With the schema on this page, the first prisma db push creates the tables. Every later run — whether or not schema.prisma changed — exits 1 and applies nothing:

Error: ERROR: relation "posts_id_seq" already exists

DB9 reports the default of a SERIAL column as nextval(CAST('public.posts_id_seq' AS pg_catalog.regclass)), where PostgreSQL reports nextval('posts_id_seq'::regclass). Prisma does not recognise DB9’s spelling as @default(autoincrement()), so it plans to make each such column autoincrement again, starting with CREATE SEQUENCE posts_id_seq — which fails, because the sequence exists. TypeORM’s synchronize trips on the same spelling (see the TypeORM guide).

Measured with Prisma 6.19.3, the version prisma@6 installs:

Command	Effect
prisma db push	succeeds against an empty schema; with lower-case table names, every later run fails with the error above and changes nothing, including a run that adds a column
prisma migrate dev	with a shadow database configured, the first migration applies; with lower-case table names, the next run generates a migration containing CREATE SEQUENCE posts_id_seq even when no model changed, and fails with P3018 (42P07, relation "posts_id_seq" already exists), leaving a failed row in _prisma_migrations. With upper-case table names the next run exits 0 instead — see below
prisma db pull	works — it writes your models into schema.prisma, reading each serial id back as @default(dbgenerated("nextval(CAST('public.users_id_seq' AS pg_catalog.regclass))"))

Table names with upper-case letters: the second push or migration “succeeds”

This page’s models are mapped to lower-case table names (@@map("users")), so the sequence Prisma tries to create already exists and the push fails. Without a @@map, a model User is stored as the table "User" with the sequence "User_id_seq", while Prisma names the new sequence in lower case — CREATE SEQUENCE user_id_seq — so nothing collides. The second push then prints Your database is now in sync with your Prisma schema, exits 0, and leaves id drawing from a new sequence that starts at 1: the next inserts into a table that already has rows fail with duplicate key value violates unique constraint "User_pkey" (23505). The push after that fails with relation "user_id_seq" already exists.

prisma migrate dev does the same: the second run exits 0 and records a migration containing CREATE SEQUENCE user_id_seq; and ALTER TABLE "User" ALTER COLUMN "id" SET DEFAULT nextval('user_id_seq');, after which inserts fail with 23505 — and because the migration is in your history, prisma migrate deploy would apply it to every other database too. Delete such a migration before you commit it.

To repair a table this has happened to, point the default back at the original sequence:

SQL

ALTER TABLE "User" ALTER COLUMN id SET DEFAULT nextval('"User_id_seq"');

Models whose ids are not autoincrement() are unaffected: with String @id @default(uuid()) keys, a second db push reports The database is already in sync with the Prisma schema and a later one applies an added column. A schema written by db pull behaves the same way, because it carries DB9’s spelling of the default — until you add a new autoincrement() model, whose second push runs into the same problem.

Otherwise keep schema.prisma as the source of truth, and apply changes you make after the first push with raw SQL (prisma.$executeRawUnsafe) rather than with db push.

Connection timeout

Ensure the username follows <tenant_id>.admin; copy the complete username from the generated connection string or db9 db connect. prisma-app.admin fails even with the right password.

JSONB query differences

Prisma’s JSONB path filtering works with DB9. If you see unexpected results with the -> operator, use ->> for text extraction or @> for containment checks in raw queries.

A JSONB key interpolated into a raw query works unchanged with ->, ->>, ? and - (delete key). With the any/all key-existence operators ?| and ?&, or jsonb_extract_path() / jsonb_extract_path_text(), whether it needs a cast depends on which query engine you run, because DB9 rejects those when the parameter arrives untyped (42P18 could not determine data type of parameter $1):

Prisma setup	Bound key for jsonb_extract_path_text() without a cast
Rust query engine — the default through v6	works; types every parameter
A driver adapter such as @prisma/adapter-pg, and all of v7	fails — delegates to pg, which sends the parameter untyped

Prisma 7 removed url from the datasource block, so an adapter is the direct-connect path there and every raw query goes through pg. Casting is harmless on the Rust engine and required on the adapter, so write the cast and the query works on both:

TypeScript

// Works everywhere
await prisma.$queryRaw`SELECT metadata ->> ${key} FROM users`;
await prisma.$queryRaw`SELECT id FROM users WHERE metadata @> ${filter}::jsonb`;

// Fails on v7 / any driver adapter; works on the Rust engine
await prisma.$queryRaw`SELECT jsonb_extract_path_text(metadata, ${key}) FROM users`;

// Works everywhere
await prisma.$queryRaw`SELECT jsonb_extract_path_text(metadata, ${key}::text) FROM users`;

Keep the ::jsonb cast on an @> operand: the Rust engine sends a string parameter typed as text, and jsonb @> text does not exist. Use ::text[] for ?| and ?&, and ::text for the jsonb_extract_path key — the full table is in the compatibility matrix.

Type mismatches in raw queries

When using $queryRaw with vector types or custom types, cast explicitly:

TypeScript

const results = await prisma.$queryRaw`
  SELECT * FROM documents
  ORDER BY embedding <=> ${vectorString}::vector
  LIMIT 10
`;

Verified Compatibility

Tested with Prisma 5.22 against DB9. Every test passes, covering:

Category	Status
Connection and pooling	Pass
CRUD operations	Pass
Transactions and isolation levels	Pass
Query filters and JSONB	Pass
Window functions, CTEs, subqueries	Pass
Vector operations and HNSW indexing	Pass
DDL operations	Pass

These cover the query layer. Schema tooling is separate and much weaker — see A second db push fails on autoincrement() ids.

Next Pages

• Drizzle — type-safe SQL with Drizzle ORM
• Connect — connection strings and authentication
• RAG with Built-in Embeddings — vector search with server-side embedding
• Compatibility Matrix — full PostgreSQL compatibility surface
• TypeScript SDK — programmatic database management