Connect from Drizzle via Postgres endpoint

Preview

This feature is in preview and is subject to change.

Drizzle is a TypeScript ORM with both relational and SQL-like query APIs. It runs in Node.js servers, Vercel functions, Cloudflare Workers, and other edge runtimes.

You can use Drizzle with MotherDuck through the Postgres endpoint. Drizzle's drizzle-orm/node-postgres integration wraps the pg driver, so you get the typed db.execute(sql\...`)` API and connection lifecycle management on top of the same Postgres-protocol connection covered in Connect from Node.js.

Use Drizzle here as a typed query executor over pg, not as a schema-and-migrations ORM. Drizzle's schema introspection, code-first migrations (drizzle-kit pull / migrate / push), and query-builder code generation all assume a Postgres backend with pg_catalog and Postgres DDL semantics — none of which the pg endpoint exposes. Define your MotherDuck schema separately (DuckDB client, MotherDuck UI, or SQL scripts) and use Drizzle for query execution.

For connection parameters, SSL options, and limitations, see the Postgres Endpoint reference.

Prerequisites

You'll need a MotherDuck access token. Set it as an environment variable:

export MOTHERDUCK_TOKEN="your_token_here"

Install Drizzle and pg:

npm install drizzle-orm pg
npm install --save-dev @types/pg

Connect

Wrap a pg client with drizzle(). As with the bare pg client, pass SSL through the config object — do not put sslrootcert=system in a connection string, since node-postgres tries to read system as a file path and throws ENOENT.

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

const client = new pg.Client({
  host: "pg.us-east-1-aws.motherduck.com",
  port: 5432,
  user: "postgres",
  password: process.env.MOTHERDUCK_TOKEN,
  database: "md:",
  ssl: { rejectUnauthorized: true },
});

await client.connect();
const db = drizzle(client);

const { rows } = await db.execute(sql`
  SELECT title, score
  FROM sample_data.hn.hacker_news
  WHERE type = ${'story'}
  LIMIT 10
`);
console.log(rows);

await client.end();

Using md: as the database name connects to your default database in workspace attach mode, so all databases attached in your MotherDuck workspace are accessible. To connect to a specific database, pass its name in database (e.g., database: "my_db") — this uses single attach mode by default.

The sql template tag is what you'll use most. It produces parameterized queries against the pg endpoint and lets you write DuckDB SQL directly, including three-part names (database.schema.table), DuckDB functions, and DuckDB-specific syntax. For pure dynamic SQL with no parameters, sql.raw("...") works too.

Read scaling and concurrency

For concurrent workloads, MotherDuck's pg endpoint can route each session to a separate read replica using the session_hint startup option — this dramatically improves throughput under concurrency. See Session affinity and routing for the underlying scaling pattern. Drizzle's Pool doesn't expose per-connection startup options, so for read scaling you'll want a raw pg.Client per session:

const client = new pg.Client({
  host: "pg.us-east-1-aws.motherduck.com",
  port: 5432,
  user: "postgres",
  password: process.env.MOTHERDUCK_TOKEN,
  database: "md:",
  ssl: { rejectUnauthorized: true },
  options: "-c session_hint=user_1",   // unique per concurrent session
});
await client.connect();
const db = drizzle(client);

In benchmarking, session_hint cut 5-user concurrent latency from ~16s to ~1.3s on the same workload.

What doesn't work

The pg endpoint speaks DuckDB SQL, not Postgres SQL, and doesn't expose Postgres system catalogs. Drizzle features that depend on either will fail:

• drizzle-kit migrate, push, generate — these execute Postgres DDL and assume Postgres migration tracking. Manage your MotherDuck schema separately.
• drizzle-kit pull / introspect — schema introspection queries pg_catalog tables that don't exist on the pg endpoint.
• pgTable(...) schema definitions for query-builder calls (db.select().from(...)) work for simple cases but are brittle: Drizzle treats the table name as a single quoted identifier, so three-part DuckDB names (database.schema.table) need careful handling. Prefer db.execute(sql\...`)` with explicit SQL until you know the shape you need.
• Standard pg endpoint limits — local-file COPY, INSTALL / LOAD, SET, temp tables, and result-creation commands are not supported. See the main pg endpoint reference for the full list.

SSL notes

Setting ssl: { rejectUnauthorized: true } is the equivalent of sslmode=verify-full with sslrootcert=system in libpq — node-postgres uses Node's built-in trusted root store. For a custom CA, see the Node.js page; the same approach applies when wrapping the client with drizzle().

For more details on SSL options across drivers, see SSL and certificate verification.