Motherduck Connect

Connect to MotherDuck

Use this skill when establishing database connectivity from any application, script, or service to MotherDuck. Start here before running queries or loading data.

Source Of Truth

• Prefer current MotherDuck connection, attach-mode, read-scaling, and multithreading docs.
• If the MotherDuck MCP ask_docs_question tool is available, use it first for current connection behavior.
• When it is unavailable, verify guidance against the public docs before making firm claims about connection strings, token types, or read-scaling behavior.

Default Posture

• Start with the PG endpoint (MotherDuck's Postgres-compatible endpoint) for backend applications, BI tools, and serverless runtimes that want PostgreSQL wire compatibility.
• For BI tools, treat the PG endpoint as the compatibility path for Power BI and Tableau Cloud when current docs list them as supported.
• Use the native DuckDB API only when you need local files, hybrid local/cloud execution, or direct DuckDB control.
• Use md: workspace connections for multi-database exploration, bootstrap flows, and temporary validation environments.
• Reuse an existing connection, connector, or environment-managed token when the user's context already provides one; do not ask for secrets that can be discovered from the active workspace.
• Start with one connection. Add pooling or read scaling only when real concurrent-read pressure exists.
• Use native DuckDB custom_user_agent where supported; for PG endpoint clients, prefer the client's application_name setting when available.

Runtime Selection

Pick the connection method (above) and the runtime separately. The runtime is what actually executes queries: an MCP server, a Python script, a Node script, or the DuckDB CLI.

Classify the workload first:

• Ad-hoc / exploration: one-shot, interactive, may be discarded. No artifact ships.
• Recurring / pipeline: scheduled, version-controlled, runs unattended. Code is checked into a repo.

Then resolve in this order, stopping at the first match:

1. MotherDuck MCP available + workload is ad-hoc → use the MCP tools (query, list_databases, list_tables, list_columns, search_catalog). No client to install. Stop here. 2. `uv` is installed (command -v uv) → run scripts via uv run --with "duckdb==<version>" script.py. Preferred for both ad-hoc scripts and pipelines because dependencies are declared inline and reproducible. 3. `python3` + `pip` available → pip install "duckdb==<version>" inside a project-managed venv. 4. `node` + `npm` available → npm install @duckdb/node-api@<version>. 5. None of the above → install the DuckDB CLI: curl -s https://install.motherduck.com | env -u motherduck_token HOME="$install_home" sh. Pick $install_home as a writable, project-local path (for example ./.duckdb) rather than polluting the user's home.

If the host project already declares a language (a pyproject.toml, package.json, or similar lockfile is present), follow that language even if the priority order would suggest otherwise. Do not introduce a second runtime alongside an existing one.

Before any install step, fetch https://motherduck.com/docs/duckdb-versions.json and pick the highest MotherDuck-supported DuckDB version. Pin that version explicitly in the install command. Latest upstream DuckDB is not automatically supported on MotherDuck.

Workflow

1. Choose one connection method and do not mix methods in the same application. 2. Put the MotherDuck token in environment-managed secrets, not in source code. 3. Establish the connection with explicit SSL settings where required. 4. Verify the connection with SELECT 1 AS connected and then list reachable tables. 5. If the workload is read-heavy and concurrent, evaluate read scaling and session_hint.

Open Next

• Read references/CONNECTION_GUIDE.md for connection-method selection, PG endpoint and native DuckDB examples, token handling, read scaling, attach modes, and common failure modes
• Read references/RUNTIME_SELECTION.md for the MCP-vs-Python-vs-Node-vs-CLI decision tree, detection commands, install snippets, and the DuckDB version-pinning workflow

Related Skills

• motherduck-explore for discovering databases, tables, columns, and shares after the connection is established
• motherduck-query for executing DuckDB SQL against the connected databases
• motherduck-duckdb-sql for DuckDB syntax and function lookup support
• motherduck-rest-api for control-plane admin operations; those use MOTHERDUCK_ADMIN_TOKEN, which is never used for database connections

Connection Guide

Reference for selecting a MotherDuck connection method, configuring authentication, and operating read-scaling or native DuckDB connections safely.

This file picks the connection method (PG endpoint, native DuckDB API, pg_duckdb, WASM). Pick the runtime that executes the connection (MCP server, Python with uv or pip, Node, or the DuckDB CLI) in RUNTIME_SELECTION.md.

Contents

Choose a Connection Method

Pick one. Do not mix methods in the same application.

Is this a backend app or script?
├── Yes ─── Do you need hybrid local/cloud execution?
│           ├── No  ──> PG Endpoint (DEFAULT — start here)
│           └── Yes ──> Native DuckDB API (md: protocol)
├── Extending an existing PostgreSQL database?
│           └── Yes ──> pg_duckdb
└── Browser-only analytics under 1GB?
            └── Yes ──> DuckDB-WASM

Use the PG endpoint for backend applications and BI tools that already want PostgreSQL wire compatibility. It is the compatibility path for supported tools such as Power BI and Tableau Cloud, as well as serverless runtimes where installing a native DuckDB client is awkward. If the runtime can use DuckDB directly and you need local files, hybrid execution, or tighter DuckDB control, use the native DuckDB API instead.

Operational Defaults

• Start with one connection.
• Add connection pooling only for long-lived read-only concurrency or queue-style backends.
• Add read scaling only when many concurrent read-only users on the same account are actually the bottleneck.
• Use single attach mode for narrow app or BI connections that should not persist attachment changes.
• Use workspace mode only when the client intentionally wants shared, persistent attachment state across sessions.
• Use a native md: workspace connection for database bootstrap, multi-database exploration, and temporary validation environments.

Language Focus

• Prefer Python for data pipelines, notebooks, FastAPI backends, ETL, orchestration, and ad hoc operational scripts. Default to uv run --with duckdb for scripts; use psycopg2 or SQLAlchemy on the PG endpoint and duckdb for native DuckDB API usage.
• Prefer TypeScript/Javascript for backend APIs, serverless functions, Next.js or Express applications, and customer-facing analytics products. Default to pg for the PG endpoint and @duckdb/node-api for native DuckDB API usage.
• For shell-driven ad hoc work where neither Python nor Node is appropriate, fall back to the DuckDB CLI. See RUNTIME_SELECTION.md for the install path and the runtime priority order overall.

Step 1: Set the Environment Variable

Store the token in an environment variable. Never hardcode tokens in source code.

export MOTHERDUCK_TOKEN="<your_token>"

Step 2: Connect via PG Endpoint

Connection String

postgresql://postgres:<MOTHERDUCK_TOKEN>@pg.us-east-1-aws.motherduck.com:5432/<database>?sslmode=verify-full&sslrootcert=system

Use the regional hostname that matches the target MotherDuck deployment.

Connection Components

Python (psycopg2)

import psycopg2
import certifi
import os

conn = psycopg2.connect(
    host="pg.us-east-1-aws.motherduck.com",
    port=5432,
    dbname="my_database",
    user="postgres",
    password=os.environ["MOTHERDUCK_TOKEN"],
    sslmode="verify-full",
    sslrootcert=certifi.where(),
)

cur = conn.cursor()
cur.execute("SELECT * FROM my_table LIMIT 10")
rows = cur.fetchall()
for row in rows:
    print(row)

cur.close()
conn.close()

Install: pip install psycopg2-binary certifi

Node.js (pg)

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: "my_database",
  ssl: { rejectUnauthorized: true },
});

await client.connect();
const { rows } = await client.query('SELECT * FROM "my_database"."main"."my_table" LIMIT 10');
console.log(rows);
await client.end();

Install: npm install pg

JDBC

jdbc:postgresql://pg.us-east-1-aws.motherduck.com:5432/my_database?user=postgres&password=<MOTHERDUCK_TOKEN>&sslmode=verify-full

Use the standard PostgreSQL JDBC driver.

Python (SQLAlchemy)

import os
from sqlalchemy import create_engine, text

token = os.environ["MOTHERDUCK_TOKEN"]
engine = create_engine(
    f"postgresql+psycopg2://postgres:{token}@pg.us-east-1-aws.motherduck.com:5432/my_database",
    connect_args={"sslmode": "verify-full", "sslrootcert": __import__("certifi").where()},
)

with engine.connect() as conn:
    result = conn.execute(text("SELECT * FROM my_table LIMIT 10"))
    for row in result:
        print(row)

Step 3: Verify the Connection

SELECT 1 AS connected;

SELECT table_name FROM duckdb_tables() WHERE database_name = 'my_database';

Native DuckDB API Alternative

Use this when you need dual execution, local file access, or direct DuckDB features that are not available through the PG endpoint.

Use duckdb.connect("md:") for workspace-level operations such as:

• creating or dropping databases
• exploring multiple databases in one session
• validating cross-database patterns with fully qualified names

Use duckdb.connect("md:my_database") when the workload is scoped to one database.

When a use-case skill emits a native DuckDB connection, watermark it with custom_user_agent=agent-skills/2.3.0(harness-<harness>;llm-<llm>). If metadata values are missing, use harness-unknown and llm-unknown.

Python

import duckdb
import os

USE_CASE_USER_AGENT = "agent-skills/2.3.0(harness-<harness>;llm-<llm>)"

# Default: token picked up from the MOTHERDUCK_TOKEN env var
conn = duckdb.connect(
    f"md:my_database?custom_user_agent={USE_CASE_USER_AGENT}"
)

# Alternative: pass the token explicitly (still sourced from the env var)
conn = duckdb.connect(
    "md:my_database"
    f"?motherduck_token={os.environ['MOTHERDUCK_TOKEN']}"
    f"&custom_user_agent={USE_CASE_USER_AGENT}"
)

result = conn.sql('SELECT * FROM "my_database"."main"."my_table" LIMIT 10')
result.show()
conn.close()

Install: pip install duckdb

Node.js (@duckdb/node-api)

import { DuckDBInstance } from "@duckdb/node-api";

const userAgent = "agent-skills/2.3.0(harness-<harness>;llm-<llm>)";
const instance = await DuckDBInstance.create(
  `md:my_database?attach_mode=single&custom_user_agent=${userAgent}`,
  {
    motherduck_token: process.env.MOTHERDUCK_TOKEN,
  }
);
const connection = await instance.connect();

const result = await connection.run('SELECT * FROM "my_database"."main"."my_table" LIMIT 10');
console.log(result);

Install: npm install @duckdb/node-api

JDBC (Native DuckDB)

jdbc:duckdb:md:my_database?motherduck_token=<MOTHERDUCK_TOKEN>&custom_user_agent=agent-skills/2.3.0(harness-<harness>;llm-<llm>)

Requires the DuckDB JDBC driver, not the PostgreSQL driver.

Authentication

Token Types

Create a Service Token

1. Go to MotherDuck UI > Settings > Access Tokens 2. Click Create token 3. Select the token type 4. Set an optional expiration date 5. Copy the token immediately

Token Best Practices

• Store tokens in environment variables or a secrets manager.
• Use service accounts for production applications, not personal tokens.
• Set expiration dates and rotate tokens regularly.
• Use read-scaling tokens for read-heavy workloads.
• Revoke compromised tokens immediately.
• Scope each service to its own token when possible.
• MOTHERDUCK_ADMIN_TOKEN is a separate env var used only for REST control-plane admin calls (service accounts, token management; see motherduck-rest-api). Database connections always use MOTHERDUCK_TOKEN or MOTHERDUCK_READ_SCALING_TOKEN.

Read Scaling and Session Affinity

Use read scaling for high-concurrency read-only workloads on the same account.

• Read scaling replicas are eventually consistent.
• Default read-scaling pool size is 4 replicas and can be increased up to 16 as a soft limit.
• Use a stable session_hint per end user, session, or tenant-facing request path.
• Prefer access_mode=read_only on read-only serving connections.
• Use dbinstance_inactivity_ttl where supported to help preserve session affinity across short connection gaps.
• If the workflow needs stricter freshness after a write, use CREATE SNAPSHOT on the writer and REFRESH DATABASE on readers.

Python

import duckdb
import os

conn = duckdb.connect(
    "md:my_database?session_hint=user-123&access_mode=read_only"
    "&dbinstance_inactivity_ttl=300"
    "&custom_user_agent=agent-skills/2.3.0(harness-<harness>;llm-<llm>)",
    config={
        "motherduck_token": os.environ["MOTHERDUCK_READ_SCALING_TOKEN"],
    },
)

Node.js

import { DuckDBInstance } from "@duckdb/node-api";

const db = await DuckDBInstance.create(
  "md:my_database?session_hint=user-123&access_mode=read_only"
  + "&dbinstance_inactivity_ttl=300"
  + "&custom_user_agent=agent-skills/2.3.0(harness-<harness>;llm-<llm>)",
  {
    motherduck_token: process.env.MOTHERDUCK_READ_SCALING_TOKEN,
  }
);

Attach Modes

• md: or md:my_database uses workspace mode and persists attachment changes across sessions.
• md:my_database?attach_mode=single uses single mode and keeps the session scoped to one database.
• For services, APIs, and BI clients, prefer single mode unless persistent multi-database workspace state is intentional.

Key Rules

• Always write DuckDB SQL, not PostgreSQL SQL.
• SSL is required for the PG endpoint.
• The PG endpoint does not support PostgreSQL-specific features such as pg_* functions, indexes, sequences, stored procedures, LISTEN/NOTIFY, or advisory locks.
• The PG endpoint does not support local file access or dual execution.
• The PG endpoint still executes DuckDB SQL; do not rewrite queries into PostgreSQL dialect just because the wire protocol is PostgreSQL-compatible.
• Nested DuckDB types can be harder to consume through PostgreSQL-compatible clients. Prefer flatter serving views for BI and server-mode embedded dashboards.
• MotherDuck documents custom_user_agent for native DuckDB connections. For PG endpoint clients, use application_name when the driver exposes it.
• Use fully qualified table names across databases.
• Do not install extensions at runtime in MotherDuck.

Common Mistakes

Writing PostgreSQL SQL Instead of DuckDB SQL

Wrong (PostgreSQL idioms that fail on MotherDuck):

SELECT to_char(order_date, 'YYYY-MM') AS month FROM my_table;
CREATE INDEX idx_name ON my_table(name);

Right:

SELECT strftime(order_date, '%Y-%m') AS month FROM my_table;
-- MotherDuck columnar storage does not use user-created indexes; rely on filters and pre-aggregation

Hardcoding Tokens

Wrong:

conn = psycopg2.connect(password="token")

Right:

conn = psycopg2.connect(password=os.environ["MOTHERDUCK_TOKEN"])

Using ORM Features That Generate PostgreSQL-Specific SQL

Test ORM-generated SQL against MotherDuck before deploying. Prefer ORMs that allow raw SQL or custom dialects.

Forgetting SSL Configuration

Wrong:

conn = psycopg2.connect(
    host="pg.us-east-1-aws.motherduck.com",
    port=5432,
    dbname="my_database",
    user="postgres",
    password=os.environ["MOTHERDUCK_TOKEN"],
)

Right:

conn = psycopg2.connect(
    host="pg.us-east-1-aws.motherduck.com",
    port=5432,
    dbname="my_database",
    user="postgres",
    password=os.environ["MOTHERDUCK_TOKEN"],
    sslmode="verify-full",
    sslrootcert=certifi.where(),
)

Using the PG Endpoint for Local File Access

Use the native DuckDB API for hybrid queries that reference local files.

Pooling Before It Is Needed

Add pooling only when a single connection is no longer enough.

Skipping session_hint on Read Scaling

Without a stable session_hint, requests from the same user can bounce between replicas and lose cache affinity.

PG Endpoint Limitations vs Native DuckDB API

Runtime Selection

Reference for choosing which runtime executes the connection to MotherDuck: MCP server, Python (with uv or pip), Node.js, or the DuckDB CLI. This is a separate decision from CONNECTION_GUIDE.md, which picks the connection method (PG endpoint vs native DuckDB API vs pg_duckdb vs WASM).

Decision Tree

Is a MotherDuck MCP server available AND the work ad-hoc/exploration?
├── Yes ─────────────────> Use MCP tools (query, list_databases, ...). STOP.
└── No  ─────────────────> Detect a runtime in this order:
                            uv installed?         ──> uv run --with "duckdb==<v>" script.py
                            python3 + pip?        ──> pip install "duckdb==<v>"
                            node + npm?           ──> npm install @duckdb/node-api@<v>
                            none of the above?    ──> install CLI via install.motherduck.com

Always stop at the first match. If the host project already declares a language (a pyproject.toml, package.json, or similar lockfile is present), follow that language even when the priority order would suggest otherwise — do not add a second runtime alongside an existing one.

Ad-hoc vs Pipeline

• Ad-hoc / exploration. One-shot, interactive, may be discarded after the answer is found. No artifact gets checked in. The MCP server is the right runtime here when it is available, because there is nothing to ship and the agent can iterate directly.
• Recurring / pipeline. Scheduled, version-controlled, runs unattended. The code lives in a repo and survives the conversation. Pipelines need a real runtime (Python, Node, or CLI) so the script is reproducible without an MCP session.

The MCP path is useful only for ad-hoc work. Even if MCP is available, a pipeline must use Python, Node, or the CLI so the source can be committed and executed in CI or production.

Detection Commands

Run these in order. The first one that exits 0 picks the runtime.

command -v uv         # preferred Python runner
command -v python3    # fallback Python
command -v node       # fallback runtime
command -v duckdb     # CLI already present

Also check whether the host project already commits to a language:

test -f pyproject.toml || test -f requirements.txt   # Python project
test -f package.json                                 # Node project

Version Pinning

MotherDuck supports a curated set of DuckDB versions; the latest upstream DuckDB release is not automatically available on MotherDuck. Always pin to a MotherDuck-supported version.

curl -s https://motherduck.com/docs/duckdb-versions.json

Parse the response and pick the highest supported version. Use that exact version string in every install command below. Re-fetch the JSON before each install — do not cache.

Install Snippets

Pin <version> to the highest version returned by the JSON above.

uv (preferred)

uv run --with "duckdb==<version>" script.py

uv resolves the dependency in an isolated environment per run, so the script is reproducible without a separate venv. This is the preferred path for both ad-hoc scripts and pipelines.

pip (fallback Python)

python3 -m venv .venv
source .venv/bin/activate
pip install "duckdb==<version>"

Use only when uv is not available and the project does not already use uv.

npm (Node.js)

npm install "@duckdb/node-api@<version>"

Use when the host project is already a Node/TypeScript project, or when no Python runtime is available.

DuckDB CLI (last resort)

curl -s https://install.motherduck.com | env -u motherduck_token HOME="$install_home" sh

Pick $install_home as a writable project-local directory (for example ./.duckdb) so the install does not pollute the user's home. The CLI is appropriate for shell-driven ad-hoc exploration and for pipelines that are themselves shell scripts; for any program that already runs Python or Node, prefer the matching client library.

When to Override the Order

• The host project already commits to a language (a pyproject.toml, package.json, or comparable lockfile is present). Follow the project's language.
• The pipeline is a shell script and the workload is a single SQL file. The CLI is appropriate even though it is last in the priority order.
• The user explicitly asks for a specific runtime. Honor the request and skip detection.