Drizzle

Drizzle connects to DB9 using the pg (node-postgres) driver over pgwire. No special adapter is needed — Drizzle’s type-safe query builder, prepared statements, and transaction support work out of the box.

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

Prerequisites

• A DB9 database (create one)
• Node.js 18+
• Drizzle ORM 0.29+

Create a DB9 Database

Terminal

db9 create --name drizzle-app

Get the connection string:

Terminal

db9 db status drizzle-app

Project Setup

Terminal

mkdir drizzle-db9 && cd drizzle-db9
npm init -y
npm install drizzle-orm pg
npm install -D drizzle-kit @types/pg typescript ts-node

Schema Definition

Drizzle defines schemas in TypeScript using pgTable:

schema.ts

import { pgTable, serial, varchar, text, boolean, integer, jsonb, timestamp } from 'drizzle-orm/pg-core';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: varchar('email', { length: 255 }).unique().notNull(),
  name: varchar('name', { length: 100 }),
  metadata: jsonb('metadata'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 500 }).notNull(),
  content: text('content'),
  published: boolean('published').default(false),
  authorId: integer('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow(),
});

Connect and Create Tables

Use raw SQL to create tables rather than drizzle-kit push, which relies on schema introspection that may not fully work with DB9:

setup.ts

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import { sql } from 'drizzle-orm';

const pool = new Pool({
  connectionString: 'postgresql://drizzle-app.admin:YOUR_PASSWORD@pg.db9.io:5433/postgres',
});

const db = drizzle(pool);

async function setup() {
  await db.execute(sql`
    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 db.execute(sql`
    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 pool.end();
}

setup();

Terminal

npx ts-node setup.ts

CRUD Operations

TypeScript

import { drizzle } from 'drizzle-orm/node-postgres';
import { Pool } from 'pg';
import { eq, and, sql } from 'drizzle-orm';
import { users, posts } from './schema';

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});
const db = drizzle(pool);

// Create
const [user] = await db.insert(users).values({
  email: 'alice@example.com',
  name: 'Alice',
  metadata: { role: 'admin', level: 3 },
}).returning();

// Create with relation
const [post] = await db.insert(posts).values({
  title: 'Getting Started with DB9',
  content: 'DB9 is a serverless PostgreSQL-compatible database.',
  authorId: user.id,
}).returning();

// Read with filter
const admins = await db.select()
  .from(users)
  .where(eq(users.name, 'Alice'));

// Update
await db.update(users)
  .set({ name: 'Alice Updated' })
  .where(eq(users.id, user.id));

// Upsert (insert ... on conflict)
await db.insert(users)
  .values({ email: 'alice@example.com', name: 'Alice V2' })
  .onConflictDoUpdate({
    target: users.email,
    set: { name: 'Alice V2' },
  });

// Delete (cascades to posts)
await db.delete(users).where(eq(users.id, user.id));

Transactions

Drizzle supports interactive transactions with PostgreSQL isolation levels:

TypeScript

// Interactive transaction
const result = await db.transaction(async (tx) => {
  const [user] = await tx.insert(users).values({
    email: 'bob@example.com',
    name: 'Bob',
  }).returning();

  const [post] = await tx.insert(posts).values({
    title: 'First Post',
    authorId: user.id,
  }).returning();

  return { user, post };
});

// With isolation level
await db.transaction(async (tx) => {
  const allUsers = await tx.select().from(users);
  return allUsers;
}, {
  isolationLevel: 'repeatable read',
});

Supported isolation levels: read committed, repeatable read, serializable (runs as REPEATABLE READ on DB9).

Raw SQL for Advanced Features

Some PostgreSQL features require raw SQL via db.execute:

Window functions

TypeScript

const ranked = await db.execute(sql`
  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 db.execute(sql`
  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 db.execute(sql`
  SELECT DISTINCT ON (author_id) *
  FROM posts
  ORDER BY author_id, created_at DESC
`);

Vector similarity search

TypeScript

// Create vector table and index
await db.execute(sql`
  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 queryVector = '[0.1, 0.2, ...]';
const results = await db.execute(sql`
  SELECT content,
    embedding <=> ${queryVector}::vector AS distance
  FROM documents
  ORDER BY distance
  LIMIT 5
`);

Schema Changes

Since drizzle-kit push may not fully work with DB9’s schema introspection, manage schema changes with raw SQL:

TypeScript

// Add a column
await db.execute(sql`
  ALTER TABLE users ADD COLUMN IF NOT EXISTS bio TEXT
`);

// Add an index
await db.execute(sql`
  CREATE INDEX IF NOT EXISTS idx_users_email ON users (email)
`);

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

Production Notes

• Connection pooling: Drizzle uses pg.Pool for connection management. DB9 supports multiple concurrent connections per tenant.
• Driver: Drizzle uses the pg (node-postgres) driver, not postgres.js. Use drizzle-orm/node-postgres, not drizzle-orm/postgres-js.
• Prepared statements: Drizzle uses prepared statements by default. These work correctly with DB9’s extended query protocol.
• Type parser conflict: Drizzle modifies the global pg type parsers at import time. If you use multiple ORMs in the same Node.js process, Drizzle’s parser changes can affect other libraries. Run Drizzle in its own process if you see unexpected type coercion.
• Connection string: Use the format postgresql://{db}.admin:{password}@pg.db9.io:5433/postgres.

Troubleshooting

drizzle-kit push fails

DB9 has limited information_schema support. Use raw SQL for DDL operations instead of drizzle-kit push. You can still use your Drizzle schema definitions for type-safe queries — the schema file is used by the query builder at compile time, not at runtime.

Connection timeout

Ensure the connection string includes the correct tenant ID in the username (e.g., drizzle-app.admin). DB9 routes connections by parsing the tenant from the username.

Type coercion issues

Drizzle overrides pg’s default type parsers for dates, numerics, and other types. If you see unexpected values (e.g., dates as strings instead of Date objects), check that your Drizzle version matches your pg driver version. You can reset type parsers after import if needed.

JSONB query differences

Drizzle’s JSONB operators work with DB9. For advanced JSONB queries not covered by the type-safe API, use db.execute(sql…) with PostgreSQL’s native ->, ->>, and @> operators.

Verified Compatibility

Tested with Drizzle v0.29+ against DB9. All 75 tests pass 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	Pass
DDL operations	Pass

Next Pages

• Prisma — Prisma ORM with DB9
• 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