Connect from Node.js via Postgres endpoint

Preview

This feature is in preview and is subject to change.

You can query MotherDuck from Node.js using node-postgres (pg) — no DuckDB installation required.

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 the pg package:

npm install pg

Connect

Use a configuration object to connect. Do not pass sslrootcert=system in a connection string — node-postgres tries to read system as a file path and throws an ENOENT error.

import pg from "pg";

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 { rows } = await client.query(
  "SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 10"
);
console.log(rows);
await client.end();

Connection pooling and timeouts

Use pg.Pool in production. Set a connection timeout so requests fail fast when new connections cannot be opened, set an idle timeout so unused connections are recycled quickly, and set a query timeout so one slow query does not let requests pile up.

import pg from "pg";

const pool = new pg.Pool({
  host: "pg.us-east-1-aws.motherduck.com",
  port: 5432,
  user: "postgres",
  password: process.env.MOTHERDUCK_TOKEN,
  database: "md:",
  ssl: { rejectUnauthorized: true },
  max: 10,
  connectionTimeoutMillis: 5_000,
  idleTimeoutMillis: 30_000,
  maxLifetimeSeconds: 300,
  query_timeout: 60_000,
});

pool.on("error", (err) => {
  console.error("Unexpected idle client error", err);
});

const { rows } = await pool.query(
  "SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 10"
);
console.log(rows);

For simple queries, prefer pool.query(...); node-postgres checks out and releases the connection for you. When you check out a client manually, always release it. If the client hits a connection-level error such as a network reset, protocol error, or server termination, destroy it with client.release(true) instead of returning it to the pool.

const client = await pool.connect();
let destroy = false;

try {
  await client.query("BEGIN");
  await client.query("SELECT 1");
  await client.query("COMMIT");
} catch (err) {
  await client.query("ROLLBACK").catch(() => {
    destroy = true;
  });
  throw err;
} finally {
  client.release(destroy);
}

statement_timeout is not supported through the Postgres endpoint today. Use query_timeout for client-side cancellation.

SSL notes

Node.js uses the operating system's certificate store by default. Setting ssl: { rejectUnauthorized: true } tells node-postgres to use TLS and verify the server certificate against these trusted roots — this is the equivalent of sslmode=verify-full with sslrootcert=system in libpq.

If you need to specify a custom CA certificate (for example, the ISRG Root X1 certificate from Let's Encrypt):

import fs from "fs";

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,
    ca: fs.readFileSync("/path/to/isrgrootx1.pem").toString(),
  },
});

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

Cloudflare Workers

Cloudflare Workers use a different socket implementation (pg-cloudflare) that handles SSL differently. See Connect from Cloudflare Workers for Workers-specific setup.