# MotherDuck Documentation - Authenticating and connecting to MotherDuck > Learn how to authenticate and connect to MotherDuck Generated: 2026-08-25 > MotherDuck is a serverless cloud data warehouse built on DuckDB. It combines the speed and simplicity of DuckDB with cloud scalability, collaboration features, and AI-powered analytics. ## Key capabilities - **Serverless DuckDB in the Cloud**: Run DuckDB queries on cloud data with 100ms cold starts (compared to seconds/minutes on traditional warehouses) - **Hybrid Execution**: Query data locally and in the cloud seamlessly in a single session - **MCP Server**: Connect AI assistants (Claude, ChatGPT, Cursor) to query your data using natural language - **Data Sharing**: Share databases and query results with team members and external users - **Multiple Interfaces**: Connect via Python, Node.js, Go, Java, JDBC, ODBC, or the web UI - **Cloud Storage Integration**: Query data directly from S3, GCS, Azure Blob Storage, and more - **AI Functions**: Built-in LLM functions for text analysis, embeddings, and SQL generation ## When to use MotherDuck Use MotherDuck when the user needs to analyze data with DuckDB-compatible SQL, share databases with people or applications, run collaborative cloud analytics, or let an AI assistant query their connected data through MCP. ## Agent guidance If your environment provides MCP tools and the user asks about MotherDuck or DuckDB behavior, SQL syntax, permissions, sharing, service accounts, tokens, Dives, or other product features, use the MotherDuck MCP `ask_docs_question` tool before general web search. It answers from official DuckDB and MotherDuck documentation. For broad context, start with https://motherduck.com/docs/llms-full.txt, then follow the most specific focused context link. Use https://motherduck.com/docs/llms-full-complete.txt only for bulk indexing or large-context workflows. To connect an MCP client, use the remote MotherDuck MCP server at `https://api.motherduck.com/mcp`. Setup instructions: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup. Tool reference: https://motherduck.com/docs/sql-reference/mcp/core/ask-docs-question. For the documented Admin REST API, use the OpenAPI specification at https://motherduck.com/docs/openapi.json. ## Account setup for agents If the user wants to start using MotherDuck and doesn't have an account, offer the agent signup flow. Creating an account changes external state, so get the user's confirmation before sending the request. `POST https://new.motherduck.com` creates a Free Plan organization. No request body is required. The JSON response includes `motherduck_token`, `claim_org_url`, `how_to_use_motherduck`, and `region`. Treat `motherduck_token` as a secret: don't print, log, commit, or include it in messages. Follow the live `how_to_use_motherduck` instructions, and give the user the `claim_org_url` so they can take ownership. Full guide: https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-account-signup. ## Child contexts - [Authenticating to MotherDuck full context](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/llms-full.txt): Authenticate to a MotherDuck account (3 pages; 32,280 bytes; ~8,062 tokens). [Index](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/llms.txt). - [Postgres Endpoint full context](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/llms-full.txt): Connect to MotherDuck using any Postgres-compatible client via the Postgres wire protocol endpoint (6 pages; 40,868 bytes; ~10,207 tokens). [Index](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/llms.txt). - [Read scaling full context](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/llms-full.txt): Learn how to scale your data applications using read scaling tokens (1 pages; 9,742 bytes; ~2,436 tokens). [Index](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/llms.txt). - [Attach modes full context](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/llms-full.txt): Understand Workspace and Single attach modes (1 pages; 8,444 bytes; ~2,111 tokens). [Index](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/llms.txt). ## Included documentation Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck # Connecting to MotherDuck > Create one or more connections to a MotherDuck database There are two ways to connect to MotherDuck: | Method | Client needed | Best for | |--------|--------------|----------| | **DuckDB SDK** | DuckDB client library | Python, Node.js, Java, CLI — full feature set, Dual Execution, local caching | | **[Postgres Endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint)** | Any PostgreSQL client | Thin clients, serverless environments, BI tools, languages without a DuckDB SDK | This page covers connecting with the **DuckDB SDK**. For the Postgres endpoint, see [Postgres Endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint). You can customize a connection by appending parameters such as `attach_mode` and `session_name` to the `md:` connection string. For the full list, see [Connection string parameters](/sql-reference/connection-string-parameters). ## Connecting with the DuckDB SDK A single DuckDB connection executes one query at a time, aiming to maximize the performance of that query, making reuse of a single connection is both simple and performant. We recommend starting with the simplest way of connecting to MotherDuck and running queries, and if that does not meet your requirements, to explore the advanced use-cases described in subsequent sections. ## Create a connection ![Image](useBaseUrl('/img/key-tasks/authenticating-and-connecting-to-motherduck/one-connection.png')) The below code snippets show how to create a connection to a MotherDuck database from the CLI, Python, JDBC, and Node.js language APIs. :::info For security reasons, it's generally recommended to use environment variables to store your MotherDuck token rather than hardcoding it in your application. ::: :::tip The `INSERT INTO` statements below are for illustration only. For loading real data, do not insert rows one at a time — use bulk methods like `INSERT INTO ... SELECT` from files, `COPY`, or DataFrame-based approaches. See [Loading data into MotherDuck](/key-tasks/loading-data-into-motherduck/loading-data-into-motherduck.mdx) for recommended approaches. ::: ### Python To connect to your MotherDuck database, use `duckdb.connect("md:my_database_name")`. This will return a `DuckDBPyConnection` object that you can use to interact with your database. There are two ways to provide your access token in Python to authenticate your user session. ### Within a config dictionary ```python import duckdb # Create connection to your default database conn = duckdb.connect("md:my_db", config={"motherduck_token" :}) # Optionally, import your token from a .env file # Run query conn.sql("CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)") conn.sql("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)") res = conn.sql("SELECT * FROM items") # Close the connection conn.close() ``` ### Included in the connection string ```python import duckdb # Create connection to your default database conn = duckdb.connect(f"md:my_db?motherduck_token={}") # Optionally, import your token directly from a .env file # Run query conn.sql("CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)") conn.sql("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)") res = conn.sql("SELECT * FROM items") # Close the connection conn.close() ``` ### JDBC To connect to your MotherDuck database, you can create a `Connection` by using the `"jdbc:duckdb:md:databaseName"` connection string format. For authentication, you need to provide a MotherDuck token. There are two ways to provide the token: ### As a connection property ```java import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import java.sql.ResultSet; import java.util.Properties; // Create properties with your MotherDuck token Properties props = new Properties(); props.setProperty("motherduck_token", ""); // Create connection to your database try (Connection conn = DriverManager.getConnection("jdbc:duckdb:md:my_db", props); Statement stmt = conn.createStatement()) { stmt.executeUpdate("CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)"); stmt.executeUpdate("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)"); try (ResultSet rs = stmt.executeQuery("SELECT * FROM items")) { while (rs.next()) { System.out.println("Item: " + rs.getString(1) + " costs " + rs.getInt(3)); } } } ``` ### As part of the connection string ```java // Create connection with token in the connection string try (Connection conn = DriverManager.getConnection("jdbc:duckdb:md:my_db?motherduck_token="); Statement stmt = conn.createStatement()) { stmt.executeUpdate("CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)"); stmt.executeUpdate("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)"); try (ResultSet rs = stmt.executeQuery("SELECT * FROM items")) { while (rs.next()) { System.out.println("Item: " + rs.getString(1) + " costs " + rs.getInt(3)); } } } ``` :::info If an environment variable named `motherduck_token` is set, it will be used automatically. ::: ### Node.js To connect to your MotherDuck database, you can create a `DuckDBInstance` with the `'md:databaseName'` connection string format. For authentication, you need to provide a MotherDuck token. There are two ways to provide the token: ### Within a config dictionary ```javascript import { DuckDBInstance } from '@duckdb/node-api'; // Create connection to your default database const instance = await DuckDBInstance.create('md:my_db', { motherduck_token: '', }); const conn = await instance.connect(); // Run queries await conn.run('CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)'); await conn.run("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)"); const result = await conn.runAndReadAll('SELECT * FROM items'); console.table(result.getRowObjects()); ``` ### Included in the connection string ```javascript import { DuckDBInstance } from '@duckdb/node-api'; // Create connection to your default database const instance = await DuckDBInstance.create('md:my_db?motherduck_token='); const conn = await instance.connect(); // Run queries await conn.run('CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER)'); await conn.run("INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2)"); const result = await conn.runAndReadAll('SELECT * FROM items'); console.table(result.getRowObjects()); ``` :::info If an environment variable named `motherduck_token` is set, it's used automatically. ::: ### CLI To connect to your MotherDuck database, run `duckdb md:`. ```shell duckdb "md:my_db" ``` Now, you will enter the DuckDB interactive terminal to interact with your database. ```sql D CREATE TABLE items (item VARCHAR, value DECIMAL(10, 2), count INTEGER); D INSERT INTO items VALUES ('jeans', 20.0, 1), ('hammer', 42.2, 2); D SELECT * FROM items; ``` ## Session names The `session_name` connection string parameter lets you give your session a name. You can set it in the connection string (`md:my_db?session_name=my_label`) or as a DuckDB setting before connecting to MotherDuck (`SET motherduck_session_name='my_label'`). :::note This parameter used to be called `session_hint`, which still works as an alias for backwards compatibility. Clients older than `v1.5.2` still need to use `session_hint`, but client versions equal or greater than `v1.5.2` should use `session_name`. ::: ### Read scaling with session names If you are planning on multiple end users connecting with a [Read Scaling Token](/documentation/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/read-scaling.mdx), ensure each user can get a dedicated backend (up to the maximum configured pool size) by passing a `session_name` in the connection string. Session names ensure that all the queries from the same end user are routed to the same backend duckling, even if they originate from different services/servers. This allows for optimal caching and resource allocation for each specific user's needs. After establishing the connection, it can be used the same way as any DuckDB/MotherDuck connection -- to run queries, and then either be closed explicitly or go out of scope, as in the examples above. ### Annotating queries with session names The `session_name` value appears in the `SESSION_NAME` column of [query history](/sql-reference/motherduck-sql-reference/md_information_schema/query_history/), making it easy to identify and group queries. This works for both read scaling and read/write connections. ### Python ```python import duckdb # Create a connection and allocate a stable backend for user123. con = duckdb.connect( "md:my_db?session_name=user123", config = {'motherduck_token': ''} ) ``` ### JDBC ```java import java.sql.Connection; import java.sql.DriverManager; import java.sql.Statement; import java.sql.ResultSet; import java.util.Properties; // Create properties with your MotherDuck token Properties props = new Properties(); props.setProperty("motherduck_token", ""); // Create a connection and allocate a stable backend for user123. try (Connection conn = DriverManager.getConnection("jdbc:duckdb:md:my_db?session_name=user123", props)) { // ... } ``` ### Node.js ```javascript import { DuckDBInstance } from '@duckdb/node-api'; // Create a connection and allocate a stable backend for user123. const instance = await DuckDBInstance.create( 'md:my_db?session_name=user123', { motherduck_token: '' } ); // ... ``` ## Multiple connections and the database instance cache DuckDB clients in Python, Go, R, JDBC, and ODBC prevent redundant reinitialization by keeping instances of database-global context cached by the database path. This usually makes external connection pools unnecessary for the DuckDB client. If your application uses connection pooling libraries, they may not be aware of this behavior. In that case, consider using the [Postgres Endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint) as a drop-in replacement for the DuckDB client. When connecting to MotherDuck, the instance is cached for an additional 15 minutes after the last connection is closed (see [Setting Custom Database Instance Cache TTL](#setting-custom-database-instance-cache-time-ttl) for how to override this value). For an application that creates and closes connections frequently, this could provide a significant speedup for connection creation, as the same catalog data can be reused across connections. This means that only the first of multiple connections to the same database will take the time to load the MotherDuck extension, verify its signature, and fetch the catalog metadata. ### Python ```python con1 = duckdb.connect("md:my_db") // MotherDuck catalog fetched con2 = duckdb.connect("md:my_db") // MotherDuck catalog reused ``` ### Java ```java // Create properties with your MotherDuck token Properties props = new Properties(); props.setProperty("motherduck_token", ""); try (var con1 = DriverManager.getConnection("jdbc:duckdb:md:my_db", props); // MotherDuck catalog fetched var con2 = DriverManager.getConnection("jdbc:duckdb:md:my_db", props); // MotherDuck catalog reused ) { // ... } ``` ### Node.js :::warning[Node.js does not cache instances automatically] Unlike some other clients, the Node.js client (`@duckdb/node-api`) does **not** cache database instances by default. Each call to `DuckDBInstance.create()` creates a new instance, which means the MotherDuck extension is reloaded and the catalog metadata is re-fetched every time. Depending on the size of your catalog this can cause significant connection delays. To avoid this, use `DuckDBInstance.fromCache()` or create a `DuckDBInstanceCache` as shown below. ::: In Node.js, you must explicitly opt in to instance caching by using `DuckDBInstance.fromCache()` instead of `DuckDBInstance.create()`. This uses a built-in default cache to ensure only one instance is created per database path, avoiding reloading the MotherDuck extension and re-fetching catalog metadata on subsequent connections. ```javascript import { DuckDBInstance } from '@duckdb/node-api'; // First call creates the instance and fetches the MotherDuck catalog const instance = await DuckDBInstance.fromCache('md:my_db', { motherduck_token: '', }); const connection1 = await instance.connect(); // Second call reuses the cached instance — no reinitialization needed const instance2 = await DuckDBInstance.fromCache('md:my_db'); const connection2 = await instance2.connect(); ``` For more control, you can create your own `DuckDBInstanceCache`: ```javascript import { DuckDBInstanceCache } from '@duckdb/node-api'; const cache = new DuckDBInstanceCache(); // Retrieves an existing instance or creates one if it doesn't exist const instance = await cache.getOrCreateInstance('md:my_db'); const connection = await instance.connect(); ``` ## Setting custom database instance cache time (TTL) By default, connections to MotherDuck established through the database instance caching supporting DuckDB APIs will reuse the same database instance for 15 minutes after the last connection is closed. In some cases, you may want to make that period longer (to avoid the redundant reinitialization) or shorter (to connect to the same database with a different configuration). The database TTL value can be set either at the initial connection time, or by using the `SET` command at any point. Any valid [DuckDB Instant part specifiers](https://duckdb.org/docs/stable/sql/functions/datepart.html#part-specifiers-usable-as-date-part-specifiers-and-in-intervals) can be used for the TTL value, for example '5s', '3m', or '1h'. :::note The examples below assume you have configured your MotherDuck token using one of the authentication methods described in the [Create a connection](#create-a-connection) section above. ::: ### Python ```python con = duckdb.connect("md:my_db?dbinstance_inactivity_ttl=1h") con.close() # different database connection string (without `?dbinstance_inactivity_ttl=1h`), no instance cached; TTL is 15 minutes (default) con2 = duckdb.connect("md:my_db") # allow the database instance to expire immediately con2.execute("SET motherduck_dbinstance_inactivity_ttl='0s'") # the database instance can only expire after the last connection is closed con2.close() # new database instance with a new TTL (the 15 minute default) con3 = duckdb.connect("md:my_db") con3.close() # the last TTL for this database was 15 minutes; the cached database instance will be reused con4 = duckdb.connect("md:my_db") ``` ### Java The TTL can be set either through the connection string or through Properties. However, be careful when using Properties as the database instance cache is keyed by the connection string. This means that if you change the TTL in Properties between connections, you'll get an error as it's trying to connect to the same database with different configurations. Here's an example that will fail: ```java Properties props = new Properties(); props.setProperty("motherduck_dbinstance_inactivity_ttl", "2m"); // First connection works fine try (var con = DriverManager.getConnection("jdbc:duckdb:md:my_db", props)) { // TTL is set to 2m } // Changing TTL in properties will fail props.setProperty("motherduck_dbinstance_inactivity_ttl", "5m"); try (var con = DriverManager.getConnection("jdbc:duckdb:md:my_db", props)) { // This will throw: "Can't open a connection to same database file // with a different configuration than existing connections" } ``` For this reason, it's generally safer to set the TTL through the connection string: ```java // Set TTL through connection string try (var con = DriverManager.getConnection("jdbc:duckdb:md:my_db?dbinstance_inactivity_ttl=1h")) { // TTL is set to 1h } // Different TTL creates a new instance try (var con = DriverManager.getConnection("jdbc:duckdb:md:my_db?dbinstance_inactivity_ttl=30m")) { // This works - creates a new instance with 30m TTL } // Can also set TTL using SQL try (var con = DriverManager.getConnection("jdbc:duckdb:md:my_db"); var st = con.createStatement()) { // allow the database instance to expire immediately st.executeUpdate("SET motherduck_dbinstance_inactivity_ttl='0s'"); } ``` :::note When using Properties, you must include the `motherduck_` prefix for the TTL property name (i.e., `motherduck_dbinstance_inactivity_ttl`). This prefix is only optional when passing the TTL through the connection string. ::: ### NodeJS ```javascript import { DuckDBInstance } from '@duckdb/node-api'; // Set TTL to 1 hour through the connection string const instance = await DuckDBInstance.fromCache('md:my_db?dbinstance_inactivity_ttl=1h'); const conn = await instance.connect(); // Or set the TTL using SQL after connecting await conn.run("SET motherduck_dbinstance_inactivity_ttl='30m'"); // Allow the database instance to expire immediately after the connection closes await conn.run("SET motherduck_dbinstance_inactivity_ttl='0s'"); ``` ## Connect to multiple databases If you need to connect to MotherDuck and run one or more queries in succession on the same account, you can use a [single database connection](#create-a-connection). If you want to connect to another database in the same account, you can either [reuse the same connection](#example-1-reuse-the-same-duckdb-connection), or [create copies](#example-2-create-copies-of-the-initial-duckdb-connection) of the connection. ### Python If you need to connect to multiple databases, you can either directly reuse the same `DuckDBPyConnection` instance, or create copies of the connection using the `.cursor()` method. :::note `FROM ` is a shorthand version of `SELECT * FROM
`. ::: ### Example 1: Reuse the same DuckDB connection ![Image](useBaseUrl('/img/key-tasks/authenticating-and-connecting-to-motherduck/one-connection.png')) To connect to different databases in the same MotherDuck account, you can use the same connection object and fully qualify the names of the tables in your query. ```python conn = duckdb.connect("md:my_db") res1 = conn.sql("FROM my_db1.main.tbl") res2 = conn.sql("FROM my_db2.main.tbl") res3 = conn.sql("FROM my_db3.main.tbl") conn.close() ``` ### Example 2: Create copies of the initial DuckDB connection ![Image](useBaseUrl('/img/key-tasks/authenticating-and-connecting-to-motherduck/one-connection-threads.png')) `conn.cursor()` returns a copy of the DuckDB connection, with a reference to the existing DuckDB database instance. Closing the original connection also closes all associated cursors. ```python conn = duckdb.connect("md:my_db") cur1 = conn.cursor() cur2 = conn.cursor() cur3 = conn.cursor() cur1.sql("USE my_db1") cur2.sql("USE my_db2") cur3.sql("USE my_db3") res = [] for cur in [cur1, cur2, cur3]: res.append(cur.sql("SELECT * FROM tbl")) # This closes the original DuckDB connection and all cursors conn.close() ``` :::note `duckdb.connect(path)` creates and caches a DuckDB instance. Subsequent calls with the same path reuse this instance. New connections to the same instance are independent, similar to `conn.cursor()`, but closing one doesn't affect others. To create a new instance instead of using the cached one, make the path unique (e.g., `md:my_db?user=`). ::: ### Example 3: Create multiple connections ![Image](useBaseUrl('/img/key-tasks/authenticating-and-connecting-to-motherduck/multiple-connections.png')) You can also create multiple connections to the same MotherDuck account using different DuckDB instances. However, keep in mind that each connection takes time to establish, and if connection times are an important factor for your application, it might be beneficial to consider [Example 1](#example-1-reuse-the-same-duckdb-connection) or [Example 2](#example-2-create-copies-of-the-initial-duckdb-connection). ### JDBC If you need to connect to multiple databases, you typically won't need to create multiple DuckDB instances. You can either directly reuse the same `DuckDBConnection` instance, or create copies of the connection using the `.duplicate()` method. ```java // Create connection with your MotherDuck token Properties props = new Properties(); props.setProperty("motherduck_token", ""); try (DuckDBConnection duckdbConn = (DuckDBConnection) DriverManager.getConnection("jdbc:duckdb:md:my_db", props)) { Connection conn1 = duckdbConn.duplicate(); Connection conn2 = duckdbConn.duplicate(); Connection conn3 = duckdbConn.duplicate(); // ... } ``` ### Node.js If you need to connect to multiple databases, you can re-use the same `DuckDBInstance` and connection. Use `fromCache` to ensure the instance is reused efficiently. ```javascript import { DuckDBInstance } from '@duckdb/node-api'; const instance = await DuckDBInstance.fromCache('md:', { motherduck_token: '', }); const conn = await instance.connect(); const result1 = await conn.runAndReadAll('FROM my_db1.main.tbl'); const result2 = await conn.runAndReadAll('FROM my_db2.main.tbl'); ``` --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/multithreading-and-parallelism # Multithreading and parallelism > Run concurrent queries against MotherDuck, and learn when to use Read Scaling or the Postgres endpoint instead of managing connection pools. Most applications don't need to manage threads or connection pools to get good concurrency from MotherDuck. The DuckDB client and MotherDuck's architecture cover the cases that connection pooling traditionally solved. This page explains what to reach for instead. ## You probably don't need a connection pool DuckDB clients in Python, Go, R, JDBC, and ODBC keep a single database instance cached by database path, and minting connections off that instance is cheap. Because of this, external connection pools are usually unnecessary, and they can work against the instance cache rather than with it. Within a single process, share one connection and create lightweight copies per thread instead of opening a new instance for every query. In Python, a single connection object [is not thread-safe](https://duckdb.org/docs/api/python/overview.html#using-connections-in-parallel-python-programs), so call `.cursor()` to get a copy for each thread. See [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck.md#multiple-connections-and-the-database-instance-cache) for how the instance cache works and how to reuse connections. For background on when concurrency improves performance, see the DuckDB documentation on [concurrency](https://duckdb.org/docs/stable/connect/concurrency.html) and [parallelism](https://duckdb.org/docs/guides/performance/how_to_tune_workloads.html#parallelism-multi-core-processing). ## Run many concurrent read-only queries To serve a high volume of concurrent read-only queries against the same database, use a [Read Scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) token. Read scaling replicas handle the fan-out, so you don't have to coordinate a pool of connections yourself. ## Use the Postgres endpoint for connection pooling If your application relies on a connection-pooling library, or you need to manage the connection lifecycle beyond a single DuckDB instance, connect through the [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint). It speaks the PostgreSQL wire protocol, so it works as a drop-in replacement with standard pooling libraries. --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-and-connecting-to-motherduck # Authenticating and connecting to MotherDuck > Learn how to authenticate and connect to MotherDuck These pages explain how to connect to MotherDuck using the CLI, Python, JDBC and NodeJS. First, you need to [authenticate to MotherDuck](./authenticating-to-motherduck) by [manual authentication](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#manual-authentication) via the Web UI, or automatic authentication via an [access token](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#authentication-using-an-access-token). Organizations on Business or Enterprise plans can also configure [Single Sign-On (SSO)](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/sso-setup/) with their identity provider. To connect to a MotherDuck database, you can [create a connection](/docs/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/). ## Included pages - [Authenticating to MotherDuck](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck): Authenticate to a MotherDuck account - [Connecting to MotherDuck](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck): Create one or more connections to a MotherDuck database - [Connect via the Postgres endpoint](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint): Connect to MotherDuck using any Postgres-compatible client via the Postgres wire protocol endpoint - [Read Scaling](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling): Learn how to scale your data applications using read scaling tokens - [Attach Modes](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes): Understand Workspace and Single attach modes - [Multithreading and parallelism](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/multithreading-and-parallelism): Run concurrent queries against MotherDuck, and learn when to use Read Scaling or the Postgres endpoint instead of managing connection pools. --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/authenticating-to-motherduck # Authenticating to MotherDuck > Authenticate to a MotherDuck account MotherDuck supports the following authentication methods: - **Manual authentication**, typically used by the MotherDuck UI (Google, GitHub, or email and password) - **Access token authentication**, more convenient for Python, CLI, or other clients - **[Single Sign-On (SSO)](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/sso-setup/)**, for organizations that want to authenticate through their corporate identity provider (available on Business and Enterprise plans) ## Manual authentication MotherDuck UI authenticates using several methods: - Google - Github - Username and password You can leverage multiple modes of authentication in your account. For example, you can authenticate both through Google and with a username and password as you see fit. To authenticate in CLI or Python, you will be redirected to an authentication web page. This happens every session. To avoid having to re-authenticate, you can save your access token, as described in the [Authenticate With an Access Token](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#authentication-using-an-access-token) section. ## Authentication using an access token If you are using Python or CLI and don't want to authenticate every session, you can securely save your credentials locally. ### Creating an access token To create an access token: - Go to the [MotherDuck UI](https://app.motherduck.com) - In top left click on organization name and then `Settings` - Click `+ Create token` - Specify a name for the token that you'll recognize (like "DuckDB CLI on my laptop") - Specify the type of token you want. Tokens can be Read/Write (default) or [Read Scaling](/docs/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/). - Choose whether you want the token to expire and then click on `Create token` - Copy the access token token to your clipboard by clicking on the copy icon ![Access tokens settings page](../img/tokens.png) ### Storing the access token as an environment variable You can save the access token as `motherduck_token` in your environment variables. An example of setting this in a terminal: ```bash export motherduck_token='' ``` You can also add this line to your `~/.zprofile` or `~/.bash_profile`, or store it in a `.env` file in your project root. Once this is done, your authentication token is saved and you can connect to MotherDuck with the following connection string: ```bash duckdb "md:my_db" ``` :::info This is the best practice for security reasons. The token is sensitive information and should be kept safe. Do not share it with others. ::: Alternatively, you can specify an access token in the MotherDuck connection string: `md:my_db?motherduck_token=`. ```bash duckdb "md:my_db?motherduck_token=" ``` When in the DuckDB CLI, you can use the `.open` command and specify the connection string as an argument. ```CLI .open md:my_db?motherduck_token= ``` ## Using connection string parameters ### Authentication using SaaS mode You can limit MotherDuck's ability to interact with your local environment using `SaaS Mode`: - Disable reading or writing local files - Disable reading or writing local DuckDB databases - Disable installing or loading any DuckDB extensions locally - Disable changing any DuckDB configurations locally This mode is useful for third-party tools, such as BI vendors, that host DuckDB themselves and require additional security controls to protect their environments. You can enable SaaS mode in two ways: 1. **Using a configuration setting** (recommended for persistent configuration): ```sql SET motherduck_saas_mode = true; ``` 2. **Using a connection string parameter** (for connection-time configuration): ### CLI ```cli .open md:[]?[motherduck_token=]&saas_mode=true ``` ### Python ```python conn = duckdb.connect("md:[]?[motherduck_token=]&saas_mode=true") ``` :::info Using the connection string parameter requires to use `.open` when using the DuckDB CLI or `duckdb.connect` when using Python. This initiates a new connection to MotherDuck and will detach any existing connection to a local DuckDB database. You cannot provide a token to `ATTACH md:` directly, only when connecting. ::: ### Using attach mode By default, MotherDuck connects in **workspace mode**, which attaches every database in your saved workspace and keeps attachment changes in sync across parallel connections. To scope the connection to a single database instead, use **single mode** by appending `?attach_mode=single` to the connection string. Single mode is useful for BI tools and other clients that get confused by multiple attached databases. For full details, see [Attach modes](/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/). For example, to connect to a database named `my_database` in single mode, run: ```bash duckdb 'md:my_database?attach_mode=single' ``` :::note `` that starts with a number cannot be connected to directly. You will need to connect without a database specified and then `CREATE` and `USE` using a double quoted name. Eg: `USE DATABASE "1database"` ::: --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/sso-setup # Setting up SSO > Configure Single Sign-On (SSO) for your MotherDuck organization using your identity provider. Single Sign-On (SSO) allows your organization to authenticate MotherDuck users through your existing identity provider (IdP). When SSO is enabled, users with a verified email domain are automatically redirected to your corporate login page, removing the need for separate MotherDuck credentials. :::note SSO is available on **Business** and **Enterprise** plans. ::: ## How SSO works When you configure SSO, MotherDuck connects to your identity provider using either the SAML or OIDC protocol. The login flow works as follows: 1. A user enters their email on the MotherDuck login page. 2. MotherDuck looks up the email domain. If the domain is verified and SSO is enabled, the user is redirected to your corporate IdP. 3. The user authenticates with the IdP. 4. MotherDuck receives the authentication response and creates or updates the user's session. Users with personal email addresses or domains without SSO configured continue to use standard login methods (Google, GitHub, or email and password). ## Supported SSO configurations MotherDuck supports four SSO configuration options: | Configuration | Protocol | Use when | | --- | --- | --- | | **Okta** | OIDC | Your organization uses Okta Workforce Identity | | **Microsoft Entra ID** | OIDC | Your organization uses Microsoft Entra ID (formerly Azure AD) | | **SAML** | SAML | Your IdP supports SAML but is not Okta or Entra ID | | **OIDC** | OIDC | Your IdP supports OpenID Connect but is not Okta or Entra ID | The generic SAML and OIDC options allow you to connect any compatible identity provider, such as Google Workspace, PingFederate, or Keycloak. ### SAML vs. OIDC **SAML** (Security Assertion Markup Language) is an XML-based protocol widely used in enterprise environments for browser-based SSO. Most traditional enterprise IdPs support SAML. **OIDC** (OpenID Connect) is a JSON-based protocol built on top of OAuth 2.0. It is more common in cloud-native and modern environments. Both protocols achieve the same result: authenticating users through your IdP. Choose the protocol that your IdP supports or that your IT team is most familiar with. ## Prerequisites Before setting up SSO, ensure you have: - Permission to configure SSO in your MotherDuck organization. The Admin preset role includes this permission by default. - A **Business** or **Enterprise** plan - Admin access to your company's identity provider - A **custom domain name** for your organization (for example, `acme.com`) and the ability to add a DNS TXT record to the domain for verification - All users in your organization use **non-aliased email addresses** (addresses like `user+tag@company.com` are not supported) :::caution SSO is supported for organizations where all users belong to a **single MotherDuck organization**. If your users are spread across multiple MotherDuck organizations (for example, separate US and EU orgs), do not enable SSO. Multi-organization SSO support is planned for a future release. ::: ## Setting up SSO ### Step 1: Start SSO configuration in MotherDuck 1. In the MotherDuck UI, click your organization name in the top left and select **Settings**. 2. Navigate to the **Authentication** tab. 3. Click **Set up SSO** to begin the setup process. ![MotherDuck Settings showing the Authentication tab with the Set up SSO button](./img/sso-authentication-settings.png) 4. Select your identity provider from the list, or choose **Custom SAML** or **Custom OIDC** if your IdP is not listed. ![Select your identity provider for SSO configuration](./img/sso-select-identity-provider.png) ### Step 2: Create a MotherDuck application in your identity provider 1. Log in to your identity provider's admin console. 2. Create a new application and name it **MotherDuck**. 3. Select the appropriate protocol (SAML or OIDC) based on your chosen configuration. ### Step 3: Configure the connection The MotherDuck setup wizard provides step-by-step instructions for each provider. Follow the instructions on the SSO onboarding portal to configure the connection between your IDP and MotherDuck. For example, the Okta configuration walks you through creating an OIDC application: ![Okta OIDC SSO configuration wizard showing the Create Application step](./img/sso-okta-create-application.png) ### Step 4: Map user attributes In your IdP, map the following attributes to the MotherDuck application: | Attribute | Required | Description | | --- | --- | --- | | `email` | Yes | The user's email address (primary login identifier) | | `given_name` | No | The user's first name | | `family_name` | No | The user's last name | ### Step 5: Assign users Assign yourself (and optionally other users) to the MotherDuck application in your IdP. ### Step 6: Verify your domain MotherDuck requires domain ownership verification before SSO can be enabled. Follow the instructions to add a DNS TXT record for your domain. Once the record is detected, your domain is verified. ![SSO configuration status showing pending domain verification](./img/sso-pending-domain-verification.png) ### Step 7: Enable SSO After domain verification succeeds, return to the setup wizard and click **Done** to complete the configuration, then click **Enable SSO** to activate the connection. ![SSO configuration dialog to confirm enabling SSO](./img/sso-enable-sso-dialog-confirmation.png) :::warning Enabling SSO is **not reversible** without contacting MotherDuck support. Before enabling, ensure that: - All users in your organization use non-aliased email addresses on the verified domain - Your users belong to **only this** MotherDuck organization - You have tested the IdP configuration by assigning yourself to the application ::: When SSO is enabled: - All existing non-SSO login methods (Google, GitHub, email/password) are **deactivated** for users with the verified domain - Any pending invitations matching the SSO domain will need to **sign up through SSO** - Users must authenticate through the configured IdP going forward ### Step 8: Test SSO login 1. Log out of MotherDuck. 2. On the login page, enter your corporate email address. 3. You should be redirected to your IdP's login page. 4. After authenticating, you are returned to the MotherDuck UI. ## Just-in-Time (JIT) user provisioning When SSO is enabled, new users from your verified domain can be automatically provisioned on their first login. This is called Just-in-Time (JIT) provisioning. JIT provisioning is enabled by default the first time you activate SSO. Changing this setting requires permission to manage the organization invite policy, which the Admin preset role includes by default. With JIT enabled: - A user enters their corporate email on the MotherDuck login page - They are redirected to your IdP and authenticate - The user is automatically given the option to join your organization at signup ### Controlling access with JIT and invite settings Configuring JIT provisioning requires permission to configure SSO. Changing the organization invite policy requires a separate permission to manage the invite policy. The Admin preset role includes both permissions by default. These two settings work together to control how new users join your organization: | Setting | When enabled | When disabled | | --- | --- | --- | | **JIT provisioning** | Users who authenticate through your IdP can join the organization on first sign-in *(default on first SSO activation)* | New users must be invited by someone with permission to invite members | | **Organization invites** | The invite policy grants members permission to invite others | Only roles that include permission to invite members can invite new users | When both organization invites and JIT provisioning are disabled, new users can only join if someone with permission to invite members invites them. When JIT is enabled but member invitations are disabled, users who have access in your IdP can still join on first sign-in. ![invite policy](./img/org-invite-policy.png) For more information on managing organization members and roles, see [Managing organizations](/docs/key-tasks/managing-organizations/). JIT provisioning handles initial account creation only. It does not manage role changes or account deletion after provisioning. For automated user lifecycle management, see [SCIM provisioning](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/scim/). ### How SCIM affects JIT and invites When SCIM provisioning is enabled, MotherDuck delegates user lifecycle to your IdP. SCIM replaces JIT as the auto-provisioning mode, and organization invites are automatically disabled (the **Invite policy** setting is locked). To re-enable manual invites or fall back to JIT, [disable SCIM](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/scim/#disabling-scim) from the Authentication settings page. :::warning If you disable SCIM and then change members from inside MotherDuck, the user state in MotherDuck and your IdP will drift. Either keep SCIM enabled and manage users in your IdP, or disable SCIM and accept that the two systems are no longer in sync. ::: ## Managing members Managing users with SSO works the same as before. You can invite any new user by supplying their email address. If the email domain matches one of your verified domains, the user will be redirected to their IdP for authentication. :::note **Everyone in an SSO-enabled organization signs in with an email on one of your verified domains.** To give an external collaborator or contractor access, provision a dedicated email address on a verified domain (for example, `contractor-name@yourcompany.com`). Your organization can then manage the account through your IdP. ::: If you have [SCIM provisioning](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/scim/) enabled, manual invites are disabled. Users are created automatically when you assign them to the MotherDuck application in your IdP, and deprovisioned when you unassign them. To hard-delete a user's record from MotherDuck, explicitly delete the user in your IdP — deprovisioning alone keeps the record for later reprovisioning. ## Limitations - **Single organization only**: SSO is supported for users who belong to a single MotherDuck organization. Multi-org SSO is planned for a future release. - **No aliased emails**: Email addresses with aliases (for example, `user+tag@company.com`) are not supported when SSO is enabled. - **One connection per domain**: Each verified domain can have only one SSO connection. Users with an email address on that domain in any MotherDuck organization will be redirected to their IdP. - **Non-reversible**: Enabling SSO cannot be undone without contacting [MotherDuck support](mailto:support@motherduck.com). - **CLI and SDK authentication**: Users authenticating through the SDKs continue to use [access tokens](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#authentication-using-an-access-token). SSO applies to browser-based login flows for the WebUI, CLI and MCP. --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/scim # Setting up SCIM provisioning > Automate user lifecycle management in MotherDuck using SCIM with your identity provider. SCIM (System for Cross-domain Identity Management) keeps your MotherDuck users in sync with your identity provider. When you assign, update, or remove a user in your IdP, the change is automatically applied in MotherDuck. :::note SCIM provisioning is available on **Business** and **Enterprise** plans, and requires an active [SSO connection](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/sso-setup/). ::: ## How SCIM complements SSO SSO and SCIM solve different problems: | | SSO | SCIM | | --- | --- | --- | | **Purpose** | Authentication — controls **how** users sign in | Provisioning — controls **which** users exist | | **Handles** | Sign-in redirects, session management | Account creation, updates, deprovisioning | | **Trigger** | User-initiated (at sign-in) | IdP-initiated (when staff changes) | With SSO alone, MotherDuck uses [just-in-time (JIT) provisioning](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/sso-setup/#just-in-time-jit-user-provisioning) to create accounts on first sign-in. JIT does not handle changes after the account is created — if an employee leaves your company, their MotherDuck account stays active until someone with permission to deprovision members or remove members [does so manually](/docs/key-tasks/managing-organizations/#deprovisioning-users). SCIM closes that gap by making your IdP the source of truth for who has access. SCIM replaces JIT as the auto-provisioning mode and disables manual invite flows, so the IdP becomes the only place where members are added or removed. ## Prerequisites Before you enable SCIM, confirm: - Permission to configure SCIM in MotherDuck. The Admin preset role includes this permission by default. - A **Business** or **Enterprise** plan. - An [SSO connection](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/sso-setup/) that is **active** (not pending). SCIM cannot be enabled on a pending connection. - Admin access to the IdP application that's already linked to your SSO connection. Configuring SCIM uses your existing SSO connection, so any of the supported enterprise SSO connection types work — **SAML**, **OIDC**, **Okta Workforce**, or **Microsoft Entra ID** (Azure AD). ## Supported operations | IdP action | Effect in MotherDuck | | --- | --- | | Assign user to the MotherDuck application | Creates a MotherDuck user with the **Explorer** role on first SCIM event | | Update user attributes (name, email) | Updates the MotherDuck user record | | Deprovision user | Deprovisions the user — sign-in is blocked, all access tokens are revoked, but data is retained and the account can be reprovisioned | | Reprovision user | Restores a deprovisioned user to active status | | Unassign / delete user | Removes the user from the organization (hard delete) | Role assignment through SCIM is not yet supported — all SCIM-provisioned users start with the **Explorer** role. Changing a user's role after provisioning requires permission to assign roles, which the Admin preset role includes by default. Use the MotherDuck **Members** page to make the change. ## Attribute mapping MotherDuck reads the following attributes from each SCIM request: | SCIM attribute | Required | Description | | --- | --- | --- | | `userName` | Yes | The user's email address. Must be on a [verified domain](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/sso-setup/#step-6-verify-your-domain) of the SSO connection. | | `emails[].value` | Yes (if `userName` is not set to email) | Used as a fallback for the email address. | | `name.givenName` | No | The user's first name. | | `name.familyName` | No | The user's last name. | | `active` | Yes | Drives deprovisioning and reprovisioning. | User email addresses with aliases (for example, `user+tag@company.com`) are not supported, matching the SSO requirement. ## Enabling SCIM ### Step 1: Generate the SCIM endpoint and token in MotherDuck 1. In the MotherDuck UI, click your organization name in the top left and select **Settings**. 2. Open the **Authentication** tab. 3. In the **SCIM** section, click **Enable SCIM**. 4. Confirm in the dialog. MotherDuck generates a SCIM endpoint URL and a SCIM token. The endpoint URL has this shape: ```text https://auth.motherduck.com/scim/v2/connections/ ``` :::warning The SCIM token is shown **once**. Copy it immediately and store it in your IdP — MotherDuck cannot show it again. If you lose the token, you can regenerate it (which revokes the previous token). ::: ### Step 2: Configure SCIM provisioning in your identity provider In your IdP's admin console, open the application that's linked to your MotherDuck SSO connection and turn on SCIM provisioning. The exact path depends on the IdP: - **Okta**: open the application's **Provisioning** tab and switch to **SCIM**. - **Microsoft Entra ID**: open the application's **Provisioning** blade and set the mode to **Automatic**. When prompted, supply: - **Tenant URL** (also called **SCIM endpoint URL** or **Base URL**): paste the URL from Step 1. - **Secret token** (also called **Bearer token**): paste the SCIM token from Step 1. Use the IdP's **Test Connection** button to verify connectivity before assigning users. ### Step 3: Map attributes in your identity provider Map your IdP's user attributes to the SCIM attributes [listed above](#attribute-mapping). Most IdPs ship a default mapping that already covers `userName`, `emails`, `name.givenName`, `name.familyName`, and `active`. ### Step 4: Assign users Assign users (or groups) to the MotherDuck application in your IdP. Each assignment triggers a SCIM `create` request, which provisions the user in MotherDuck with the **Explorer** role. For ongoing changes, your IdP automatically sends: - A SCIM `update` request when a user's attributes change. - A SCIM `update` or `patch` request with `active=false` when a user is deprovisioned or unassigned. - A SCIM `delete` request when the user is fully removed. ## Managing SCIM after enablement ### Regenerating the token If the SCIM token is lost or compromised, regenerate it from **Settings → Authentication → SCIM → Regenerate token**. The previous token is revoked immediately, so update the new token in your IdP right away to avoid provisioning failures. ### Disabling SCIM To stop SCIM provisioning, click **Disable SCIM** on the **Authentication** page. Disabling: - Removes the SCIM connection from MotherDuck's identity layer (your IdP can no longer make SCIM requests). - Switches auto-provisioning back to [JIT](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/sso-setup/#just-in-time-jit-user-provisioning). - Leaves existing user accounts untouched. Previously deprovisioned users remain deprovisioned. You can re-enable SCIM later, but a new endpoint URL and token will be issued. ### Manual invites are disabled and JIT is replaced When SCIM is enabled, MotherDuck switches the auto-provisioning mode from JIT to SCIM and disables manual invite flows, so the IdP stays the single source of truth for who can sign in: - The **Invite** action in the org menu and on the Members page is disabled. - The **Invite policy** setting on the org details page is disabled. To grant a new user access, assign them to the MotherDuck application in your IdP. ### Deprovisioned users on the members page Deprovisioned users appear on the **Members** page with a `deprovisioned` badge. Hover the badge for a reminder that the user can no longer sign in and that all of their access tokens have been revoked. The Members page status filter lets you narrow the list to **active**, **invited**, or **deprovisioned** users. Impersonating a deprovisioned user requires permission to impersonate deprovisioned members, which the Admin preset role includes by default. Impersonating a service account requires a separate permission, which the Admin and Builder preset roles include by default. Active human users cannot be impersonated. ## Deletion vs. deprovisioning Deprovisioning and deletion are distinct user states with different recovery semantics: | State | What happens | How to enter | How to exit | | --- | --- | --- | --- | | **Deprovisioned** | The user record and data are retained, the identity is disabled, and all PATs and short-lived tokens are revoked. The user cannot sign in. A user with the required impersonation permission can still impersonate the account. | IdP sends `active=false` (PATCH or PUT) | Reprovision the user in your IdP — works at any time, including past the deletion fail-safe window | | **Deleted** | The user is removed from the organization. Email is freed for reuse. No one can impersonate a deleted account. | IdP sends a SCIM `delete` request | Restore the account within the **7-day fail-safe** window through MotherDuck support | ### How deletion is triggered - **From the IdP (SCIM orgs)**: removing the user from the MotherDuck application — or deleting them from the IdP entirely — sends a SCIM `delete` event when your IdP is configured to forward delete events. SCIM-enabled organizations cannot hard-delete users from inside MotherDuck; the IdP is the only authoritative path. - **From inside MotherDuck (non-SCIM orgs only)**: the in-app **Remove member** action is available only when SCIM is disabled. :::note Some IdPs do not forward delete events to applications by default — they only mark the user inactive on their side. In that case, MotherDuck sees the inactive signal and the user appears as **deprovisioned** rather than deleted. Configure your IdP to forward delete events if you want hard deletes to flow through. ::: ### Restoring after deprovisioning or deletion - A **deprovisioned** user can be reprovisioned at any time from your IdP. The next SCIM event will restore their account. Their data is preserved, but issued access tokens are not — affected users need to mint new tokens. - A **deleted** user can be restored within the **7-day fail-safe** window by contacting [MotherDuck support](mailto:support@motherduck.com). After the window elapses, the account and its data are gone. ## Limitations - **One SCIM connection per organization**: SCIM uses the same Auth0 connection as SSO. Each MotherDuck organization can have only one SCIM connection, matching its single SSO connection. - **No role mapping yet**: SCIM-provisioned users start as **Explorer**. Adjusting roles in MotherDuck after provisioning requires permission to assign roles, which the Admin preset role includes by default. - **No aliased emails**: Addresses like `user+tag@company.com` are rejected, the same as for SSO. - **PATCH `remove` operations are ignored**: MotherDuck handles SCIM `add` and `replace` operations on `active`. `remove` operations are logged and skipped to keep behavior predictable across IdPs. ## Related - [Setting up SSO](/docs/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/sso-setup/) - [Managing organizations](/docs/key-tasks/managing-organizations/) --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/postgres-endpoint # Connect via the Postgres endpoint > Connect to MotherDuck using any Postgres-compatible client via the Postgres wire protocol endpoint MotherDuck's Postgres endpoint lets you query your databases using any client that speaks the [PostgreSQL wire protocol](https://www.postgresql.org/docs/current/protocol.html) — without installing a DuckDB client library. This is ideal for serverless environments, BI tools, or languages without a DuckDB SDK. For full-featured access — including Dual Execution, local caching, and the complete DuckDB extension ecosystem — use the [DuckDB SDK](/getting-started/interfaces/client-apis/) instead. ## Before you start You'll need a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck). Set it as an environment variable: ```bash export MOTHERDUCK_TOKEN="your_token_here" ``` ## Connect with psql ```bash PGPASSWORD=$MOTHERDUCK_TOKEN psql \ -h pg.us-east-1-aws.motherduck.com \ -p 5432 \ -U postgres \ "dbname=md: sslmode=verify-full sslrootcert=system" ``` ## Connect with a URI ```sh postgresql://postgres:$MOTHERDUCK_TOKEN@pg.us-east-1-aws.motherduck.com:5432/md:?sslmode=verify-full&sslrootcert=system ``` Use `md:` as the database name, or replace it with a specific database name, for example `sample_data`. :::info For security, always use environment variables for your MotherDuck token. Never hardcode tokens in your application code. ::: ## Secure your connection Always connect with SSL enabled. The recommended approach is `sslmode=verify-full` with `sslrootcert=system`, which verifies the server certificate against your operating system's trusted roots. If your client doesn't support this, you can download the [ISRG Root X1](https://letsencrypt.org/certs/isrgrootx1.pem) certificate from Let's Encrypt and set `sslrootcert` to its path. Some libraries (psycopg2, JDBC, node-postgres) handle SSL differently — see the language-specific guides below or the [SSL reference](/sql-reference/postgres-endpoint#ssl-and-certificate-verification) for details. ## Key things to know - You're writing **DuckDB SQL**, not PostgreSQL SQL. Queries and MotherDuck SQL that run entirely inside MotherDuck generally work, but the Postgres endpoint is not a full DuckDB client. - Commands that depend on **local files, local attachments, or extension management** are not supported over the Postgres endpoint. Examples: local-file `COPY`, `EXPORT DATABASE`, `IMPORT DATABASE`, `ATTACH ':memory:'`, `ATTACH '/path/to/file.duckdb'`, `CREATE DATABASE ... FROM '/path/to/file.duckdb'`, `MD_RUN=LOCAL` on table functions, `INSTALL`, and `LOAD`. - Use the Postgres endpoint for query execution, DDL and DML on MotherDuck tables, metadata inspection, and server-side reads from remote storage. - Avoid using `SET` statements, temporary tables, or result-creation commands — those are not supported in Postgres-endpoint server mode. - Prefer **long-lived connections** rather than opening and closing per query. For high-concurrency applications, use a connection pool with configured connect, idle, and query timeouts. ## DuckLake databases You can query and write to MotherDuck-managed [DuckLake](/concepts/ducklake/) databases over the Postgres endpoint the same way as native-storage MotherDuck databases — connect with a [read-write token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#authentication-using-an-access-token) and run `SELECT`, DDL, and DML against them. The standard Postgres endpoint limitations above still apply (for example, client-side `COPY` from local files is not supported). Using the Postgres endpoint as the metadata catalog for a self-hosted DuckLake by pointing a DuckDB client running DuckLake at the endpoint as its catalog backend, is not supported yet. ## Language and platform guides - [Connect from Python (psycopg2 / psycopg3)](./python) - [Connect from Java (JDBC)](./java) - [Connect from Node.js](./nodejs) - [Connect from Cloudflare Workers](./cloudflare-workers) - [Connect from Drizzle](./drizzle) ## Reference For connection parameters, SSL options, session settings, and limitations, see the [Postgres Endpoint reference](/sql-reference/postgres-endpoint). --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/python # Connect from Python via Postgres endpoint > Connect to MotherDuck from Python using psycopg2 or psycopg3 via the Postgres wire protocol You can query MotherDuck from Python using standard PostgreSQL client libraries. No DuckDB installation is required. This guide covers [psycopg2](https://www.psycopg.org/docs/) and [psycopg (v3)](https://www.psycopg.org/psycopg3/docs/). For connection parameters, SSL options, and limitations, see the [Postgres Endpoint reference](/sql-reference/postgres-endpoint). ## Prerequisites You need a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck). Set it as an environment variable: ```bash export MOTHERDUCK_TOKEN="your_token_here" ``` ## Connect ### psycopg (v3) ```python # /// script # dependencies = ["psycopg"] # /// import os import psycopg with psycopg.connect( host="pg.us-east-1-aws.motherduck.com", # or us-west-2-aws, eu-central-1-aws, eu-west-1-aws, ap-northeast-1-aws, or ap-southeast-2-aws port=5432, dbname="md:", user="postgres", password=os.environ["MOTHERDUCK_TOKEN"], sslmode="verify-full", sslrootcert="system", # available in libpq 16+ ) as conn: with conn.cursor() as cur: cur.execute( """ SELECT title, score FROM sample_data.hn.hacker_news WHERE type = 'story' ORDER BY score DESC LIMIT 5 """ ) for row in cur: print(row) ``` You can also use a connection URI: ```python import os import psycopg token = os.environ["MOTHERDUCK_TOKEN"] with psycopg.connect( f"postgresql://postgres:{token}@pg.us-east-1-aws.motherduck.com:5432/md:?sslmode=verify-full&sslrootcert=system" ) as conn: with conn.cursor() as cur: cur.execute("SELECT current_database()") print(cur.fetchone()) ``` ### psycopg2 ```python # /// script # dependencies = ["psycopg2-binary", "certifi"] # /// import os import certifi import psycopg2 conn = psycopg2.connect( host="pg.us-east-1-aws.motherduck.com", # or us-west-2-aws, eu-central-1-aws, eu-west-1-aws, ap-northeast-1-aws, or ap-southeast-2-aws port=5432, dbname="md:", user="postgres", password=os.environ["MOTHERDUCK_TOKEN"], sslmode="verify-full", sslrootcert=certifi.where(), ) with conn: with conn.cursor() as cur: cur.execute( """ SELECT title, score FROM sample_data.hn.hacker_news WHERE type = 'story' ORDER BY score DESC LIMIT 5 """ ) for row in cur.fetchall(): print(row) ``` Use `md:` as the database name, or replace it with a specific database name such as `sample_data`. ## Connection pooling and timeouts Use a connection pool in production. With psycopg v3, install pool support: ```bash pip install "psycopg[pool]" ``` Then create one pool per application process: ```python import os from psycopg_pool import ConnectionPool pool = ConnectionPool( conninfo=( "host=pg.us-east-1-aws.motherduck.com " "port=5432 " "dbname=md: " "user=postgres " "sslmode=verify-full " "sslrootcert=system" ), kwargs={"password": os.environ["MOTHERDUCK_TOKEN"]}, min_size=0, max_size=10, timeout=5, max_idle=30, max_lifetime=300, ) with pool.connection() as conn: with conn.cursor() as cur: cur.execute( "SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 10" ) print(cur.fetchall()) ``` `timeout=5` fails fast when the pool cannot provide a connection. `max_idle=30` closes unused connections quickly when the pool can shrink, and `max_lifetime=300` periodically replaces long-lived connections. The pool context manager returns healthy connections to the pool and discards broken ones. If you catch database errors inside the block, roll back failed transactions before reusing the connection. `statement_timeout` is not supported through the Postgres endpoint today. For sync psycopg code, use a client-side timer that calls `conn.cancel()`: ```python import threading import psycopg with pool.connection() as conn: with conn.cursor() as cur: timer = threading.Timer(60, conn.cancel) timer.start() try: cur.execute("SELECT count(*) FROM sample_data.hn.hacker_news") print(cur.fetchone()) except psycopg.errors.QueryCanceled as exc: conn.rollback() raise TimeoutError("MotherDuck query exceeded 60 seconds") from exc finally: timer.cancel() ``` For async psycopg code, wrap the query in `asyncio.timeout(...)`; psycopg sends cancellation when the task is cancelled. ## Loading data from Python For loading through the Postgres endpoint, the recommended pattern is server-side reads from remote storage: - Use `psycopg` or SQLAlchemy to execute `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 those file reads. Example with SQLAlchemy: ```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": "require", }, ) 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 ) """ ) ) ``` The following patterns are not supported from Python over the Postgres endpoint: - `COPY ... FROM '/local/file.csv'` - `cursor.copy(...)` / `COPY FROM STDIN` - `psql \copy` - `MD_RUN = LOCAL` - SQLAlchemy's default `executemany` path for bulk ingest If the rows exist only in application memory and the volume is modest, prefer explicit multi-values `INSERT` statements. For large local bulk loads, switch to a DuckDB client path instead. See [Loading data through the Postgres endpoint](/key-tasks/loading-data-into-motherduck/loading-data-via-postgres-endpoint) for the full decision guide. ## SSL notes - **psycopg (v3)** wraps libpq and supports `sslrootcert=system` directly. - **psycopg2** bundles its own statically linked OpenSSL, so `sslrootcert=system` is not supported. Use the `certifi` package to point to CA certificates, or download the [ISRG Root X1](https://letsencrypt.org/certs/isrgrootx1.pem) certificate and set `sslrootcert` to its path. For more details on SSL options, see [SSL and certificate verification](/sql-reference/postgres-endpoint#ssl-and-certificate-verification). --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/java # Connect from Java via Postgres endpoint > Connect to MotherDuck from Java using the PostgreSQL JDBC driver via the Postgres wire protocol You can query MotherDuck from Java using the standard [PostgreSQL JDBC driver](https://jdbc.postgresql.org/) — no DuckDB installation required. For connection parameters, SSL options, and limitations, see the [Postgres Endpoint reference](/sql-reference/postgres-endpoint). ## Prerequisites You'll need a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck). Set it as an environment variable: ```bash export MOTHERDUCK_TOKEN="your_token_here" ``` Add the PostgreSQL JDBC driver to your project: ### Maven ```xml org.postgresql postgresql 42.7.11 ``` ### Gradle ```groovy implementation 'org.postgresql:postgresql:42.7.11' ``` ## Connect ```java import java.sql.*; public class MotherDuckExample { public static void main(String[] args) throws SQLException { String token = System.getenv("MOTHERDUCK_TOKEN"); String url = "jdbc:postgresql://pg.us-east-1-aws.motherduck.com:5432/md:" + "?sslmode=verify-full" + "&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory"; try (Connection conn = DriverManager.getConnection(url, "postgres", token); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 10")) { ResultSetMetaData meta = rs.getMetaData(); int columnCount = meta.getColumnCount(); while (rs.next()) { for (int i = 1; i <= columnCount; i++) { System.out.print(meta.getColumnName(i) + "=" + rs.getString(i)); if (i < columnCount) System.out.print(", "); } System.out.println(); } } } } ``` You can also configure the connection using a `Properties` object: ```java import java.sql.*; import java.util.Properties; Properties props = new Properties(); props.setProperty("user", "postgres"); props.setProperty("password", System.getenv("MOTHERDUCK_TOKEN")); props.setProperty("sslmode", "verify-full"); props.setProperty("sslfactory", "org.postgresql.ssl.DefaultJavaSSLFactory"); Connection conn = DriverManager.getConnection( "jdbc:postgresql://pg.us-east-1-aws.motherduck.com:5432/md:", props ); ``` ## Connection pooling and timeouts Use a JDBC connection pool in production. With HikariCP, set a connection timeout, idle timeout, maximum lifetime, and query timeout: ```xml com.zaxxer HikariCP 6.3.3 ``` ```java import com.zaxxer.hikari.HikariConfig; import com.zaxxer.hikari.HikariDataSource; import java.sql.*; HikariConfig config = new HikariConfig(); config.setJdbcUrl( "jdbc:postgresql://pg.us-east-1-aws.motherduck.com:5432/md:" + "?sslmode=verify-full" + "&sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory" ); config.setUsername("postgres"); config.setPassword(System.getenv("MOTHERDUCK_TOKEN")); config.setMaximumPoolSize(10); config.setMinimumIdle(0); config.setConnectionTimeout(5_000); config.setIdleTimeout(30_000); config.setMaxLifetime(300_000); config.addDataSourceProperty("connectTimeout", "5"); config.addDataSourceProperty("cancelSignalTimeout", "5"); try (HikariDataSource dataSource = new HikariDataSource(config); Connection conn = dataSource.getConnection(); Statement stmt = conn.createStatement()) { stmt.setQueryTimeout(60); try (ResultSet rs = stmt.executeQuery( "SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 10" )) { while (rs.next()) { System.out.println(rs.getString("title")); } } } ``` `setConnectionTimeout(5_000)` fails fast when a connection cannot be checked out. `setIdleTimeout(30_000)` and `setMinimumIdle(0)` let HikariCP close unused connections quickly, and `setMaxLifetime(300_000)` periodically replaces long-lived connections. HikariCP validates connections before reuse and removes broken connections from the pool. `statement_timeout` is not supported through the Postgres endpoint today. Use JDBC `Statement.setQueryTimeout(...)` for client-side cancellation. ## SSL notes The PostgreSQL JDBC driver looks for a root certificate at `~/.postgresql/root.crt` by default. To use your JVM's built-in trust store instead (which includes standard CAs like Let's Encrypt), set `sslfactory=org.postgresql.ssl.DefaultJavaSSLFactory`. If certificate verification doesn't work in your environment, you can fall back to `sslmode=require`, which encrypts the connection but doesn't verify the server certificate. For more details on SSL options, see [SSL and certificate verification](/sql-reference/postgres-endpoint#ssl-and-certificate-verification). --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/nodejs # Connect from Node.js via Postgres endpoint > Connect to MotherDuck from Node.js using the pg (node-postgres) library via the Postgres wire protocol You can query MotherDuck from Node.js using [node-postgres](https://node-postgres.com/) (`pg`) — no DuckDB installation required. For connection parameters, SSL options, and limitations, see the [Postgres Endpoint reference](/sql-reference/postgres-endpoint). ## Prerequisites You'll need a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck). Set it as an environment variable: ```bash export MOTHERDUCK_TOKEN="your_token_here" ``` Install the `pg` package: ```bash npm install pg ``` ## Connect Use a configuration object to connect. Do **not** pass `sslrootcert=system` in a connection string — node-postgres tries to read `system` as a file path and throws an `ENOENT` error. ```js 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: "md:", ssl: { rejectUnauthorized: true }, }); await client.connect(); const { rows } = await client.query( "SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 10" ); console.log(rows); await client.end(); ``` ## Connection pooling and timeouts Use `pg.Pool` in production. Set a connection timeout so requests fail fast when new connections cannot be opened, set an idle timeout so unused connections are recycled quickly, and set a query timeout so one slow query does not let requests pile up. ```js import pg from "pg"; const pool = new pg.Pool({ host: "pg.us-east-1-aws.motherduck.com", port: 5432, user: "postgres", password: process.env.MOTHERDUCK_TOKEN, database: "md:", ssl: { rejectUnauthorized: true }, max: 10, connectionTimeoutMillis: 5_000, idleTimeoutMillis: 30_000, maxLifetimeSeconds: 300, query_timeout: 60_000, }); pool.on("error", (err) => { console.error("Unexpected idle client error", err); }); const { rows } = await pool.query( "SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 10" ); console.log(rows); ``` For simple queries, prefer `pool.query(...)`; node-postgres checks out and releases the connection for you. When you check out a client manually, always release it. If the client hits a connection-level error such as a network reset, protocol error, or server termination, destroy it with `client.release(true)` instead of returning it to the pool. ```js const client = await pool.connect(); let destroy = false; try { await client.query("BEGIN"); await client.query("SELECT 1"); await client.query("COMMIT"); } catch (err) { await client.query("ROLLBACK").catch(() => { destroy = true; }); throw err; } finally { client.release(destroy); } ``` `statement_timeout` is not supported through the Postgres endpoint today. Use `query_timeout` for client-side cancellation. ## SSL notes Node.js uses the operating system's certificate store by default. Setting `ssl: { rejectUnauthorized: true }` tells node-postgres to use TLS and verify the server certificate against these trusted roots — this is the equivalent of `sslmode=verify-full` with `sslrootcert=system` in libpq. If you need to specify a custom CA certificate (for example, the [ISRG Root X1](https://letsencrypt.org/certs/isrgrootx1.pem) certificate from Let's Encrypt): ```js import fs from "fs"; const client = new pg.Client({ host: "pg.us-east-1-aws.motherduck.com", port: 5432, user: "postgres", password: process.env.MOTHERDUCK_TOKEN, database: "md:", ssl: { rejectUnauthorized: true, ca: fs.readFileSync("/path/to/isrgrootx1.pem").toString(), }, }); ``` For more details on SSL options, see [SSL and certificate verification](/sql-reference/postgres-endpoint#ssl-and-certificate-verification). :::info[Cloudflare Workers] Cloudflare Workers use a different socket implementation (`pg-cloudflare`) that handles SSL differently. See [Connect from Cloudflare Workers](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/cloudflare-workers) for Workers-specific setup. ::: --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/cloudflare-workers # Connect from Cloudflare Workers > Query MotherDuck from Cloudflare Workers using the Postgres wire protocol Cloudflare Workers do not support native DuckDB bindings, but they can connect to MotherDuck through the [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint) using the [`pg`](https://www.npmjs.com/package/pg) npm package. This gives you a thin-client path to query MotherDuck from edge functions without any DuckDB dependencies. This guide walks through building a Worker that queries NYC taxi data from MotherDuck's built-in `sample_data` database. The full source code is available in the [motherduck-cookbook](https://github.com/motherduckdb/motherduck-cookbook/tree/main/cloudflare-workers) repository. ## Prerequisites - [Node.js](https://nodejs.org/) v18+ - A [Cloudflare account](https://dash.cloudflare.com/sign-up) - A [MotherDuck account](https://motherduck.com/) and [access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) ## Project setup Create a new directory and install dependencies: ```bash mkdir motherduck-worker && cd motherduck-worker npm init -y npm install pg@^8.16.3 npm install --save-dev wrangler @types/pg ``` ### Configure wrangler.toml ```toml name = "motherduck-taxi-stats" main = "src/index.ts" compatibility_date = "2026-04-02" compatibility_flags = ["nodejs_compat"] [vars] MOTHERDUCK_HOST = "pg.us-east-1-aws.motherduck.com" MOTHERDUCK_DB = "sample_data" ``` The `nodejs_compat` flag is required — it enables the `node:net` module that the `pg` package uses for TCP connections. Use a `compatibility_date` on or after `2024-09-23`; in practice, set it to today's date when you create the project. Generate the Worker binding types after you save `wrangler.toml`: ```bash npx wrangler types ``` ### Store your token as a secret ```bash npx wrangler secret put MOTHERDUCK_TOKEN ``` This prompts you to paste your MotherDuck token. It's stored encrypted and injected as an environment variable at runtime — it never appears in your source code or `wrangler.toml`. For local development, create a `.dev.vars` file (add this to `.gitignore`): ```text MOTHERDUCK_TOKEN="your_token_here" ``` ## Write the Worker Create `src/index.ts`. We'll build this in two parts: first the connection and routing, then the route handlers. ### Connect and route requests ```typescript import { Client } from "pg"; interface Env { MOTHERDUCK_HOST: string; MOTHERDUCK_DB: string; MOTHERDUCK_TOKEN: string; } function createClient(env: Env): Client { return new Client({ connectionString: `postgresql://user:${env.MOTHERDUCK_TOKEN}@${env.MOTHERDUCK_HOST}:5432/${env.MOTHERDUCK_DB}?sslmode=require`, connectionTimeoutMillis: 5_000, query_timeout: 60_000, }); } export default { async fetch(request: Request, env: Env): Promise { const url = new URL(request.url); if (url.pathname === "/stats") { return handleStats(env, url); } return handleDefault(env); }, }; ``` The connection string is assembled from the environment variables defined in `wrangler.toml` and the secret token. The `?sslmode=require` parameter tells `pg` to open a TLS connection, and the Workers runtime performs certificate verification. The `fetch` handler routes first and opens a database connection only inside the route handlers. That keeps validation failures on `/stats` returning `400` instead of depending on database connectivity. `connectionTimeoutMillis` fails fast when a connection cannot be opened. `query_timeout` sends client-side cancellation for queries that exceed the configured time. `statement_timeout` is not supported through the Postgres endpoint today. ### Handle route logic Add the two handler functions to the same file. The `/stats` route accepts date range parameters and returns aggregated fare data. It validates inputs before querying and uses parameterized queries (`$1`, `$2`) to prevent SQL injection — never interpolate user input directly into SQL strings. ```typescript async function handleStats(env: Env, url: URL): Promise { const startDate = url.searchParams.get("start"); const endDate = url.searchParams.get("end"); if (!startDate || !endDate) { return Response.json( { error: "Both 'start' and 'end' query parameters are required. Use YYYY-MM-DD format." }, { status: 400 } ); } const datePattern = /^\d{4}-\d{2}-\d{2}$/; if (!datePattern.test(startDate) || !datePattern.test(endDate)) { return Response.json( { error: "Invalid date format. Use YYYY-MM-DD." }, { status: 400 } ); } const client = createClient(env); try { await client.connect(); const result = await client.query( `SELECT sum(passenger_count)::INTEGER AS total_passengers, round(sum(fare_amount), 2) AS total_fare FROM nyc.taxi WHERE tpep_pickup_datetime >= $1 AND tpep_pickup_datetime < $2`, [`${startDate} 00:00:00`, `${endDate} 00:00:00`] ); return Response.json({ start: startDate, end: endDate, ...result.rows[0], }); } finally { await client.end(); } } ``` The default route returns a sample of recent taxi trips — no user input needed: ```typescript async function handleDefault(env: Env): Promise { const client = createClient(env); try { await client.connect(); const result = await client.query( `SELECT tpep_pickup_datetime AS pickup, tpep_dropoff_datetime AS dropoff, passenger_count, trip_distance, fare_amount, tip_amount, total_amount FROM nyc.taxi ORDER BY tpep_pickup_datetime DESC LIMIT 20` ); return Response.json(result.rows); } finally { await client.end(); } } ``` ## Test locally ```bash npx wrangler dev ``` Then open `http://localhost:8787/` or try the stats endpoint with a date range: ```text http://localhost:8787/stats?start=2022-11-01&end=2022-12-01 ``` If `wrangler dev` starts successfully but direct Postgres queries fail locally with `Connection terminated`, switch to the Hyperdrive setup below and use a `localConnectionString` for local testing, or run `npx wrangler dev --remote` to exercise the Cloudflare runtime directly. ## Deploy ```bash npx wrangler deploy ``` ## Using Hyperdrive for connection pooling For production workloads, [Cloudflare Hyperdrive](https://developers.cloudflare.com/hyperdrive/) provides built-in connection pooling. This reduces latency by reusing connections across Worker invocations instead of opening a new connection per request. Prefer Hyperdrive for production Workers instead of trying to manage a process-local `pg.Pool` inside the Worker. ### 1. create a Hyperdrive configuration ```bash npx wrangler hyperdrive create motherduck-db \ --connection-string="postgresql://user:$MOTHERDUCK_TOKEN@pg.us-east-1-aws.motherduck.com:5432/sample_data?sslmode=require" ``` ### 2. update wrangler.toml ```toml name = "motherduck-taxi-stats" main = "src/index.ts" compatibility_date = "2026-04-02" compatibility_flags = ["nodejs_compat"] [[hyperdrive]] binding = "MD_HYPERDRIVE" id = "" ``` ### 3. update the connection code Replace the connection string construction with: ```typescript const client = new Client({ connectionString: env.MD_HYPERDRIVE.connectionString, connectionTimeoutMillis: 5_000, query_timeout: 60_000, }); ``` Hyperdrive handles connection pooling and credential injection automatically. For local development with Hyperdrive, configure a direct connection string for `wrangler dev`: ```bash export CLOUDFLARE_HYPERDRIVE_LOCAL_CONNECTION_STRING_MD_HYPERDRIVE="postgresql://user:$MOTHERDUCK_TOKEN@pg.us-east-1-aws.motherduck.com:5432/sample_data?sslmode=require" npx wrangler dev ``` ## SSL notes Cloudflare Workers use `pg-cloudflare` for socket connections, which delegates TLS to the Workers runtime through `cloudflare:sockets`. The runtime encrypts the connection and verifies the server certificate against Cloudflare's trust store, but those verification settings are not exposed through the `pg` client. In this environment, application code uses the runtime-managed TLS configuration rather than supplying `rejectUnauthorized`, custom CA certificates, or `sslmode=verify-full`. Use `?sslmode=require` in the connection string. This tells `pg` to initiate TLS using STARTTLS, and the Workers runtime handles the actual certificate verification at the socket level. For standard Node.js environments where you can configure certificate verification directly, see [Connect from Node.js](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/nodejs). --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/drizzle # Connect from Drizzle via Postgres endpoint > Use Drizzle as a typed wrapper around the pg driver to query MotherDuck via the Postgres wire protocol [Drizzle](https://orm.drizzle.team/) is a TypeScript ORM with both relational and SQL-like query APIs. It runs in Node.js servers, Vercel functions, Cloudflare Workers, and other edge runtimes. You can use Drizzle with MotherDuck through the Postgres endpoint. Drizzle's `drizzle-orm/node-postgres` integration wraps the `pg` driver, so you get the typed `db.execute(sql\`...\`)` API and connection lifecycle management on top of the same Postgres-protocol connection covered in [Connect from Node.js](./nodejs.md). Use Drizzle here as a **typed query executor over `pg`**, not as a schema-and-migrations ORM. Drizzle's schema introspection, code-first migrations (`drizzle-kit pull` / `migrate` / `push`), and query-builder code generation all assume a Postgres backend with `pg_catalog` and Postgres DDL semantics — none of which the pg endpoint exposes. Define your MotherDuck schema separately (DuckDB client, MotherDuck UI, or SQL scripts) and use Drizzle for query execution. For connection parameters, SSL options, and limitations, see the [Postgres Endpoint reference](/sql-reference/postgres-endpoint). ## Prerequisites You'll need a [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck). Set it as an environment variable: ```bash export MOTHERDUCK_TOKEN="your_token_here" ``` Install Drizzle and `pg`: ```bash npm install drizzle-orm pg npm install --save-dev @types/pg ``` ## Connect Wrap a `pg` client with `drizzle()`. As with the bare `pg` client, pass SSL through the config object — do **not** put `sslrootcert=system` in a connection string, since node-postgres tries to read `system` as a file path and throws `ENOENT`. ```ts import pg from "pg"; import { drizzle } from "drizzle-orm/node-postgres"; import { sql } from "drizzle-orm"; const client = new pg.Client({ host: "pg.us-east-1-aws.motherduck.com", port: 5432, user: "postgres", password: process.env.MOTHERDUCK_TOKEN, database: "md:", ssl: { rejectUnauthorized: true }, }); await client.connect(); const db = drizzle(client); const { rows } = await db.execute(sql` SELECT title, score FROM sample_data.hn.hacker_news WHERE type = ${'story'} LIMIT 10 `); console.log(rows); await client.end(); ``` Use `md:` as the database name, or pass a specific database name in `database` (e.g., `database: "my_db"`). For more details, see [Attach modes](/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/). The `sql` template tag is what you'll use most. It produces parameterized queries against the pg endpoint and lets you write DuckDB SQL directly, including three-part names (`database.schema.table`), DuckDB functions, and DuckDB-specific syntax. For pure dynamic SQL with no parameters, `sql.raw("...")` works too. ## Connection pooling and timeouts For production applications, wrap a `pg.Pool` with `drizzle()` instead of sharing one checked-out `pg.Client`. Set `connectionTimeoutMillis`, `idleTimeoutMillis`, and `query_timeout` on the underlying pool. ```ts import pg from "pg"; import { drizzle } from "drizzle-orm/node-postgres"; import { sql } from "drizzle-orm"; const pool = new pg.Pool({ host: "pg.us-east-1-aws.motherduck.com", port: 5432, user: "postgres", password: process.env.MOTHERDUCK_TOKEN, database: "md:", ssl: { rejectUnauthorized: true }, max: 10, connectionTimeoutMillis: 5_000, idleTimeoutMillis: 30_000, maxLifetimeSeconds: 300, query_timeout: 60_000, }); pool.on("error", (err) => { console.error("Unexpected idle client error", err); }); const db = drizzle(pool); const { rows } = await db.execute(sql` SELECT title, score FROM sample_data.hn.hacker_news WHERE type = ${"story"} LIMIT 10 `); ``` Drizzle delegates connection lifecycle behavior to node-postgres. If you manually check out a client from the pool for transaction control, release healthy clients normally and destroy clients that saw connection-level errors with `client.release(true)`. `statement_timeout` is not supported through the Postgres endpoint today. Use node-postgres `query_timeout` on the pool for client-side cancellation. ## Read scaling and concurrency For concurrent workloads, MotherDuck's pg endpoint can route each session to a separate read replica using the `session_name` startup option — this dramatically improves throughput under concurrency. See [Session affinity and routing](/concepts/scaling-patterns/#session-affinity-and-routing) for the underlying scaling pattern. Drizzle's `Pool` doesn't expose per-connection startup options, so for read scaling you'll want a raw `pg.Client` per session: ```ts const client = new pg.Client({ host: "pg.us-east-1-aws.motherduck.com", port: 5432, user: "postgres", password: process.env.MOTHERDUCK_TOKEN, database: "md:", ssl: { rejectUnauthorized: true }, options: "-c session_name=user_1", // unique per concurrent session }); await client.connect(); const db = drizzle(client); ``` In benchmarking, `session_name` cut 5-user concurrent latency from ~16s to ~1.3s on the same workload. ## What doesn't work The pg endpoint speaks DuckDB SQL, not Postgres SQL, and doesn't expose Postgres system catalogs. Drizzle features that depend on either will fail: - **`drizzle-kit migrate`, `push`, `generate`** — these execute Postgres DDL and assume Postgres migration tracking. Manage your MotherDuck schema separately. - **`drizzle-kit pull` / `introspect`** — schema introspection queries `pg_catalog` tables that don't exist on the pg endpoint. - **`pgTable(...)` schema definitions for query-builder calls** (`db.select().from(...)`) work for simple cases but are brittle: Drizzle treats the table name as a single quoted identifier, so three-part DuckDB names (`database.schema.table`) need careful handling. Prefer `db.execute(sql\`...\`)` with explicit SQL until you know the shape you need. - **Standard pg endpoint limits** — local-file `COPY`, `INSTALL` / `LOAD`, `SET`, temp tables, and result-creation commands are not supported. See the [main pg endpoint reference](/sql-reference/postgres-endpoint) for the full list. ## SSL notes Setting `ssl: { rejectUnauthorized: true }` is the equivalent of `sslmode=verify-full` with `sslrootcert=system` in libpq — node-postgres uses Node's built-in trusted root store. For a custom CA, see the [Node.js page](./nodejs.md#ssl-notes); the same approach applies when wrapping the client with `drizzle()`. For more details on SSL options across drivers, see [SSL and certificate verification](/sql-reference/postgres-endpoint#ssl-and-certificate-verification). --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/read-scaling # Read Scaling > Learn how to scale your data applications using read scaling tokens Connecting read-heavy applications, BI tools, or fleets of AI agents with many concurrent users through a single MotherDuck account can sometimes lead to performance bottlenecks. By default, all connections using the same account share a single cloud DuckDB instance, called a "duckling". In addition to your read/write duckling, you can use Read Scaling to spin up additional read-only ducklings for read-heavy workloads. These replicas are **eventually consistent**. Results may lag a few minutes behind the latest database state. This tradeoff prioritizes high availability and performance while achieving near real-time synchronization across all replicas. Diagram summary: Horizontal scaling adds read-only Ducklings so concurrent users can run read queries across a pool. ## Configuring a read scaling duckling pool ### Creating a read scaling token To use Read Scaling, you use a read scaling access token from the **MotherDuck UI** when [generating an access token][md-access-token] or through the [REST API](/docs/sql-reference/rest-api/users-create-token/). ### Connect with a read scaling token Once you have a read scaling token, you can use it to connect to MotherDuck from any DuckDB client as you would with any other authorization token. See [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/#session-names). ### Duckling assignment Read scaling ducklings remain idle until a connection is initialized from a DuckDB client. When a DuckDB client connects to MotherDuck with a read scaling token, the connection is assigned to one of the read scaling replicas. As more users connect, additional ducklings are spun up until you reach your Read Scaling Duckling Pool size. If the number of connections exceeds your pool size, new connections are assigned to existing ducklings in a round-robin fashion. The default Read Scaling Duckling Pool Size is 4 and can be increased up to 16. This is a soft limit, so if you need more ducklings in your pool, please [contact support](https://motherduck.com/contact-us/support/). ### Permissions A read scaling token grants permission for **read operations** (`SELECT`) while restricting write and administrative operations (updating tables, creating new databases, attaching or detaching databases). ## Ensuring data freshness In read scaling mode, ducklings sync changes from the primary read-write instance within a few minutes which works for most use cases. If your application requires stricter synchronization, you can manually trigger updates to be more frequent by: 1. Calling [CREATE SNAPSHOT](/sql-reference/motherduck-sql-reference/create-snapshot.md) on the writer duckling 2. Calling [REFRESH DATABASES](/sql-reference/motherduck-sql-reference/refresh-database.md) on any read scaling ducklings This approach guarantees that readers see the most recent snapshot. ::::warning[Watch Out] Creating a snapshot of a database will interrupt any ongoing queries interacting with that database. :::: ## Best practices Here are a few tips to get the most out of MotherDuck's read scaling capabilities. ### Optimize your read scaling duckling pool size For the best experience, aim for one duckling per concurrent user to take advantage of DuckDB's single-node power and efficiency. You can scale up as much as you need by configuring a maximum pool size based on expected concurrency and cost considerations. Users are also able to share ducklings if needed. While the default limit is 16 replicas, this is a soft limit. [Get in touch with MotherDuck support](https://motherduck.com/contact-us/support/) if you need more. ### Leverage local processing where possible Consider using DuckDB WASM to run client instances directly in the browser when possible to fully utilize client resources. ### Maintain user-duckling affinity with `session_name` Diagram summary: Session affinity routes repeat connections with the same `session_name` to the same Duckling when possible, improving cache locality. To ensure users consistently connect to the same replica (improving caching and consistency), the DuckDB connection string supports the [`session_name`](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/#session-names) parameter: - Clients providing the same `session_name` value are directed to the same replica. This improves caching effectiveness, provides a more consistent view of data across queries for that user and offers better isolation between concurrent users. - This parameter can be set to the ID of a user session, a user ID, or a hashed value for privacy. By leveraging read scaling tokens and `session_name`, you can efficiently scale read operations and group user sessions for optimal performance. ### Instance caching with `dbinstance_inactivity_ttl` Some DuckDB client library integrations support an *instance cache* to keep connections to the same database instance alive for a short period after use. This improves read scaling by helping maintain session affinity even across separate queries or short connection gaps. This caching behavior boosts the effectiveness of `session_name`, making it more likely that frequent queries from the same client land on the same duckling, even with short breaks between connections. See [Connecting to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/#setting-custom-database-instance-cache-time-ttl) for more details. [md-access-token]: /key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#authentication-using-an-access-token --- Source: https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/attach-modes # Attach Modes > Understand Workspace and Single attach modes ## MotherDuck attach modes: workspace and single modes This guide explains MotherDuck's two connection modes: **workspace** and **single**. Workspace mode is designed for working with multiple databases persistently across sessions, while single mode uses a non-persistent, isolated session that does not reuse your saved workspace. :::tip **TL;DR** Use single mode for service accounts and automated workflows, use workspace mode for personal usage across sessions, for example the MotherDuck UI, your AI agent and the local DuckDB CLI. ::: ### Connection modes MotherDuck offers two connection modes: workspace and single. The mode you use determines how your attachments and detachments are handled and whether these changes are saved for future sessions. Both modes allow you to `ATTACH` databases, the difference is whether those attachments are remembered for your next session. * **Workspace Mode** is the default mode when you want to work with all attached MotherDuck databases. When you attach or detach a database in this mode, that change is remembered for your next session. This is useful when you consistently work with the same set of databases. Parallel connections to MotherDuck in workspace mode will keep their attachments in sync. E.g. detaching a database in one client in workspace mode will detach it in all other clients that are connected in workspace mode. * **Single Mode** is for when you want a one-time, non-persistent session that does not reuse or change your saved workspace. This is useful in automated workflows and minimizes the catalog size. Any databases you attach or detach during this session will not affect the saved workspace for the next time you connect or interfere with attachment state of other parallel connections to MotherDuck. You can still attach multiple databases in a single-mode session, including databases shared with you. For example, you can start with your own database and then `ATTACH 'md:_share/...'` to attach a share. Single mode is useful with BI tools that only support a single attached database at a time. :::tip You can't switch between modes in the middle of a session. The mode is set by the first command you use to connect to MotherDuck. ::: ### Connecting to MotherDuck with a connection string When you first connect to MotherDuck in a session, the connection string you use determines the attach mode. This applies to most of clients, like the DuckDB CLI (`duckdb 'md:...'`) and Python (`duckdb.connect('md:...')`). * **To connect in Workspace Mode (default):** * Use `md:` or `md:`. * This connects to your MotherDuck workspace, attaching *all* databases from your last saved session. * If you specify a database name, it becomes the active database. * Any changes to attachments (attaching or detaching databases) are saved and will be restored in your next workspace session. * **To connect in Single Mode:** * Use `md:?attach_mode=single`. * This connects to the specified database without using your saved workspace. * Attachment changes are *temporary* and will *not* be saved. * Note: You must specify a database name to use single mode. Connecting with `md:?attach_mode=single` is not allowed, as this mode requires a specific database target. ### Connecting to MotherDuck using the ATTACH command If you are already in a DuckDB session, but **not** connected to MotherDuck yet, your first ATTACH command that targets MotherDuck establishes the attach mode for that session. * **To connect in Workspace Mode:** * Use `ATTACH 'md:'`. * This attaches your entire saved workspace. * The session is now in workspace mode, and any subsequent attachment changes will be persisted for future sessions. * **To connect in Single Mode:** * Use `ATTACH 'md:'`. * This attaches the specified database without using your saved workspace. * The session is implicitly set to single mode. Attachment changes are not saved. * Once in single mode, you cannot attach the entire workspace using `ATTACH 'md:'`. ### Tips & tricks Further Notes: * You can also explicitly set the attach mode before connecting to MotherDuck. ```sql LOAD motherduck; SET motherduck_attach_mode = 'workspace'; -- or 'single' ATTACH 'md:foo'; -- database created by your account ``` * The MotherDuck UI always connects in workspace mode. --- ## Docs feedback MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`. For agents and automated tools, feedback submission should be user-confirmed before sending. URL-encode query parameter values and send a GET request: ```text GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fkey-tasks%2Fauthenticating-and-connecting-to-motherduck%2F&page_title=MotherDuck%20Documentation%20-%20Authenticating%20and%20connecting%20to%20MotherDuck&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.