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.

DB9 passes 100% of Prisma compatibility tests (89/89) covering CRUD, transactions, relations, advanced SQL, and vector operations.

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:

Terminal

DATABASE_URL="postgresql://prisma-app.admin:YOUR_PASSWORD@pg.db9.io:5433/postgres"

Schema Definition

Edit prisma/schema.prisma:

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

Use raw SQL to create tables rather than prisma migrate, which relies on schema introspection that may not fully work with DB9:

TypeScript

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

Since prisma migrate may not fully work with DB9’s schema introspection, manage schema changes with 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 fails

DB9 has limited information_schema support. Use raw SQL for DDL operations instead of prisma migrate dev. You can still use prisma generate to regenerate the client from your schema file.

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.

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 v5.7+ against DB9. All 89 tests pass covering:

Category	Tests	Status
Connection and pooling	5	Pass
CRUD operations	19	Pass
Transactions and isolation levels	11	Pass
Query filters and JSONB	23	Pass
Window functions, CTEs, subqueries	30+	Pass
Vector operations and HNSW	22	Pass
DDL operations	1	Pass

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