Drizzle

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

Prerequisites

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

Create a DB9 Database

db9 create --name drizzle-app

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

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 drizzle-app shows the connection string without the password; if you lose it, db9 db reset-password drizzle-app sets a new password and prints a new connection string.

Project Setup

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

Schema Definition

Drizzle defines schemas in TypeScript using pgTable:

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

This guide creates the tables with the raw SQL script below. drizzle-kit push would create them once, on a database with no tables yet, but every push after that fails (see Troubleshooting). To let drizzle-kit manage the schema instead, skip the script and use drizzle-kit generate and drizzle-kit migrate.

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

const pool = new Pool({
  connectionString: 'postgresql://YOUR_TENANT_ID.admin:YOUR_PASSWORD@pg.db9.io:5433/postgres?sslmode=require',
});

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

npx tsx setup.ts

CRUD Operations

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:

// 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 is not implemented, and it does not error: isolationLevel: 'serializable' is accepted, the transaction silently runs at repeatable read, and the only signal is a server WARNING (TiKV provides snapshot isolation; SERIALIZABLE has been downgraded to REPEATABLE READ) that pg delivers as a notice event rather than throwing. Nothing in the await path tells you the guarantee you asked for was not applied, so set repeatable read explicitly and make the intent visible in your code.

Raw SQL for Advanced Features

Some PostgreSQL features require raw SQL via db.execute:

Window functions

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

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

const latest = await db.execute(sql`
  SELECT DISTINCT ON (author_id) *
  FROM posts
  ORDER BY author_id, created_at DESC
`);

Vector similarity search

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

drizzle-kit push fails once the tables exist (see Troubleshooting), so apply schema changes with raw SQL or with migration files that drizzle-kit generates.

Raw SQL

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

Migrations with drizzle-kit

drizzle-kit generate writes the SQL for each change to schema.ts into a migrations folder, and drizzle-kit migrate applies the files that have not run yet, recording each one in drizzle.__drizzle_migrations. Both work against DB9 — tested with drizzle-kit 0.31.11 and drizzle-orm 0.45.4 on an initial migration, a re-run with nothing to apply, and a later ADD COLUMN migration. Point drizzle-kit at the schema file and the database:

import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  dialect: 'postgresql',
  schema: './schema.ts',
  out: './drizzle',
  dbCredentials: { url: process.env.DATABASE_URL! },
});

Then generate and apply the first migration, and run both commands again after each change to schema.ts:

export DATABASE_URL='postgresql://YOUR_TENANT_ID.admin:YOUR_PASSWORD@pg.db9.io:5433/postgres?sslmode=require'
npx drizzle-kit generate
npx drizzle-kit migrate

Use this in place of setup.ts, on a database that script has not run against: the first migration creates the tables, and on tables that already exist drizzle-kit migrate fails with relation "posts" already exists — which drizzle-kit 0.31.11 does not print; it exits with status 1.

Production Notes

Connection pooling: The examples pass a pg.Pool to Drizzle. DB9 supports multiple concurrent connections per tenant.
Driver: drizzle-orm/postgres-js with the postgres (postgres.js) package also works. Inserts and updates with RETURNING, transactions and prepared statements behaved the same as with pg (tested with drizzle-orm 0.45.4, postgres 3.4.9 and pg 8.23.1).
Prepared statements: Drizzle uses prepared statements by default. These work correctly with DB9’s extended query protocol.
Type parser conflict: Up to drizzle-orm 0.34.1, calling drizzle() changes the global pg type parsers, so other libraries in the same Node.js process get timestamptz values as strings instead of Date objects (checked with 0.29.5 through 0.34.1). From 0.35.0 Drizzle sets its parsers per query and leaves the global ones alone (checked with 0.35.0 through 0.45.4). On an older release, upgrade or run Drizzle in its own process.
Connection string: Use the format postgresql://<tenant_id>.admin:<password>@pg.db9.io:5433/postgres?sslmode=require, using the tenant ID prefix from the generated connection string.

Troubleshooting

`drizzle-kit push` fails

drizzle-kit push works once: when the target schema has no tables yet, it creates them. Every run after that fails and applies nothing, whether or not schema.ts changed — and so does a first push into a schema that already holds tables, however they were created. drizzle-kit 0.31.11 gets as far as Pulling schema from database... and then exits with status 1 and no error message. The earlier releases tested — 0.27.2, 0.30.6, 0.31.5 and 0.31.8 — print error: relation "pg_stat_user_indexes" does not exist instead, and exit with status 0, so a script that checks the exit code sees success.

Before it diffs anything, the push introspects each existing table in the schema, and for each one it reads the catalog view pg_stat_user_indexes to enumerate the table’s indexes. DB9 implements pg_stat_all_indexes but not the _user_ variant. With no tables there is nothing to introspect, which is why the first push goes through. Query the view by hand to see the error behind a silent exit:

ERROR: relation "pg_stat_user_indexes" does not exist   (42P01)

This is not an information_schema limitation — DB9’s information_schema is complete enough that Knex’s schema builder works against it.

Pinning an older drizzle-kit is not a way around it: the earlier releases above fail the same way, and 0.25.0 refuses to run next to drizzle-orm 0.45.4 (This version of drizzle-kit is outdated).

There is no flag that avoids the view. Apply DDL with drizzle-kit generate and drizzle-kit migrate, which work, or with raw SQL. 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 username follows <tenant_id>.admin; copy the complete username from the generated connection string or db9 db connect. drizzle-app.admin fails even with the right password.

Type coercion issues

Drizzle turns off pg’s parsers for timestamp, timestamptz, date and interval (for its own queries from 0.35.0, globally up to 0.34.1 — see Production Notes) and maps the values through your column definitions instead. The query builder returns a Date for timestamp() and date({ mode: 'date' }) columns and a string for date() and interval(), but db.execute() returns all four as strings. numeric and bigint arrive as strings from pg itself; declare bigint('col', { mode: 'number' }) to get numbers from the query builder. If dates arrive as strings in another library in the same process, see the type parser note under Production Notes.

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.

Drizzle’s sql template turns an interpolated value into a bind parameter (a column reference such as ${users.metadata} is inlined as an identifier instead). As the key, path or operand of ->, ->>, #>, #>>, #-, @>, <@, ? and - (delete key), that parameter works unchanged. The any/all key-existence operators ?| and ?&, and jsonb_extract_path() / jsonb_extract_path_text(), are the exception — DB9 cannot infer the parameter’s type there, and the query fails with 42P18 could not determine data type of parameter $1:

// Works: ->>, @> and ? infer an interpolated key or operand
await db.execute(sql`SELECT ${users.metadata} ->> ${key} FROM users`);
await db.execute(sql`SELECT id FROM users WHERE ${users.metadata} @> ${filter}`);
await db.execute(sql`SELECT id FROM users WHERE ${users.metadata} ? ${key}`);

// Fails: an interpolated key for jsonb_extract_path_text()
await db.execute(sql`SELECT jsonb_extract_path_text(${users.metadata}, ${key}) FROM users`);

// Works: cast the parameter (also valid PostgreSQL)
await db.execute(sql`SELECT jsonb_extract_path_text(${users.metadata}, ${key}::text) FROM users`);

Use ::text[] for ?| and ?&, and ::text for the jsonb_extract_path key — the full table is in the compatibility matrix.

Verified Compatibility

Tested with Drizzle 0.29 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

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