# MotherDuck Documentation - Interfaces > MotherDuck Offers a variety of interfaces (APIs) for integration 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 - [Client APIs full context](https://motherduck.com/docs/getting-started/interfaces/client-apis/llms-full.txt): Client APIs for MotherDuck (11 pages; 20,601 bytes; ~5,148 tokens). [Index](https://motherduck.com/docs/getting-started/interfaces/client-apis/llms.txt). - [MotherDuck CLI full context](https://motherduck.com/docs/getting-started/interfaces/motherduck-cli/llms-full.txt): Drive MotherDuck from your terminal: run queries, build Dives and Flights, and script it all with JSON output. (5 pages; 25,966 bytes; ~6,491 tokens). [Index](https://motherduck.com/docs/getting-started/interfaces/motherduck-cli/llms.txt). ## Included documentation Source: https://motherduck.com/docs/getting-started/interfaces/connect-query-from-duckdb-cli # DuckDB CLI > Learn to connect and query databases using MotherDuck from the DuckDB CLI ## Installation :::note MotherDuck supports DuckDB client versions 1.4.1 through 1.5.5 in all regions. For the range each region supports, see [client version support](/about-motherduck/cloud-regions/#client-version-support). ::: Download and install the DuckDB binary, depending on your operating system. ### Windows The recommended way to install the CLI is with the MotherDuck install script: ### Install with PowerShell ```powershell powershell -c "irm https://install.motherduck.com | iex" ``` The script installs a MotherDuck-supported DuckDB version to `%LOCALAPPDATA%\duckdb\cli`, installs the `motherduck` extension, and can fetch and persist a MotherDuck token. If your PowerShell execution policy blocks the command above, use the `cmd.exe` fallback: ```bat curl -sfL -o install.bat https://install.motherduck.com/install.bat && install.bat ``` The `cmd.exe` script installs the `windows-amd64` build only and cannot run the interactive token flow. On ARM64, or to use the token flow, use the PowerShell script. ### Download the binary To install manually instead: 1. Download the 64-bit Windows binary [duckdb_cli-windows-amd64.zip](https://github.com/duckdb/duckdb/releases/download/v1.5.5/duckdb_cli-windows-amd64.zip) 2. Extract the zip file. ### macOS The recommended way to install the CLI is with the MotherDuck install script: ### Install with bash ```bash curl -s https://install.motherduck.com | sh ``` ### Linux The recommended way to install the CLI is with the MotherDuck install script: ### Install with sh ```bash curl -s https://install.motherduck.com | sh ``` The script detects your architecture, installs the matching `linux-amd64` or `linux-arm64` binary, and pins a MotherDuck-supported DuckDB version. ### Download the binary To install manually instead: 1. Download the Linux binary: - For 64-bit, download the binary [duckdb_cli-linux-amd64.zip](https://github.com/duckdb/duckdb/releases/download/v1.5.5/duckdb_cli-linux-amd64.zip) - For arm64/aarch64, download the binary [duckdb_cli-linux-aarch64.zip](https://github.com/duckdb/duckdb/releases/download/v1.5.5/duckdb_cli-linux-aarch64.zip) 2. Extract the zip file. For more information, see the [DuckDB installation documentation](https://duckdb.org/docs/installation/). ## Try it Walk through starting DuckDB, attaching MotherDuck, and running your first query in the playground below. Each step explains what happens before you press Enter, so you can preview the full flow before running it on your machine. Interactive CLI demo omitted from generated Markdown. Static walkthrough: ```bash duckdb ATTACH 'md:'; SHOW DATABASES; FROM duckdb_tables() WHERE database_name = 'sample_data'; ``` ## Step by step ### Start the DuckDB CLI After installing, start DuckDB from your terminal: ```sh duckdb ``` DuckDB opens an in-memory database by default, so any tables you create won't persist when you exit. Pass a filename to open or create a persistent local database: ```sh duckdb mydatabase.duckdb ``` ### Connect to MotherDuck From inside the DuckDB CLI, attach MotherDuck: ```sql ATTACH 'md:'; ``` DuckDB downloads the signed MotherDuck extension and opens your default browser to authenticate. Follow the instructions in the terminal. To list your MotherDuck databases and confirm the connection, run: ```sql SHOW DATABASES; ``` You can query local DuckDB data and MotherDuck databases from the same session. For more on persisting your authentication credentials, see [Authenticating to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/authenticating-to-motherduck.md). :::tip You can also connect to MotherDuck directly when starting DuckDB: ```bash duckdb "md:" ``` ::: :::note[Manual extension update] When MotherDuck releases a new extension version you can force-reinstall the extension from the CLI. ```sh FORCE INSTALL motherduck; ``` ::: ### Open the MotherDuck UI from the CLI Launch the MotherDuck UI from your terminal: ```bash duckdb -ui ``` If you're already in a DuckDB session, run `CALL start_ui();` instead. --- Source: https://motherduck.com/docs/getting-started/interfaces/motherduck-quick-tour # MotherDuck Web UI > A guide to the MotherDuck Web UI — write SQL with Instant SQL, use AI to fix and edit queries, and explore your data interactively. ## Getting started To log in to the MotherDuck UI, go to [app.motherduck.com](https://app.motherduck.com/). :::info You can also open the web UI directly from the DuckDB CLI: ```bash duckdb "md:" -ui ``` ::: ### Main window The MotherDuck UI is organized around a notebook-style editor with a database browser on the left and results inspection on the right. ![UI](../img/screenshot_ui.png) ## Instant SQL: write SQL with real time feedback **Instant SQL** gives you keystroke-fast query previews — results update as you type, with no run button needed. Under the hood, MotherDuck uses [Dual Execution](/concepts/architecture-and-capabilities/#dual-execution) to parse and run your query locally first, giving you immediate feedback while full cloud results load in the background. A caching indicator in the cell header shows when results are served from local cache. ### Enabling Instant SQL Toggle Instant SQL on or off per cell using: - The **Instant SQL toggle** in the cell header - The keyboard shortcut `Ctrl`/`⌘` + `Shift` + `.` ### What works with Instant SQL - **Filtering in real time:** Add or change a `WHERE` clause and watch results narrow instantly. - **Multi-statement cells:** Click on any individual statement within a multi-statement cell to preview just that one. - **Window functions:** Window functions are fully supported in Instant SQL previews. ## Fix errors and edit queries with AI MotherDuck's AI features help you fix broken queries, rewrite SQL in plain English, and generate queries from scratch — all without leaving the editor. ### "Help me fix this broken query" — FixIt When you run a query that has an error, **FixIt** automatically analyzes the error and suggests an inline fix. Click to accept and re-run in one step. By default, FixIt auto-suggests fixes whenever an error occurs. You can turn off auto-suggest and still trigger FixIt manually by clicking **Suggest fix** at the bottom of any error message. ![FixIt manual trigger](../../key-tasks/img/fixit-manual-suggestion.png) Toggle auto-suggest in **Settings → Preferences → Enable inline SQL error fix suggestions**. :::tip[Free for all users] FixIt is available on all plans, including the Lite plan (with limits). ::: ### "Modify my SQL using plain english" — edit Select text in your query (or place your cursor anywhere) and press `Ctrl`/`⌘` + `Shift` + `E` to open the **Edit** dialog. Describe what you want to change in natural language: ![Edit prompt](../../key-tasks/img/edit-prompt.png) Review the suggestion, then iterate with follow-up prompts if needed: ![Edit follow-up](../../key-tasks/img/edit-follow-up.png) When you're happy with the result, click **Apply edit** to update your query. ![Edit applied](../../key-tasks/img/edit-follow-up-2.png) ### Going further with SQL assistant functions For programmatic AI access (text-to-SQL, query explanation, schema understanding), see the [SQL Assistant functions](/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/) reference. These are available in any DuckDB client connected to MotherDuck, not just the web UI. ## Explore your results ### Interactive data grid Query results load into an interactive data grid where you can sort, filter, and pivot without writing more SQL. Click the **Expand** button at the top right of any cell to go full-screen on the editor and results. ![Expand cells](../img/screenshot_expand_cells.png) ### Column Explorer The Column Explorer shows statistics for every column in a table or result set — value frequencies, NULL percentages, histograms for numeric columns, and time-series charts for timestamp columns. Toggle the Column Explorer with `Ctrl`/`⌘` + `I` or the toggle button at the top right of the results panel. ### Cell content pane Click any cell in the results grid to see its full contents in the Cell Content Pane. ![Cell content — long text](../img/cell_content_long_text.png) For JSON columns, you can expand and collapse nodes, copy the value, or copy the key path to any nested field. ![Cell content — JSON](../img/cell_content_json.png) ## Write queries faster ### Autocomplete Autocomplete suggests SQL syntax, table names, column names, and functions as you type. Turn it off in **Settings → Preferences → Enable autocomplete when typing**. ### Inline docs Hover over any SQL function in the editor to see its description, parameter types, and return type. Click the **Docs** link in the tooltip to open the full reference. ![Image](useBaseUrl('/img/getting-started/ui-inline-docs.png')) Turn off Inline Docs in **Settings → Preferences → Enable Inline Docs**. ### Format SQL Press `Ctrl`/`⌘` + `Alt`/`⌥` + `O` to auto-format the SQL in your current cell. When text is selected, only the selection is formatted. ## Navigate the workspace ### Object explorer & Favorites ![Favorites section holding a Weekly reporting folder with a notebook and a Dive, plus a pinned share and database, above a hovered database row showing its star](require('../img/favorites.png').src) Browse your databases, schemas, and tables in the left-hand panel. Toggle it with `Ctrl`/`⌘` + `B`. Each section collapses on its own, so you can keep the tree focused on what you are working on. Pin the objects you use most to a **Favorites** section at the top of the Object explorer. Hover a database, [share](/key-tasks/sharing-data/), notebook, or [Dive](/key-tasks/dives/) and click the star. Click the star again, or choose **Remove from favorites** in the row menu, to unpin it. Use the new-folder button in the **Favorites** header to group related items, then drag rows into a folder or into the order you want. Favorites are personal to your account, so each member of an organization keeps their own set. ### Command menu Press `Ctrl`/`⌘` + `K` to open the command menu for quick access to actions, notebooks, and settings. ### Notebook and worksheet views Toggle between notebook view (multiple cells) and worksheet view (single expanded cell) with `Ctrl`/`⌘` + `E`. ### Running queries The Running Queries page, found under **Settings** → **Running Queries**, lets you monitor and manage long-running queries on your Duckling. For each query, you can see: - **Query**: The SQL text of the query (click to expand the full statement). - **Status**: Whether the query is active or has completed. - **Start time**: When the query started executing. - **Elapsed time**: How long the query has been running. This is useful for identifying queries that are taking longer than expected. You can cancel a running query directly from this page. For programmatic access to active connections and query cancellation through SQL, see [`md_active_server_connections()`](/sql-reference/motherduck-sql-reference/connection-management/monitor-connections/) and [`md_interrupt_server_connection()`](/sql-reference/motherduck-sql-reference/connection-management/interrupt-connections/). For a broader view of query activity across your organization, see the [`RECENT_QUERIES`](/sql-reference/motherduck-sql-reference/md_information_schema/recent_queries/) and [`QUERY_HISTORY`](/sql-reference/motherduck-sql-reference/md_information_schema/query_history/) views. ### Duckling overview The Duckling overview page, found under **Settings** → **Duckling overview**, gives you an at-a-glance view of activity across every Duckling in the organization over the last 24 hours. Viewing it requires permission to view organization-wide Duckling activity, which the Admin and Builder preset roles include by default. For each Duckling, you can see: - **Account**: The MotherDuck user or service account the Duckling belongs to. - **Status**: Whether the Duckling is running normally or has encountered errors. - **Spills**: Whether queries on this Duckling spilled to disk, which indicates memory pressure from larger-than-memory workloads. - **Active minutes**: How long the Duckling was actively running queries over the last 24 hours. ![Duckling overview list showing every Duckling in the organization with its account, size, active minutes, query volume, and error counts](../img/duckling-overview.png) Click a Duckling row to drill in. A bar chart visualizes query activity over time, and a table below lists individual queries. Click a query to open a side panel with the full SQL text, or open a dedicated focus page for a single query. ![Duckling overview drill-down showing summary stats, a query activity bar chart, and a table of top queries](../img/duckling-overview-drilldown.png) Use the timezone toggle in the page header to switch between UTC and your local time. This page requires permission to view organization-wide Duckling activity and is built on the [`QUERY_HISTORY`](/sql-reference/motherduck-sql-reference/md_information_schema/query_history/) view, so it has the same ingestion delay — queries from the last few seconds may not appear yet. The Admin and Builder preset roles include this permission by default. For a programmable view of the same data, or a more real-time view of ongoing queries, see the [`QUERY_HISTORY`](/sql-reference/motherduck-sql-reference/md_information_schema/query_history/) and [`RECENT_QUERIES`](/sql-reference/motherduck-sql-reference/md_information_schema/recent_queries/) views. ## Keyboard shortcuts Use `Ctrl` for Windows/Linux and `⌘` (Command) for Mac. Use `Alt` for Windows/Linux and `⌥` (Option) for Mac. ### Running queries | Command | Action | |---------|--------| | `Ctrl`/`⌘` + `Enter` | Run the current cell. | | `Ctrl`/`⌘` + `Shift` + `Enter` | Run selected text in the current cell. If no text is selected, run the whole cell. | | `Shift` + `Enter` or `Alt`/`⌥` + `Enter` | Run the current cell, then advance to the next cell (creates a new one if needed). | ### Editing | Command | Action | |---------|--------| | `Ctrl`/`⌘` + `z` | Undo within current cell. | | `Ctrl`/`⌘` + `Shift` + `z` | Redo within current cell. | | `Ctrl`/`⌘` + `Alt`/`⌥` + `o` | Format SQL in the current cell (or selection). | | `Ctrl`/`⌘` + `/` | Toggle line comments (`--`). | | `Tab` | Indent current line (in editor). | | `Shift` + `Tab` | De-indent current line (in editor). | ### AI features | Command | Action | |---------|--------| | `Ctrl`/`⌘` + `Shift` + `.` | Toggle [Instant SQL](#instant-sql-write-sql-with-real-time-feedback) on/off for the active cell. | | `Ctrl`/`⌘` + `Shift` + `e` | Open [Edit](#modify-my-sql-using-plain-english--edit) for your current cell or selected text. | ### Navigation and layout | Command | Action | |---------|--------| | `Ctrl`/`⌘` + `k` | Open the command menu. | | `Ctrl`/`⌘` + `/` | Search notebooks, databases and more. | | `Ctrl`/`⌘` + `b` | Toggle the Object Explorer (left panel). | | `Ctrl`/`⌘` + `i` | Toggle the Column Explorer (right panel). | | `Ctrl`/`⌘` + `e` | Toggle notebook/worksheet view for the active cell. | | `Ctrl`/`⌘` + `↑` | Move current cell up. | | `Ctrl`/`⌘` + `↓` | Move current cell down. | | `Esc` | Switch `Tab` to UI navigation mode (reverts on next cell selection). | ## Settings Settings are found by clicking your profile at the top-left. | Section | Setting | Description | |---------|---------|-------------| | **Organization** | Details | Changing the organization display name requires permission to update it, included in Admin by default. See [Managing organizations](/key-tasks/managing-organizations). | | | Plans | Viewing invoices and selecting a plan each require the corresponding permission, included in Admin by default. | | | Members | Viewing members and roles requires the corresponding permission, included in every preset role. Managing them requires separate permissions included in Admin. Invitations for Builder and Explorer depend on the invite policy. Members include human users and [service accounts](/key-tasks/service-accounts-guide/). | | **My Account** | Preferences | Enable [autocomplete](#autocomplete), inline [SQL error fix suggestions](#help-me-fix-this-broken-query--fixit) (FixIt), and [Inline Docs](#inline-docs). | | | Notifications | Configure notification preferences. | | | Ducklings | Manage [Duckling sizes](/about-motherduck/billing/duckling-sizes/#duckling-sizes), [Read Scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) pool size, version information, and Duckling reset for troubleshooting. | | **Integrations** | Access Tokens | Create tokens for programmatically [authenticating to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck). Tokens can have expiry dates. | | | Secrets | Storing credentials requires permission to create secrets, while removing them requires permission to delete secrets. Admin and Builder include both permissions by default. See [AWS S3](/integrations/cloud-storage/amazon-s3), [Azure Blob Storage](/integrations/cloud-storage/azure-blob-storage), and [Google Cloud Storage](/integrations/cloud-storage/google-cloud-storage). | | **Monitor** | Running Queries | View and manage active queries. | | | Duckling overview | Viewing organization-wide Duckling activity requires the corresponding permission, included in Admin and Builder by default. See [Duckling overview](#duckling-overview). | | **Data** | Databases | Browse and manage your databases. | | | Shares | View and manage [shared databases](/key-tasks/sharing-data/). | | **Content** | Dives | Manage your saved [Dives](/key-tasks/dives/). | ### Databases Under **Data** → **Databases**, viewing every database in the organization requires permission to view all organization databases, which Admin includes by default. Without it, you see databases you own and shared databases you can access. Per-database [storage breakdowns](/concepts/storage-lifecycle#breaking-down-storage-usage) require a separate permission to view organization-wide storage information, also included in Admin by default. Click a row to view its lifecycle stages. ![Databases settings page](img/databases.png) ### Shares Under **Data** → **Shares**, view and manage the databases you've [shared](/key-tasks/sharing-data/) and the ones shared with you. ![Shares settings page](img/shares.png) ### Access tokens Under **Integrations** → **Access Tokens**, create and revoke tokens for [authenticating to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) from the CLI, Python, or other clients. ![Access tokens settings page](img/tokens.png) ### Dives Under **Content** → **Dives**, find every [Dive](/key-tasks/dives/) in your organization, including those created by teammates. ![Dives list page](img/dives.png) --- Source: https://motherduck.com/docs/getting-started/interfaces/postgres-endpoint # Postgres endpoint > Query MotherDuck from any Postgres-compatible client without installing DuckDB MotherDuck's Postgres endpoint lets you query your databases using any client that speaks the PostgreSQL wire protocol, no DuckDB installation required. This is ideal for serverless environments, BI tools, or languages without a DuckDB SDK. ## Quick start with psql Set your access token and connect: ```bash export MOTHERDUCK_TOKEN="your_token_here" PGPASSWORD=$MOTHERDUCK_TOKEN psql \ -h pg.us-east-1-aws.motherduck.com \ -p 5432 \ -U postgres \ "dbname=sample_data sslmode=verify-full sslrootcert=system" ``` Run a query: ```sql SELECT title, score FROM sample_data.hn.hacker_news WHERE type = 'story' ORDER BY score DESC LIMIT 5; ``` ## Quick start with Python ```python # /// script # dependencies = ["psycopg"] # /// import psycopg, os conn = psycopg.connect( host="pg.us-east-1-aws.motherduck.com", port=5432, dbname="sample_data", user="postgres", password=os.environ["MOTHERDUCK_TOKEN"], sslmode="verify-full", sslrootcert="system", ) with conn.cursor() as cur: cur.execute("SELECT title, score FROM sample_data.hn.hacker_news WHERE type='story' LIMIT 5") for row in cur: print(row) conn.close() ``` ## 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. - The Postgres endpoint is best for query execution, DDL and DML on MotherDuck tables, metadata inspection, and server-side reads from remote storage. - Features that depend on DuckDB client session state, such as temporary tables or result creation, require a DuckDB client path instead. - Always connect with **SSL enabled** (`sslmode=verify-full` recommended). - Use your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) as the password. ## Next steps - [Postgres Endpoint reference](/sql-reference/postgres-endpoint) — connection parameters, SSL options, session options, and known limitations - [Connect from Python](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/python) — psycopg2 and psycopg3 setup - [Connect from Java](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/java) — PostgreSQL JDBC driver setup - [Connect from Node.js](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/nodejs) — node-postgres setup - [Connect from Cloudflare Workers](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint/cloudflare-workers) — serverless edge deployment --- Source: https://motherduck.com/docs/getting-started/interfaces/third-party-tools # Third-Party Tools with PostgreSQL > Connect third-party tools and IDEs to MotherDuck using the Postgres wire protocol endpoint :::info[Preview feature] The Postgres endpoint is in preview. Functionality and compatibility may change as we expand support. ::: MotherDuck's [Postgres endpoint](/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint) lets you connect third-party tools and database IDEs that do not support DuckDB or MotherDuck directly, but do support PostgreSQL data sources. ## Compatibility | Tool | Status | Notes | |------|--------|-------| | psql | Supported | Full support through the CLI. | | DBeaver | Basic querying | Querying works. Schema browser may show extra objects from other databases. Use `attach_mode=single` (see below). | | Tableau | Planned | Tracking internally. | | Looker | Planned | Under evaluation. | | Metabase | Supported | See [Metabase integration guide](/integrations/bi-tools/metabase). | | Qlik | Supported | | ## General connection guidance When connecting any Postgres-compatible tool, use the following connection parameters: | Parameter | Value | |-----------|-------| | **Host** | `pg.-aws.motherduck.com` | | **Port** | `5432` | | **Database** | Your MotherDuck database name | | **User** | postgres | | **Password** | Your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck) | ### Use single attach mode For the best experience with IDEs and BI tools, set `attach_mode=single` so the tool only sees objects from your target database. Without this, schema browsers may display tables from all attached databases. ### Setting connection options If the tool supports `PGOPTIONS` or connection options you can also set these: ```bash PGOPTIONS="--attach_mode=single" ``` See [Attach Modes](/key-tasks/authenticating-and-connecting-to-motherduck/attach-modes/) for more details. ### Remember: you're writing DuckDB SQL The Postgres endpoint delivers DuckDB SQL over the PostgreSQL wire protocol. Use [DuckDB SQL syntax](https://duckdb.org/docs/sql/introduction) in your queries. PostgreSQL-specific functions and features are not available. ### Use a secure connection Use your own (system) SSL certificate to make sure you connect securely to the Postgres endpoint. This is done by setting sslmode="verify-full" and sslrootcert="system, which is available since Postgres version >=16. If you do not have a certificate available, you can also use a certificate from a certificate authority like Let's Encrypt at `https://letsencrypt.org/certs/isrgrootx1.pem`. You can download and use this certificate instead: `sslmode=verify-ca sslrootcert=isrgrootx1.pem`. If none of these options work you can fall back to the less secure `sslmode=require`. ## Request support for a tool Want BI tool support for a tool not listed above? Reach out to [support@motherduck.com](mailto:support@motherduck.com). --- Source: https://motherduck.com/docs/getting-started/interfaces/interfaces # MotherDuck Interfaces > MotherDuck Offers a variety of interfaces (APIs) for integration ## Client interfaces ## Included pages - [Client APIs](https://motherduck.com/docs/getting-started/interfaces/client-apis): Client APIs for MotherDuck - [MotherDuck CLI](https://motherduck.com/docs/getting-started/interfaces/motherduck-cli): Drive MotherDuck from your terminal: run queries, build Dives and Flights, and script it all with JSON output. - [Install and connect with the DuckDB CLI](https://motherduck.com/docs/getting-started/interfaces/connect-query-from-duckdb-cli): Learn to connect and query databases using MotherDuck from the DuckDB CLI - [MotherDuck Web UI](https://motherduck.com/docs/getting-started/interfaces/motherduck-quick-tour): A guide to the MotherDuck Web UI — write SQL with Instant SQL, use AI to fix and edit queries, and explore your data interactively. - [Postgres endpoint](https://motherduck.com/docs/getting-started/interfaces/postgres-endpoint): Query MotherDuck from any Postgres-compatible client without installing DuckDB - [Third-Party Tools with PostgreSQL](https://motherduck.com/docs/getting-started/interfaces/third-party-tools): Connect third-party tools and IDEs to MotherDuck using the Postgres wire protocol endpoint --- Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/index # Client APIs > Client APIs for MotherDuck MotherDuck works with all DuckDB client APIs. Choose your preferred language or driver below. ## Included pages - [Python](https://motherduck.com/docs/getting-started/interfaces/client-apis/python): Connect and query MotherDuck from Python - [Other client APIs](https://motherduck.com/docs/getting-started/interfaces/client-apis/other): Other DuckDB client APIs that work with MotherDuck --- Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/python/installation-authentication # Installation & authentication > How to install DuckDB and connect to MotherDuck ## Prerequisites MotherDuck Python supports the following operating systems: - Linux (x64, glibc v2.31+, equivalent to ubuntu v20.04+) - Mac OSX 11+ (M1/ARM or x64) - Python 3.4 or later Please let us know if your configuration is unsupported. ## Installing DuckDB :::note MotherDuck supports DuckDB client versions 1.4.1 through 1.5.5 in all regions. For the range each region supports, see [client version support](/about-motherduck/cloud-regions/#client-version-support). ::: Use the following `pip` command to install the supported version of DuckDB: 

{`pip install duckdb==${ duckdbVersionRanges["us-east-1"].max }`}

## Connect to MotherDuck

You can connect to and work with multiple local and MotherDuck-hosted DuckDB databases at the same time. The connection syntax varies depending on how you’re opening local DuckDB and MotherDuck.

### Authenticating to MotherDuck

You can authenticate to MotherDuck using either browser-based authentication or an access token. Here are examples of both methods:

#### Using browser-based authentication

```python
import duckdb

# connect to MotherDuck using 'md:' or 'motherduck:'
con = duckdb.connect('md:')
```

When you run this code:

1. A URL and a code will be displayed in your terminal.
2. Your default web browser will automatically open to the URL.
3. You'll see a confirmation request to approve the connection.
4. Once, approved, if you're not already logged in to MotherDuck, you'll be prompted to do so.
5. Finally, you can close the browser tab and return to your Python environment.

This method is convenient for interactive sessions and doesn't require managing access tokens.

#### Using an access token

For automated scripts or environments where browser-based auth isn't suitable, you can use an access token:

```python
import duckdb

# Initiate a MotherDuck connection using an access token
con = duckdb.connect('md:?motherduck_token=')
```

Replace `` with an actual token generated from the MotherDuck UI.

To learn more about creating and managing access tokens, as well as other authentication options, see our guide on [Authenticating to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/authenticating-to-motherduck.md).

### Connecting to MotherDuck

Once you've authenticated, you can connect to MotherDuck and start working with your data. Let's look at a few common scenarios.

#### Connecting directly to MotherDuck

Here's how to connect to MotherDuck and run a simple query:

```python
import duckdb

# Connect to MotherDuck via browser-based authentication
con = duckdb.connect('md:my_db')

# Run a query to verify the connection
con.sql("SHOW DATABASES").show()
```

:::tip
When connecting to MotherDuck, you need to specify a database name (like `my_db` in the example). If you're a new user, a default database called `my_db` is automatically created when your account is first set up. You can query any table in your connected database by just using its name. To switch databases, use the `USE` command.
:::

#### Working with both MotherDuck and local databases

MotherDuck lets you work with both cloud and local databases simultaneously. Here's how:

````python
import duckdb

# Connect to MotherDuck first, specifying a database
con = duckdb.connect('md:my_db')

# Then attach local DuckDB databases
con.sql("ATTACH 'local_database1.duckdb'")
con.sql("ATTACH 'local_database2.duckdb'")

# List all connected databases
con.sql("SHOW DATABASES").show()
````

#### Adding MotherDuck to an existing local connection

If you're already working with a local DuckDB database, you can add a MotherDuck connection:

````python
import duckdb

# Start with a local DuckDB database
local_con = duckdb.connect('local_database.duckdb')

# Add a MotherDuck connection, specifying a database
local_con.sql("ATTACH 'md:my_db'")
````

This is another approach to give you the flexibility to work with both local and cloud data in the same session.

---

Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/python/choose-database

# Specify MotherDuck database
> Specify MotherDuck database
When you connect to MotherDuck you can specify a database name or omit the database name and connect to the default database.

- If you use `md:` without a database name, you connect to a default MotherDuck database called `my_db`.
- If you use `md:`, you connect to the `` database.

After you establish the connection, either the default database or the one you specify becomes the current database.

You can run the `USE` command to switch the current database, as shown in the following example.

```python
#list the current database
con.sql("SELECT current_database()").show()
# ('database1')

#switch the current database to database2
con.sql("USE database2")
```

To query a table in the current database, you can specify just the table name. To query a table in a different database, you can include the database name when you specify the table. You don't need to switch the current database. The following examples demonstrate each method.

```sql
#querying a table in the current database
con.sql("SELECT count(*) FROM mytable").show()

#querying a table in another database
con.sql("SELECT count(*) FROM another_db.another_table").show()
```

---

Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/python/loading-data-into-md

# Loading data into MotherDuck with Python
> Load CSV, Parquet, and JSON files into MotherDuck from local, S3, or HTTPS sources using Python.
## Copying a table from a local DuckDB database into MotherDuck

You can use `CREATE TABLE AS SELECT` to load CSV, Parquet, and JSON files into MotherDuck from either local, Amazon S3, or https sources as shown in the following examples.

```python
# load from local machine into table mytable of the current/active used database
con.sql("CREATE TABLE mytable AS SELECT * FROM '~/filepath.csv'");
# load from an S3 bucket into table mytable of the current/active database
con.sql("CREATE TABLE mytable AS SELECT * FROM 's3://bucket/path/*.parquet'")
```

If the source data matches the table’s schema exactly you can also use `INSERT INTO ... SELECT` to append data, as shown in the following example.

```python
# append to table mytable in the currently selected database from S3
con.sql("INSERT INTO mytable SELECT * FROM ‘s3://bucket/path/*.parquet’")
```

:::tip
Use `INSERT INTO ... SELECT` to load data from files as shown above. Do not use single-row `INSERT INTO ... VALUES` statements in a loop — this is significantly slower because each statement incurs separate network overhead. See [Loading data best practices](/key-tasks/loading-data-into-motherduck/considerations-for-loading-data/) for more detail.
:::

## Copying an entire local DuckDB database to MotherDuck

MotherDuck supports copying your opened DuckDB database into a MotherDuck database. The following example copies a local DuckDB database named `localdb` into a MotherDuck-hosted database named `clouddb`.

```python
 # open the local db
local_con = duckdb.connect("localdb.ddb")
# connect to MotherDuck
local_con.sql("ATTACH 'md:'")
# The from indicates the file to upload. An empty path indicates the current database
local_con.sql("CREATE DATABASE clouddb FROM CURRENT_DATABASE()")
```

A local DuckDB database can also be copied by its file path:

```sql
local_con = duckdb.connect("md:")
local_con.sql("CREATE DATABASE clouddb FROM 'localdb.ddb'")
```

See [Loading Data into MotherDuck](/key-tasks/loading-data-into-motherduck/loading-data-into-motherduck.mdx) for more detail.

---

Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/python/query-data

# Query data
> Execute SQL queries against MotherDuck using Python with hybrid local and cloud execution.
For more information about database manipulation, see [MotherDuck SQL reference](/docs/sql-reference/motherduck-sql-reference/).

MotherDuck uses DuckDB under the hood, so nearly all [DuckDB SQL](https://duckdb.org/docs/) works in MotherDuck without differences.

MotherDuck uses [Dual Execution](/concepts/architecture-and-capabilities/#dual-execution) to decide where each part of a query runs, including across more than one location at once. If your data lives on your laptop, MotherDuck runs the query against that data on your laptop. If you are joining data on your laptop to data on Amazon S3, MotherDuck runs each part of the query where the data lives before bringing the results together locally.

## Querying data in MotherDuck

You can query data loaded into MotherDuck the same way you query data in your DuckDB databases. MotherDuck executes these queries using resources in the cloud.

```sql
# table table_name is in MotherDuck storage
con.sql("SELECT * FROM table_name").show();
```

## Querying data on your machine

You can use MotherDuck to query files on your local machine. These queries execute using your machine's resources.

```sql
# query a Parquet file on your local machine
con.sql("SELECT * FROM '~/file.parquet'").show();

# query a table in a local DuckDB database
con.sql("SELECT * FROM local_table").show();
```

## Joining data across multiple locations

You can use MotherDuck to join data:

- In MotherDuck
- On S3 or other cloud object stores (Azure, GCS, R2, etc)
- On your local machine

## What's next ?
Ready to share your DuckDB data with your colleagues? Read up on [Sharing In MotherDuck](/key-tasks/sharing-data/sharing-data.mdx).

---

Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/python/index

# Python


> Connect and query MotherDuck from Python

Learn how to connect to MotherDuck and query your data using Python.

## Included pages

- [DuckDB Python installation and authentication](https://motherduck.com/docs/getting-started/interfaces/client-apis/python/installation-authentication): How to install DuckDB and connect to MotherDuck
- [Specify MotherDuck database](https://motherduck.com/docs/getting-started/interfaces/client-apis/python/choose-database): Specify MotherDuck database
- [Loading data into MotherDuck with Python](https://motherduck.com/docs/getting-started/interfaces/client-apis/python/loading-data-into-md): Load CSV, Parquet, and JSON files into MotherDuck from local, S3, or HTTPS sources using Python.
- [Query data](https://motherduck.com/docs/getting-started/interfaces/client-apis/python/query-data): Execute SQL queries against MotherDuck using Python with hybrid local and cloud execution.

---

Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/other/c

# C
> MotherDuck + C
The MotherDuck integration with C is no different than DuckDB. For more information, see [C](https://duckdb.org/docs/stable/clients/c/overview.html) in DuckDB Documentation.

---

Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/other/odbc

# ODBC
> Connect to MotherDuck with the DuckDB ODBC driver, and authenticate a DSN with an access token instead of a browser sign-in.
The MotherDuck integration with ODBC is no different than DuckDB. For more information, see [ODBC](https://duckdb.org/docs/stable/clients/odbc/overview.html) in DuckDB Documentation.

To reach MotherDuck, set the **Database** field of your DSN to a MotherDuck database with the `md:` prefix:

```text
md:my_database
```

## Authenticating with an access token

With no token configured, the driver opens a browser sign-in prompt, and every application that uses the DSN prompts again each session. To authenticate the DSN itself, append your [access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck/#creating-an-access-token) to the **Database** field as a connection string parameter:

```text
md:my_database?motherduck_token=
```

This works for any application that reads the DSN, including tools that don't expose a separate field for driver connection properties.

Add any other [connection string parameter](/sql-reference/connection-string-parameters) the same way, separated by `&`. BI tools often benefit from `attach_mode=single`, because their catalog browsers can be confused by multiple attached databases:

```text
md:my_database?motherduck_token=&attach_mode=single
```

To serve several people from one DSN, create the token on a service account and give it a [read scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/read-scaling.mdx) pool size that matches your expected number of concurrent users.

### Windows DSN fields truncate long tokens

:::warning
**The ODBC Data Source Administrator shortens the Database field.** The text box holds around 255 characters and saves a truncated value without reporting an error. A truncated token fails to authenticate, so the connection falls back to the browser prompt.
:::

Write the full value into the registry instead:

1. Open Registry Editor by running `regedit`.
2. Navigate to the key for your DSN:
   - System DSN: `HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\`
   - 32-bit System DSN: `HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\ODBC\ODBC.INI\`
   - User DSN: `HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\`
3. Double-click `Database`, paste the full `md:my_database?motherduck_token=` value, and click OK.

Restart the application that uses the DSN to pick up the change.

---

Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/other/rust

# Rust
> MotherDuck + Rust
The MotherDuck integration with Rust is no different than DuckDB. For more information, see [Rust](https://duckdb.org/docs/stable/clients/rust.html) in DuckDB Documentation.

---

Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/other/wasm

# WebAssembly (Wasm)
> MotherDuck + WebAssembly
The MotherDuck offers its own fork of DuckDB Wasm, which is [documented here](/sql-reference/wasm-client/).

For more information about DuckDB Wasm, see [WebAssembly](https://duckdb.org/docs/stable/clients/wasm/overview.html) in DuckDB Documentation.

---

Source: https://motherduck.com/docs/getting-started/interfaces/client-apis/other/index

# Other client APIs


> Other DuckDB client APIs that work with MotherDuck

MotherDuck has dedicated guides for several DuckDB client APIs:

- [Go driver](/integrations/language-apis-and-drivers/go-driver/)
- [JDBC driver](/integrations/language-apis-and-drivers/jdbc-driver/)
- [Node.js](/integrations/language-apis-and-drivers/node-js/)
- [R](/integrations/language-apis-and-drivers/r/)

For other DuckDB client APIs, use the pages below. For the complete list of client APIs, see the [DuckDB documentation](https://duckdb.org/docs/stable/clients/overview.html).

## Included pages

- [C](https://motherduck.com/docs/getting-started/interfaces/client-apis/other/c): MotherDuck + C
- [ODBC](https://motherduck.com/docs/getting-started/interfaces/client-apis/other/odbc): Connect to MotherDuck with the DuckDB ODBC driver, and authenticate a DSN with an access token instead of a browser sign-in.
- [Rust](https://motherduck.com/docs/getting-started/interfaces/client-apis/other/rust): MotherDuck + Rust
- [WebAssembly (Wasm)](https://motherduck.com/docs/getting-started/interfaces/client-apis/other/wasm): MotherDuck + WebAssembly

---

Source: https://motherduck.com/docs/getting-started/interfaces/motherduck-cli/index

# MotherDuck CLI
> Drive MotherDuck from your terminal: run queries, build Dives and Flights, and script it all with JSON output.
The MotherDuck CLI drives MotherDuck from your terminal. Use it to sign in, run
queries, and build [Dives](/key-tasks/dives/) and
[Flights](/key-tasks/flights/) without leaving your editor.

It's built for people and for AI agents alike. Every command that returns
structured results takes `--output json`. Agents can create and publish a Dive
or a Flight from local files, and read the built-in guides for working with
Dives and Flights.

```bash
curl -s https://install.motherduck.com | SKIP_DUCKDB_CLI=1 sh
motherduck login
motherduck query "SELECT count(*) FROM sample_data.nyc.taxi"
```

## Where to start

| Page | What it covers |
|---|---|
| [Install and upgrade](./install.md) | Getting the CLI onto macOS, Linux, or Windows, and keeping it current |
| [Authentication](./authentication.md) | Signing in, and using tokens in CI |
| [Quickstart](./quickstart.md) | A full workflow: query your data, build a Dive, publish it, and script it with JSON output |
| [Working with agents](./agents.md) | Letting an AI agent author Dives and Flights through the CLI |
| [Command reference](/sql-reference/motherduck-cli/) | Every command, argument, and option |

## Commands

| Command | What it does |
|---|---|
| [`dive`](/sql-reference/motherduck-cli/dive/) | Build, preview, and publish Dives |
| [`flight`](/sql-reference/motherduck-cli/flight/) | Build Flights, then schedule and operate their runs |
| [`login`](/sql-reference/motherduck-cli/login/), [`logout`](/sql-reference/motherduck-cli/logout/) | Sign in through your browser, or remove the saved token |
| [`new`](/sql-reference/motherduck-cli/new/) | Create a MotherDuck account and organization, and sign in with it |
| [`query`](/sql-reference/motherduck-cli/query/) | Run SQL and write the results to stdout |
| [`status`](/sql-reference/motherduck-cli/status/) | Show who you're signed in as and what you're connected to |
| [`upgrade`](/sql-reference/motherduck-cli/upgrade/) | Move to the latest CLI release |

Run `motherduck --help`, or `motherduck  --help`, to get the same
information at the terminal. The deepest level carries the examples.

---

Source: https://motherduck.com/docs/getting-started/interfaces/motherduck-cli/install

# Install and upgrade
> Install the MotherDuck CLI on macOS, Linux, or Windows, keep it current with motherduck upgrade, and control where it stores its files.
## Quick install

### macOS

```bash
curl -s https://install.motherduck.com | SKIP_DUCKDB_CLI=1 sh
```

Runs on Apple silicon (aarch64) and Intel (x86_64).

### Linux

```bash
curl -s https://install.motherduck.com | SKIP_DUCKDB_CLI=1 sh
```

Runs on aarch64 and x86_64, and needs glibc. Only glibc builds are published,
so musl-based distributions such as Alpine stop with an error rather than a
failed exec. 32-bit hosts do the same.

### Windows

```powershell
powershell -c "$env:SKIP_DUCKDB_CLI=1; irm https://install.motherduck.com | iex"
```

Runs on aarch64 and x86_64. Where PowerShell's execution policy blocks this,
see [Windows without PowerShell](#windows-without-powershell).

The installer downloads the build for your platform, installs it under
`~/.motherduck/`, and puts it on your `PATH`.

Open a new shell so the `PATH` change applies, then check the install:

```bash
motherduck --version
```

:::note
The MotherDuck CLI bundles the DuckDB library. The minimal version of DuckDB bundled is the supported DuckDB version.
:::

## Windows without PowerShell

On hosts where PowerShell's execution policy blocks the quick install,
`install.bat` installs the DuckDB CLI only, then points at the PowerShell
installer for the MotherDuck CLI:

```bat
curl -sfL -o install.bat https://install.motherduck.com/install.bat && install.bat
```

It can't detect ARM64 or install the MotherDuck CLI, so use the PowerShell
script wherever you can.

## Upgrade

```bash
motherduck upgrade
```

Replaces the MotherDuck CLI binary on your `PATH`. It does not upgrade the
DuckDB CLI in `~/.duckdb/`, which has its own version: update that through
[DuckDB's own installation](/getting-started/interfaces/connect-query-from-duckdb-cli.mdx#installation).
On a CLI that's already current, `upgrade` says so rather than downloading
again.

## Next steps

- [Sign in](./authentication.md), or create an account with `motherduck new`
- [Quickstart](./quickstart.md)
- [Command reference](/sql-reference/motherduck-cli/)

---

Source: https://motherduck.com/docs/getting-started/interfaces/motherduck-cli/authentication

# Authentication
> Sign the MotherDuck CLI in through your browser, on a headless machine, or with a token in CI.
The CLI needs a credential before it can do anything but print help. There are
two ways to give it one, and which fits depends on who's at the keyboard.

| Approach | Use it when |
|---|---|
| [Sign in](#signing-in) with `motherduck login` | You have an account, or you're about to [sign up](https://app.motherduck.com/) for one |
| [Set a token](#using-access-tokens-in-unattended-environments) | An unattended run needs credentials: CI, a container, a scheduled job |
| [`motherduck new`](/sql-reference/motherduck-cli/new/) | There's no account to sign in to yet, and you want one from the terminal |

## Signing in

```bash
motherduck login
```

This opens your browser, completes an OAuth device flow, and saves the token to
`~/.motherduck/credentials.json`, in plain text. Later commands read it from
there, so you sign in once per machine, and
[`motherduck logout`](#signing-out) deletes the file.

:::tip
Set `MOTHERDUCK_HOME` to override where the credential files and the asset
cache are stored. This gives parallel runs in CI and sandboxes an isolated
environment each.

```bash
export MOTHERDUCK_HOME=/workspace/.motherduck
```

:::

On a machine with no browser, start the headless login flow. Open the printed
sign in URL on any other device, then resume:

```bash
motherduck login --headless
motherduck login --device-code 
```

The first command prints a device code and returns rather than polling. Pass
that code to the second command to complete the sign in.

Check the result at any time:

```bash
motherduck status
```

## Using access tokens in unattended environments

For CI and other unattended runs, set a token rather than signing in. The CLI
reads `MOTHERDUCK_TOKEN` before it looks at the saved credentials, so it wins
wherever both exist:

```bash
export MOTHERDUCK_TOKEN=
```

`motherduck status` reports which credential is active, under **Token source**. When
a command touches an account you didn't expect, read that row first.

## Signing out

```bash
motherduck logout
```

This removes the saved token. It has no effect on `MOTHERDUCK_TOKEN`, so unset
that variable too if you set it.

## Related

- [`login`](/sql-reference/motherduck-cli/login/), [`logout`](/sql-reference/motherduck-cli/logout/), and [`status`](/sql-reference/motherduck-cli/status/) in the command reference
- [`new`](/sql-reference/motherduck-cli/new/) creates an account and organization when there isn't one to sign in to
- [Securing read-only access](../../../key-tasks/ai-and-motherduck/securing-read-only-access.mdx)

---

Source: https://motherduck.com/docs/getting-started/interfaces/motherduck-cli/quickstart

# Quickstart
> Query MotherDuck from the terminal, build a Dive from the result, publish it, and script the whole thing with JSON output.
This walkthrough goes from an empty terminal to a published Dive: you'll
explore data with `motherduck query`, save a result as a table, build a small
React app on top of it, and publish it. The last section shows how to drive the
same commands from a script with `--output json`.

It takes about ten minutes.

## Before you begin

[Install the CLI](./install.md) and sign in:

```bash
motherduck login
```

Without a MotherDuck account, [`motherduck new`](/sql-reference/motherduck-cli/new/) creates
one from the terminal and leaves you signed in to it.

Confirm which account you're working in:

```bash
motherduck status
```

This walkthrough uses `sample_data`, which is attached to every account, and
writes one table into your default database, `my_db`.

## Step 1: Explore the data

`motherduck query` runs SQL and writes the result to stdout. Start by looking
at what's in the sample taxi table:

```bash
motherduck query "DESCRIBE sample_data.nyc.taxi"
```

Then shape the numbers you want to chart, daily trip counts and average fares
for one month:

```bash
motherduck query "
  SELECT strftime(tpep_pickup_datetime, '%Y-%m-%d') AS trip_day,
         count(*) AS trips,
         round(avg(fare_amount), 2) AS avg_fare
  FROM sample_data.nyc.taxi
  WHERE tpep_pickup_datetime >= '2022-11-01'
    AND tpep_pickup_datetime < '2022-12-01'
  GROUP BY ALL
  ORDER BY trip_day
  LIMIT 5
"
```

That prints one row per day, with the trip count and average fare.

Long statements are easier to keep in a file. `--file` reads one, and
`--timeout` raises the 120-second default when a statement needs it:

```bash
motherduck query --file daily_trips.sql --timeout 600
```

## Step 2: Save the result as a table

A Dive queries MotherDuck live, so give it something to read. Drop the `LIMIT`
and write the result into `my_db`:

```bash
motherduck query "
  CREATE OR REPLACE TABLE my_db.main.taxi_daily AS
  SELECT strftime(tpep_pickup_datetime, '%Y-%m-%d') AS trip_day,
         count(*) AS trips,
         round(avg(fare_amount), 2) AS avg_fare
  FROM sample_data.nyc.taxi
  WHERE tpep_pickup_datetime >= '2022-11-01'
    AND tpep_pickup_datetime < '2022-12-01'
  GROUP BY ALL
"
```

## Step 3: Scaffold the Dive

```bash
motherduck dive init taxi_trips --title "Taxi trips"
```

That creates `taxi_trips/`, holding the component and its metadata file.
Nothing has reached MotherDuck yet.

## Step 4: Write the component

Replace `taxi_trips/index.tsx` with a chart over the table you created:

```tsx
import { useSQLQuery } from '@motherduck/react-sql-query';
import { Bar, BarChart, ResponsiveContainer, Tooltip, XAxis, YAxis } from 'recharts';

export const REQUIRED_DATABASES = [
  { type: 'database', path: 'md:my_db', alias: 'my_db' },
];

const N = (value: unknown): number => (value == null ? 0 : Number(value));

export default function TaxiTrips() {
  const dailyQuery = useSQLQuery(`
    SELECT trip_day, trips, avg_fare
    FROM "my_db"."main"."taxi_daily"
    ORDER BY trip_day
  `);

  const rows = Array.isArray(dailyQuery.data) ? dailyQuery.data : [];
  const chartData = rows.map((row) => ({
    day: String(row.trip_day),
    trips: N(row.trips),
  }));

  return (
    

      
NYC taxi trips, November 2022

      {dailyQuery.isLoading ? (
        
Loading trips...

      ) : (
        
          
            
            
            
            
          
        
      )}
    

  );
}
```

`REQUIRED_DATABASES` is the part `push` reads. It takes the Dive's dependency
list from that export, so there's nothing to keep in step by hand.

The rest — the query API, the numeric conversion, the quoted table name —
follows the Dive authoring guide. Run `motherduck dive guide` before writing or
editing a Dive. It ships with the CLI, so it describes the runtime you actually
have.

## Step 5: Preview it locally

```bash
motherduck dive watch taxi_trips
```

This serves the Dive at `http://127.0.0.1:5173` and re-renders it on every
save, against your live MotherDuck data. Edit `index.tsx` and watch the chart
change. `--port` picks another port, and `--no-open` leaves the browser alone.

## Step 6: Publish it

```bash
motherduck dive push taxi_trips
```

The first push creates the Dive, records its ID in `dive.metadata.json`, and
prints the URL to open. Every later push adds a version:

```bash
motherduck dive push taxi_trips --version-description "add the fare axis"
motherduck dive list-versions taxi_trips
```

## Step 7: Read the output as JSON

Everything above also works unattended. `-o json` names the resource a command
acted on, so a script can pull one value out with `jq`:

```bash
DIVE_URL=$(motherduck dive push taxi_trips -o json | jq -r '.dive.url')
echo "Published to $DIVE_URL"
```

`query` is the exception, returning rows as a bare array. Failures exit
non-zero across every command, with an error object in place of the result. See
[output formats](/sql-reference/motherduck-cli/#output-formats) for the shapes.

Because the exit code is meaningful, a query can gate the rest of a script:

```bash
if ! motherduck query --file checks.sql -o json > result.json; then
  echo "checks failed" >&2
  exit 1
fi
```

`csv` suits results that are naturally tabular:

```bash
motherduck query "SELECT * FROM my_db.main.taxi_daily" -o csv > taxi_daily.csv
motherduck dive list -o csv > dives.csv
```

In CI, skip `motherduck login` and pass a token instead. See
[authentication](./authentication.md#using-access-tokens-in-unattended-environments).

## Clean up

```bash
motherduck dive delete --dive 
motherduck query "DROP TABLE my_db.main.taxi_daily"
```

`dive delete` asks you to confirm. Your local `taxi_trips/` directory stays
where it is.

## Next steps

- [Command reference](/sql-reference/motherduck-cli/) for every command and option
- [`flight`](/sql-reference/motherduck-cli/flight/) to run a Python pipeline on a schedule
- [Working with agents](./agents.md) to let an AI agent do all of this
- [Dives](/key-tasks/dives/) for theming, embedding, and governance

---

Source: https://motherduck.com/docs/getting-started/interfaces/motherduck-cli/agents

# Work with agents
> Let an AI agent author Dives and Flights through the MotherDuck CLI, using the built-in authoring guides and JSON output.
The MotherDuck CLI is designed for both AI agents and people.
An agent can read authoring guides to learn how to best build Dives and Flights with the CLI.
Because the CLI works through files and stdout rather than tool results, it
handles large files and multi-step automation with far less context than the
[MCP server](../../../key-tasks/ai-and-motherduck/mcp-setup.mdx), which is the
better fit for exploring data from a chat client. See
[choosing between the CLI and MCP](#choosing-between-the-cli-and-mcp).

Whether through an agent, in your local development environment, or in CI, the
CLI lets you create, publish, and automate your MotherDuck workflows with output
both humans and machines can understand.

## Point the agent at the built-in guides

`motherduck dive guide` and `motherduck flight guide` print the authoring
guide for each. They cover the shape the runtime requires, the query APIs, the
libraries you can import, and the patterns that don't work.

```bash
motherduck dive guide
motherduck flight guide
```

These guides are long and specific, which is what an agent needs. Have the agent
run the relevant one before it writes any code, and you avoid the usual failure
where a model invents a component or an import the runtime doesn't have.

:::tip
Put the instruction in your project's agent memory file, such as `CLAUDE.md` or
`AGENTS.md`, so it applies to every session:

```markdown
Before writing or editing a Dive or a Flight, get the latest instructions from
the output of running `motherduck [dive | flight] guide`.
```

:::

## Give the agent a task

With the guides available, the prompt can stay short. Ask for the outcome and
let the agent discover the rest:

```text
Build a Dive that charts daily taxi trip counts and average fare for
November 2022 from sample_data.nyc.taxi, with a day-of-week filter.
Preview it locally, and once it renders, publish it.
```

A capable agent works through something close to this:

```bash
motherduck dive guide                      # read the authoring guide
motherduck query "DESCRIBE sample_data.nyc.taxi" --output json
motherduck dive init taxi_trips --title "Taxi trips"   # scaffold the directory
# ... writes index.tsx ...
motherduck dive watch taxi_trips --no-open   # render it, read the events
motherduck dive push taxi_trips --output json      # publish, capture the URL
```

`--no-open` keeps the preview from stealing focus, and `--log-file` writes
render and query outcomes as NDJSON so the agent can read whether its component
compiled instead of asking you to look:

```bash
motherduck dive watch taxi_trips --no-open --log-file preview.ndjson
```

## JSON output everywhere for programmatic use

The `--output json` option makes the CLI's output easy to parse
programmatically. Commands that act on a Dive or a Flight return it under a key
named for the resource, described under
[result shape](/sql-reference/motherduck-cli/#result-shape):

```bash
motherduck dive push taxi_trips --output json
```

```json
{
  "success": true,
  "dive": {
    "id": "123e4567-e89b-12d3-a456-426614174000",
    "title": "Taxi trips",
    "version": 2,
    "url": "https://app.motherduck.com/dives/taxi-trips-123e4567-e89b-12d3-a456-426614174000"
  }
}
```

So a script reads one field instead of the whole message:

```bash
motherduck dive push taxi_trips --output json | jq -r '.dive.url'
```

A failure prints `{"success": false, "error": "..."}` and exits non-zero, so an
agent checks one field rather than reading prose.

:::note
The `success` field doesn't appear in the output of
[`query`](/sql-reference/motherduck-cli/query/), which returns its rows as a
bare JSON array. See
[output formats](/sql-reference/motherduck-cli/#output-formats).
:::

That's what lets an agent chain steps in a script rather than in its context
window. Each command hands the next one a single field, so a multi-step
workflow costs a few tokens instead of a transcript of full outputs:

```bash
#!/usr/bin/env bash
set -euo pipefail

# Trigger a Flight, then wait for the run to settle.
RUN=$(motherduck flight run nightly_load --output json | jq -r '.run.run_number')

while :; do
  STATUS=$(motherduck flight list-runs nightly_load --limit 1 --output json \
    | jq -r '.runs[0].status')
  [[ "$STATUS" == "PENDING" || "$STATUS" == "RUNNING" ]] || break
  sleep 10
done

# On failure, surface the reason and stop.
if [[ "$STATUS" != "SUCCEEDED" ]]; then
  motherduck flight logs nightly_load --run "$RUN" | tail -20 >&2
  exit 1
fi

# The data landed, so publish a Dive over it.
motherduck dive push daily_totals --output json | jq -r '.dive.url'
```

The agent writes that once and reads one URL back, instead of holding every
intermediate result in its context.

## Give the run its own credentials

Pass a token rather than running the browser flow, and point the CLI at a
directory of its own:

```bash
export MOTHERDUCK_TOKEN=
export MOTHERDUCK_HOME=/workspace/.motherduck
```

`MOTHERDUCK_HOME` gives the run its own credentials and asset directory, which
keeps parallel agents from sharing state. It has to be an absolute path.

Where there's no account to get a token from,
[`motherduck new`](/sql-reference/motherduck-cli/new/) creates one from the terminal without a
browser or a signup form.

:::warning
An agent with a MotherDuck token can read and write whatever that token can.
Scope it to what the task needs, and prefer a read-only token for agents that
only query. See [securing read-only access](../../../key-tasks/ai-and-motherduck/securing-read-only-access.mdx).
:::

## Choosing between the CLI and MCP

Both let an agent work with MotherDuck. The deciding question is whether the
agent has a shell and a filesystem:

- **The CLI** fits agents that run commands and write files: a coding agent
  building a Dive or a Flight in a repository, a CI job, or a shell script.
- **[The MCP server](../../../key-tasks/ai-and-motherduck/mcp-setup.mdx)** fits
  agents in a chat client with no shell, such as Claude or ChatGPT on the web.
  Use it to explore data, answer a question, and render a Dive inline in the
  conversation.

They work together: an agent can explore through MCP, then use the CLI to build
and publish what it found.

### Why the CLI costs fewer tokens for file-shaped work

An MCP tool result is a message. Whatever the server returns, a Dive's component
code, a Flight's source, a list of every Dive in the workspace, or a thousand
query rows, is serialized into the model's context. It takes up the context
window and gets resent on every turn that follows.

The CLI writes to stdout or to files on disk, and the agent picks what to read
back. It can filter a listing through `jq`, read only the function it's changing
out of a Dive it pulled, or hand a file straight to the next command. Only what
the agent reads reaches the context window.

So for anything file-shaped, prefer the CLI:

| Task | Through MCP | Through the CLI |
|---|---|---|
| Read a Dive or a Flight | `read_dive` or `get_flight` returns the whole source in the response | `dive pull` or `flight pull` writes the files to disk, and the agent reads the part it needs |
| Save an edit | The agent sends the changed content back as a tool argument | The agent edits the file in place, and `dive push` or `flight push` reads it from disk |
| List Dives or Flights | `list_dives` or `list_flights` returns every field of every result | `dive list --output json` piped through `jq` returns the IDs alone |
| Return a large result set | Every row lands in the context window | Redirect it: `motherduck query "..." --output csv > result.csv` |
| Chain several steps | Each intermediate result passes through the model | One shell script hands each command's output to the next |

The gap widens the more you iterate. Pull a Dive once and the local file carries
every revision after that, so the agent patches a few lines instead of moving
the whole component through the conversation twice per round.

---

## 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=%2Fgetting-started%2Finterfaces%2F&page_title=MotherDuck%20Documentation%20-%20Interfaces&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.