MotherDuck

MotherDuck connections use the md: database prefix. Database names may not contain commas.

from sqlalchemy import create_engine

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

Quick start with config

DuckDB settings (threads, memory limits, etc.) can be passed via connect_args:

engine = create_engine(
    "duckdb:///md:my_db",
    connect_args={"config": {"threads": 4, "memory_limit": "1GB"}},
)

Tokens

Set MOTHERDUCK_TOKEN (or motherduck_token) in the environment and it will be picked up automatically when you connect to md: databases. The environment token is not added when the URL or connect_args already carry a credential (token, motherduck_token, motherduck_oauth_token, oauth_token, or slt), and it is never added for local DuckDB files. If you authenticate with an OAuth flow instead, pass motherduck_oauth_token through the URL or connect_args["config"].

export MOTHERDUCK_TOKEN="..."

You can also pass the token in the URL or via connect_args:

engine = create_engine(
    "duckdb:///md:my_db",
    connect_args={"config": {"motherduck_token": "..."}},
)

Multiprocessing (fork)

DuckDB’s Python client is not fork-safe, so multiprocessing children created with fork can fail when opening new connections (commonly observed with MotherDuck or file-backed databases). Use the spawn or forkserver start methods and create engines/connections inside the child process.

Options

Connection-string parameters (instance cache key)

MotherDuck (and DuckDB) cache client instances by database path/connection string. Parameters that affect routing or instance identity must end up in the database string so pooling and caching behave predictably. Pass them as URL query params or via connect_args["config"]; the dialect moves them into the database string for MotherDuck connections.

Parameters that are treated as part of the database string:

• user
• host, region_host, port, tls, grpc_local_subchannel_pool
• session_name (read-scaling affinity)
• attach_mode (workspace or single. single attaches only the named database and starts faster, but needs a database name: md:?attach_mode=single fails to connect)
• access_mode (read_only for read-scaling tokens)
• dbinstance_inactivity_ttl (preferred; motherduck_dbinstance_inactivity_ttl remains supported but is deprecated)
• connection_timeout (bounds how long opening the MotherDuck connection may take. motherduck_connection_timeout is accepted too)
• saas_mode (pgendpoint is supported when you need PG endpoint-compatible routing)
• cache_buster

Interval parameters (connection_timeout, dbinstance_inactivity_ttl) accept DuckDB interval strings such as 30s or 15m, or a datetime.timedelta.

For backward compatibility the dialect also accepts session_hint, motherduck_session_hint, motherduck_session_name, motherduck_attach_mode, motherduck_saas_mode, and cachebust, but it normalizes them to the canonical keys above and emits a DeprecationWarning. The same applies to the motherduck_host, motherduck_region_host, motherduck_port, motherduck_use_tls, and motherduck_grpc_local_subchannel_pool spellings.

Example:

duckdb:///md:my_db?attach_mode=single&access_mode=read_only&session_name=team-a

For local or staging routing, pass the endpoint override the same way:

duckdb:///md:my_db?host=localhost&port=1984&tls=off

If you pass these in connect_args["config"], the dialect will move them into the database string automatically.

Config parameters

Other DuckDB settings can be passed as URL query params or via connect_args["config"]:

• Any DuckDB SET-table config option (for example memory_limit, threads)
• MotherDuck startup auth keys such as motherduck_token, token, motherduck_oauth_token, and oauth_token

Example with a MotherDuck host override / PG endpoint-style routing:

engine = create_engine(
    "duckdb:///md:my_db?host=custom.motherduck.com&port=443&tls=true&saas_mode=pgendpoint"
)

Connection timeout

By default the MotherDuck extension waits for as long as the service asks it to, for example while a dedicated Duckling is still starting. Set connection_timeout to fail fast instead:

from datetime import timedelta

from duckdb_sqlalchemy import create_motherduck_engine

engine = create_motherduck_engine(
    database="md:analytics",
    connection_timeout=timedelta(seconds=30),
)

The timeout covers the token exchange and the initial attach. It is a soft bound, so a connection can still succeed shortly after the deadline. When it expires, connecting raises sqlalchemy.exc.ProgrammingError wrapping a DuckDB InvalidInputException whose message says Timed out connecting to MotherDuck. The setting needs a MotherDuck extension that supports it (the one for DuckDB 1.5.6 does), and older extensions ignore it. The MOTHERDUCK_CONNECTION_TIMEOUT environment variable sets the same timeout without changing the URL.

Server-side temporary tables

motherduck_enable_server_side_temp_tables is a per-connection MotherDuck setting, not a startup parameter. The dialect runs SET motherduck_enable_server_side_temp_tables = 'true' on every new pooled connection when you pass it as a URL parameter, in connect_args["config"], or to create_motherduck_engine:

engine = create_motherduck_engine(
    database="md:analytics",
    motherduck_enable_server_side_temp_tables=True,
)

Recommended defaults for apps/BI

• Use attach_mode=single unless you need workspace-wide attach behavior.
• For read scaling tokens, add access_mode=read_only and a stable session_name.

Helpers

MotherDuck URL builder

Use MotherDuckURL to validate routing/instance-cache parameters. The dialect moves them into the database string when it connects:

from duckdb_sqlalchemy import MotherDuckURL

url = MotherDuckURL(
    database="md:my_db",
    attach_mode="single",
    access_mode="read_only",
    session_name="team-a",
    query={"memory_limit": "1GB"},
)

A token passed to MotherDuckURL stays in URL.query, so str(url) shows it. create_motherduck_engine and create_engine_from_paths move tokens into connect_args["config"] instead, so the engine URL and its repr do not contain them. Prefer those helpers, or the MOTHERDUCK_TOKEN environment variable, whenever the URL may be logged.

Database string helpers

append_query_to_database adds routing params to an md: database string. Keys you pass replace the same keys already in the string. validate_motherduck_database_name raises ValueError for names DuckDB cannot route, such as names that contain a comma, and does nothing for non-MotherDuck databases:

from duckdb_sqlalchemy import (
    append_query_to_database,
    validate_motherduck_database_name,
)

append_query_to_database("md:analytics", {"attach_mode": "single"})
# 'md:analytics?attach_mode=single'

validate_motherduck_database_name("md:analytics")

Explicit read-scaling engine

from duckdb_sqlalchemy import create_motherduck_engine, stable_session_name

engine = create_motherduck_engine(
    database="md:analytics",
    attach_mode="single",
    access_mode="read_only",
    session_name=stable_session_name("user-123", salt="org-1"),
    performance=True,
)

Read scaling session names

Use a stable hash to keep per-user affinity:

from duckdb_sqlalchemy import stable_session_name

session_name = stable_session_name("user-123", salt="org-1")

Read scaling consistency notes

Read scaling routes queries to read replicas. If you need the freshest data, use a non-read-scaling token or route those queries to a separate writer engine. For per-user affinity, keep a stable session_name; to refresh routing, rotate the session_name or recycle the connection/pool.

Performance-first engine helper

create_motherduck_engine(..., performance=True) applies MotherDuck-friendly pooling defaults (QueuePool, pool_pre_ping=True, pool_recycle=23h). Standard create_engine keyword arguments such as pool_size, max_overflow, echo, or connect_args are passed to create_engine; the remaining keyword arguments become URL params. Pool sizing arguments without an explicit poolclass select QueuePool:

from duckdb_sqlalchemy import create_motherduck_engine

engine = create_motherduck_engine(
    database="md:my_db",
    attach_mode="single",
    performance=True,
)

Transient retry (opt-in)

For read-only statements you can opt-in to transient retries:

from sqlalchemy import text

with engine.connect() as conn:
    conn = conn.execution_options(
        duckdb_retry_on_transient=True,
        duckdb_retry_count=2,
        duckdb_retry_backoff=0.5,
    )
    conn.execute(text("select 1"))

An error is transient when it reports HTTP 429, 502, 503, or 504, or says the service is temporarily unavailable or rate limited. Retries only happen for idempotent statements, and only when no earlier statement in the current transaction would be lost: either outside a transaction, or when the failed statement was the first one in it (the dialect rolls back and begins again). Otherwise the original error is raised. Queries that call md_* or ducklake_* functions, *_execute functions such as postgres_execute, nextval, or setval are never retried, and neither are EXPLAIN ANALYZE statements. Only errors raised while the statement executes are retried; an error raised later while rows are fetched from a streaming result is not.

Calls made through the raw driver connection count as earlier transaction work too, so the dialect will not roll them back silently to retry a later read. Opaque SQL functions (query, quack_query, postgres_query, mysql_query, sqlite_query) are excluded because bound SQL can contain side effects that the statement text does not reveal. Arbitrary user functions or views can also have side effects: enable retries only for workloads you know are idempotent.

Multiple client-side instances

To force distinct client instances, rotate across multiple database paths:

from duckdb_sqlalchemy import create_engine_from_paths

engine = create_engine_from_paths(
    ["md:my_db?user=1", "md:my_db?user=2", "md:my_db?user=3"],
    pool_size=3,
    max_overflow=0,
)

Passing pool_size or max_overflow selects QueuePool, and each new pooled connection rotates to the next path.