# SQLAlchemy with DuckDB and MotherDuck
> Connect to MotherDuck from SQLAlchemy using either the PostgreSQL connector through MotherDuck's Postgres endpoint or the DuckDB SQLAlchemy driver.
[SQLAlchemy](https://www.sqlalchemy.org/) is a Python SQL toolkit and Object-Relational Mapping (ORM) system that supports a wide range of database dialects. Many business intelligence tools support SQLAlchemy out of the box.

You can connect SQLAlchemy to MotherDuck through two paths:

- **Recommended:** [MotherDuck's Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/) with SQLAlchemy's built-in PostgreSQL dialect and the `psycopg` driver. This path uses the standard PostgreSQL wire protocol and doesn't require DuckDB in your application environment.
- **DuckDB SQLAlchemy driver:** the [`duckdb-engine`](https://github.com/Mause/duckdb_engine) dialect, which connects through a DuckDB connection string. Use this path when you need DuckDB-specific SQLAlchemy behavior or local DuckDB features such as local-file access, local attachments, hybrid execution, or DuckDB extension management.

## Why use the Postgres endpoint

The DuckDB SQLAlchemy driver can connect to MotherDuck through a DuckDB connection string, but most SQLAlchemy applications should use the Postgres endpoint instead:

- **Standard connector support**: Use SQLAlchemy's built-in PostgreSQL dialect with the `psycopg` driver.
- **No DuckDB dependency**: Connect from serverless runtimes, containers, and application servers without bundling DuckDB.
- **Production connection management**: Use SQLAlchemy pooling with long-lived Postgres-compatible connections.
- **Consistent integration path**: Share the same connection parameters used by other Postgres-compatible tools.

Use the DuckDB SQLAlchemy driver only when your application needs local DuckDB features such as local-file access, local attachments, hybrid execution, or DuckDB extension management.

## Before you start

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

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

You also need your MotherDuck Postgres host. You can find it in [MotherDuck Postgres settings](https://app.motherduck.com/settings/postgres). The examples below use `pg.us-east-1-aws.motherduck.com`; use the host shown for your account.

## Install SQLAlchemy and psycopg

Install SQLAlchemy and the PostgreSQL connector:

```bash
pip install --upgrade sqlalchemy psycopg
```

## Connect with SQLAlchemy

Create a SQLAlchemy engine with the PostgreSQL dialect and the `psycopg` driver:

```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": "verify-full",
        "sslrootcert": "system",
    },
    pool_pre_ping=True,
)

with engine.connect() as conn:
    result = conn.execute(text("SHOW DATABASES"))
    for row in result:
        print(row)
```

Using `md:` as the database name connects to your default database and uses workspace attach mode, which makes the databases in your MotherDuck workspace available from the session.

To connect to a specific database, replace `md:` with the database name:

```python
engine = create_engine(
    "postgresql+psycopg://postgres@pg.us-east-1-aws.motherduck.com:5432/sample_data",
    connect_args={
        "password": os.environ["MOTHERDUCK_TOKEN"],
        "sslmode": "verify-full",
        "sslrootcert": "system",
    },
)
```

## Query MotherDuck

Execute SQL with SQLAlchemy's `text()` construct:

```python
from sqlalchemy import text

with engine.connect() as conn:
    result = conn.execute(
        text(
            """
            SELECT title, score
            FROM sample_data.hn.hacker_news
            WHERE type = 'story'
            ORDER BY score DESC
            LIMIT 5
            """
        )
    )

    for row in result:
        print(row.title, row.score)
```

The Postgres endpoint is a PostgreSQL-wire interface to MotherDuck. You write **DuckDB SQL**, not PostgreSQL SQL.

## Loading data

For loading data through SQLAlchemy and the Postgres endpoint, prefer server-side reads from remote storage:

- Use `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 remote file reads.

```python
from sqlalchemy import text

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

Use a DuckDB client path instead for local-file ingestion, `COPY FROM STDIN`, `MD_RUN = LOCAL`, or high-volume inserts from application memory.

## Operational notes

- **Use SSL**: The Postgres endpoint requires encrypted connections. `sslmode=verify-full` with `sslrootcert=system` verifies the server certificate when supported by your client.
- **Keep tokens out of code**: Pass your MotherDuck access token through an environment variable or secret manager.
- **Prefer long-lived connections**: Configure SQLAlchemy pooling for application workloads instead of opening a new connection per query.
- **Avoid unsupported Postgres features**: PostgreSQL-specific functions, indexes, sequences, stored procedures, and temporary tables are not supported.

## Connecting with the DuckDB SQLAlchemy driver

If your application needs DuckDB-specific SQLAlchemy behavior or local DuckDB features, use the [DuckDB SQLAlchemy driver](https://github.com/Mause/duckdb_engine) and the DuckDB SQLAlchemy URI style instead.

### Install the DuckDB SQLAlchemy driver

```bash
pip install --upgrade duckdb-engine
```

### Connect to a local DuckDB database

Access a local DuckDB database with the SQLAlchemy URI:

```bash
duckdb:///path/to/file.db
```

### Connect to MotherDuck

The general pattern for the SQLAlchemy URI to access a MotherDuck database is:

```bash
duckdb:///md:<my_database>?motherduck_token=<my_token>
```

The database name `<my_database>` in the connection string is optional. Omitting it lets you query multiple databases with one connection to MotherDuck.

You can authenticate in several ways:

**1. Web login**

If no token is available, the process directs you to a web login for authentication, which lets you obtain a token.

```python
from sqlalchemy import create_engine, text

eng = create_engine("duckdb:///md:my_db")

with eng.connect() as conn:
    result = conn.execute(text("SHOW DATABASES"))
    for row in result:
        print(row)
```

When you run the above, you'll see something like this to authenticate:

![motherduck login](../img/sqlalchemy_auth.png)

**2. `MOTHERDUCK_TOKEN` environment variable**

```python
from sqlalchemy import create_engine, text

eng = create_engine("duckdb:///md:my_db")

with eng.connect() as conn:
    result = conn.execute(text("SHOW DATABASES"))
    for row in result:
        print(row)
```

**3. Configuration dictionary**

```python
from sqlalchemy import create_engine, text

config = {}
token = 'asdfwerasdf'  # Fill in your token
config["motherduck_token"] = token
eng = create_engine(
    "duckdb:///md:my_db",
    connect_args={'config': config}
)

with eng.connect() as conn:
    result = conn.execute(text("SHOW DATABASES"))
    for row in result:
        print(row)
```

**4. Token as a connection string parameter**

```python
from sqlalchemy import create_engine, text

token = 'asdfwerasdf'  # Fill in your token
eng = create_engine(f"duckdb:///md:my_db?motherduck_token={token}")

with eng.connect() as conn:
    result = conn.execute(text("SHOW DATABASES"))
    for row in result:
        print(row)
```

:::info
The DuckDB Python API has a `.sql()` method on the connection API, but SQLAlchemy doesn't. Both share the `.execute()` function and concept. For more, see the [SQLAlchemy connection documentation](https://docs.sqlalchemy.org/en/20/core/connections.html#sqlalchemy.engine.Connection).
:::

## Related content

- **Connect through the Postgres endpoint**: [Postgres endpoint guide](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/)
- **Review connection parameters**: [Postgres Endpoint reference](/sql-reference/postgres-endpoint/)
- **Connect from Python**: [Python through the Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/python/)
- **Choose an interface**: [Client APIs](/getting-started/interfaces/client-apis/)


---

## Docs feedback

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

Payload:

```json
{
  "page_path": "/integrations/language-apis-and-drivers/python/sqlalchemy/",
  "page_title": "SQLAlchemy with DuckDB and MotherDuck",
  "text": "<the user's feedback, max 2000 characters>",
  "source": "<optional identifier for your interface, for example 'claude.ai' or 'chatgpt'>"
}
```

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