Do AI Agents Need a Semantic Layer?Livestream August 26

Skip to main content

SQLAlchemy with DuckDB and MotherDuck

SQLAlchemy is a SQL toolkit and Object-Relational Mapping (ORM) system for Python, providing full support for SQL expression language constructs and various database dialects. A lot of Business Intelligence tools supports SQLAlchemy out of the box.

SQLAlchemy 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 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 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, Dual 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, Dual Execution, or DuckDB extension management.

Before you start​

You need a MotherDuck access token. Store it in an environment variable:

export MOTHERDUCK_TOKEN="your_token_here"

You also need your MotherDuck Postgres host. You can find it in MotherDuck Postgres settings. 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:

pip install --upgrade sqlalchemy psycopg

Connect with SQLAlchemy​

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

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:

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:

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.
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 and the DuckDB SQLAlchemy URI style instead.

Install the DuckDB SQLAlchemy driver​

pip install --upgrade duckdb-engine

Connect to a local DuckDB database​

Access a local DuckDB database with the SQLAlchemy URI:

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

Connect to MotherDuck​

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

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.

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

2. MOTHERDUCK_TOKEN environment variable

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

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

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.

Related content​