Enable saas_mode parameter when connecting to motherduck

[pietro-fantini]

Describe the use case

I want to connect to a motherduck db, but I don't want that motherduck interacts with my client. They enable this by putting the saas_mode=true parameter in the connection uri. From their documentation:

conn = duckdb.connect("md:[<database_name>]?[motherduck_token=<motherduck_token>]&saas_mode=true")

Example Use

Currently sqlalchemy doesn't support to pass this parameter. When I try to create the engine with:

sqlalchemy.create_engine(url="duckdb:///md:<database>?motherduck_token=<token>&saas_mode=true")

It raises the error:

ProgrammingError: (duckdb.duckdb.InvalidInputException) Invalid Input Error: The following options were not recognized: saas_mode
(Background on this error at: https://sqlalche.me/e/20/f405)

[Mause]

This should in theory be already supported thanks to https://github.com/Mause/duckdb_engine/pull/1204, weird

[Mause]

Can you drop some versions? The issue template should have forced you to include it

[pietro-fantini]

Hello Mause, thanks for replying! Versions:

"duckdb>=1.3.2,<1.4.0",
"duckdb-engine>=0.17,<0.18",
"pandas>=2.2,<2.4",
"sqlalchemy>=2.0,<2.1",
"tzdata>=2025.2,<2025.3",

Code snippet:

import pandas as pd
import sqlalchemy as sqla
from sqlalchemy import create_engine, exc


def read(uri, query) -> pd.DataFrame:
    try:
        engine = sqla.create_engine(uri)
        with engine.connect() as conn:
            parsed_query = sqla.text(query)
            df = pd.read_sql_query(parsed_query, con=conn, dtype_backend="pyarrow")
    except exc.SQLAlchemyError as err:
        raise err
    except Exception as err:
        raise err
    return df

db = ""
token = ""

uri = f"duckdb:///md:{db}?motherduck_token={token}&saas_mode=true"

query = ""

df = read(uri, query)

Stack trace:

sqlalchemy/engine/base.py:143, in Connection.__init__(self, engine, connection, _has_events, _allow_revalidate, _allow_autobegin)
    142 try:
--> 143     self._dbapi_connection = engine.raw_connection()
    144 except dialect.loaded_dbapi.Error as err:

sqlalchemy/engine/base.py:3301, in Engine.raw_connection(self)
   3280 """Return a "raw" DBAPI connection from the connection pool.
   3281 
   3282 The returned object is a proxied version of the DBAPI
   (...)   3299 
   3300 """
-> 3301 return self.pool.connect()

sqlalchemy/pool/base.py:447, in Pool.connect(self)
    440 """Return a DBAPI connection from the pool.
    441 
    442 The connection is instrumented such that when its
   (...)    445 
    446 """
--> 447 return _ConnectionFairy._checkout(self)

sqlalchemy/pool/base.py:1264, in _ConnectionFairy._checkout(cls, pool, threadconns, fairy)
   1263 if not fairy:
...
    303 for extension in preload_extensions:
    304     conn.execute(f"LOAD {extension}")

ProgrammingError: (duckdb.duckdb.InvalidInputException) Invalid Input Error: The following options were not recognized: saas_mode
(Background on this error at: https://sqlalche.me/e/20/f405)