Skip to content

motherduckdb/agent-skills / plugins/motherduck-skills-copilot/skills/motherduck-connect

motherduck-connect

Verified

Set up or troubleshoot MotherDuck connections, the MotherDuck MCP server in AI clients, authentication, client runtimes, and read scaling.

FileKindSize
SKILL.mdskill md5.1 KB
references/CONNECTION_GUIDE.mdmarkdown14.0 KB
references/MCP_SETUP.mdmarkdown2.7 KB
references/RUNTIME_SELECTION.mdmarkdown4.3 KB

references/CONNECTION_GUIDE.md

View on GitHub
# 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

| Section | Covers |
|---|---|
| Choose a Connection Method | Decision tree: PG endpoint vs native DuckDB vs pg_duckdb vs WASM |
| Operational Defaults | Pooling, read scaling, attach-mode defaults |
| Language Focus | Python vs TypeScript/JavaScript vs CLI |
| Steps 1-3 | Token env var, PG endpoint connection examples, verification |
| Native DuckDB API Alternative | `md:` connections, watermarking, Python/Node/JDBC examples |
| Authentication | Token types, service tokens, token best practices |
| Read Scaling and Session Affinity | `session_name`, `access_mode`, replica freshness |
| Attach Modes | Workspace vs single mode |
| Key Rules / Common Mistakes | PG endpoint constraints and failure patterns |
| PG Endpoint Limitations vs Native DuckDB API | Capability comparison table |

## Choose a Connection Method

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

```text
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 with a device-tested dataset?
            └── 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.

```bash
export MOTHERDUCK_TOKEN="<your_token>"
```

## Step 2: Connect via PG Endpoint

### Connection String

```text
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

| Component | Value | Notes |
|---|---|---|
| Host | `pg.us-east-1-aws.motherduck.com` | Example regional host; verify the target region |
| Port | `5432` | Standard PostgreSQL port |
| User | `postgres` | Fixed value |
| Password | MotherDuck access token | Use env vars or a secret manager |
| Database | MotherDuck database name | For example `my_database` |
| SSL | `sslmode=verify-full` | Required |

### Python (`psycopg2`)

```python
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`)

```javascript
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

```text
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)

```python
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

```sql
SELECT 1 AS connected;
```

```sql
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/3.2.0(harness-<harness>;llm-<llm>)`. If metadata values are missing, use `harness-unknown` and `llm-unknown`.

### Python

```python
import duckdb
import os

USE_CASE_USER_AGENT = "agent-skills/3.2.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`)

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

const userAgent = "agent-skills/3.2.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)

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

Requires the DuckDB JDBC driver, not the PostgreSQL driver.

## Authentication

### Token Types

| Token Type | Use Case | Access Level |
|---|---|---|
| Read/Write | Application backends, data pipelines | Full read and write |
| Read Scaling | High-concurrency read workloads, CFA apps | Read-only, distributed across replicas |

### 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_name` per end user, session, or tenant-facing request path. Connections with the same `session_name` on a read-scaling token go to the same replica. `session_hint` is the deprecated older name; replace it when editing existing code. The Dive embed-session REST API still takes `session_hint`.
- 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

```python
import duckdb
import os

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

### Node.js

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

const db = await DuckDBInstance.create(
  "md:my_database?session_name=user-123&access_mode=read_only"
  + "&dbinstance_inactivity_ttl=300"
  + "&custom_user_agent=agent-skills/3.2.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):

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

Right:

```sql
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:

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

Right:

```python
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:

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

Right:

```python
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_name` on Read Scaling

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

## PG Endpoint Limitations vs Native DuckDB API

| Capability | PG Endpoint | Native DuckDB API |
|---|---|---|
| SQL dialect | DuckDB SQL | DuckDB SQL |
| Local file access | No | Yes |
| Dual execution | No | Yes |
| `SET` configuration statements | Restricted | Full support |
| DDL/DML | Limited | Full support |
| SSL | Required | Optional |
| DuckDB installation required | No | Yes |
| Works with any PG driver | Yes | No |
reverse-engineer-anythingmorluto/reaReverse engineer native, managed, Electron/JavaScript, packaged, firmware, and browser targets with REA, and build agent environments (AgentEnv bundles) of web applications from a live instance or a capture, and of Linux desktop apps with a task from a request. Use shipped-artifact or requested runtime evidence to explain features, compare versions, decompile code, or guide a reconstruction. Skip REA for ordinary source-repository architecture analysis.Stars: 472kUpdated Claude CodeCodexVerifiedInstall
diagnosing-superpowersobra/superpowersUse when a superpowers session went wrong and your human partner wants to know why — repeated work, ignored plans, stumbles, poor results, a skill that didn't fire, "it took too long", "why is it so expensive", "what is it doing" — or wants to build a bug report for the superpowers maintainers, for the current session or a past one identified by id or path, on any harness.Stars: 323kUpdated Claude CodeCodexVerifiedInstall
dispatching-parallel-agentsobra/superpowersUse when facing 2+ independent tasks that can be worked on without shared state or sequential dependenciesStars: 323kUpdated Claude CodeCodexVerifiedInstall
executing-plansobra/superpowersUse when executing an implementation plan in the current session as the implementer yourself — your human partner chose inline execution, or no subagent tool is availableStars: 323kUpdated Claude CodeCodexVerifiedInstall