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: prisma db push works once against an empty schema, then every command that reads the schema back — including the next db push — fails. See Prisma cannot read back a schema before you plan your migration workflow.

Prerequisites

• A DB9 database (create one)
• Node.js 18+
• Prisma 5.7+

Create a DB9 Database

Terminal

db9 create --name prisma-app

Get the connection string:

Terminal

db9 db status prisma-app

Project Setup

Terminal

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

Set the connection string in .env:

Use port 5433 and database name “postgres”

DB9 uses port 5433 (not 5432) and the database name is always postgres. If Prisma generates a connection string with different values, update it manually.

Terminal

DATABASE_URL="postgresql://prisma-app.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

It will not work a second time — see Prisma cannot read back a schema — and prisma migrate dev needs a shadow database DB9 will not create under the name Prisma picks (see P3014). 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 ts-node 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. Note the search below
  -- binds the query vector as a parameter, which does NOT use the index --
  -- only an inlined literal probe does. 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 to an existing DB9 schema, 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://{db}.admin:{password}@pg.db9.io:5433/postgres.

Troubleshooting

prisma migrate dev fails with P3014

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

Original error: ERROR: internal error

prisma migrate dev creates a temporary shadow database to diff migrations against. This is not a permission problem — the admin role is a superuser and CREATE DATABASE works. The failure is a name-validity bug: 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:  internal error   -- XX000; any hyphen, dot or space does this

You can work around the first migration by pre-creating an underscore-named database and pointing shadowDatabaseUrl at it:

prisma

datasource db {
  provider          = "postgresql"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")  // e.g. .../prisma_shadow
}

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.

Prisma cannot read back a schema that has NOT NULL columns

Every Prisma command that describes an existing database hits the same wall:

Error: Error in Schema engine.
Reason: [schema-engine/sql-schema-describer/src/postgres.rs:1230:39] called `Result::unwrap()` on an
`Err` value: "Getting constraint_type from Resultrow ResultRow { … Text(Some("n")) … } as char failed"

DB9 records one pg_constraint row per NOT NULL constraint with contype = 'n' — a PostgreSQL 17 behaviour, while DB9 reports server_version 16.0 — and returns contype as text where PostgreSQL declares it "char". Prisma reads that column as a single character and panics on the first such row.

This is one root cause with three symptoms:

Command	Effect
prisma db push	succeeds against an empty schema, fails on every subsequent run
prisma db pull	fails against any schema containing a NOT NULL column
prisma migrate dev	fails at its describe step, after the shadow database

prisma db push --force-reset does not help: the describer runs before the reset.

A schema with no NOT NULL columns at all reads back fine, which is why a trivial first push succeeds. In practice that means Prisma can create a DB9 schema but cannot evolve one. 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 connection string includes the correct tenant ID in the username (e.g., prisma-app.admin). DB9 routes connections by parsing the tenant from the username.

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 ->>. With the key-existence operators ?, ?| and ?&, the - (delete key) operator, 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 ? 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 id FROM users WHERE metadata ? ${key}`;

// Works everywhere
await prisma.$queryRaw`SELECT id FROM users WHERE metadata ? ${key}::text`;

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 ?| and ?& — 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 Prisma cannot read back a schema.

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