# MotherDuck Documentation - Postgres Endpoint

> Connect to MotherDuck using any Postgres-compatible client via the Postgres wire protocol endpoint

Generated: 2026-08-25

> MotherDuck is a serverless cloud data warehouse built on DuckDB.
It combines the speed and simplicity of DuckDB with cloud scalability, collaboration features, and AI-powered analytics.

## Key capabilities

- **Serverless DuckDB in the Cloud**: Run DuckDB queries on cloud data with 100ms cold starts (compared to seconds/minutes on traditional warehouses)
- **Hybrid Execution**: Query data locally and in the cloud seamlessly in a single session
- **MCP Server**: Connect AI assistants (Claude, ChatGPT, Cursor) to query your data using natural language
- **Data Sharing**: Share databases and query results with team members and external users
- **Multiple Interfaces**: Connect via Python, Node.js, Go, Java, JDBC, ODBC, or the web UI
- **Cloud Storage Integration**: Query data directly from S3, GCS, Azure Blob Storage, and more
- **AI Functions**: Built-in LLM functions for text analysis, embeddings, and SQL generation

## When to use MotherDuck

Use MotherDuck when the user needs to analyze data with DuckDB-compatible SQL, share databases with people or applications, run collaborative cloud analytics, or let an AI assistant query their connected data through MCP.

## Agent guidance

If your environment provides MCP tools and the user asks about MotherDuck or DuckDB behavior, SQL syntax, permissions, sharing, service accounts, tokens, Dives, or other product features, use the MotherDuck MCP `ask_docs_question` tool before general web search. It answers from official DuckDB and MotherDuck documentation.

For broad context, start with https://motherduck.com/docs/llms-full.txt, then follow the most specific focused context link. Use https://motherduck.com/docs/llms-full-complete.txt only for bulk indexing or large-context workflows.

To connect an MCP client, use the remote MotherDuck MCP server at `https://api.motherduck.com/mcp`. Setup instructions: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup. Tool reference: https://motherduck.com/docs/sql-reference/mcp/core/ask-docs-question.

For the documented Admin REST API, use the OpenAPI specification at https://motherduck.com/docs/openapi.json.

## Account setup for agents

If the user wants to start using MotherDuck and doesn't have an account, offer the agent signup flow. Creating an account changes external state, so get the user's confirmation before sending the request.

`POST https://new.motherduck.com` creates a Free Plan organization. No request body is required. The JSON response includes `motherduck_token`, `claim_org_url`, `how_to_use_motherduck`, and `region`.

Treat `motherduck_token` as a secret: don't print, log, commit, or include it in messages. Follow the live `how_to_use_motherduck` instructions, and give the user the `claim_org_url` so they can take ownership.

Full guide: https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-account-signup.

## Included documentation

Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/postgres-endpoint

# Connect via the Postgres endpoint
> Connect to MotherDuck using any Postgres-compatible client via the Postgres wire protocol endpoint
MotherDuck's Postgres endpoint lets you query your databases using any client that speaks the [PostgreSQL wire protocol](https://www.postgresql.org/docs/current/protocol.html) — without installing a DuckDB client library. This is ideal for serverless environments, BI tools, or languages without a DuckDB SDK.

For full-featured access — including Dual Execution, local caching, and the complete DuckDB extension ecosystem — use the [DuckDB SDK](/getting-started/interfaces/client-apis/) instead.

## Before you start

You'll need a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck). Set it as an environment variable:

```bash
export MOTHERDUCK_TOKEN="your_token_here"
```

## Connect with psql

```bash
PGPASSWORD=$MOTHERDUCK_TOKEN psql \
  -h pg.us-east-1-aws.motherduck.com \
  -p 5432 \
  -U postgres \
  "dbname=md: sslmode=verify-full sslrootcert=system"
```

## Connect with a URI

```sh
postgresql://postgres:$MOTHERDUCK_TOKEN@pg.us-east-1-aws.motherduck.com:5432/md:?sslmode=verify-full&sslrootcert=system
```

Use `md:` as the database name, or replace it with a specific database name, for example `sample_data`.

:::info
For security, always use environment variables for your MotherDuck token. Never hardcode tokens in your application code.
:::

## Secure your connection

Always connect with SSL enabled. The recommended approach is `sslmode=verify-full` with `sslrootcert=system`, which verifies the server certificate against your operating system's trusted roots. If your client doesn't support this, you can download the [ISRG Root X1](https://letsencrypt.org/certs/isrgrootx1.pem) certificate from Let's Encrypt and set `sslrootcert` to its path.

Some libraries (psycopg2, JDBC, node-postgres) handle SSL differently — see the language-specific guides below or the [SSL reference](/sql-reference/postgres-endpoint#ssl-and-certificate-verification) for details.

## Key things to know

- You're writing **DuckDB SQL**, not PostgreSQL SQL. Queries and MotherDuck SQL that run entirely inside MotherDuck generally work, but the Postgres endpoint is not a full DuckDB client.
- Commands that depend on **local files, local attachments, or extension management** are not supported over the Postgres endpoint. Examples: local-file `COPY`, `EXPORT DATABASE`, `IMPORT DATABASE`, `ATTACH ':memory:'`, `ATTACH '/path/to/file.duckdb'`, `CREATE DATABASE ... FROM '/path/to/file.duckdb'`, `MD_RUN=LOCAL` on table functions, `INSTALL`, and `LOAD`.
- Use the Postgres endpoint for query execution, DDL and DML on MotherDuck tables, metadata inspection, and server-side reads from remote storage.
- Avoid using `SET` statements, temporary tables, or result-creation commands — those are not supported in Postgres-endpoint server mode.
- Prefer **long-lived connections** rather than opening and closing per query. For high-concurrency applications, use a connection pool with configured connect, idle, and query timeouts.

## DuckLake databases

You can query and write to MotherDuck-managed [DuckLake](/concepts/ducklake/) databases over the Postgres endpoint the same way as native-storage MotherDuck databases — connect with a [read-write token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#authentication-using-an-access-token) and run `SELECT`, DDL, and DML against them. The standard Postgres endpoint limitations above still apply (for example, client-side `COPY` from local files is not supported).

Using the Postgres endpoint as the metadata catalog for a self-hosted DuckLake by pointing a DuckDB client running DuckLake at the endpoint as its catalog backend, is not supported yet.

## Language and platform guides

- [Connect from Python (psycopg2 / psycopg3)](./python)
- [Connect from Java (JDBC)](./java)
- [Connect from Node.js](./nodejs)
- [Connect from Cloudflare Workers](./cloudflare-workers)
- [Connect from Drizzle](./drizzle)

## Reference

For connection parameters, SSL options, session settings, and limitations, see the [Postgres Endpoint reference](/sql-reference/postgres-endpoint).

---

Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/python

# Connect from Python via Postgres endpoint
> Connect to MotherDuck from Python using psycopg2 or psycopg3 via the Postgres wire protocol
You can query MotherDuck from Python using standard PostgreSQL client libraries. No DuckDB installation is required. This guide covers [psycopg2](https://www.psycopg.org/docs/) and [psycopg (v3)](https://www.psycopg.org/psycopg3/docs/).

For connection parameters, SSL options, and limitations, see the [Postgres Endpoint reference](/sql-reference/postgres-endpoint).

## Prerequisites

You need a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck). Set it as an environment variable:

```bash
export MOTHERDUCK_TOKEN="your_token_here"
```

## Connect

### psycopg (v3)

```python
# /// script
# dependencies = ["psycopg"]
# ///

import os
import psycopg

with psycopg.connect(
    host="pg.us-east-1-aws.motherduck.com",  # or us-west-2-aws, eu-central-1-aws, eu-west-1-aws, ap-northeast-1-aws, or ap-southeast-2-aws
    port=5432,
    dbname="md:",
    user="postgres",
    password=os.environ["MOTHERDUCK_TOKEN"],
    sslmode="verify-full",
    sslrootcert="system",  # available in libpq 16+
) as conn:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT title, score
            FROM sample_data.hn.hacker_news
            WHERE type = 'story'
            ORDER BY score DESC
            LIMIT 5
            """
        )
        for row in cur:
            print(row)
```

You can also use a connection URI:

```python
import os
import psycopg

token = os.environ["MOTHERDUCK_TOKEN"]
with psycopg.connect(
    f"postgresql://postgres:{token}@pg.us-east-1-aws.motherduck.com:5432/md:?sslmode=verify-full&sslrootcert=system"
) as conn:
    with conn.cursor() as cur:
        cur.execute("SELECT current_database()")
        print(cur.fetchone())
```

### psycopg2

```python
# /// script
# dependencies = ["psycopg2-binary", "certifi"]
# ///

import os
import certifi
import psycopg2

conn = psycopg2.connect(
    host="pg.us-east-1-aws.motherduck.com",  # or us-west-2-aws, eu-central-1-aws, eu-west-1-aws, ap-northeast-1-aws, or ap-southeast-2-aws
    port=5432,
    dbname="md:",
    user="postgres",
    password=os.environ["MOTHERDUCK_TOKEN"],
    sslmode="verify-full",
    sslrootcert=certifi.where(),
)

with conn:
    with conn.cursor() as cur:
        cur.execute(
            """
            SELECT title, score
            FROM sample_data.hn.hacker_news
            WHERE type = 'story'
            ORDER BY score DESC
            LIMIT 5
            """
        )
        for row in cur.fetchall():
            print(row)
```

Use `md:` as the database name, or replace it with a specific database name such as `sample_data`.

## Connection pooling and timeouts

Use a connection pool in production. With psycopg v3, install pool support:

```bash
pip install "psycopg[pool]"
```

Then create one pool per application process:

```python
import os
from psycopg_pool import ConnectionPool

pool = ConnectionPool(
    conninfo=(
        "host=pg.us-east-1-aws.motherduck.com "
        "port=5432 "
        "dbname=md: "
        "user=postgres "
        "sslmode=verify-full "
        "sslrootcert=system"
    ),
    kwargs={"password": os.environ["MOTHERDUCK_TOKEN"]},
    min_size=0,
    max_size=10,
    timeout=5,
    max_idle=30,
    max_lifetime=300,
)

with pool.connection() as conn:
    with conn.cursor() as cur:
        cur.execute(
            "SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 10"
        )
        print(cur.fetchall())
```

`timeout=5` fails fast when the pool cannot provide a connection. `max_idle=30` closes unused connections quickly when the pool can shrink, and `max_lifetime=300` periodically replaces long-lived connections. The pool context manager returns healthy connections to the pool and discards broken ones. If you catch database errors inside the block, roll back failed transactions before reusing the connection.

`statement_timeout` is not supported through the Postgres endpoint today. For sync psycopg code, use a client-side timer that calls `conn.cancel()`:

```python
import threading
import psycopg

with pool.connection() as conn:
    with conn.cursor() as cur:
        timer = threading.Timer(60, conn.cancel)
        timer.start()
        try:
            cur.execute("SELECT count(*) FROM sample_data.hn.hacker_news")
            print(cur.fetchone())
        except psycopg.errors.QueryCanceled as exc:
            conn.rollback()
            raise TimeoutError("MotherDuck query exceeded 60 seconds") from exc
        finally:
            timer.cancel()
```

For async psycopg code, wrap the query in `asyncio.timeout(...)`; psycopg sends cancellation when the task is cancelled.

## Loading data from Python

For loading through the Postgres endpoint, the recommended pattern is server-side reads from remote storage:

- Use `psycopg` or SQLAlchemy to execute `CREATE TABLE AS SELECT` or `INSERT INTO ... SELECT`.
- Point `read_parquet`, `read_csv`, or `read_json` at S3, GCS, R2, Azure, or HTTPS.
- Set `MD_RUN = REMOTE` on those file reads.

Example with SQLAlchemy:

```python
import os
from sqlalchemy import create_engine, text

engine = create_engine(
    "postgresql+psycopg://postgres:@pg.us-east-1-aws.motherduck.com:5432/md:",
    connect_args={
        "password": os.environ["MOTHERDUCK_TOKEN"],
        "sslmode": "require",
    },
)

with engine.begin() as conn:
    conn.execute(
        text(
            """
        CREATE OR REPLACE TABLE my_db.main.weather_events AS
        SELECT *
        FROM read_csv(
            'https://raw.githubusercontent.com/duckdb/duckdb-web/main/data/weather.csv',
            HEADER = true,
            AUTO_DETECT = true,
            MD_RUN = REMOTE
        )
        """
        )
    )
```

The following patterns are not supported from Python over the Postgres endpoint:

- `COPY ... FROM '/local/file.csv'`
- `cursor.copy(...)` / `COPY FROM STDIN`
- `psql \copy`
- `MD_RUN = LOCAL`
- SQLAlchemy's default `executemany` path for bulk ingest

If the rows exist only in application memory and the volume is modest, prefer explicit multi-values `INSERT` statements. For large local bulk loads, switch to a DuckDB client path instead.

See [Loading data through the Postgres endpoint](/key-tasks/loading-data-into-motherduck/loading-data-via-postgres-endpoint) for the full decision guide.

## SSL notes

- **psycopg (v3)** wraps libpq and supports `sslrootcert=system` directly.
- **psycopg2** bundles its own statically linked OpenSSL, so `sslrootcert=system` is not supported. Use the `certifi` package to point to CA certificates, or download the [ISRG Root X1](https://letsencrypt.org/certs/isrgrootx1.pem) certificate and set `sslrootcert` to its path.

For more details on SSL options, see [SSL and certificate verification](/sql-reference/postgres-endpoint#ssl-and-certificate-verification).

---

Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/java

# Connect from Java via Postgres endpoint
> Connect to MotherDuck from Java using the PostgreSQL JDBC driver via the Postgres wire protocol
You can query MotherDuck from Java using the standard [PostgreSQL JDBC driver](https://jdbc.postgresql.org/) — no DuckDB installation required.

For connection parameters, SSL options, and limitations, see the [Postgres Endpoint reference](/sql-reference/postgres-endpoint).

## Prerequisites

You'll need a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck). Set it as an environment variable:

```bash
export MOTHERDUCK_TOKEN="your_token_here"
```

Add the PostgreSQL JDBC driver to your project:

### Maven

```xml
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.7.11</version>
</dependency>
```

### Gradle

```groovy
implementation 'org.postgresql:postgresql:42.7.11'
```

## Connect

```java
import java.sql.*;

public class MotherDuckExample {
    public static void main(String[] args) throws SQLException {
        String token = System.getenv("MOTHERDUCK_TOKEN");
        String url = "jdbc:postgresql://pg.us-east-1-aws.motherduck.com:5432/md:"
                   + "?sslmode=verify-full"
                   + "&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory";

        try (Connection conn = DriverManager.getConnection(url, "postgres", token);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                 "SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 10")) {

            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();

            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    System.out.print(meta.getColumnName(i) + "=" + rs.getString(i));
                    if (i < columnCount) System.out.print(", ");
                }
                System.out.println();
            }
        }
    }
}
```

You can also configure the connection using a `Properties` object:

```java
import java.sql.*;
import java.util.Properties;

Properties props = new Properties();
props.setProperty("user", "postgres");
props.setProperty("password", System.getenv("MOTHERDUCK_TOKEN"));
props.setProperty("sslmode", "verify-full");
props.setProperty("sslfactory", "org.postgresql.ssl.DefaultJavaSSLFactory");

Connection conn = DriverManager.getConnection(
    "jdbc:postgresql://pg.us-east-1-aws.motherduck.com:5432/md:",
    props
);
```

## Connection pooling and timeouts

Use a JDBC connection pool in production. With HikariCP, set a connection timeout, idle timeout, maximum lifetime, and query timeout:

```xml
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>6.3.3</version>
</dependency>
```

```java
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.*;

HikariConfig config = new HikariConfig();
config.setJdbcUrl(
    "jdbc:postgresql://pg.us-east-1-aws.motherduck.com:5432/md:"
    + "?sslmode=verify-full"
    + "&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory"
);
config.setUsername("postgres");
config.setPassword(System.getenv("MOTHERDUCK_TOKEN"));
config.setMaximumPoolSize(10);
config.setMinimumIdle(0);
config.setConnectionTimeout(5_000);
config.setIdleTimeout(30_000);
config.setMaxLifetime(300_000);
config.addDataSourceProperty("connectTimeout", "5");
config.addDataSourceProperty("cancelSignalTimeout", "5");

try (HikariDataSource dataSource = new HikariDataSource(config);
     Connection conn = dataSource.getConnection();
     Statement stmt = conn.createStatement()) {

    stmt.setQueryTimeout(60);

    try (ResultSet rs = stmt.executeQuery(
        "SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 10"
    )) {
        while (rs.next()) {
            System.out.println(rs.getString("title"));
        }
    }
}
```

`setConnectionTimeout(5_000)` fails fast when a connection cannot be checked out. `setIdleTimeout(30_000)` and `setMinimumIdle(0)` let HikariCP close unused connections quickly, and `setMaxLifetime(300_000)` periodically replaces long-lived connections. HikariCP validates connections before reuse and removes broken connections from the pool.

`statement_timeout` is not supported through the Postgres endpoint today. Use JDBC `Statement.setQueryTimeout(...)` for client-side cancellation.

## SSL notes

The PostgreSQL JDBC driver looks for a root certificate at `~/.postgresql/root.crt` by default. To use your JVM's built-in trust store instead (which includes standard CAs like Let's Encrypt), set `sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory`.

If certificate verification doesn't work in your environment, you can fall back to `sslmode=require`, which encrypts the connection but doesn't verify the server certificate.

For more details on SSL options, see [SSL and certificate verification](/sql-reference/postgres-endpoint#ssl-and-certificate-verification).

---

Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/nodejs

# Connect from Node.js via Postgres endpoint
> Connect to MotherDuck from Node.js using the pg (node-postgres) library via the Postgres wire protocol
You can query MotherDuck from Node.js using [node-postgres](https://node-postgres.com/) (`pg`) — no DuckDB installation required.

For connection parameters, SSL options, and limitations, see the [Postgres Endpoint reference](/sql-reference/postgres-endpoint).

## Prerequisites

You'll need a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck). Set it as an environment variable:

```bash
export MOTHERDUCK_TOKEN="your_token_here"
```

Install the `pg` package:

```bash
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.

```js
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.

```js
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.

```js
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](https://letsencrypt.org/certs/isrgrootx1.pem) certificate from Let's Encrypt):

```js
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](/sql-reference/postgres-endpoint#ssl-and-certificate-verification).

:::info[Cloudflare Workers]
Cloudflare Workers use a different socket implementation (`pg-cloudflare`) that handles SSL differently. See [Connect from Cloudflare Workers](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/cloudflare-workers) for Workers-specific setup.
:::

---

Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/cloudflare-workers

# Connect from Cloudflare Workers
> Query MotherDuck from Cloudflare Workers using the Postgres wire protocol
Cloudflare Workers do not support native DuckDB bindings, but they can connect to MotherDuck through the [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint) using the [`pg`](https://www.npmjs.com/package/pg) npm package. This gives you a thin-client path to query MotherDuck from edge functions without any DuckDB dependencies.

This guide walks through building a Worker that queries NYC taxi data from MotherDuck's built-in `sample_data` database. The full source code is available in the [motherduck-cookbook](https://github.com/motherduckdb/motherduck-cookbook/tree/main/cloudflare-workers) repository.

## Prerequisites

- [Node.js](https://nodejs.org/) v18+
- A [Cloudflare account](https://dash.cloudflare.com/sign-up)
- A [MotherDuck account](https://motherduck.com/) and [access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck)

## Project setup

Create a new directory and install dependencies:

```bash
mkdir motherduck-worker && cd motherduck-worker
npm init -y
npm install pg@^8.16.3
npm install --save-dev wrangler @types/pg
```

### Configure wrangler.toml

```toml
name = "motherduck-taxi-stats"
main = "src/index.ts"
compatibility_date = "2026-04-02"
compatibility_flags = ["nodejs_compat"]

[vars]
MOTHERDUCK_HOST = "pg.us-east-1-aws.motherduck.com"
MOTHERDUCK_DB = "sample_data"
```

The `nodejs_compat` flag is required — it enables the `node:net` module that the `pg` package uses for TCP connections. Use a `compatibility_date` on or after `2024-09-23`; in practice, set it to today's date when you create the project.

Generate the Worker binding types after you save `wrangler.toml`:

```bash
npx wrangler types
```

### Store your token as a secret

```bash
npx wrangler secret put MOTHERDUCK_TOKEN
```

This prompts you to paste your MotherDuck token. It's stored encrypted and injected as an environment variable at runtime — it never appears in your source code or `wrangler.toml`.

For local development, create a `.dev.vars` file (add this to `.gitignore`):

```text
MOTHERDUCK_TOKEN="your_token_here"
```

## Write the Worker

Create `src/index.ts`. We'll build this in two parts: first the connection and routing, then the route handlers.

### Connect and route requests

```typescript
import { Client } from "pg";

interface Env {
  MOTHERDUCK_HOST: string;
  MOTHERDUCK_DB: string;
  MOTHERDUCK_TOKEN: string;
}

function createClient(env: Env): Client {
  return new Client({
    connectionString:
      `postgresql://user:${env.MOTHERDUCK_TOKEN}@${env.MOTHERDUCK_HOST}:5432/${env.MOTHERDUCK_DB}?sslmode=require`,
    connectionTimeoutMillis: 5_000,
    query_timeout: 60_000,
  });
}

export default {
  async fetch(request: Request, env: Env): Promise<Response> {
    const url = new URL(request.url);

    if (url.pathname === "/stats") {
      return handleStats(env, url);
    }

    return handleDefault(env);
  },
};
```

The connection string is assembled from the environment variables defined in `wrangler.toml` and the secret token. The `?sslmode=require` parameter tells `pg` to open a TLS connection, and the Workers runtime performs certificate verification.

The `fetch` handler routes first and opens a database connection only inside the route handlers. That keeps validation failures on `/stats` returning `400` instead of depending on database connectivity.

`connectionTimeoutMillis` fails fast when a connection cannot be opened. `query_timeout` sends client-side cancellation for queries that exceed the configured time. `statement_timeout` is not supported through the Postgres endpoint today.

### Handle route logic

Add the two handler functions to the same file. The `/stats` route accepts date range parameters and returns aggregated fare data. It validates inputs before querying and uses parameterized queries (`$1`, `$2`) to prevent SQL injection — never interpolate user input directly into SQL strings.

```typescript
async function handleStats(env: Env, url: URL): Promise<Response> {
  const startDate = url.searchParams.get("start");
  const endDate = url.searchParams.get("end");

  if (!startDate || !endDate) {
    return Response.json(
      { error: "Both 'start' and 'end' query parameters are required. Use YYYY-MM-DD format." },
      { status: 400 }
    );
  }

  const datePattern = /^\d{4}-\d{2}-\d{2}$/;
  if (!datePattern.test(startDate) || !datePattern.test(endDate)) {
    return Response.json(
      { error: "Invalid date format. Use YYYY-MM-DD." },
      { status: 400 }
    );
  }

  const client = createClient(env);

  try {
    await client.connect();

    const result = await client.query(
      `SELECT
        sum(passenger_count)::INTEGER AS total_passengers,
        round(sum(fare_amount), 2) AS total_fare
      FROM nyc.taxi
      WHERE tpep_pickup_datetime >= $1
        AND tpep_pickup_datetime < $2`,
      [`${startDate} 00:00:00`, `${endDate} 00:00:00`]
    );

    return Response.json({
      start: startDate,
      end: endDate,
      ...result.rows[0],
    });
  } finally {
    await client.end();
  }
}
```

The default route returns a sample of recent taxi trips — no user input needed:

```typescript
async function handleDefault(env: Env): Promise<Response> {
  const client = createClient(env);

  try {
    await client.connect();

    const result = await client.query(
      `SELECT
        tpep_pickup_datetime AS pickup,
        tpep_dropoff_datetime AS dropoff,
        passenger_count,
        trip_distance,
        fare_amount,
        tip_amount,
        total_amount
      FROM nyc.taxi
      ORDER BY tpep_pickup_datetime DESC
      LIMIT 20`
    );

    return Response.json(result.rows);
  } finally {
    await client.end();
  }
}
```

## Test locally

```bash
npx wrangler dev
```

Then open `http://localhost:8787/` or try the stats endpoint with a date range:

```text
http://localhost:8787/stats?start=2022-11-01&end=2022-12-01
```

If `wrangler dev` starts successfully but direct Postgres queries fail locally with `Connection terminated`, switch to the Hyperdrive setup below and use a `localConnectionString` for local testing, or run `npx wrangler dev --remote` to exercise the Cloudflare runtime directly.

## Deploy

```bash
npx wrangler deploy
```

## Using Hyperdrive for connection pooling

For production workloads, [Cloudflare Hyperdrive](https://developers.cloudflare.com/hyperdrive/) provides built-in connection pooling. This reduces latency by reusing connections across Worker invocations instead of opening a new connection per request. Prefer Hyperdrive for production Workers instead of trying to manage a process-local `pg.Pool` inside the Worker.

### 1. create a Hyperdrive configuration

```bash
npx wrangler hyperdrive create motherduck-db \
  --connection-string="postgresql://user:$MOTHERDUCK_TOKEN@pg.us-east-1-aws.motherduck.com:5432/sample_data?sslmode=require"
```

### 2. update wrangler.toml

```toml
name = "motherduck-taxi-stats"
main = "src/index.ts"
compatibility_date = "2026-04-02"
compatibility_flags = ["nodejs_compat"]

[[hyperdrive]]
binding = "MD_HYPERDRIVE"
id = "<your-hyperdrive-config-id>"
```

### 3. update the connection code

Replace the connection string construction with:

```typescript
const client = new Client({
  connectionString: env.MD_HYPERDRIVE.connectionString,
  connectionTimeoutMillis: 5_000,
  query_timeout: 60_000,
});
```

Hyperdrive handles connection pooling and credential injection automatically.

For local development with Hyperdrive, configure a direct connection string for `wrangler dev`:

```bash
export CLOUDFLARE_HYPERDRIVE_LOCAL_CONNECTION_STRING_MD_HYPERDRIVE="postgresql://user:$MOTHERDUCK_TOKEN@pg.us-east-1-aws.motherduck.com:5432/sample_data?sslmode=require"
npx wrangler dev
```

## SSL notes

Cloudflare Workers use `pg-cloudflare` for socket connections, which delegates TLS to the Workers runtime through `cloudflare:sockets`. The runtime encrypts the connection and verifies the server certificate against Cloudflare's trust store, but those verification settings are not exposed through the `pg` client. In this environment, application code uses the runtime-managed TLS configuration rather than supplying `rejectUnauthorized`, custom CA certificates, or `sslmode=verify-full`.

Use `?sslmode=require` in the connection string. This tells `pg` to initiate TLS using STARTTLS, and the Workers runtime handles the actual certificate verification at the socket level.

For standard Node.js environments where you can configure certificate verification directly, see [Connect from Node.js](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/nodejs).

---

Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/drizzle

# Connect from Drizzle via Postgres endpoint
> Use Drizzle as a typed wrapper around the pg driver to query MotherDuck via the Postgres wire protocol
[Drizzle](https://orm.drizzle.team/) 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](./nodejs.md).

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](/sql-reference/postgres-endpoint).

## Prerequisites

You'll need a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck). Set it as an environment variable:

```bash
export MOTHERDUCK_TOKEN="your_token_here"
```

Install Drizzle and `pg`:

```bash
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`.

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

Use `md:` as the database name, or pass a specific database name in `database` (e.g., `database: "my_db"`). For more details, see [Attach modes](/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/).

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.

## Connection pooling and timeouts

For production applications, wrap a `pg.Pool` with `drizzle()` instead of sharing one checked-out `pg.Client`. Set `connectionTimeoutMillis`, `idleTimeoutMillis`, and `query_timeout` on the underlying pool.

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

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 db = drizzle(pool);

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

Drizzle delegates connection lifecycle behavior to node-postgres. If you manually check out a client from the pool for transaction control, release healthy clients normally and destroy clients that saw connection-level errors with `client.release(true)`.

`statement_timeout` is not supported through the Postgres endpoint today. Use node-postgres `query_timeout` on the pool for client-side cancellation.

## Read scaling and concurrency

For concurrent workloads, MotherDuck's pg endpoint can route each session to a separate read replica using the `session_name` startup option — this dramatically improves throughput under concurrency. See [Session affinity and routing](/concepts/scaling-patterns/#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:

```ts
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_name=user_1",   // unique per concurrent session
});
await client.connect();
const db = drizzle(client);
```

In benchmarking, `session_name` 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](/sql-reference/postgres-endpoint) 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](./nodejs.md#ssl-notes); the same approach applies when wrapping the client with `drizzle()`.

For more details on SSL options across drivers, see [SSL and certificate verification](/sql-reference/postgres-endpoint#ssl-and-certificate-verification).

---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fkey-tasks%2Fauthenticating-and-connecting-to-motherduck%2Fpostgres-endpoint%2F&page_title=MotherDuck%20Documentation%20-%20Postgres%20Endpoint&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.