`.
When updating a `SHARE` with the same database, the URL does not change.
```sql
UPDATE SHARE ;
```
**Example:** Database 'mydb' was previously shared by creating a share 'myshare', and the database 'mydb' has been updated since. The owner wants colleagues to receive the new version:
```sql
# 'myshare' was previously created on the database 'mydb'
UPDATE SHARE "myshare";
```
**Expected result:** The share is updated with the latest data from the source database.
**Recovery:** If you lost your database share URL, you can use the `LIST SHARES` command to list all your shares or `DESCRIBE SHARE ` to get specific details about a given share name.
## Refreshing shared data (consumer side)
**Use this when you need to:** Get the most up-to-date data from a share or read-scaling Duckling after the producer has made updates.
**Prerequisites:** You must have attached a share or be connected to a read-scaling Duckling.
**You'll know you're done when:** Your local copy reflects the latest data from the producer.
By default, shares and read-scaling Ducklings _automatically sync every minute_. However, if you need the most up-to-date data sooner, you can manually refresh after the producer executes their update command.
### Complete workflow for maximum freshness
For the freshest possible data, follow this two-step process:
1. **Producer side:** Either wait for normal checkpoints or force an update
2. **Consumer side:** Run `REFRESH DATABASE` to pull the latest changes
**Producer (writer connection):**
```sql
-- Make your changes
INSERT INTO my_db.my_table VALUES (...);
-- Option 1: Wait for normal checkpoint (automatic)
-- Data becomes available after the next checkpoint occurs
-- Option 2: Force a snapshot to make data immediately available
CREATE SNAPSHOT OF my_db;
```
**Consumer (read-scaling connection):**
```sql
-- Refresh to get the latest snapshot
REFRESH DATABASES; -- Refreshes all connected databases and shares
-- OR
REFRESH DATABASE my_db; -- Refresh just one specific database
```
**Producer (share owner):**
```sql
-- Make your changes
INSERT INTO my_db.my_table VALUES (...);
-- Option 1: Wait for normal checkpoint (automatic)
-- Data becomes available after the next checkpoint occurs
-- Option 2: Force a share update to make data immediately available
UPDATE SHARE "myshare";
```
**Consumer (share recipient):**
```sql
-- Refresh to get the latest share data
REFRESH DATABASES; -- Refreshes all connected databases and shares
-- OR
REFRESH DATABASE my_share; -- Refresh just one specific share
```
### Understanding the refresh output
When you run `REFRESH DATABASES`, you'll see output showing which databases were refreshed:
```sql
REFRESH DATABASES;
┌─────────┬───────────────────┬──────────────────────────┬───────────┐
│ name │ type │ fully_qualified_name │ refreshed │
│ varchar │ varchar │ varchar │ boolean │
├─────────┼───────────────────┼──────────────────────────┼───────────┤
│ my_db │ motherduck │ md:my_db │ false │
│ myshare │ motherduck share │ md:_share/myshare/uuid │ true │
└─────────┴───────────────────┴──────────────────────────┴───────────┘
```
The `refreshed` column shows `true` for databases that were successfully refreshed with new data.
Learn more about [`REFRESH DATABASE`](/sql-reference/motherduck-sql-reference/refresh-database.md).
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/_include-thing-for-differences-with-duckdb
{props.thing} in MotherDuck {props.verb} differences from DuckDB. When referencing information about {props.thing} in DuckDB Documentation at {props.ddburl}, consider the differences listed in this topic.
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/_include-thing-for-parity-with-duckdb
{props.thing} in MotherDuck {props.verb} no different than in DuckDB. For more information, see {props.ddburl} in DuckDB Documentation.
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/aggregate-functions
---
sidebar_position: 6
title: Aggregate functions
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
Aggregate Functions} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/configurations
---
sidebar_position: 8
title: Configurations
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
Configuration} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/constraints
---
sidebar_position: 9
title: Constraints
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
Constraints} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/data-types
---
sidebar_position: 3
title: Data types
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
Data Types} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-sql-reference
---
title: DuckDB SQL
description: DuckDB SQL Reference
---
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/alter-table
---
title: ALTER TABLE
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
ALTER TABLE} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/attach-detach
---
title: ATTACH/DETACH
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
ATTACH/DETACH} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/call
---
title: CALL
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
CALL} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/comment-on
---
title: COMMENT ON
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
COMMENT ON} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/copy
---
title: COPY
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
COPY} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/create-index
---
title: CREATE INDEX
---
# CREATE INDEX
The `CREATE INDEX` statement in MotherDuck has differences from DuckDB. While the syntax is supported, indexes are not currently utilized for query acceleration in MotherDuck. This is generally not a concern as MotherDuck is already highly optimized for analytical workloads and provides excellent query performance through optimized data storage and processing.
## Key Differences
- Indexes can be created but do not provide performance benefits
- Queries that would use an index scan in DuckDB will use a sequential scan in MotherDuck instead
## Example
```sql
-- Create a table and an index
CREATE TABLE users(id INTEGER, name VARCHAR);
INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
CREATE INDEX idx_user_id ON users(id);
-- This query will use a sequential scan in MotherDuck
-- even though an index scan would be used in DuckDB
SELECT * FROM users WHERE id = 1;
```
You can verify this behavior using the [EXPLAIN](/sql-reference/motherduck-sql-reference/explain/) statement:
```sql
EXPLAIN SELECT * FROM users WHERE id = 100;
-- Will show SEQ_SCAN in MotherDuck
-- Would show INDEX_SCAN in DuckDB
```
:::note
While queries that would benefit from index acceleration in DuckDB will use different execution plans in MotherDuck, MotherDuck's architecture is designed to provide fast analytical query performance even without indexes. The platform uses various optimizations and a cloud-native architecture to ensure efficient query execution.
:::
Additionally, it's worth noting that indexes can significantly slow down `INSERT` operations, as the index needs to be updated with each new record. Since indexes don't provide query acceleration benefits in MotherDuck, creating them will only add this overhead without any corresponding advantages.
For reference, you can learn more about how indexes work in DuckDB in their [Indexes documentation](https://duckdb.org/docs/sql/indexes).
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/create-macro
---
title: CREATE MACRO
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
CREATE MACRO} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/create-table
---
title: CREATE TABLE
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
CREATE TABLE} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/delete
---
title: DELETE
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
DELETE} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/drop
---
title: DROP
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
DROP} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/duckdb-statements
---
title: DuckDB statements
description: DuckDB statements
---
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/export
---
title: EXPORT
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
Export & Import Database} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/insert
---
title: INSERT
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
INSERT Statement} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/pivot
---
title: PIVOT
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
PIVOT Statement} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/select
---
title: SELECT
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
SELECT Statement} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/set-reset
---
title: SET/RESET
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
SET/RESET} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/unpivot
---
title: UNPIVOT
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
UNPIVOT Statement} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/update
---
title: UPDATE
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
UPDATE Statement} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/use
---
title: USE
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
USE} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/vacuum
---
title: VACUUM
---
import PartialExample from '../_include-thing-for-parity-with-duckdb.mdx';
VACUUM} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/enum
---
sidebar_position: 3
title: Enum data type
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
enum data type} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/expressions
---
sidebar_position: 3
title: Expressions
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
Expressions} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/functions
---
sidebar_position: 5
title: Functions
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
Functions} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/information-schema
---
sidebar_position: 10
title: Information schema
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
Information Schema} />
If you want to query information about your MotherDuck entities, take a look at [md_information_schema](/sql-reference/motherduck-sql-reference/md_information_schema/introduction).
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/metadata-functions
---
sidebar_position: 11
title: Metadata functions
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
DuckDB_% Metadata Functions} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/pragma-statements
---
sidebar_position: 12
title: PRAGMA statements
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
Pragmas} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/query-syntax
---
sidebar_position: 2
title: Query syntax
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
SELECT Clause} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/sample
---
sidebar_position: 13
title: SAMPLE
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
Samples} />
---
Source: https://motherduck.com/docs/sql-reference/duckdb-sql-reference/window-functions
---
sidebar_position: 7
title: Window functions
---
import PartialExample from './_include-thing-for-parity-with-duckdb.mdx';
Window Functions} />
---
Source: https://motherduck.com/docs/sql-reference/mcp/ask-docs-question
---
sidebar_position: 7
title: ask_docs_question
description: Ask questions about DuckDB or MotherDuck documentation
---
# ask_docs_question
Ask a question about DuckDB or MotherDuck and get answers from official documentation.
## Description
The `ask_docs_question` tool queries the official DuckDB and MotherDuck documentation to answer questions about SQL syntax, features, best practices, and more. This is useful when you need help with DuckDB-specific SQL syntax or MotherDuck features.
The tool uses MotherDuck's documentation assistant to provide accurate answers based on official documentation sources.
## Input Parameters
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `question` | string | Yes | Question about DuckDB or MotherDuck |
## Output Schema
```json
{
"success": boolean,
"question": string, // Original question (on success)
"answer": string, // Documentation-based answer (on success)
"sources": string, // Source references (optional, on success)
"error": string // Error message (on failure)
}
```
## Example Usage
**Ask about DuckDB syntax:**
```
How do I use window functions in DuckDB?
```
The AI assistant will call the tool with:
```json
{
"question": "How do I use window functions in DuckDB?"
}
```
**Ask about MotherDuck features:**
```
How do I create a share in MotherDuck?
```
```json
{
"question": "How do I create a share in MotherDuck?"
}
```
**Ask about data types:**
```
What's the difference between LIST and ARRAY types in DuckDB?
```
```json
{
"question": "What's the difference between LIST and ARRAY types in DuckDB?"
}
```
## Success Response Example
```json
{
"success": true,
"question": "How do I use window functions in DuckDB?",
"answer": "Window functions in DuckDB allow you to perform calculations across a set of rows related to the current row. Here's how to use them:\n\n**Basic syntax:**\n```sql\nSELECT \n column,\n SUM(value) OVER (PARTITION BY category ORDER BY date) as running_total\nFROM table_name;\n```\n\n**Common window functions:**\n- `ROW_NUMBER()` - assigns unique row numbers\n- `RANK()` and `DENSE_RANK()` - ranking with/without gaps\n- `LAG()` and `LEAD()` - access previous/next rows\n- `FIRST_VALUE()` and `LAST_VALUE()` - first/last value in window\n\n**Using QUALIFY:**\nDuckDB supports the QUALIFY clause to filter window function results:\n```sql\nSELECT *\nFROM sales\nQUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) = 1;\n```\n\nThis returns only the top sale per region.",
"sources": "https://duckdb.org/docs/sql/window_functions"
}
```
## Tips for Good Questions
- Be specific about what you want to know
- Include context about what you're trying to accomplish
- Mention specific functions or features if known
---
Source: https://motherduck.com/docs/sql-reference/mcp/list-columns
---
sidebar_position: 4
title: list_columns
description: List columns of a table or view with types and comments
---
# list_columns
List all columns of a table or view with their types and comments.
## Description
The `list_columns` tool returns detailed column information for a specified table or view, including data types, nullability, and any comments. This is useful for understanding table structure before writing queries.
## Input Parameters
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `table` | string | Yes | Table or view name |
| `database` | string | Yes | Database name |
| `schema` | string | No | Schema name (defaults to `main`) |
## Output Schema
```json
{
"success": boolean,
"database": string, // Database name
"schema": string, // Schema name
"table": string, // Table or view name
"objectType": "table" | "view", // Whether it's a table or view
"columns": [ // List of columns (on success)
{
"name": string, // Column name
"type": string, // Data type
"nullable": boolean, // Whether nulls are allowed
"comment": string | null // Column comment if set
}
],
"columnCount": number, // Number of columns
"error": string // Error message (on failure)
}
```
## Example Usage
**Get columns for a table:**
```
What columns does the customers table have in my_database?
```
The AI assistant will call the tool with:
```json
{
"table": "customers",
"database": "my_database"
}
```
**Get columns in a specific schema:**
```
Show me the schema of staging.raw_events in analytics_db
```
```json
{
"table": "raw_events",
"database": "analytics_db",
"schema": "staging"
}
```
## Success Response Example
```json
{
"success": true,
"database": "my_database",
"schema": "main",
"table": "customers",
"objectType": "table",
"columns": [
{
"name": "id",
"type": "INTEGER",
"nullable": false,
"comment": "Primary key"
},
{
"name": "email",
"type": "VARCHAR",
"nullable": false,
"comment": "Customer email address"
},
{
"name": "name",
"type": "VARCHAR",
"nullable": true,
"comment": "Full name"
},
{
"name": "created_at",
"type": "TIMESTAMP",
"nullable": false,
"comment": null
},
{
"name": "metadata",
"type": "JSON",
"nullable": true,
"comment": "Additional customer attributes"
}
],
"columnCount": 5
}
```
## Error Response Example
```json
{
"success": false,
"error": "Catalog Error: Table \"nonexistent_table\" does not exist"
}
```
---
Source: https://motherduck.com/docs/sql-reference/mcp/list-databases
---
sidebar_position: 1
title: list_databases
description: List all databases in your MotherDuck account
---
# list_databases
List all databases in your MotherDuck account with their names and types.
## Description
The `list_databases` tool returns all databases accessible to your MotherDuck account, including both owned databases and attached shared databases. This is useful for discovering what data is available before running queries.
## Input Parameters
This tool takes no input parameters.
## Output Schema
```json
{
"success": boolean,
"databases": [ // List of databases (on success)
{
"alias": string, // Database name/alias
"is_attached": boolean, // Whether the database is currently attached
"type": string // Database type (e.g., "motherduck", "memory")
}
],
"error": string // Error message (on failure)
}
```
## Example Usage
**List available databases:**
```
What databases do I have access to?
```
The AI assistant will call the tool with no parameters.
## Success Response Example
```json
{
"success": true,
"databases": [
{
"alias": "my_db",
"is_attached": true,
"type": "motherduck"
},
{
"alias": "analytics",
"is_attached": true,
"type": "motherduck"
},
{
"alias": "shared_sales_data",
"is_attached": true,
"type": "motherduck"
}
]
}
```
---
Source: https://motherduck.com/docs/sql-reference/mcp/list-shares
---
sidebar_position: 2
title: list_shares
description: List database shares that have been shared with you
---
# list_shares
List all database [shares](/key-tasks/sharing-data/sharing-overview) that have been shared with you.
## Description
The `list_shares` tool returns all database shares that have been shared with you by other users. Each share includes its name and URL, which can be used to attach the share as a database using the `query` tool.
To attach a share, execute: `ATTACH '' AS my_alias;`
To detach a share: `DETACH ;`
## Input Parameters
This tool takes no input parameters.
## Output Schema
```json
{
"success": boolean,
"shares": [ // List of shares (on success)
{
"name": string, // Share name
"url": string // Share URL for attaching
}
],
"error": string // Error message (on failure)
}
```
## Example Usage
**List available shares:**
```
What shares have been shared with me?
```
The AI assistant will call the tool with no parameters.
**Attach a share after listing:**
```
Attach the sales_data share so I can query it
```
After getting the share URL from `list_shares`, the AI will use the `query` tool:
```json
{
"database": "my_db",
"sql": "ATTACH 'md:_share/org123/sales_data' AS sales_data"
}
```
## Success Response Example
```json
{
"success": true,
"shares": [
{
"name": "sales_data",
"url": "md:_share/org123/sales_data"
},
{
"name": "product_catalog",
"url": "md:_share/org456/product_catalog"
},
{
"name": "analytics_benchmark",
"url": "md:_share/org789/analytics_benchmark"
}
]
}
```
## Empty Response Example
When no shares have been shared with you:
```json
{
"success": true,
"shares": []
}
```
## Related
- [Sharing Overview](/key-tasks/sharing-data/sharing-overview) - Learn about MotherDuck's data sharing capabilities
- [Managing Shares](/key-tasks/sharing-data/managing-shares) - How to create and manage shares
---
Source: https://motherduck.com/docs/sql-reference/mcp/list-tables
---
sidebar_position: 3
title: list_tables
description: List tables and views in a MotherDuck database
---
# list_tables
List all tables and views in a MotherDuck database with their comments.
## Description
The `list_tables` tool returns all tables and views in a specified database, including their schema, type (table or view), and any comments that have been added. You can optionally filter by schema.
## Input Parameters
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `database` | string | Yes | Database name to list tables from |
| `schema` | string | No | Schema name to filter by (defaults to all schemas) |
## Output Schema
```json
{
"success": boolean,
"database": string, // Database name
"schema": string, // Schema filter used ("all" if not specified)
"tables": [ // List of tables and views (on success)
{
"schema": string, // Schema name
"name": string, // Table or view name
"type": "table" | "view", // Object type
"comment": string | null // Table/view comment if set
}
],
"tableCount": number, // Number of tables
"viewCount": number, // Number of views
"error": string // Error message (on failure)
}
```
## Example Usage
**List all tables in a database:**
```
Show me all tables in my_database
```
The AI assistant will call the tool with:
```json
{
"database": "my_database"
}
```
**List tables in a specific schema:**
```
What tables are in the staging schema of analytics_db?
```
```json
{
"database": "analytics_db",
"schema": "staging"
}
```
## Success Response Example
```json
{
"success": true,
"database": "my_database",
"schema": "all",
"tables": [
{
"schema": "main",
"name": "customers",
"type": "table",
"comment": "Customer master data"
},
{
"schema": "main",
"name": "orders",
"type": "table",
"comment": "Order transactions"
},
{
"schema": "main",
"name": "monthly_sales",
"type": "view",
"comment": "Aggregated monthly sales view"
},
{
"schema": "staging",
"name": "raw_events",
"type": "table",
"comment": null
}
],
"tableCount": 3,
"viewCount": 1
}
```
## Error Response Example
```json
{
"success": false,
"error": "Catalog Error: Database \"nonexistent_db\" does not exist"
}
```
---
Source: https://motherduck.com/docs/sql-reference/mcp/mcp
---
sidebar_position: 0
title: MCP Server
description: Connect AI assistants to your MotherDuck data using the Model Context Protocol
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import DocCardList from '@theme/DocCardList';
import ClaudeIcon from '../../../static/img/icons/brands/claude-icon';
import CursorIcon from '../../../static/img/icons/brands/cursor-icon';
import ExternalLinkIcon from '../../../static/img/icons/external-link-icon';
# MotherDuck MCP Server
The MotherDuck MCP Server enables AI assistants to query and explore your MotherDuck databases using the [Model Context Protocol (MCP)](https://modelcontextprotocol.io/). Connect your favorite AI agent to explore schemas, run SQL queries, and ask documentation questions with natural language.
## Quick Start
Select your MCP client and follow the instructions to connect to the MotherDuck MCP server.
Add MotherDuck to Claude
Or manually:
1. Go to **Settings** → **Connectors**
2. Click **Browse Connectors** to find the MotherDuck connector
A browser window should open for authentication. After authentication you can double check the connection by asking "List all my databases on MotherDuck."
If you haven't already, make sure to first turn on 'Developer Mode' on chatgpt.com under [Settings → Apps → Advanced](https://chatgpt.com/#settings/Connectors/Advanced)
1. Go to [ChatGPT Settings → Connectors](https://chatgpt.com/#settings/Connectors)
2. Click **Create App**
3. Enter the following:
- **Name:** `MotherDuck`
- **MCP Server URL:** `https://api.motherduck.com/mcp`
4. Click **Create** and authenticate with your MotherDuck account
→ [ChatGPT Connectors Documentation](https://help.openai.com/en/articles/11487775-connectors-in-chatgpt)
Add MotherDuck to Cursor
1. Open **Cursor Settings** (`Cmd/Ctrl + ,`)
2. Navigate to **Tools & MCP**
3. Click **+ New MCP Server**
4. Add the following to the configuration file:
```json
{
"MotherDuck": {
"url": "https://api.motherduck.com/mcp",
"type": "http"
}
}
```
5. Save and click **Connect** to authenticate with your MotherDuck account
→ [Cursor MCP Documentation](https://docs.cursor.com/context/model-context-protocol)
1. Run the following command in your terminal:
```bash
claude mcp add MotherDuck --transport http https://api.motherduck.com/mcp
```
2. Run `claude` to start Claude Code
3. Type `/mcp`, select **MotherDuck** from the list, and press **Enter**
4. Select **Authenticate** and confirm the authorization dialog
→ [Claude Code MCP Documentation](https://code.claude.com/docs/en/mcp)
If you're using **Windsurf**, **Zed**, or another MCP-compatible client, use the following JSON configuration:
```json
{
"mcpServers": {
"MotherDuck": {
"url": "https://api.motherduck.com/mcp",
"type": "http"
}
}
}
```
### Authentication
The MCP server supports OAuth authentication. When you add the server to your AI client, you'll be redirected to authenticate with your MotherDuck account. The server uses your MotherDuck credentials to access your databases with the same permissions as your account.
Some clients also support simple authentication - in that case, you can provide your [MotherDuck access token](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck#creating-an-access-token) as a Bearer header.
## Server Capabilities
With the MCP server, your agent can:
- Execute read-only SQL queries against your databases
- Explore database schemas, tables, and columns
- Attach and detach [shares](/key-tasks/sharing-data/sharing-overview) that have been shared with you
- Ask questions about DuckDB and MotherDuck documentation
**Example prompts:**
- *"Analyze monthly revenue trends and identify our fastest-growing product categories"*
- *"Compare customer retention rates across different acquisition channels"*
- *"Build a cohort analysis showing user engagement over their first 90 days"*
For clients that [support MCP instructions](https://modelcontextprotocol.io/clients#feature-support-matrix), the server provides detailed [query guidelines](https://app.motherduck.com/assets/docs/mcp_server_instructions.md) to help AI assistants write effective DuckDB SQL.
Learn more about [using the MotherDuck MCP Server](/key-tasks/ai-and-motherduck/mcp-workflows).
## Available Tools
The MCP server provides the following tools for AI assistants:
## Regional Availability
The MotherDuck MCP server is available in all MotherDuck regions. Requests are routed to the MCP server closest to where the client runs:
- **Desktop clients** (Cursor, Claude Code): Routed based on your physical location
- **Web-based agents** (Claude.ai, ChatGPT): Routed based on the agent provider's server location
Your data is always processed in your MotherDuck organization's region. However, query results transit through the MCP server. If you have strict data residency requirements, ensure your MCP client runs within your region.
## Self-Hosted MCP Server
For local DuckDB databases or self-hosted scenarios, MotherDuck maintains an open-source MCP server that you can run locally.
📦 **mcp-server-motherduck** - Open-source MCP server for DuckDB and MotherDuck
The self-hosted server supports:
- **Read-write operations** on local and cloud databases
- Local DuckDB databases (no cloud connection required)
- MotherDuck cloud databases with your access token
- Custom configurations and security settings
## Related Resources
- [MCP User Guide](/key-tasks/ai-and-motherduck/mcp-workflows) - Tips and workflows for using the MotherDuck MCP Server
- [Building Analytics Agents](/key-tasks/ai-and-motherduck/building-analytics-agents) - Guide to building AI agents with MotherDuck
- [MCP Specification (2025-06-18)](https://modelcontextprotocol.io/specification/2025-06-18) - Official protocol documentation
---
Source: https://motherduck.com/docs/sql-reference/mcp/query
---
sidebar_position: 6
title: query
description: Execute read-only SQL queries against MotherDuck databases
---
# query
Execute read-only DuckDB SQL queries against MotherDuck databases.
## Description
The `query` tool executes SQL queries against your MotherDuck databases. For cross-database queries, use fully qualified names: `database.schema.table` (or `database.table` for the main schema).
This tool is read-only. The following operations are blocked:
- `CREATE TABLE` / `DROP TABLE` / `ALTER TABLE`
- `INSERT INTO` / `MERGE INTO`
- `CREATE DATABASE` / `DROP DATABASE`
- `CREATE SHARE` / `DROP SHARE`
- `CREATE SECRET` / `DROP SECRET`
- `CREATE SNAPSHOT` / `REFRESH DATABASE`
## Input Parameters
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `database` | string | Yes | Database name to query |
| `sql` | string | Yes | DuckDB SQL query to execute |
## Output Schema
```json
{
"success": boolean,
"columns": string[], // Column names (on success)
"columnTypes": string[], // Column types (on success)
"rows": any[][], // Query results (on success)
"rowCount": number, // Number of rows returned (on success)
"error": string, // Error message (on failure)
"errorType": string // Error type (on failure)
}
```
## Limits
- **Result limit:** Maximum 2,048 rows and 50,000 characters. Results exceeding these limits will be truncated with a truncation message.
- **Query timeout:** 55 seconds, to stay within common client timeouts. Queries exceeding this limit will be cancelled server-side and the tool will respond with an error message.
## Example Usage
**Simple query:**
```
Query the top 5 customers by total orders from my_database
```
The AI assistant will call the tool with:
```json
{
"database": "my_database",
"sql": "SELECT customer_name, COUNT(*) as order_count FROM orders GROUP BY customer_name ORDER BY order_count DESC LIMIT 5"
}
```
**Cross-database query:**
```
Join the users table from auth_db with orders from sales_db
```
```json
{
"database": "auth_db",
"sql": "SELECT u.name, o.order_id, o.amount FROM auth_db.main.users u JOIN sales_db.main.orders o ON u.id = o.user_id LIMIT 100"
}
```
## Success Response Example
```json
{
"success": true,
"columns": ["customer_name", "order_count"],
"columnTypes": ["VARCHAR", "BIGINT"],
"rows": [
["Acme Corp", 150],
["TechStart Inc", 89],
["Global Services", 72]
],
"rowCount": 3
}
```
## Error Response Example
```json
{
"success": false,
"error": "This query type is not permitted in read-only mode. CREATE DATABASE, DROP DATABASE, CREATE SHARE, DROP SHARE, CREATE SECRET, DROP SECRET, CREATE SNAPSHOT, and REFRESH DATABASE are blocked.",
"errorType": "ForbiddenQueryError"
}
```
---
Source: https://motherduck.com/docs/sql-reference/mcp/search-catalog
---
sidebar_position: 5
title: search_catalog
description: Fuzzy search across databases, schemas, tables, columns, and shares
---
# search_catalog
Search the catalog for databases, schemas, tables, columns, and shares using fuzzy matching.
## Description
The `search_catalog` tool performs fuzzy search across your entire MotherDuck catalog. It finds matching objects by name using partial matching, supporting underscores, dots, and multi-word queries. This is useful for discovering available data when you don't know exact names.
The search uses Jaro-Winkler similarity scoring and returns results ranked by relevance. Results are limited per category to provide a balanced view across different object types.
## Input Parameters
| Parameter | Type | Required | Description |
|-----------|------|----------|-------------|
| `query` | string | Yes | Search term to find in object names (supports partial matching, underscores, dots) |
| `object_types` | string[] | No | Filter results to specific types: `"database"`, `"schema"`, `"table"`, `"column"`, `"share"` |
## Output Schema
```json
{
"success": boolean,
"query": string, // Search query used
"resultCount": number, // Total results found
"results": [ // Search results (on success)
{
"type": "database" | "schema" | "table" | "column" | "share",
"name": string, // Object name
"fullyQualifiedName": string, // Full path (e.g., "db.schema.table.column")
"database": string | null, // Database (null for shares)
"schema": string | null, // Schema (null for databases/shares)
"table": string | null, // Table (only for columns)
"dataType": string | null, // Data type (columns) or URL (shares)
"comment": string | null, // Object comment if set
"relevanceScore": number // Match score 0-1 (higher is better)
}
],
"error": string, // Error message (on failure)
"errorType": string // Error type (on failure)
}
```
## Result Limits
Results are limited per object type to provide balanced coverage:
- Shares: 10 results
- Columns: 40 results
- Tables: 30 results
- Schemas: 20 results
- Databases: 20 results
Maximum total results: 100
## Example Usage
**Search for tables with "sales" in the name:**
```
Find all tables related to sales data
```
The AI assistant will call the tool with:
```json
{
"query": "sales"
}
```
**Search only for columns:**
```
Find columns containing "email"
```
```json
{
"query": "email",
"object_types": ["column"]
}
```
**Search with qualified name:**
```
Find anything matching analytics.events
```
```json
{
"query": "analytics.events"
}
```
## Success Response Example
```json
{
"success": true,
"query": "sales",
"resultCount": 8,
"results": [
{
"type": "table",
"name": "sales_data",
"fullyQualifiedName": "analytics.main.sales_data",
"database": "analytics",
"schema": "main",
"table": null,
"dataType": null,
"comment": "Daily sales transactions",
"relevanceScore": 0.95
},
{
"type": "table",
"name": "monthly_sales",
"fullyQualifiedName": "analytics.main.monthly_sales",
"database": "analytics",
"schema": "main",
"table": null,
"dataType": null,
"comment": null,
"relevanceScore": 0.89
},
{
"type": "column",
"name": "total_sales",
"fullyQualifiedName": "analytics.main.revenue.total_sales",
"database": "analytics",
"schema": "main",
"table": "revenue",
"dataType": "DECIMAL(18,2)",
"comment": "Total sales amount",
"relevanceScore": 0.87
},
{
"type": "share",
"name": "regional_sales_share",
"fullyQualifiedName": "regional_sales_share",
"database": "regional_sales_share",
"schema": null,
"table": null,
"dataType": "md:_share/org123/regional_sales_share",
"comment": null,
"relevanceScore": 0.82
}
]
}
```
## Error Response Example
```json
{
"success": false,
"error": "Search query cannot be empty",
"errorType": "ValidationError"
}
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/ai-functions
---
sidebar_position: 0
title: AI Functions
---
import DocCardList from '@theme/DocCardList';
# AI Functions
MotherDuck AI functions reference. These functions leverage AI models to perform various tasks including text generation, embeddings, and SQL assistance.
For more practical guidance, see our [AI and MotherDuck](/category/ai-and-motherduck/) how-to guides.
Costs can be found on the [Pricing Page](/about-motherduck/billing/pricing/#ai-function-pricing). Information about regional data processing of AI functions can be found at the bottom of the individual function pages.
## Available Functions
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/embedding
---
sidebar_position: 1
title: EMBEDDING
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Admonition from '@theme/Admonition';
::::warning[Preview Feature]
This is a preview feature. Preview features may be operationally incomplete and may offer limited backward compatibility.
::::
## Embedding Function
The `embedding` function allows you to generate vector representations (embeddings) of text directly from SQL. These embeddings capture semantic meaning, enabling powerful [semantic search](/key-tasks/ai-and-motherduck/text-search-in-motherduck/#embedding-based-search) and other natural language processing tasks.
The function uses OpenAI's models: `text-embedding-3-small` (default) with 512 dimensions or `text-embedding-3-large` with 1024 dimensions. Both models support single- and multi-row inputs, enabling batch processing.
The maximum input size is limited to 2048 characters - larger inputs will be truncated.
Consumption is measured in [AI Units](/about-motherduck/billing/pricing#ai-function-pricing). One AI Unit equates to approximately:
- 60,000 embedding rows with `text-embedding-3-small`
- 12,000 embedding rows with `text-embedding-3-large`
These estimates assume an input size of 1,000 characters.
### Syntax
```sql
SELECT embedding(my_text_column) FROM my_table; -- returns FLOAT[512] column
```
### Parameters
The `embedding` function accepts parameters using named parameter syntax with the `:=` operator.
| **Parameter** | **Required** | **Description** |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `text_input` | Yes | The text to be converted into an embedding vector |
| `model` | No | Model type, either `'text-embedding-3-small'` (default) or `'text-embedding-3-large'` |
### Return Types
The `embedding` function returns different array sizes depending on the model used:
- With `text-embedding-3-small`: Returns `FLOAT[512]`
- With `text-embedding-3-large`: Returns `FLOAT[1024]`
### Examples
#### Basic Embedding Generation
```sql
-- Generate embeddings using the default model (text-embedding-3-small)
SELECT embedding('This is a sample text') AS text_embedding;
-- Generate embeddings using the larger model for higher dimensionality
SELECT embedding('This is a sample text', model:='text-embedding-3-large') AS text_embedding;
```
#### Batch Processing
```sql
-- Generate embeddings for multiple rows at once
SELECT
title,
embedding(overview) AS overview_embeddings
FROM kaggle.movies
LIMIT 10;
```
### Use Cases
#### Creating an Embedding Database
This example uses the sample movies dataset from [MotherDuck's sample data database](/getting-started/sample-data-queries/datasets).
```sql
--- Create a new table with embeddings for the first 100 overview entries
CREATE TABLE my_db.movies AS
SELECT title,
overview,
embedding(overview) AS overview_embeddings
FROM kaggle.movies
LIMIT 100;
```
If write access to the source table is available, the embedding column can also be added in place:
```sql
--- Update the existing table to add new column for embeddings
ALTER TABLE my_db.movies ADD COLUMN overview_embeddings FLOAT[512];
--- Populate the column with embeddings
UPDATE my_db.movies
SET overview_embeddings = embedding(overview);
```
The movies table now contains a new column `overview_embeddings` with vector representations of each movie description:
```sql
SELECT * FROM my_db.movies;
```
| **title** | **overview** | **overview_embeddings** |
| ----------------- | ----------------- |----------------------------------------------------|
| 'Toy Story 3' | 'Led by Woody, Andy's toys live happily in [...]' | [0.023089351132512093, -0.012809964828193188, ...] |
| 'Jumanji' | 'When siblings Judy and Peter discover an [...]' | [-0.005538413766771555, 0.0799209326505661, ...] |
| ... | ... | ... |
#### Semantic Similarity Search
The `array_cosine_similarity` function can be used to compute similarities between embeddings.
This enables semantic search to retrieve entries that are conceptually / semantically similar to a query, even if they don't share the same keywords.
```sql
-- Find movies similar to "Toy Story" based on semantic similarity
SELECT
title,
overview,
array_cosine_similarity(
embedding('Led by Woody, Andy''s toys live happily [...]'),
overview_embeddings
) AS similarity
FROM kaggle.movies
WHERE title != 'Toy Story'
ORDER BY similarity DESC
LIMIT 5;
```
| **title** | **overview** | **similarity** |
|-----------------|-----------------|-----------------|
|'Toy Story 3'|'Woody, Buzz, and the rest of Andy's toys haven't [...]'|0.7372807860374451|
|'Toy Story 2'|'Andy heads off to Cowboy Camp, leaving his toys [...]'|0.7222828269004822|
|... |... |... |
For advanced similarity search techniques including document chunking, hybrid search, and performance optimization, see the [Embedding-Based Search](/key-tasks/ai-and-motherduck/text-search-in-motherduck/#embedding-based-search) section in the Text Search guide.
#### Building a Recommendation System
Embeddings can be used to build content-based recommendation systems:
```sql
-- Create a macro to recommend similar movies
CREATE OR REPLACE MACRO recommend_similar_movies(movie_title) AS TABLE (
WITH target_embedding AS (
SELECT embedding(overview) AS emb
FROM sample_data.kaggle.movies
WHERE title = movie_title
LIMIT 1
)
SELECT
m.title AS recommended_title,
m.overview,
array_cosine_similarity(t.emb, m.overview_embeddings) AS similarity
FROM
sample_data.kaggle.movies m,
target_embedding t
WHERE
m.title != movie_title
ORDER BY
similarity DESC
LIMIT 5
);
-- Use the macro to get recommendations
SELECT * FROM recommend_similar_movies('The Matrix');
```
#### Retrieval-Augmented Generation (RAG)
Embeddings are a key component in building [RAG](https://motherduck.com/blog/search-using-duckdb-part-2/) systems,
which can be combined with the [[`prompt` function]](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/prompt/#retrieval-augmented-generation-rag) for powerful question-answering capabilities:
```sql
-- Create a reusable macro for question answering
CREATE OR REPLACE TEMP MACRO ask_question(question_text) AS TABLE (
SELECT question_text AS question, prompt(
'User asks the following question:\n' || question_text || '\n\n' ||
'Here is some additional information:\n' ||
STRING_AGG('Title: ' || title || '; Description: ' || overview, '\n') || '\n' ||
'Please answer the question based only on the additional information provided.',
model := 'gpt-4o'
) AS response
FROM (
SELECT title, overview
FROM sample_data.kaggle.movies
ORDER BY array_cosine_similarity(overview_embeddings, embedding(question_text)) DESC
LIMIT 3
)
);
-- Use the macro to answer questions
SELECT question, response
FROM ask_question('Can you recommend some good sci-fi movies about AI?');
```
### Security Considerations
When passing free-text arguments from external sources to the embedding function (e.g., user questions in a RAG application), always use prepared statements to prevent SQL injection.
```python
# Using prepared statements in Python
user_query = "Led by Woody, Andy's toys live happily [...]"
con.execute("""
SELECT title, overview, array_cosine_similarity(embedding(?), overview_embeddings) as similarity
FROM kaggle.movies
ORDER BY similarity DESC
LIMIT 5""", [user_query])
```
### Error Handling
When usage limits have been reached or an unexpected error occurs while computing embeddings,
the function will not fail the entire query but will return `NULL` values for the affected rows.
To check if all embeddings were computed successfully:
```sql
-- Check for NULL values in embedding column
SELECT count(*)
FROM my_db.movies
WHERE overview_embeddings IS NULL AND overview IS NOT NULL;
```
Missing values can be filled in with a separate query:
```sql
-- Fill in missing embedding values
UPDATE my_db.movies
SET overview_embeddings = embedding(overview)
WHERE overview_embeddings IS NULL AND overview IS NOT NULL;
```
### Performance Considerations
- **Batch Processing**: when processing multiple rows, consider using `LIMIT` to control the number of API calls.
- **Model Selection**: use `text-embedding-3-small` for faster, less expensive embeddings when the highest precision isn't critical.
- **Caching**: results are not cached between queries, so consider storing embeddings in tables for repeated use.
- **Dimensionality**: higher dimensions (using `text-embedding-3-large`) provide more precise semantic representation but require more storage and computation time.
### Notes
These capabilities are provided by MotherDuck's integration with Azure OpenAI and inputs to the embedding function will be processed by Azure OpenAI.
For availability and usage limits, see [MotherDuck's Pricing Model](/about-motherduck/billing/pricing#motherduck-pricing-model).
Usage limits are in place to safeguard your spend, not because of throughput limitations. MotherDuck has the capacity to handle high-volume embedding workloads and is always open to working alongside customers to support any type of workload and model requirements.
If you need higher usage limits or have specific requirements, please see our [support page](/troubleshooting/support/).
#### Regional Processing
Requests are processed based on the region of the MotherDuck organization according to the table below. Functions that are not available within the region (no checkmark) will be processed with global compute resources.
| Function | Global | Europe |
|----------|--------|-------------------------|
| `EMBEDDING` (`text-embedding-3-small`) | ✓ | |
| `EMBEDDING` (`text-embedding-3-large`) | ✓ | ✓ |
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/prompt
---
sidebar_position: 1
title: PROMPT
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Admonition from '@theme/Admonition';
::::warning[Preview Feature]
This is a preview feature. Preview features may be operationally incomplete and may offer limited backward compatibility.
::::
## Prompt Function
The `prompt` function allows you to interact with Large Language Models (LLMs) directly from SQL. You can generate both free-form text and structured data outputs.
The function supports OpenAI's `gpt-5` series (`gpt-5`, `gpt-5-mini`, `gpt-5-nano`), `gpt-4o-mini` (default), `gpt-4o`, and the `gpt-4.1` series. All models support single- and multi-row inputs, enabling batch processing.
Consumption is measured in [AI Units](/about-motherduck/billing/pricing#ai-function-pricing). When reasoning over table rows, one AI Unit equates to approximately:
- 480 rows responses with `gpt-4o`
- 8,000 rows responses with `gpt-4o-mini`
- 600 rows responses with `gpt-4.1`
- 3,000 rows responses with `gpt-4.1-mini`
- 12,000 rows responses with `gpt-4.1-nano`
- 720 rows responses with `gpt-5`
- 3,600 rows with `gpt-5-mini`
- 18,000 rows with `gpt-5-nano`
These estimates assume an input size of 1,000 characters and response size of 250 characters.
## Syntax
```sql
SELECT prompt('Write a poem about ducks'); -- returns a single cell table with the response
```
### Parameters
| **Parameter** | **Required** | **Description** |
|--------------------|--------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| `prompt_text` | Yes | The text input to send to the model |
| `model` | No | Model type: `'gpt-5'`, `'gpt-5-mini'`, `'gpt-5-nano'`, `'gpt-4o-mini'` (default), `'gpt-4o'`, `'gpt-4.1'`, `'gpt-4.1-mini'`, or `'gpt-4.1-nano'` |
| `temperature` | No | Model temperature value between `0` and `1`, default: `0.1`. Lower values produce more deterministic outputs. **Not supported with GPT-5 models** (use `reasoning_effort` instead). |
| `reasoning_effort` | No | Controls reasoning depth for GPT-5 models only. Valid values: `'minimal'` (default), `'low'`, `'medium'`, `'high'`. Higher effort may improve accuracy for complex tasks. **Only available for GPT-5 series models**. |
| `return_type` | No | Specifies the exact SQL type to return (e.g., `'INTEGER'`, `'BOOLEAN'`, `'DATE'`, `'VARCHAR[]'`, `'STRUCT(name VARCHAR, age INTEGER)'`). Supports most DuckDB types including primitives, arrays, structs, and enums. Mutually exclusive with `struct` and `json_schema`. |
| `struct` | No | Output schema as struct, e.g. `{summary: 'VARCHAR', persons: 'VARCHAR[]'}`. Will result in `STRUCT` output. Mutually exclusive with `return_type` and `json_schema`. |
| `struct_descr` | No | Descriptions for struct fields that will be added to the model's context, e.g. `{summary: 'a 1 sentence summary of the text', persons: 'an array of all persons mentioned in the text'}` |
| `json_schema` | No | A JSON schema that adheres to [OpenAI's structured output guide](https://platform.openai.com/docs/guides/structured-outputs/supported-schemas). Provides more flexibility than the struct/struct_descr parameters. Will result in `JSON` output. Mutually exclusive with `return_type` and `struct`. |
**Note**: The `return_type` and `struct` parameters support enum types for classification tasks. Define enum types first using `CREATE TYPE`, then reference them in the struct schema (e.g., `sentiment: 'sentiment_enum'` or `categories: 'category_enum[]'` for arrays).
### Return Types
The `prompt` function can return different data types depending on the parameters used:
- Without structure parameters: Returns `VARCHAR`
- With `return_type` parameter: Returns the exact SQL type specified (e.g., `INTEGER`, `BOOLEAN`, `DATE`, `VARCHAR[]`, `STRUCT(...)`)
- With `struct` parameter: Returns a `STRUCT` with the specified schema
- With `json_schema` parameter: Returns `JSON`
**Note**: The `return_type`, `struct`, and `json_schema` parameters are mutually exclusive - only one can be used at a time.
## Example Usage
### Basic Text Generation
```sql
-- Call gpt-4o-mini (default) to generate text
SELECT prompt('Write a poem about ducks') AS response;
-- Call gpt-4o with higher temperature for more creative outputs
SELECT prompt('Write a poem about ducks', model:='gpt-4o', temperature:=1) AS response;
```
### Structured Output with Struct
```sql
-- Extract structured information from text using struct parameter
SELECT prompt('My zoo visit was amazing, I saw elephants, tigers, and penguins. The staff was friendly.',
struct:={summary: 'VARCHAR', favourite_animals:'VARCHAR[]', star_rating:'INTEGER'},
struct_descr:={star_rating: 'visit rating on a scale from 1 (bad) to 5 (very good)'}) AS zoo_review;
```
This returns a `STRUCT` value that can be accessed with dot notation:
```sql
SELECT
zoo_review.summary,
zoo_review.favourite_animals,
zoo_review.star_rating
FROM (
SELECT prompt('My zoo visit was amazing, I saw elephants, tigers, and penguins. The staff was friendly.',
struct:={summary: 'VARCHAR', favourite_animals:'VARCHAR[]', star_rating:'INTEGER'},
struct_descr:={star_rating: 'visit rating on a scale from 1 (bad) to 5 (very good)'}) AS zoo_review
);
```
### Structured Output with JSON Schema
```sql
-- Extract structured information using JSON schema
SELECT prompt('My zoo visit was amazing, I saw elephants, tigers, and penguins. The staff was friendly.',
json_schema := '{
"name": "zoo_visit_review",
"schema": {
"type": "object",
"properties": {
"summary": { "type": "string" },
"sentiment": { "type": "string", "enum": ["positive", "negative", "neutral"] },
"animals_seen": { "type": "array", "items": { "type": "string" } }
},
"required": ["summary", "sentiment", "animals_seen"],
"additionalProperties": false
},
"strict": true
}') AS json_review;
```
This returns a `JSON` value that, if saved, can be accessed using JSON extraction functions:
```sql
SELECT
json_extract_string(json_review, '$.summary') AS summary,
json_extract_string(json_review, '$.sentiment') AS sentiment,
json_extract(json_review, '$.animals_seen') AS animals_seen
FROM (
SELECT prompt('My zoo visit was amazing, I saw elephants, tigers, and penguins. The staff was friendly.',
json_schema := '{ ... }') AS json_review
);
```
### Typed Output with Return Type
The `return_type` parameter allows you to specify the exact SQL type for the model's response, providing strong typing for single-value extractions:
```sql
-- Extract an integer from text
SELECT prompt('The answer is 42', return_type := 'INTEGER') AS answer;
-- Returns: 42 (as INTEGER type)
-- Extract a boolean
SELECT prompt('Is the sky blue?', return_type := 'BOOLEAN') AS is_blue;
-- Returns: true (as BOOLEAN type)
-- Extract a date
SELECT prompt('When is January 15, 2025?', return_type := 'DATE') AS event_date;
-- Returns: 2025-01-15 (as DATE type)
-- Extract multiple structured fields
SELECT prompt(
'John is 30 years old and lives in NYC',
return_type := 'STRUCT(name VARCHAR, age INTEGER, city VARCHAR)'
) AS person_info;
-- Returns: {'name': 'John', 'age': 30, 'city': 'NYC'} (as STRUCT type)
-- Extract arrays
SELECT prompt('List the days of the week', return_type := 'VARCHAR[]') AS weekdays;
-- Returns: ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']
```
The `return_type` parameter supports most DuckDB types including:
- **Primitives**: `VARCHAR`, `INTEGER`, `BIGINT`, `DOUBLE`, `BOOLEAN`, `DATE`, `TIMESTAMP`, etc.
- **Arrays**: `INTEGER[]`, `VARCHAR[]`, `DOUBLE[]`, etc.
- **Structs**: `STRUCT(field1 TYPE1, field2 TYPE2, ...)`
- **Enums**: Custom enum types created with `CREATE TYPE`
### GPT-5 Reasoning Effort
The `reasoning_effort` parameter controls how much computational effort GPT-5 models spend on reasoning. This is only available for GPT-5 series models (`gpt-5`, `gpt-5-mini`, `gpt-5-nano`):
```sql
-- Use minimal reasoning (fastest, default)
SELECT prompt('What is 2+2?', 'gpt-5-mini',
reasoning_effort := 'minimal',
return_type := 'INTEGER') AS result;
-- Use low reasoning for simple tasks
SELECT prompt('Count the letters in "hello"', 'gpt-5-nano',
reasoning_effort := 'low',
return_type := 'INTEGER') AS letter_count;
-- Use medium reasoning for moderate complexity
SELECT prompt('Calculate 5 factorial', 'gpt-5-mini',
reasoning_effort := 'medium',
return_type := 'INTEGER') AS factorial;
-- Use high reasoning for complex tasks
SELECT prompt('Solve this logic puzzle: ...', 'gpt-5',
reasoning_effort := 'high') AS solution;
```
**Note**: The `reasoning_effort` parameter cannot be used with non-GPT-5 models, and `temperature` cannot be used with GPT-5 models. They are mutually exclusive ways of controlling model behavior.
## Use Cases
### Text Generation
Using the prompt function to write a poem about ducks:
```sql
--- Prompt LLM to write a poem about ducks
SELECT prompt('Write a poem about ducks') AS response;
```
| **response** |
|------------------------------------------------------------------------------------------------------------------|
| 'Beneath the whispering willow trees, Where ripples dance with wayward breeze, A symphony of quacks arise [...]' |
### Summarization
We use the prompt function to create a one-sentence summary of movie descriptions.
The example is based on the sample movies dataset from [MotherDuck's sample data database](/docs/getting-started/interfaces/client-apis/connect-query-from-python/query-data).
```sql
--- Create a new table with summaries for the first 100 overview texts
CREATE TABLE my_db.movies AS
SELECT title,
overview,
prompt('Summarize this movie description in one sentence: ' || overview) AS summary
FROM kaggle.movies
LIMIT 100;
```
If write access to the source table is available, the summary column can also be added in place:
```sql
--- Update the existing table to add new column for summaries
ALTER TABLE my_db.movies ADD COLUMN summary VARCHAR;
--- Populate the column with summaries
UPDATE my_db.movies
SET summary = prompt('Summarize this movie description in one sentence: ' || overview);
```
The movies table now contains a new column `summary` with one-sentence summaries of the movies:
```sql
SELECT title, overview, summary
FROM my_db.movies;
```
| **title** | **overview** | **summary** |
|-----------|----------------------------------------------|------------------------------------------------------|
| Toy Story | Led by Woody, Andy's toys live happily [...] | In "Toy Story," Woody's jealousy of the new [...] |
| Jumanji | When siblings Judy and Peter discover [...] | In this thrilling adventure, siblings Judy and [...] |
| ... | ... | ... |
### Structured Data Extraction
The prompt function can be used to extract structured data from text.
The example is based on the same sample movies dataset from [MotherDuck's sample data database](/getting-started/sample-data-queries/datasets). This time we aim to extract structured metadata from the movie's overview description.
We are interested in the main characters mentioned in the descriptions, as well as the movie's genre and a rating of how much action the movie contains, given a scale of 1 (no action) to 5 (lot of action).
For this, we make use of the `struct` and `struct_descr` parameters, which will result in structured output.
```sql
--- Update the existing table to add new column for structured metadata
ALTER TABLE my_db.movies ADD COLUMN metadata STRUCT(main_characters VARCHAR[], genre VARCHAR, action INTEGER);
--- Populate the column with structured information
UPDATE my_db.movies
SET metadata = prompt(
overview,
struct:={main_characters: 'VARCHAR[]', genre: 'VARCHAR', action: 'INTEGER'},
struct_descr:={
main_characters: 'an array of the main character names mentioned in the movie description',
genre: 'the primary genre of the movie based on the description',
action: 'rate on a scale from 1 (no action) to 5 (high action) how much action the movie contains'
}
);
```
The resulting `metadata` field is a `STRUCT` that can be accessed as follows:
```sql
SELECT title,
overview,
metadata.main_characters,
metadata.genre,
metadata.action
FROM my_db.movies;
```
| **title** | **overview** | **metadata.main_characters** | **metadata.genre** | **action** |
|-----------|----------------------------------------------|-------------------------------------------------------------------------|------------------------------|------------|
| Toy Story | Led by Woody, Andy's toys live happily [...] | ['"Woody"', '"Buzz Lightyear"', '"Andy"', '"Mr. Potato Head"', '"Rex"'] | Animation, Adventure, Comedy | 3 |
| Jumanji | When siblings Judy and Peter discover [...] | ['"Judy Shepherd"', '"Peter Shepherd"', '"Alan Parrish"'] | Adventure, Fantasy, Family | 4 |
| ... | ... | ... | ... | ... |
### Classification with Enums
The `prompt` function supports enum types for classification tasks, ensuring consistent and constrained outputs. This is particularly useful for sentiment analysis, categorization, and other classification scenarios.
#### Sentiment Analysis
```sql
-- Define an enum for sentiment classification
CREATE TYPE sentiment_type AS ENUM ('positive', 'negative', 'neutral');
-- Classify customer reviews
SELECT
review_text,
prompt(
'Classify the sentiment of this review: ' || review_text,
struct := {sentiment: 'sentiment_type'}
).sentiment AS sentiment
FROM (
VALUES
('The product is amazing, I love it!'),
('Terrible quality, waste of money.'),
('It works fine, nothing special.')
) AS reviews(review_text);
```
This returns:
| **review_text** | **sentiment** |
|-----------------|---------------|
| The product is amazing, I love it! | positive |
| Terrible quality, waste of money. | negative |
| It works fine, nothing special. | neutral |
#### Extracting Multiple Categories
Use enum arrays to extract multiple instances of the same category from text:
```sql
-- Define enums for different types of skills mentioned in text
CREATE TYPE skill_type AS ENUM ('sql', 'python', 'javascript', 'react', 'aws', 'docker', 'git');
CREATE TYPE topic_type AS ENUM ('database', 'frontend', 'backend', 'devops', 'analytics', 'security');
-- Extract skills and topics from job descriptions
SELECT
description,
prompt(
'Extract the technical skills and topics mentioned in this text: ' || description,
struct := {
skills: 'skill_type[]',
topics: 'topic_type[]'
}
) AS extracted
FROM (
VALUES
('Looking for a developer with Python and SQL experience for database analytics work'),
('Frontend role using React and JavaScript, plus Git for version control'),
('DevOps engineer needed for AWS and Docker deployment automation')
) AS jobs(description);
```
This returns arrays of enum values:
| **description** | **extracted.skills** | **extracted.topics** |
|-----------------|---------------------|---------------------|
| Looking for a developer with Python and SQL experience for database analytics work | ['python', 'sql'] | ['database', 'analytics'] |
| Frontend role using React and JavaScript, plus Git for version control | ['javascript', 'react', 'git'] | ['frontend'] |
| DevOps engineer needed for AWS and Docker deployment automation | ['aws', 'docker'] | ['devops'] |
### Retrieval-Augmented Generation (RAG)
The `prompt` function can be combined with [similarity search on embeddings](/docs/sql-reference/motherduck-sql-reference/ai-functions/embedding/) to build a [RAG](https://motherduck.com/blog/search-using-duckdb-part-2/) pipeline. For advanced retrieval strategies including hybrid search, reranking, and HyDE, see the [Text Search guide](/key-tasks/ai-and-motherduck/text-search-in-motherduck/).
```sql
-- Create a reusable macro for question answering
CREATE OR REPLACE TEMP MACRO ask_question(question_text) AS TABLE (
SELECT question_text AS question, prompt(
'User asks the following question:\n' || question_text || '\n\n' ||
'Here is some additional information:\n' ||
STRING_AGG('Title: ' || title || '; Description: ' || overview, '\n') || '\n' ||
'Please answer the question based only on the additional information provided.',
model := 'gpt-4o'
) AS response
FROM (
SELECT title, overview
FROM kaggle.movies
ORDER BY array_cosine_similarity(overview_embeddings, embedding(question_text)) DESC
LIMIT 3
)
);
-- Use the macro to answer questions
SELECT question, response
FROM ask_question('Can you recommend some good sci-fi movies about AI?');
```
This will result in the following output:
| **question** | **response** |
|-----------------------------------------------------|-----------------------------------------------------------------------------------|
| Can you recommend some good sci-fi movies about AI? | Based on the information provided, here are some sci-fi movies about AI that you might enjoy: [...] |
:::warning
When passing free-text arguments from external sources to the prompt function (e.g., user questions in a RAG application), always use prepared statements to prevent SQL injection.
:::
Using prepared statements in [Python](/docs/getting-started/interfaces/client-apis/connect-query-from-python/query-data/):
```python
# First register the macro
con.execute("""
CREATE OR REPLACE TEMP MACRO ask_question(question_text) AS TABLE (
-- Macro definition as above
);
""")
# Then use prepared statements for user input
user_query = "Can you recommend some good sci-fi movies about AI?"
result = con.execute("""
SELECT response FROM ask_question(?)
""", [user_query]).fetchone()
print(result[0])
```
## Batch Processing
The `prompt` function can process multiple rows in a single query:
```sql
--- Process multiple rows at once
SELECT
title,
prompt('Write a tagline for this movie: ' || overview) AS tagline
FROM kaggle.movies
LIMIT 10;
```
## Error Handling
When usage limits have been reached or an unexpected error occurs while computing prompt responses,
the function will not fail the entire query but will return `NULL` values for the affected rows.
To check if all responses were computed successfully, check if any values in the resulting column are null.
```sql
-- Check for NULL values in response column
SELECT count(*)
FROM my_db.movies
WHERE response IS NULL AND overview IS NOT NULL;
```
Missing values can be filled in with a separate query:
```sql
-- Fill in missing prompt responses
UPDATE my_db.movies
SET response = prompt('Summarize this movie description in one sentence: ' || overview)
WHERE response IS NULL AND overview IS NOT NULL;
```
## Performance Considerations
- **Batch Processing**: When processing multiple rows, consider using `LIMIT` to control the number of API calls.
- **Model Selection**: Use `gpt-4o-mini` for faster, less expensive responses when high accuracy isn't critical.
- **Caching**: Results are not cached between queries, so consider storing results in tables for repeated use.
## Notes
These capabilities are provided by MotherDuck's integration with Azure OpenAI. Inputs to the prompt function will be processed by Azure OpenAI.
For availability and usage limits, see [MotherDuck's Pricing Model](/about-motherduck/billing/pricing#motherduck-pricing-model).
Usage limits are in place to safeguard your spend, not because of throughput limitations. MotherDuck has the capacity to handle high-volume embedding workloads and is always open to working alongside customers to support any type of workload and model requirements.
If you need higher usage limits or have specific requirements, please see our [support page](/troubleshooting/support/).
### Regional Processing
Requests are processed based on the region of the MotherDuck organization according to the table below. Functions that are not available within the region (no checkmark) will be processed with global compute resources.
| Function | Global | Europe |
|----------|--------|-------------------------|
| `PROMPT` | ✓ | ✓ |
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/index
---
sidebar_position: 0
title: SQL Assistant
---
import DocCardList from '@theme/DocCardList';
# SQL Assistant
Built-in SQL functions that use AI to help you work with SQL. Generate SQL queries, execute read-only questions directly, fix errors, explain queries, and more.
These functions can be useful building blocks for [AI-driven analytics solutions](/key-tasks/ai-and-motherduck/building-analytics-agents/) or used stand-alone on all MotherDuck surfaces (including the CLI).
To use external tools like Claude Desktop or Cursor with MotherDuck, see [MCP Server](/sql-reference/mcp/).
## Available Functions
## Notes
SQL assistant functions operate on your current database by evaluating the schemas and contents of the database. You can specify which tables and columns should be considered using the optional `include_tables` parameter. By default, all tables in the current database are considered.
To point the SQL assistant functions at a specific database, execute the `USE database` command ([learn more about switching databases](/key-tasks/database-operations/switching-the-current-database)).
These capabilities are provided by MotherDuck's integration with Azure OpenAI.
For availability and pricing, see [MotherDuck's Pricing Model](/about-motherduck/billing/pricing#motherduck-pricing-model).
If you have further questions or specific requirements, please see our [support page](/troubleshooting/support/).
### Regional Processing
Requests are processed based on the region of the MotherDuck organization according to the table below. Functions that are not available within the region (no checkmark) will be processed with global compute resources.
| Function | Global | Europe |
|----------|--------|-------------------------|
| SQL Assistant Functions | ✓ | ✓ |
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-explain
---
sidebar_position: 0.9
title: PROMPT_EXPLAIN
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Admonition from '@theme/Admonition';
## Explain a query
The `prompt_explain` table function allows MotherDuck AI to analyze and explain SQL queries in plain English. This feature helps you understand complex queries, verify that a query does what you intend, and learn SQL concepts through practical examples.
::::tip
This function is particularly useful for understanding queries written by others or for automatically documenting your own queries for future reference.
::::
### Syntax
```sql
CALL prompt_explain('', [include_tables=['', '']]);
```
### Parameters
| **Parameter** | **Required** | **Description** |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `query` | Yes | The SQL query to explain |
| `include_tables` | No | Array of table names to consider for context (defaults to all tables in current database). Can also be a dictionary in the format `{'table_name': ['column1', 'column2']}` to specify which columns to include for each table. |
### Example usage
Here are several examples using MotherDuck's sample [Hacker News dataset](/getting-started/sample-data-queries/hacker-news) from [MotherDuck's sample data database](/getting-started/sample-data-queries/datasets).
#### Explaining a complex query
```sql
CALL prompt_explain('
SELECT COUNT(*) as domain_count,
SUBSTRING(SPLIT_PART(url, ''//'', 2), 1, POSITION(''/'' IN SPLIT_PART(url, ''//'', 2)) - 1) as domain
FROM hn.hacker_news
WHERE url IS NOT NULL GROUP BY domain ORDER BY domain_count DESC LIMIT 10;
');
```
**Output**: when you run a `prompt_explain` query, you'll receive a single-column table with a detailed explanation:
| **explanation** |
|-----------------|
|The query retrieves the top 10 most frequent domains from the `url` field in the `hn.hacker_news` table. It counts the occurrences of each domain by extracting the domain part from the URL (after the '//' and before the next '/'), groups the results by domain, and orders them in descending order of their count. The result includes the count of occurrences (`domain_count`) and the domain name itself (`domain`). |
#### Using dictionary format for include_tables
You can specify which columns to include for each table using the dictionary format:
```sql
CALL prompt_explain('
SELECT u.id, u.name, COUNT(s.id) AS story_count
FROM hn.users u
LEFT JOIN hn.stories s ON u.id = s.user_id
GROUP BY u.id, u.name
HAVING COUNT(s.id) > 5
ORDER BY story_count DESC
LIMIT 20;
', include_tables={'hn.users': ['id', 'name'], 'hn.stories': ['id', 'user_id']});
```
This approach allows you to focus the explanation on only the relevant columns, which can be helpful for tables with many columns.
#### How it works
The `prompt_explain` function processes your query in several steps:
1. **Parsing**: analyzes the SQL syntax to understand the query structure
2. **Schema analysis**: examines the referenced tables and columns to understand the data model
3. **Operation analysis**: identifies the operations being performed (filtering, joining, aggregating, etc.)
4. **Translation**: converts the technical SQL into a clear, human-readable explanation
5. **Context addition**: adds relevant context about the purpose and expected results of the query
### Best practices
For the best results with `prompt_explain`:
1. **Provide complete queries**: include all parts of the query for the most accurate explanation
2. **Use table aliases consistently**: this helps the function understand table relationships
3. **Specify relevant tables**: use the `include_tables` parameter for large databases
4. **Review explanations**: verify that the explanation matches your understanding of the query
5. **Use for documentation**: save explanations as comments in your code for future reference
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-fix-line
---
sidebar_position: 0.9
title: PROMPT_FIX_LINE
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Admonition from '@theme/Admonition';
## Fix your query line-by-line
The `prompt_fix_line` table function allows MotherDuck AI to correct specific lines in your SQL queries that contain syntax or spelling errors. Unlike [`prompt_fixup`](../prompt-fixup), which rewrites the entire query, this function targets only the problematic lines, making it faster and more precise for localized errors.
::::tip
This function is ideal for fixing minor syntax errors in large queries where you want to preserve most of the original query structure and formatting.
::::
### Syntax
```sql
CALL prompt_fix_line('', error='', [include_tables=['', '']]);
```
### Parameters
| **Parameter** | **Required** | **Description** |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `query` | Yes | The SQL query that needs correction |
| `error` | No | The error message from the SQL parser (helps identify the problematic line) |
| `include_tables` | No | Array of table names to consider for context (defaults to all tables in current database) |
### Example usage
Here are several examples using MotherDuck's sample [Hacker News dataset](/getting-started/sample-data-queries/hacker-news) from [MotherDuck's sample data database](/getting-started/sample-data-queries/datasets).
#### Fixing simple syntax errors
```sql
-- Fixing a misspelled keyword with error message
CALL prompt_fix_line('SEELECT COUNT(*) as domain_count FROM hn.hackers', error='
Parser Error: syntax error at or near "SEELECT"
LINE 1: SEELECT COUNT(*) as domain_count FROM h...
^');
-- Fixing a typo in a column name
CALL prompt_fix_line('SELECT user_id, titlee, score FROM hn.stories LIMIT 10');
-- Fixing incorrect operator usage
CALL prompt_fix_line('SELECT * FROM hn.stories WHERE score => 100');
```
#### Fixing errors in multi-line queries
```sql
-- Fixing a specific line in a complex query
CALL prompt_fix_line('SELECT
user_id,
COUNT(*) AS post_count,
AVG(scor) AS average_score
FRUM hn.stories
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10', error='
Parser Error: syntax error at or near "FRUM"
LINE 5: FRUM hn.stories
^');
```
### Example output
When you run a `prompt_fix_line` query, you'll receive a two-column table with the line number and corrected content:
| **line_number** | **line_content** |
|-----------------|-------------------------------------------------|
| 1 | SELECT COUNT(*) as domain_count FROM hn.hackers |
For multi-line queries, only the problematic line is corrected:
| **line_number** | **line_content** |
|-----------------|-------------------------------------------------|
| 5 | FROM hn.stories |
#### How it works
The `prompt_fix_line` function processes your query in a targeted way:
1. **Error localization**: uses the error message (if provided) to identify the specific line with issues
2. **Context analysis**: examines surrounding lines to understand the query's structure and intent
3. **Targeted correction**: fixes only the problematic line while preserving the rest of the query
4. **Line replacement**: returns the corrected line with its line number for easy integration
For example, when fixing a syntax error in a single line:
```sql
CALL prompt_fix_line('SEELECT COUNT(*) as domain_count FROM hn.hackers', error='
Parser Error: syntax error at or near "SEELECT"
LINE 1: SEELECT COUNT(*) as domain_count FROM h...
^');
```
The function will focus only on line 1, correcting the misspelled keyword:
| **line_number** | **line_content** |
|-----------------|-------------------------------------------------|
| 1 | SELECT COUNT(*) as domain_count FROM hn.hackers |
For multi-line queries with an error on a specific line:
```sql
CALL prompt_fix_line('SELECT
user_id,
COUNT(*) AS post_count,
AVG(scor) AS average_score
FRUM hn.stories
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10', error='
Parser Error: syntax error at or near "FRUM"
LINE 5: FRUM hn.stories
^');
```
The function will only correct line 5, leaving the rest of the query untouched:
| **line_number** | **line_content** |
|-----------------|-------------------------------------------------|
| 5 | FROM hn.stories |
This allows you to apply the fix by replacing just the problematic line in your original query, which is especially valuable for large, complex queries where a complete rewrite would be disruptive.
When multiple errors exist, you would run `prompt_fix_line` multiple times, fixing one line at a time:
```sql
-- First fix
CALL prompt_fix_line('SELECT
user_id,
COUNT(*) AS post_count,
AVG(scor) AS average_score
FRUM hn.stories
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10', error='
Parser Error: syntax error at or near "FRUM"
LINE 5: FRUM hn.stories
^');
-- After applying the first fix, run again for the second error
CALL prompt_fix_line('SELECT
user_id,
COUNT(*) AS post_count,
AVG(scor) AS average_score
FROM hn.stories
GROUP BY user_id
ORDER BY post_count DESC
LIMIT 10', error='
Parser Error: column "scor" does not exist
LINE 4: AVG(scor) AS average_score
^');
```
The second call would return:
| **line_number** | **line_content** |
|-----------------|-------------------------------------------------|
| 4 | AVG(score) AS average_score |
Note: you need to run `prompt_fix_line` multiple times to fix all errors.
### Best practices
For the best results with `prompt_fix_line`:
1. **Include the error message**: the parser error helps pinpoint the exact issue
2. **Preserve query structure**: use this function when you want to maintain most of your original query
3. **Fix one error at a time**: to address multiple errors, run `prompt_fix_line` multiple times
4. **Include context**: provide the complete query, not just the problematic line
5. **Be specific with table names**: use the `include_tables` parameter for large databases
### Limitations
While `prompt_fix_line` is efficient, be aware of these limitations:
- Only fixes syntax errors, not logical errors in query structure
- Accurate error messages help identify the problematic line and improve output
- May not be able to fix errors that span multiple lines
- Cannot fix issues related to missing tables or columns in your database
- Works best with standard SQL patterns and common table structures
### Troubleshooting
If you're not getting the expected results:
- Ensure you've included the complete error message
- Check that the line numbers in the error message match your query
- For complex errors, try using `prompt_fixup` instead
- If multiple lines need fixing, address them one at a time
- Verify that your database schema is accessible to the function
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-fixup
---
sidebar_position: 0.9
title: PROMPT_FIXUP
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Admonition from '@theme/Admonition';
## Fix up your query
The `prompt_fixup` table function allows MotherDuck AI to correct and **completely rewrite** SQL queries that have logical or severe syntactical issues. This powerful feature analyzes your problematic query, identifies issues, and generates a corrected version that follows proper SQL syntax and semantics.
::::tip
For minor syntax errors or typos in large queries, consider using the [`prompt_fix_line`](../prompt-fix-line) function instead, which is faster and more precise as it only rewrites the problematic line.
::::
### Syntax
```sql
CALL prompt_fixup('', [include_tables=['', '']]);
```
### Parameters
| **Parameter** | **Required** | **Description** |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `query` | Yes | The SQL query that needs correction |
| `include_tables` | No | Array of table names to consider for context (defaults to all tables in current database) |
### Example Usage
Here are several examples using MotherDuck's sample [Hacker News dataset](/getting-started/sample-data-queries/hacker-news) from [MotherDuck's sample data database](/getting-started/sample-data-queries/datasets).
#### Fixing syntax errors
```sql
-- Fixing misspelled keywords
CALL prompt_fixup('SEELECT COUNT(*) as domain_count FROM hn.hackers');
-- Fixing incorrect table names
CALL prompt_fixup('SELECT * FROM hn.stories WHERE score > 100 ODER BY score DESC');
-- Fixing missing clauses
CALL prompt_fixup('SELECT AVG(score) hn.hacker_news GROUP score > 10');
```
#### Fixing logical errors
```sql
-- Fixing incorrect join syntax
CALL prompt_fixup('SELECT u.name, s.title FROM hn.users u, hn.stories s WHERE u.id = s.user_id ORDER BY s.score');
-- Fixing aggregation issues
CALL prompt_fixup('SELECT user_id, AVG(score) FROM hn.stories GROUP BY score');
-- Fixing complex query structure
CALL prompt_fixup('SELECT COUNT(*) FROM hn.stories WHERE timestamp > "2020-01-01" AND timestamp < "2020-12-31" WITH score > 100');
```
### Example output
When you run a `prompt_fixup` query, you'll receive a single-column table with the corrected SQL:
| **query** |
|-----------------|
| SELECT COUNT(*) as domain_count FROM hn.hacker_news |
#### How it works
The `prompt_fixup` function processes your query in several steps:
1. **Analysis**: examines your query to identify syntax errors, logical issues, and structural problems
2. **Schema validation**: checks your query against the database schema to ensure table and column references are valid
3. **Correction**: applies fixes based on the identified issues and your likely intent
4. **Rewriting**: generates a complete, corrected version of your query that maintains your original goal
For example, when fixing this query with multiple issues:
```sql
CALL prompt_fixup('SEELECT AVG(scor) FRUM hn.stories WERE timestamp > "2020-01-01" GRUP BY user_id');
```
The function will:
- Correct misspelled keywords (`SEELECT` → `SELECT`, `FRUM` → `FROM`, `WERE` → `WHERE`, `GRUP` → `GROUP`)
- Fix column name typos (`scor` → `score`)
- Ensure proper clause ordering and syntax
Resulting in a properly formatted query:
| **query** |
|-----------------|
| SELECT AVG(score) FROM hn.stories WHERE timestamp > '2020-01-01' GROUP BY user_id |
For logical errors, the process is similar but focuses on semantic correctness:
```sql
CALL prompt_fixup('SELECT user_id, AVG(score) FROM hn.stories GROUP BY score');
```
Will be corrected to:
| **query** |
|-----------------|
| SELECT user_id, AVG(score) FROM hn.stories GROUP BY user_id |
The function recognized that grouping should be by `user_id` (the non-aggregated column) rather than by `score` (which is being averaged).
### Best practices
For the best results with `prompt_fixup`:
1. **Include the entire query**: even if only part of it has issues
2. **Be specific with table names**: use the `include_tables` parameter for large databases
3. **Review the fixed query**: always check that the corrected query matches your intent
4. **Use for complex issues**: prefer this function for logical errors or major syntax problems
5. **Consider alternatives**: for simple typos, `prompt_fix_line` may be more efficient
### Limitations
While `prompt_fixup` is powerful, be aware of these limitations:
- May change query logic if the original intent isn't clear
- Performance depends on the complexity of your query
- Works best with standard SQL patterns and common table structures
- May not preserve exact formatting or comments from the original query
- Cannot fix issues related to missing tables or columns in your database
### Troubleshooting
If you're not getting the expected results:
- Check that you've included all relevant tables in the `include_tables` parameter
- Ensure your database schema is accessible to the function
- For very complex queries, try breaking them into smaller parts
- If the fixed query doesn't match your intent, try providing more context in comments
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-query
---
sidebar_position: 0.1
title: PROMPT_QUERY
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Admonition from '@theme/Admonition';
## Answer questions about your data
The `prompt_query` pragma allows you to ask questions about your data in natural language. This feature translates your plain English questions into SQL, executes the query, and returns the results.
Under the hood, MotherDuck analyzes your database schema, generates appropriate SQL and executes the query on your behalf. This makes data exploration and analysis accessible to users of all technical levels.
For comprehensive guidance on building analytics agents, including best practices and implementation patterns, see [Building Analytics Agents with MotherDuck](/key-tasks/ai-and-motherduck/building-analytics-agents/).
::::info
The `prompt_query` pragma is a read-only operation and does not allow queries that modify the database.
::::
### Syntax
```sql
PRAGMA prompt_query('')
```
### Parameters
| **Parameter** | **Required** | **Description** |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `question` | Yes | The natural language question about your data |
### Example usage
Here are several examples using MotherDuck's sample [Hacker News dataset](/getting-started/sample-data-queries/hacker-news) from [MotherDuck's sample data database](/getting-started/sample-data-queries/datasets).
`prompt_query` can be used to answer both simple and complex questions.
#### Basic questions
```sql
-- Find the most shared domains
PRAGMA prompt_query('what are the top domains being shared on hacker_news?')
-- Analyze posting patterns
PRAGMA prompt_query('what day of the week has the most posts?')
-- Identify trends
PRAGMA prompt_query('how has the number of posts changed over time?')
```
#### Complex questions
```sql
-- Multi-part analysis
PRAGMA prompt_query('what are the top 5 domains with the highest average score, and how many stories were posted from each?')
-- Time-based analysis
PRAGMA prompt_query('compare the average score of posts made during weekdays versus weekends')
-- Conditional filtering
PRAGMA prompt_query('which users have posted the most stories about artificial intelligence or machine learning?')
```
### Best practices
For the best results with `prompt_query`:
1. **Be specific**: clearly state what information you're looking for
2. **Provide context**: include relevant details about the data you want to analyze
3. **Use natural language**: phrase your questions as you would ask a data analyst
4. **Start simple**: begin with straightforward questions and build to more complex ones
5. **Refine iteratively**: if results aren't what you expected, try rephrasing your question
### Limitations
While `prompt_query` is powerful, be aware of these limitations:
- Only performs read operations (`SELECT` queries)
- Works best with well-structured data with clear column names
- Complex statistical analyses will likely require you (or an LLM) to write SQL
- Performance depends on the complexity of your question and database size
- May not understand highly domain-specific terminology without you giving more context
### Troubleshooting
If you're not getting the expected results:
- Check that you're connected to the correct database
- Ensure your question is clear and specific
- Try rephrasing your question using different terms
- For complex analyses, break down into multiple simpler questions
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-schema
---
sidebar_position: 0.9
title: PROMPT_SCHEMA
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Admonition from '@theme/Admonition';
## Describe contents of a database
The `prompt_schema` table function allows MotherDuck AI to analyze and describe the contents of your current database in plain English. This feature helps you understand the structure, purpose, and relationships between tables in your database without having to manually inspect each table's schema.
::::tip
This function is particularly useful when working with unfamiliar databases or when you need a high-level overview of a complex database structure.
::::
### Syntax
```sql
CALL prompt_schema([include_tables=['', '']]);
```
### Parameters
| **Parameter** | **Required** | **Description** |
|--------------------|--------------|--------------------------------------------------------------------------------------------------------------------------|
| `include_tables` | No | Array of table names to consider for analysis (defaults to all tables in current database) |
### Example usage
Here are several examples using MotherDuck's [sample data database](/getting-started/sample-data-queries/datasets).
#### Describing the entire database
```sql
CALL prompt_schema();
```
#### Example output
When you run a `prompt_schema` query, you'll receive a single-column table with a detailed description:
| **summary** |
|-----------------|
| The database contains tables related to ambient air quality data, Stack Overflow survey results, NYC taxi and service requests, rideshare data, movie information with embeddings, and Hacker News articles, capturing a wide range of information from environmental metrics to user-generated content and transportation data. |
#### Describing specific tables
```sql
CALL prompt_schema(include_tables=['hn.hacker_news', 'hn.stories']);
```
| **summary** |
|-----------------|
| The database contains information about Hacker News posts, including details such as the title, URL, content, author, score, time of posting, type of post, and various identifiers and status flags. |
#### How it works
The `prompt_schema` function processes your database in several steps:
1. **Schema extraction**: examines the structure of tables, including column names and data types
2. **Data sampling**: analyzes sample data to understand the content and purpose of each table
3. **Relationship detection**: identifies potential relationships between tables based on column names and values
4. **Domain recognition**: categorizes tables into domains or subject areas based on their content
5. **Summary generation**: creates a human-readable description of the database structure and purpose
### Best practices
For the best results with `prompt_schema`:
1. **Focus on relevant tables**: use the `include_tables` parameter to analyze specific parts of large databases
2. **Run on updated databases**: ensure your database is up-to-date for the most accurate description
3. **Use for documentation**: save the output as part of your database documentation
4. **Combine with other tools**: use alongside `DESCRIBE` and `SHOW` commands for complete understanding
5. **Share with team members**: use the output to help new team members understand the database structure
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-sql
---
sidebar_position: 0.8
title: PROMPT_SQL
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
## Overview
The `prompt_sql` function allows you to generate SQL queries using natural language. Simply describe what you want to analyze in plain English, and MotherDuck AI will translate your request into a valid SQL query based on your database schema and content.
This function helps users who are less familiar with SQL syntax to generate queries and experienced SQL users save time when working with unfamiliar schemas.
For comprehensive guidance on building analytics agents, including best practices and implementation patterns, see [Building Analytics Agents with MotherDuck](/key-tasks/ai-and-motherduck/building-analytics-agents/).
## Syntax
```sql
CALL prompt_sql(''[, include_tables=]);
```
## Parameters
| Parameter | Type | Description | Required |
|-----------|------|-------------|----------|
| `natural language question` | STRING | Your query in plain English describing the data you want to analyze | Yes |
| `include_tables` | ARRAY or MAP | Specifies which tables and columns to consider for query generation. When not provided, all tables in the current database will be considered. | No |
### Include tables parameter
You can specify which tables and columns should be considered during SQL generation using the `include_tables` parameter. This is particularly useful when:
- You want to focus on specific tables in a large database
- You want to improve performance by reducing the schema analysis scope
The parameter accepts three formats:
1. **Array of table names**: include all columns from specified tables:
```sql
include_tables=['table1', 'table2']
```
2. **Map of tables to columns**: include only specific columns from tables:
```sql
include_tables={'table1': ['column1', 'column2'], 'table2': ['column3']}
```
3. **Map with column regex patterns**: include columns matching patterns:
```sql
include_tables={'table1': ['column_prefix.*', 'exact_column']}
```
## Examples
### Basic example
Let's start with a simple example using MotherDuck's sample [Hacker News dataset](/getting-started/sample-data-queries/hacker-news):
```sql
CALL prompt_sql('what are the top domains being shared on hacker_news?');
```
Output:
| **query** |
|-----------------|
| SELECT regexp_extract(url, 'https?://([^/]+)') AS domain, COUNT(*) AS count FROM hn.hacker_news WHERE url IS NOT NULL GROUP BY domain ORDER BY count DESC; |
### Intermediate example
This example demonstrates how to generate a more complex query with filtering, aggregation, and time-based analysis:
```sql
CALL prompt_sql('Show me the average score of stories posted by each author who has posted at least 5 stories in 2022, sorted by average score');
```
Output:
| **query** |
|-----------------|
| SELECT 'by', AVG(score) AS average_score FROM hn.hacker_news WHERE EXTRACT(YEAR FROM 'timestamp') = 2022 GROUP BY 'by' HAVING COUNT(id) >= 5 ORDER BY average_score; |
### Advanced Example: Multi-table Analysis with Specific Columns
This example shows how to generate a query that focuses on specific columns:
```sql
CALL prompt_sql(
'Find the top 10 users who submitted the most stories with the highest average scores in 2023',
include_tables={
'hn.hacker_news': ['id', 'by', 'score', 'timestamp', 'type', 'title']
}
);
```
Output:
| **query** |
|-----------------|
| SELECT "by", AVG(score) AS avg_score, COUNT(*) AS story_count FROM hn.hacker_news WHERE "type" = 'story' AND EXTRACT(YEAR FROM "timestamp") = 2023 GROUP BY "by" ORDER BY story_count DESC, avg_score DESC LIMIT 10; |
### Expert example
This example demonstrates generating a complex query with subqueries, window functions, and complex logic:
```sql
CALL prompt_sql('For each month in 2022, show me the top 3 users who posted stories with the highest scores, and how their average score compares to the previous month');
```
Output:
| **query** |
|-----------------|
| WITH monthly_scores AS (
SELECT
"by" AS user,
DATE_TRUNC('month', "timestamp") AS month,
AVG(score) AS avg_score
FROM hn.hacker_news
WHERE "type" = 'story' AND DATE_PART('year', "timestamp") = 2022
GROUP BY user, month
),
... |
## Failure example
This example shows that for some complex queries, the model might not generate a valid SQL query. Therefore the output will be the following error message:
```sql
CALL prompt_sql('Identify the most discussed technology topics in Hacker News stories from the past year based on title keywords, and show which days of the week have the highest engagement for each topic');
```
Output:
| **query** |
|-----------------|
| Invalid Input Error: The AI could not generate valid SQL. Try re-running the command or rephrasing your question. |
To generate a valid SQL query, you can try to break down the question into simpler parts.
## Best practices
1. **Be specific in your questions**: the more specific your natural language query, the more accurate the generated SQL will be.
2. **Start simple and iterate**: begin with basic queries and gradually add complexity as needed.
3. **Use the `include_tables` parameter**: when working with large databases, specify relevant tables to improve performance and accuracy.
4. **Review generated SQL**: always review the generated SQL before executing it, especially for complex queries.
5. **Understand your schema**: knowing your table structure helps you phrase questions that align with available data.
6. **Use domain-specific terminology**: include field names in your questions when possible.
7. **Provide context in your questions**: mention time periods, specific metrics, or business context to get more relevant results.
## Notes
- By default, all tables in the current database are considered. Use the `include_tables` parameter to narrow the scope.
- To target a specific database, first execute the `USE ` command ([learn more about switching databases](/key-tasks/database-operations/switching-the-current-database)).
- The quality of generated SQL depends on the clarity of your natural language question and the quality of your database schema (table and column names).
## Troubleshooting
If you encounter issues with the `prompt_sql` function, consider the following troubleshooting steps:
1. **Check your database schema**: ensure that the tables and columns you're querying are present in the current database.
2. **Be specific in your questions**: the more specific your natural language query, the more accurate the generated SQL will be.
3. **Use the `include_tables` parameter**: when working with large databases, specify relevant tables to improve performance and accuracy.
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/attach
---
sidebar_position: 1
title: ATTACH
---
# ATTACH
The `ATTACH` command in MotherDuck can be used to:
- Attach a local database to access local data
- Re-attach a previously detached MotherDuck database or [share](https://motherduck.com/docs/key-tasks/sharing-data/sharing-overview/)
- Attach a public [share](https://motherduck.com/docs/key-tasks/sharing-data/) created by any MotherDuck user in the same cloud region as your Organization, including users outside your Organization
:::note "Aliasing Databases"
Aliasing behavior in MotherDuck considers your relationship to the database itself. For databases owned by your user, aliases are not allowed. You will see an error that says `Database aliases are not yet supported by MotherDuck in workspace mode` when attempting to do this.
For shares, database aliases are _optional_, the default name is the string following `_share`, i.e. `ATTACH md:_share/birds/e9ads7-dfr32-41b4-a230-bsadgfdg32tfa;` will have the aliase `birds`. When attaching a share, the alias name remains in effect for as long as the database is attached. If the database is detached for any reason, the associated alias name is automatically cleared as well.
:::
## Attaching Databases
### Syntax for Databases
```sql
ATTACH 'md:'
```
Parameters:
* `database_name`: The name of the database to which to connect. If omitted, it defaults to 'workspace', which connects to all databases.
:::note
Shares are region-scoped based on your Organization's cloud region. MotherDuck Organizations are currently scoped to a single cloud region that must be chosen at Org creation when signing up.
:::
### Examples of Database Attachment
```sql
-- Connect to a specific MotherDuck database
ATTACH 'md:';
-- Connect to all MotherDuck databases in the workspace:
ATTACH 'md:';
-- Connect to a local database
ATTACH '/path/to/my_database.duckdb';
ATTACH 'a_new_local_duckdb';
```
:::note
Shares are region-scoped based on your Organization's cloud region. MotherDuck Organizations are currently scoped to a single cloud region that must be chosen at Org creation when signing up.
:::
### Important Notes for Database Attachment
* Local database `ATTACH` operations:
* Are temporary and last only for the current session
* Data stays local and isn't uploaded to MotherDuck
* Use file paths instead of share URLs
* MotherDuck database `ATTACH` operations:
* Are persistent, as they attach the database/share to your MotherDuck account.
* Requires read/write permissions for the database.
* The database must have been created by the active user and must have already been detached.
* If the remote database was not detached prior to running the `ATTACH` command, using the `md:` prefix will produce an error rather than creating a local database and attaching it.
* For a remote MotherDuck database, the database name is used to indicate what to attach and no alias is permitted.
## Attaching Shares
Sharing in MotherDuck is done through shares. Recipients of a share must `ATTACH` the share, which creates a read-only database. This is a zero-copy, zero-cost, metadata-only operation. [Learn more about sharing in MotherDuck](/key-tasks/sharing-data/sharing-overview.md).
### Syntax for Shares
```sql
ATTACH [AS ];
```
### Shorthand Convention for Shares
You may choose to name the new database by using `AS `. If you omit this clause, the new database will be given the same name as the source database that's being shared.
### Examples of Attaching Shares
```sql
ATTACH 'md:_share/ducks/0a9a026ec5a55946a9de39851087ed81' AS birds; # attaches the share as database `birds`
ATTACH 'md:_share/ducks/0a9a026ec5a55946a9de39851087ed81'; # attaches the share as database `ducks`
```
## Troubleshooting
### Finding Share URL
The Share URL is provided when you [create a share](/key-tasks/sharing-data/sharing-overview/#creating-a-share).
You can also see shares available for attachment by querying the [`MD_INFORMATION_SCHEMA.SHARED_WITH_ME`](/sql-reference/motherduck-sql-reference/md_information_schema/shared_with_me/) view.
### Handling name conflicts between local and remote databases
In case of name conflict between a local database and a remote database, there are two possible paths:
1. Attach the local database with a different name using an alias with `AS`. For instance : `ATTACH 'my_db.db' AS my_new_name`
2. Create a share out of your remote database and attach it with an alias. Shares are read-only.
### Using `SHARES` with [Read-Scaling Replicas](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/)
A database cannot be attached with a [read_scaling](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/#understanding-read-scaling-tokens) token. Databases should first be attached by an account + token with read_write permission, then accessed via read_scaling tokens.
For more information, see the [Attach & Detach](/key-tasks/database-operations/detach-and-reattach-motherduck-database) guide.
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/connection-management/connection-duckdb-id
---
sidebar_position: 3
title: Identify client connection and DuckDB ID
---
:::info
This is a preview feature. Preview features may be operationally incomplete and may offer limited backward compatibility.
:::
# Identify client connection and DuckDB ID
`md_current_client_connection_id` and `md_current_client_duckdb_id` are two scalar functions that can be used to identify the current `client_connection_id` and `client_duckdb_id`.
## Syntax
```sql
SELECT md_current_client_connection_id();
SELECT md_current_client_duckdb_id();
```
## Example usage
To [interrupt](documentation/sql-reference/motherduck-sql-reference/connection-management/interrupt-connections.md) all server-side connections that are initiated by the current client DuckDB instance, we can use:
```sql
SELECT md_interrupt_server_connection(client_connection_id)
FROM md_active_server_connections()
WHERE client_duckdb_id = md_current_client_duckdb_id()
AND client_connection_id != md_current_client_connection_id();
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/connection-management/interrupt-connections
---
sidebar_position: 2
title: Interrupting active server connections
---
:::info
This is a preview feature. Preview features may be operationally incomplete and may offer limited backward compatibility.
:::
# Interrupting active server connections
The `md_interrupt_server_connection` scalar function can be used to interrupt an active transaction on a server-side connection.
This will interrupt and fail / rollback the active transaction (when executing for example a long-running query), but will allow the connection to be used for future transactions and queries.
The function takes as input the `client_connection_id`, i.e. the unique identifier for the client DuckDB connection that initiated the server connection.
## Syntax
```sql
SELECT md_interrupt_server_connection();
```
## Example usage
Interrupting a specific connection:
```sql
SELECT md_interrupt_server_connection('2601e799-51b3-47a7-a64f-18688d148887');
```
Using `md_interrupt_server_connection` in conjunction with [`md_active_server_connections`](documentation/sql-reference/motherduck-sql-reference/connection-management/monitor-connections.md) to interrupt a subset or all of the currently active connections:
```sql
-- Interrupt all connections where a `CREATE TABLE` query is running
SELECT md_interrupt_server_connection(client_connection_id)
FROM md_active_server_connections()
WHERE starts_with(client_query, 'CREATE TABLE');
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/connection-management/monitor-connections
---
sidebar_position: 1
title: Monitoring active server connections
---
:::info
This is a preview feature. Preview features may be operationally incomplete and may offer limited backward compatibility.
:::
# Monitoring active server connections
The `md_active_server_connections` table function can be used to list all server-side connections that have active transactions.
## Syntax
```sql
FROM md_active_server_connections();
```
This returns a list of active server connections, with the following information:
| **column_name** | **column_type** | **description** |
|-------------------------------------|-----------------|----------------------------------------------------------------------------------|
| client_duckdb_id | UUID | Unique identifier for the client DuckDB instance that initiated the connection |
| client_user_agent | VARCHAR | User agent for the client |
| client_duckdb_version | USMALLINT[3] | DuckDB version from the client |
| client_connection_id | UUID | Unique identifier for the client DuckDB connection that initiated the connection |
| client_transaction_id | UBIGINT | Identifier for the transaction within the current connection |
| server_transaction_stage | VARCHAR | Stage the server-side transaction is in |
| server_transaction_elapsed_time | INTERVAL | How long the server-side transaction has been in the current stage |
| client_query_id | UBIGINT | Identifier for the query within the current transaction |
| client_query | VARCHAR | Query string (possibly truncated) |
| server_query_elapsed_time | INTERVAL | How long the query has been running on the server-side |
| server_query_execution_elapsed_time | INTERVAL | How long the connection has been interrupted |
| server_query_progress | DOUBLE | Progress information (value between 0.0 and 1.0) |
| server_interrupt_elapsed_time | INTERVAL | How long the connection has been interrupted |
| server_interrupt_reason | VARCHAR | Why the connection was interrupted |
| query_total_upload_size | UBIGINT | Data uploaded in Bytes |
| query_total_download_size | UBIGINT | Data downloaded in Bytes |
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/copy-database-overwrite
---
sidebar_position: 1
title: COPY FROM DATABASE (OVERWRITE)
---
# COPY FROM DATABASE (OVERWRITE)
The `COPY FROM DATABASE ... (OVERWRITE)` statement will make the target_db contain exactly the same data as source_db via zero-copy cloning, effectively overwriting it.
This command will wait on any ongoing write transactions on the target database to complete, and prevent new ones from starting while it is in progress.
:::note
The syntax is supported in MotherDuck only, as it operates on a MotherDuck metadata level.
:::
:::tip Zero-copy clone
This command operates purely at the MotherDuck metadata layer, so it is a **zero-copy clone**. The operation is almost instantaneous and does not duplicate any underlying data.
:::
## Syntax
```sql
COPY FROM DATABASE (OVERWRITE) [ TO ]
```
### Parameters
- ``: The name or path of the source database to copy from, can be either a MotherDuck database or a share.
- ``: The name or path of the target database to create, must be a MotherDuck database that the user owns.
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/copy-database
---
sidebar_position: 1
title: COPY FROM DATABASE
hide_title: true
description: Copy a database from one location to another in MotherDuck
keywords:
- copy database
- clone database
- duplicate database
- database copy
- database clone
---
# COPY FROM DATABASE
The `COPY FROM DATABASE` statement creates a new database from an existing one by copying its structure and data. This command can be used to:
[Interact with MotherDuck Databases](#copy-a-motherduck-database-to-a-motherduck-database)
- Copy MotherDuck databases to MotherDuck databases
[Interact with Local Databases](#interacting-with-local-databases)
- Copy local databases to MotherDuck databases
- Copy MotherDuck databases to local databases
- Copy local databases to local databases
The `COPY FROM DATABASE` command is a multiple statement macro. Multiple statement macros are not supported in Wasm and as a result, this command will not work in the MotherDuck Web UI when copying both schema and data. However, the command works in the MotherDuck Web UI if either the `(DATA)` option is specified or the `(SCHEMA)` option is specified. All other drivers support this command, including the DuckDB CLI.
:::caution No zero-copy clone
`COPY FROM DATABASE` creates a *physical* copy of both the schema and the data. It **does not** use MotherDuck's zero-copy cloning, so the operation may take longer to run and will consume additional storage proportional to the size of the source database. If you want to zero-copy clone a database, use the [`COPY FROM DATABASE (OVERWRITE)`](/sql-reference/motherduck-sql-reference/copy-database-overwrite.md) or the [`CREATE DATABASE ... FROM` statement](/sql-reference/motherduck-sql-reference/create-database.md).
:::
## Syntax
```sql
COPY FROM DATABASE TO [ (SCHEMA) | (DATA) ]
```
### Parameters
- ``: The name or path of the source database to copy from
- ``: The name or path of the target database to create
- `(SCHEMA)`: Optional parameter to copy only the database schema without data
- `(DATA)`: Optional parameter to copy only the database data without schema
## Example Usage
### Copy a MotherDuck database to a MotherDuck database
This is the same as [creating a new database from an existing one](/sql-reference/motherduck-sql-reference/create-database.md).
```sql
COPY FROM DATABASE my_db TO my_db_copy;
```
### Interacting with Local Databases
These operations can be done with access to the local filesystem, i.e. inside the DuckDB CLI.
#### Copy a local database to a MotherDuck database
```sql
ATTACH 'md:';
ATTACH 'local_database.db';
CREATE DATABASE md_database;
COPY FROM DATABASE local_database TO md_database;
```
#### Copy a MotherDuck database to a local database
To copy a MotherDuck database to a local database requires some extra steps.
```sql
ATTACH 'md:';
ATTACH 'local_database.db' as local_db;
COPY FROM DATABASE my_db TO local_db;
```
#### Copy a local database to a local database
To copy a local database to a local database, please see the [DuckDB documentation](https://duckdb.org/docs/stable/sql/statements/copy.html#copy-from-database--to).
### Copying the Database Schema
```sql
COPY FROM DATABASE my_db TO my_db_copy (SCHEMA);
```
This will copy the schema of the database, but not the data.
### Copying the Database Data
```sql
COPY FROM DATABASE my_db TO my_db_copy (DATA);
```
This will copy the data of the database, but not the schema.
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/create-database
---
sidebar_position: 1
title: CREATE DATABASE
hide_title: true
description: Create a database, zero-copy clone from an existing database, or import a local DuckDB file into MotherDuck.
---
## CREATE DATABASE
The `CREATE DATABASE` statement creates a new MotherDuck database. You can also use it to:
- Create a MotherDuck database from a local DuckDB database.
- Create a MotherDuck database from another MotherDuck database or [share](https://motherduck.com/docs/key-tasks/sharing-data/sharing-overview/) via zero-copy clone (without physically copying data).
:::note Copy to local database
To copy a MotherDuck database to a local database, use the [`COPY FROM DATABASE`](/sql-reference/motherduck-sql-reference/copy-database.md) statement.
:::
::::tip Zero-copy clone
When the source is another MotherDuck database or a share, `CREATE DATABASE ... FROM` performs a zero-copy clone. The command completes almost instantly because no data is physically duplicated. When the source is a local file, data is physically copied to MotherDuck.
::::
## Syntax
```sql
CREATE [ OR REPLACE ] DATABASE [ IF NOT EXISTS ]
[
FROM |
FROM '' |
FROM 'md:_share/...' |
FROM CURRENT_DATABASE() -- Important: this command does not work with attached shares
]
[(DATABASE OPTIONS)];
```
You can also pass the name of an attached share or a share URL as the database name, for example `CREATE DATABASE FROM my_share` or `CREATE DATABASE FROM 'md:_share/...'`.
If the database name already exists, the statement returns an error unless you specify `IF NOT EXISTS`.
Similar to DuckDB table name conventions, database names that start with a number or contain special characters must be double-quoted when used. Example: `CREATE DATABASE "123db"`
Creating a database does not change the active database. Run `USE DATABASE ` to switch.
## Database Options
For native storage-backed databases, MotherDuck supports two ways for users to configure historical retention periods. At database creation, users can either set the database type as transient or standard (default). Each database type comes with its own failsafe period.
| Name | Data Type | Value |
|------------|----------------|------------------------------------------------------------------------------------------------------------------------------------------------------------|
| STANDARD | native storage format | Leave blank; any database created in MotherDuck will default to a standard, native storage-backed database. |
| TRANSIENT | native storage format | Specify `TRANSIENT` at database creation to enable it to use transient storage. Refer to [Storage Lifecycle Management](/concepts/Storage-lifecycle#storage-management) for more details. |
| DUCKLAKE | integrated data lake and catalog format | Specify `TYPE DUCKLAKE` at database creation to create a fully managed DuckLake. Refer to the [DuckLake Overview](https://motherduck.com/docs/integrations/file-formats/ducklake/) for more details. |
Once these properties are set, they cannot be changed. However, MotherDuck supports cloning databases and copying data content between transient and standard databases. Note that the following syntax `CREATE DATABASE empty_duck FROM non_empty_duck (TRANSIENT);` is not supported. Please refer to the examples below for supported methods.
## Example Usage
To create an empty database:
```sql
CREATE DATABASE empty_ducks;
```
If the database name already exists, the statement fails unless you use `OR REPLACE` or `IF NOT EXISTS`.
```sql
CREATE DATABASE ducks;
-- Succeeds if 'ducks' does not exist
CREATE DATABASE ducks;
-- Error: Failed to create database: database with name 'ducks' already exists
CREATE OR REPLACE DATABASE ducks; -- Replaces existing 'ducks' with an empty database
CREATE DATABASE IF NOT EXISTS ducks; -- No-op if 'ducks' already exists
```
To *copy* an entire database from your local DuckDB instance into MotherDuck:
```sql
USE ducks_db;
CREATE DATABASE ducks FROM CURRENT_DATABASE();
-- Or alternatively:
CREATE OR REPLACE DATABASE ducks FROM ducks_db;
```
To configure database options in MotherDuck:
```sql
-- Create a Transient database:
CREATE DATABASE cloud_db (TRANSIENT)
-- Create a DuckLake:
CREATE DATABASE cloud_ducklake (TYPE DUCKLAKE);
```
To copy content between standard and transient databases:
```sql
-- Option 1: Clone with inherited properties
-- The new database inherits the transient/standard property from the source
CREATE OR REPLACE DATABASE dest_db FROM source_db;
-- Option 2: Copy content while preserving destination properties
-- Replaces the contents of dest_db without changing its configuration
CREATE DATABASE dest_db (TRANSIENT);
COPY FROM DATABASE source_db (OVERWRITE) TO dest_db;
```
:::note Limitations
You cannot copy data from a transient database into a standard database using `COPY FROM DATABASE (OVERWRITE)`, as this would violate the standard database's 7-day failsafe guarantee. To copy transient data into a standard database, use Option 1.
:::
To zero-copy clone a database that is already attached in MotherDuck:
```sql
CREATE DATABASE cloud_db FROM another_cloud_db;
```
To upload a local DuckDB database file:
```sql
CREATE DATABASE flying_ducks FROM './databases/local_ducks.db';
```
To upload an attached local DuckDB database:
```sql
ATTACH './databases/local_ducks.db';
CREATE DATABASE flying_ducks FROM local_ducks;
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/create-secret
---
sidebar_position: 1
title: CREATE SECRET
description: Create a secret in MotherDuck
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
# CREATE SECRET
MotherDuck enables you to store your cloud storage credentials for convenience, using the familiar DuckDB `CREATE SECRET` syntax. See [DuckDB CREATE SECRET documentation](https://duckdb.org/docs/sql/statements/create_secret.html).
Make sure to add the `PERSISTENT` or `IN MOTHERDUCK` keyword to create MotherDuck secrets. Secrets stored in MotherDuck are fully encrypted and scoped to the user who created them. They are not shared with other users in your organization.
:::note
You can use the `PERSISTENT` keyword to create a local file persistent secret in local DuckDB as well. It gets stored unencrypted in the `~/.duckdb/stored_secrets` directory.
When you've loaded the MotherDuck extension, `PERSISTENT` secrets are stored encrypted in MotherDuck. Locally persisted secrets are not impacted.
You can still create locally persisted secrets when using MotherDuck by specifying the secret storage backend: `CREATE SECRET IN LOCAL_FILE`.
:::
When using MotherDuck, the statement below creates a cloud-persistent secret stored in MotherDuck.
## Syntax
```sql
CREATE [OR REPLACE] (PERSISTENT SECRET [secret_name] | SECRET [secret_name] IN MOTHERDUCK)
(
TYPE ,
);
```
## Secret parameters
Supported parameters for S3, GCS, and R2 secrets:
| Name | Description | Secret | Type | Default |
|------|-------------|--------|------|---------|
| ENDPOINT | Specify a custom S3 endpoint | S3, GCS, R2 | STRING | s3.amazonaws.com for S3 |
| KEY_ID | The ID of the key to use | S3, GCS, R2 | STRING | - |
| REGION | The region used for authentication (this should match the region of the bucket to query) | S3, GCS, R2 | STRING | Orgs will default to the region that was chosen at signup: us-east-1 or eu-central-1 |
| SECRET | The secret of the key to use | S3, GCS, R2 | STRING | - |
| SESSION_TOKEN | Optionally, a session token can be passed to use temporary credentials | S3, GCS, R2 | STRING | - |
| URL_COMPATIBILITY_MODE | Can help when URLs contain problematic characters | S3, GCS, R2 | BOOLEAN | true |
| URL_STYLE | Either vhost or path | S3, GCS, R2 | STRING | vhost for S3, path for R2 and GCS |
| USE_SSL | Whether to use HTTPS or HTTP | S3, GCS, R2 | BOOLEAN | true |
| ACCOUNT_ID | The R2 account ID to use for generating the endpoint URL | R2 | STRING | - |
| KMS_KEY_ID | AWS KMS (Key Management Service) key for Server Side Encryption S3 | S3 | STRING | - |
| SCOPE | Scope of secret resolution; In the case of multiple matching secrets, the longest prefix is chosen | S3, GCS, R2 | STRING | - |
::::info
Because of SSL certificate verification requirements, S3 bucket names that contain dots (.) cannot be accessed using vhost style URLs. This is due to AWS's SSL wildcard certificate (*.s3.amazonaws.com) which only validates single-level subdomains. To resolve this SSL issue, use `URL_STYLE path` in your secret.
::::
## Examples
### Manually defined S3 secret
To manually create an S3 secret in MotherDuck:
```sql
CREATE SECRET IN MOTHERDUCK (
TYPE S3,
KEY_ID 's3_access_key',
SECRET 's3_secret_key',
REGION 'us-east-1',
SCOPE 'my-bucket-path'
);
```
This creates a new secret with a default name (for S3, `__default_s3`) and a default scope (i.e., `[s3://, s3n://, s3a://]`) used for path matching explained below.
::::info
DuckDB uses the `SCOPE` parameter to determine which secret to use. When using persistent secrets or public buckets, scoping the secrets is important so that the database uses the correct secret. Imprecise scoping will lead to authentication errors.
Learn more in the [DuckDB documentation](https://duckdb.org/docs/stable/configuration/secrets_manager.html#creating-multiple-secrets-for-the-same-service-type).
::::
### Secret providers
MotherDuck supports the same [secret providers](https://duckdb.org/docs/configuration/secrets_manager.html#secret-providers) as DuckDB.
To create a secret by automatically fetching credentials using mechanisms provided by the AWS SDK, see [AWS CREDENTIAL_CHAIN provider](https://duckdb.org/docs/extensions/httpfs/s3api#credential_chain-provider).
To create a secret by automatically fetching credentials using mechanisms provided by the Azure SDK, see [Azure CREDENTIAL_CHAIN provider](https://duckdb.org/docs/extensions/azure#credential_chain-provider).
To create a secret by automatically fetching credentials using mechanisms provided by the Hugging Face CLI, see [Hugging Face CREDENTIAL_CHAIN provider](https://duckdb.org/docs/extensions/httpfs/hugging_face#authentication).
To store a secret from a given secret provider in MotherDuck, specify the `PERSISTENT` or `IN MOTHERDUCK` keyword in addition.
### Provider examples
To store a secret configured through `aws configure`:
```sql
CREATE PERSISTENT SECRET aws_secret (
TYPE S3,
PROVIDER CREDENTIAL_CHAIN
);
```
To store a secret configured through `az configure`:
```sql
CREATE SECRET azure_secret IN MOTHERDUCK (
TYPE AZURE,
PROVIDER CREDENTIAL_CHAIN,
ACCOUNT_NAME 'some-account'
);
```
## Querying with secrets
[Secret scope](https://duckdb.org/docs/configuration/secrets_manager.html#creating-multiple-secrets-for-the-same-service-type) is supported in the same way as in DuckDB to allow multiple secrets of the same type to be stored in MotherDuck.
When there are multiple local (i.e. in memory and store in local file) and remote (i.e. MotherDuck) secrets of the same type, scope matching (secret scope against the file path) happens to determine which secret to use to open a file. Both local and remote secrets are considered in scope matching.
In the case of multiple matching secrets, the secret with the longest matching scope prefix is chosen.
In the case of multiple secrets stored in different secret storages sharing the same scope (e.g. the default scope if not specified), matching secret is chosen based on the following order: local temp secret > local_file secret > MotherDuck secret.
To see which secret (either local or remote) is being used by MotherDuck, the DuckDB `which_secret` table function can be used, which takes a path and the secret type.
### Example Usage
To see which secret is used to open a file:
```sql
FROM which_secret('s3://my-bucket/my_dataset.parquet', 's3');
┌───────────────────────┬────────────┬────────────┐
│ name │ persistent │ storage │
│ varchar │ varchar │ varchar │
├───────────────────────┼────────────┼────────────┤
│ __default_s3 │ PERSISTENT │ motherduck │
└───────────────────────┴────────────┴────────────┘
```
## Troubleshooting
If you encounter issues creating or using secrets, check out our troubleshooting guides:
- **[AWS S3 Secrets Troubleshooting](/documentation/troubleshooting/aws-s3-secrets.md)** - Common issues with AWS S3 authentication and credentials
- **[Error Messages](/documentation/troubleshooting/error_messages.md)** - Understanding MotherDuck error messages
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/create-share
---
sidebar_position: 1
title: CREATE SHARE
---
# CREATE SHARE
The `CREATE SHARE` statement creates a new a share from a database. This command is used to share databases with other users. [Learn more about sharing in MotherDuck](/key-tasks/sharing-data/sharing-overview.md).
## Syntax
```sql
CREATE [ OR REPLACE ] SHARE [ IF NOT EXISTS ] [] [FROM ] (
[ACCESS ORGANIZATION | UNRESTRICTED | RESTRICTED],
[VISIBILITY DISCOVERABLE | HIDDEN],
[UPDATE MANUAL | AUTOMATIC]
);
```
If you attempt to create a share, yet a share with that name already exists, no new share will be created and the query will return an error.
The error will be silenced when you specify `IF NOT EXISTS`.
This statement returns a share URL of the form `md:_share//`.
- If the share is **Hidden**, you must pass this URL to the **data consumer**, who will need to [`ATTACH`](attach.md) the share.
- If the share is **Discoverable**, passing the URL to the **data consumer** is optional.
### _OR REPLACE_ Clause
When you use the `OR REPLACE` clause to create or replace a share named `foo`, the share's **URL changes**. This means that
any clients currently connected to the old share URL will be **disconnected within a few minutes**.
To continue using the share named `foo`, clients must **re-attach** to it using the **new URL** provided by the `CREATE SHARE`
command. The old share URL will no longer be valid.
### _ACCESS_ Clause
You can configure scope of access of the share:
- `ACCESS ORGANIZATION` (default) - only members of your Organization can access the share.
- `ACCESS UNRESTRICTED` - all MotherDuck users in the same cloud region as the share creator can access the share.
- `ACCESS RESTRICTED` - the share owner will be the only user with access to the share initially. Access for other users the share can be updated via the [`GRANT`](grant-access.md) and [`REVOKE`](revoke-access.md) commands.
If omitted, defaults to `ACCESS ORGANIZATION`.
:::note
Shares are **region-scoped** based on your Organization's cloud region. Each MotherDuck Organization is currently scoped to a single cloud region that must be chosen at Org creation when signing up.
MotherDuck is currently available on AWS in two regions:
- **US East (N. Virginia):** `us-east-1`
- **Europe (Frankfurt):** `eu-central-1`
:::
### _VISIBILITY_ Clause
For Organization scoped shares **only**, you may choose to make them Discoverable:
- `VISIBILITY DISCOVERABLE` (default) - all members of your Organization will be able to list/find the share in the UI or SQL.
- `VISIBILITY HIDDEN` - the share can only be accessed directly by the share URL, and is not listed to other users. A Share can be hidden only if it has its `ACCESS` set to `RESTRICTED`.
If omitted, Organization-scoped and Restricted shares default to `VISIBILITY DISCOVERABLE`. Unrestricted shares can only be **Hidden**.
### _UPDATE_ Clause
Shares can be automatically or manually updated by the share creator.
- `UPDATE MANUAL` (default) - shares are only updated via the [`UPDATE SHARE`](update-share.md) command.
- `UPDATE AUTOMATIC` - the share is automatically updated when the underlying database changes. Typically changes on the underlying database will automatically be published to the share within at most 5 minutes, after writes have completed. Ongoing overlapping writes may prolong share updating.
If omitted, defaults to `UPDATE MANUAL`.
### Shorthand Convention
- If the database name is omitted, a share will be created from the current/active database.
- If the share name is omitted, the share will be named after the source database.
- If both database and share names are omitted, the share will be named and created after the current/active database.
## Example Usage
```sql
-- If ducks_share exists, it will be replaced with a new share.
--A new share URL is returned.
CREATE OR REPLACE SHARE ducks_share;
-- If ducks_share exists, nothing is done. Its existing share URL is returned.
--Otherwise, a new share is created and its share URL is returned.
CREATE SHARE IF NOT EXISTS ducks_share;
```
```sql
USE mydb;
-- Using shorthand: Create a share named ''mydb'' from the current database ''mydb''.
-- Defaults: ACCESS ORGANIZATION, VISIBILITY DISCOVERABLE, UPDATE MANUAL
CREATE SHARE;
-- Using shorthand: Create a share named ''db2'' from the specified database ''db2''.
-- Defaults: ACCESS ORGANIZATION, VISIBILITY DISCOVERABLE, UPDATE MANUAL
CREATE SHARE FROM db2;
-- Explicitly create a share named ''birds_share'' from database ''birds''.
-- Set specific access, visibility, and update behavior.
CREATE SHARE birds_share FROM birds (
ACCESS RESTRICTED, -- Only the share owner has initial access
VISIBILITY HIDDEN, -- Not listed; requires direct URL access
UPDATE AUTOMATIC -- Automatically updates with source DB changes
);
```
:::note
All shares created prior to June 6, 2024 are Unrestricted and Hidden. To make these legacy shares Organization-scoped and Discoverable, you can alter them in the UI or delete and create new shares.
:::
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/create-snapshot
---
sidebar_position: 1
title: CREATE SNAPSHOT
---
# CREATE SNAPSHOT
`CREATE SNAPSHOT OF ` creates a new read-only snapshot of the specified database for read-scaling Ducklings. Only one database can be snapshotted per command.
In the background, a snapshot of each database is taken every minute to sync changes with read-scaling Ducklings. If writing queries are active on a database, the snapshot is skipped to avoid disruption.
To force a snapshot, run `CREATE SNAPSHOT` manually. This command will wait on any ongoing write queries on the database to complete, and prevent new ones from starting. As soon as all ongoing write queries are completed, the command create the snapshot, ensuring that read-scaling connections can access the most up-to-date data.
Read-scaling Duckling picks up the latest available snapshot every minute. To minimize delay and ensure access to the latest
data, use `CREATE SNAPSHOT` on the writer connection, followed by a `REFRESH DATABASE ` on the read scaling connection.
```sql
CREATE SNAPSHOT OF ;
```
Lean more about [REFRESH DATABASES](/sql-reference/motherduck-sql-reference/refresh-database.md).
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/delete-secret
---
sidebar_position: 1
title: DROP SECRET
---
# DROP SECRET
The DuckDB `DROP SECRET` statement (see DuckDB [DROP SECRET documentation](https://duckdb.org/docs/sql/statements/create_secret#syntax-for-drop-secret)) works in MotherDuck to delete the secret previously created with `CREATE SECRET` statement.
# Syntax
```sql
DROP SECRET ;
```
When there are multiple secrets with the same name stored in different secret storages (e.g. in memory vs. in MotherDuck), either the persistent type or the secret storage type needs to be specified to remove ambiguity when dropping the secret.
# Example Usage
Disambiguate by specifying the storage type when dropping a secret:
```sql
DROP SECRET __default_s3 FROM motherduck;
```
Disambiguate by specifying the persistence type when dropping a secret:
```sql
DROP PERSISTENT SECRET __default_s3;
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/describe-share
---
sidebar_position: 1
title: DESCRIBE SHARE
---
# DESCRIBE SHARE
The `DESCRIBE SHARE` statement is used to get details about a specific share.
:::info
The **creator** of the share object can execute this statement by passing the **share name**. The **receiver** of the share object can execute this statement by passing the **share link**.
:::
# Syntax
```sql
DESCRIBE SHARE [ | ];
```
# Example
Let's use the `sample_data` database which is auto attached to MotherDuck users to illustrate the command
```sql
DESCRIBE SHARE 'md:_share/sample_data/23b0d623-1361-421d-ae77-62d701d471e6';
```
It returns a table with the following columns:
| column_name | column_type | description |
|---------------| ----------- |-----------------------------|
| name | VARCHAR | Name of the share |
| url | VARCHAR | URL of the share |
| source_db_name | VARCHAR | Name of the database shared |
| source_db_uuid | UUID | uid of the database shared |
| access | VARCHAR | Whether anyone (referred to as UNRESTRICTED) within the same cloud region or only organization members (referred to as ORGANIZATION) can attach to the share by its share_url |
| visibility | VARCHAR | Whether the share is DISCOVERABLE or HIDDEN |
| update | VARCHAR | The share’s update mode (MANUAL vs. AUTOMATIC) |
| created_ts | TIMESTAMP WITH TIME ZONE | The share’s creation time |
You can be specific about what which columns you want to return by using the table function :
```sql
SELECT name, url, source_db_name FROM md_describe_database_share('md:_share/sample_data/23b0d623-1361-421d-ae77-62d701d471e6');
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/detach
---
sidebar_position: 1
title: DETACH
---
# DETACH
The `DETACH` command in MotherDuck can be used to:
- Detach a local DuckDB database
- Detach a remote MotherDuck database
- Detach a shared database
:::info
Database aliases are not persisted when [Shares](/key-tasks/sharing-data/) are detached.
:::
## Detaching Databases
After a database has been created, it can be detached. This will prevent queries from accessing or modifying that database while it is detached. This command may be used on both local DuckDB databases and remote MotherDuck databases.
For a local database, specify the name of the database to detach and not the full path.
In the case of a remote MotherDuck database, the [`ATTACH`](attach.md) command can be used to re-attach at any point, so this is designed to be a convenience feature, not a security feature. `DETACH` can be used to isolate work on specific databases, while preserving the contents of the detached databases.
To see all databases, both attached and detached, use the [`SHOW ALL DATABASES` command](show-databases.md).
### Syntax for Databases
```sql
DETACH ;
```
### Examples of Database Detachment
```sql
-- Prior command:
-- ATTACH '/path/to/local_database.duckdb';
DETACH local_database;
-- Prior command:
-- CREATE DATABASE my_md_database;
DETACH my_md_database;
```
## Detaching Shares
Attached shares are sticky, and will continue to appear in your catalog unless you explicitly detach them.
### Syntax for Shares
```sql
DETACH ;
```
### Examples of Share Detachment
```sql
DETACH ducks;
```
For more information, see the [Attach & Detach](/key-tasks/database-operations/detach-and-reattach-motherduck-database) guide.
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/drop-database
---
sidebar_position: 1
title: DROP DATABASE
---
# DROP DATABASE
The `DROP` statement removes a database entry added previously with the `CREATE` command.
By default (or if the `RESTRICT` clause is provided), the entry will not be dropped if there are any existing database shares that were created from it. If the `CASCADE` clause is provided then all the shares that are dependent on the database will be dropped as well.
# Syntax
```sql
DROP DATABASE [IF EXISTS] [CASCADE | RESTRICT];
```
# Example usage
```sql
DROP DATABASE ducks; -- drops database named `ducks`
DROP DATABASE ducks CASCADE; -- drops database named `ducks` and all the shares created from `ducks`
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/drop-share
---
sidebar_position: 1
title: DROP SHARE
---
# DROP SHARE
`DROP SHARE` is used to delete a share by the share creator. Users who have attached the share will lose access.
This will throw an error if the share does not exist.
`DROP SHARE IF EXISTS` is used to delete a share by the share creator and will not throw an error if the share does not exist.
Shares can be attached with an alias name.
To **drop a share** with `DROP SHARE`, you must reference its **original name**, which you can find by running `LIST SHARES`.
If you want to remove a share from your workspace without deleting it completely, use [`DETACH`](/sql-reference/motherduck-sql-reference/detach.md) instead.
# Syntax
```sql
DROP SHARE "";
DROP SHARE IF EXISTS "";
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/explain-analyze
---
sidebar_position: 1
title: EXPLAIN ANALYZE
---
# EXPLAIN ANALYZE
`EXPLAIN ANALYZE` displays and executes the query plan, showing performance metrics and cardinality information for each operator.
:::note
The [query profiling guide on DuckDB](https://duckdb.org/docs/stable/dev/profiling.html) is a great place to start with this topic.
:::
## Syntax
This SQL query shows the physical query plan for an example query.
```sql
EXPLAIN ANALYZE
```
## Example Usage
```sql
EXPLAIN ANALYZE
SELECT 1 AS col
UNION ALL
FROM (SELECT vendorId
FROM sample_data.nyc.taxi LIMIT 1);
```
This will return the analyzed plan, which shows which elements of the query are running locally and some are running remotely on MotherDuck.
```
┌─────────────────────────────────────┐
│┌───────────────────────────────────┐│
││ Query Profiling Information ││
│└───────────────────────────────────┘│
└─────────────────────────────────────┘
EXPLAIN ANALYZE SELECT 1 AS col UNION ALL FROM (SELECT vendorId FROM sample_data.nyc.taxi LIMIT 1)
-- MD_SQL_METADATA: {"source":"hatchling","purpose":"runCellStatement","containsUserSQL":true}
┌────────────────────────────────────────────────┐
│┌──────────────────────────────────────────────┐│
││ Total Time: 0.0955s ││
│└──────────────────────────────────────────────┘│
└────────────────────────────────────────────────┘
┌───────────────────────────┐
│ QUERY │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ EXTENSION │
│ ──────────────────── │
│ md_type: │
│ HYBRID_STATS_COLLECTOR │
│ │
│ 0 Rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ EXPLAIN_ANALYZE │
│ ──────────────────── │
│ 0 Rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ EXTENSION │
│ ──────────────────── │
│ md_type: │
│ HYBRID_RUNNER │
│ │
│ 0 Rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ EXTENSION │
│ ──────────────────── │
│ md_type: │
│ DOWNLOAD_SOURCE │
│ │
│ bridge_id: 1 │
│ │
│ 2 Rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ EXTENSION │
│ ──────────────────── │
│ md_type: │
│ DOWNLOAD_SINK │
│ │
│ bridge_id: 1 │
│ parallel: false │
│ │
│ 0 Rows │
│ (0.00s) │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│ UNION │
│ ──────────────────── │
│ 2 Rows ├──────────────┐
│ (0.00s) │ │
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ PROJECTION ││ STREAMING_LIMIT │
│ ──────────────────── ││ ──────────────────── │
│ col ││ │
│ ││ │
│ 1 Rows ││ 1 Rows │
│ (0.00s) ││ (0.00s) │
└─────────────┬─────────────┘└─────────────┬─────────────┘
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ DUMMY_SCAN ││ TABLE_SCAN │
│ ──────────────────── ││ ──────────────────── │
│ ││ Table: taxi │
│ ││ Type: Sequential Scan │
│ ││ Projections: VendorID │
│ ││ │
│ 1 Rows ││ 4096 Rows │
│ (0.00s) ││ (0.00s) │
└───────────────────────────┘└───────────────────────────┘
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/explain
---
sidebar_position: 1
title: EXPLAIN
---
# EXPLAIN
The `EXPLAIN` statement shows the physical query plan that will be executed. It displays a tree of operators that will run in sequence to produce the query results. The query optimizer transforms this plan to improve performance.
On MotherDuck queries, `(L)` indicates queries that are executed **locally** and `(R)` indicates the queries are executed **remotely**.
For more detailed query analysis, review the documentation on [`EXPLAIN ANALYZE`](/sql-reference/motherduck-sql-reference/explain-analyze/).
:::note
The [query profiling guide on DuckDB](https://duckdb.org/docs/stable/dev/profiling.html) is a great place to start with this topic.
:::
## Syntax
This SQL query shows the physical query plan for an example query.
```sql
EXPLAIN
```
## Example Usage
```sql
EXPLAIN
SELECT 1 AS col
UNION ALL
SELECT 2
```
This will return the physical plan, which executes entirely locally.
```
Physical Plan
┌───────────────────────────┐
│ UNION (L) ├──────────────┐
└─────────────┬─────────────┘ │
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ PROJECTION (L) ││ PROJECTION (L) │
│ ──────────────────── ││ ──────────────────── │
│ col ││ 2 │
│ ││ │
│ ~1 Rows ││ ~1 Rows │
└─────────────┬─────────────┘└─────────────┬─────────────┘
┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│ DUMMY_SCAN (L) ││ DUMMY_SCAN (L) │
└───────────────────────────┘└───────────────────────────┘
```
#### `EXPLAIN` in MotherDuck compared to DuckDB
The MotherDuck `EXPLAIN` plan is similar to the DuckDB `EXPLAIN` plan, with two main differences:
* Operations that run locally are marked as (L), and operations running remotely on the MotherDuck service are marked as (R).
* The MotherDuck DuckDB extension adds four new type of custom operators, to exchange data between your local DuckDB and the MotherDuck service:
* The **`UploadSink`** operator runs locally and sends data from your local DuckDB to the remote MotherDuck service.
* The **`UploadSource`** operator runs remotely in the DuckDB on the MotherDuck side and consumes the uploaded data.
* The **`DownloadSink`** operator runs remotely on the MotherDuck side and prepares the data to be downloaded by the local DuckDB.
* The **`DownloadSource`** operator runs in your local DuckDB, fetching the data from the MotherDuck service made available via the remote DownloadSink.
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/grant-access
---
sidebar_position: 1
title: GRANT READ ON SHARE
---
# GRANT READ ON SHARE
For restricted shares, use the `GRANT` command to explicitly give users access to the share. After a user has been `GRANT`-ed access they will still need to run an `ATTACH` command to be able to run queries against the shared database. Only the owner of the share can use the `GRANT` command to give access to others.
## Syntax
```sql
GRANT READ ON SHARE TO [, , ];
```
## Example usage
```sql
-- Owner: gives the user with username 'duck' access to the share 'birds'
GRANT READ ON SHARE birds TO duck;
-- Owner: gives the users with usernames 'usr1' and 'usr2' access to the share 'taxis'
GRANT READ ON SHARE taxis TO user_1, user_2;
```
If a username contains special characters, such as '@', it must be enclosed in double quotes (`"`).
## Complete workflow example
Below is a complete workflow showing how to share a database with a restricted audience and how recipients can access it. We will first create a share and grant access to specific users. Then, we will show how recipients can attach and query the shared database. For more information on each step, refer to the [CREATE SHARE](create-share.md), [LIST SHARES](list-shares.md), and [ATTACH](attach.md) documentation.
### 1. Owner creates a share and grants access
```sql
-- Owner: create a restricted share of database 'analytics'
-- Using CREATE OR REPLACE allows updating the share if it already exists.
-- ACCESS RESTRICTED is required to use GRANT/REVOKE.
CREATE OR REPLACE SHARE analytics_share FROM analytics (ACCESS RESTRICTED);
-- Owner: Grant access to specific users.
-- If a username contains special characters like '@', enclose it in double quotes.
GRANT READ ON SHARE analytics_share TO user_1, "user_2@example-com";
-- Owner retrieves the share URL to provide to recipients.
-- The URL uniquely identifies the share.
LIST SHARES;
-- Example output contains URL like: md:_share/analytics/0a9a026ec5a55946a9de39851087ed81
-- Owner shares the full URL (e.g., 'md:_share/analytics/0a9a026ec5a55946a9de39851087ed81') with the granted users.
```
### 2. Recipient attaches and queries the shared database
```sql
-- Recipient: attach the shared database using the full URL provided by the owner.
-- Using the full URL prevents naming conflicts.
ATTACH 'md:_share/analytics/0a9a026ec5a55946a9de39851087ed81' AS analytics_data;
-- Recipient: switch to the attached database
USE analytics_data;
-- Recipient: query the shared database
SELECT * FROM customer_metrics LIMIT 10;
```
When the share is attached, it creates a read-only reference to the shared database that doesn't consume additional storage for the recipient. Recipients can query the data but cannot modify it.
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/list-secrets
---
sidebar_position: 1
title: LIST SECRETS
---
# LIST SECRETS
Secrets can be listed in the same way as in DuckDB by using the table function `duckdb_secrets()`.
# Syntax
```sql
FROM duckdb_secrets();
```
| name | type | provider | persistent | storage | scope | secret_string |
|-----------------|-------|------------------|------------|------------|-------------------------|-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| __default_azure | azure | credential_chain | false | memory | [azure://, az://] | name=__default_azure;type=azure;provider=credential_chain;serializable=true;scope=azure://,az://;account_name=some-account |
| __default_s3 | s3 | credential_chain | false | memory | [s3://, s3n://, s3a://] | name=__default_s3;type=s3;provider=credential_chain;serializable=true;scope=s3://,s3n://,s3a://;endpoint=s3.amazonaws.com;key_id=my_key;region=us-east-1;secret=redacted;session_token=redacted |
| __default_r2 | r2 | config | true | motherduck | [r2://] | name=__default_r2;type=r2;provider=config;serializable=true;scope=r2://;endpoint=my_account.r2.cloudflarestorage.com;key_id=my_key;region=us-east-1;s3_url_compatibility_mode=0;secret=redacted;session_token=redacted;url_style=path;use_ssl=1 |
| __default_gcs | gcs | config | true | motherduck | [gcs://, gs://] | name=__default_gcs;type=gcs;provider=config;serializable=true;scope=gcs://,gs://;endpoint=storage.googleapis.com;key_id=my_key;region=us-east-1;s3_url_compatibility_mode=0;secret=redacted;session_token=redacted;url_style=path;use_ssl=1 |
:::note
DuckDB allows you to specify `redact` when listing secrets (it's set to `true` by default). However, MotherDuck secrets are always redacted for security reasons despite the flag.
:::
# Example Usage
To inspect specific field(s) in `secret_string`:
```sql
select
name, storage,
list_filter(split(secret_string,';'), x -> starts_with(x, 'region'))[1]
from duckdb_secrets(redact=false) where name='__default_s3';
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/list-shares
---
sidebar_position: 1
title: LIST SHARES
---
# LIST SHARES
The `LIST SHARES` statement lists all shares created by the current user.
It provides the same information as querying the [`MD_INFORMATION_SCHEMA.OWNED_SHARES`](/sql-reference/motherduck-sql-reference/md_information_schema/owned_shares/) view. You can also use the table function `md_list_database_shares()` for a subset of this information.
:::tip
To see shares that have been shared *with* you (by others), query the [`MD_INFORMATION_SCHEMA.SHARED_WITH_ME`](/sql-reference/motherduck-sql-reference/md_information_schema/shared_with_me/) view instead.
:::
## Syntax
```sql
-- Using DDL (lists all owned shares with details)
LIST SHARES;
-- Equivalent to querying the information schema view
SELECT * FROM MD_INFORMATION_SCHEMA.OWNED_SHARES;
-- Using table function (returns a subset of columns)
SELECT name, url, source_db_name FROM md_list_database_shares();
```
## Output
The `LIST SHARES` statement and `SELECT * FROM MD_INFORMATION_SCHEMA.OWNED_SHARES` return a table with the following columns:
| Column Name | Data Type | Value |
| ---------------- | --------- | ------------------------------------------------------------------------------------------------------------------------------------------------- |
| NAME | STRING | The name of the share |
| URL | STRING | The `share_url` which can be used to attach the share |
| SOURCE_DB_NAME | STRING | The name of the database where this share was created from |
| SOURCE_DB_UUID | UUID | UUID of the database where this share was created from |
| ACCESS | STRING | Whether anyone (`UNRESTRICTED`) within the same cloud region or only organization members (`ORGANIZATION`) can attach to the share by its `share_url`. RESTRICTED shares are hidden from the list. |
| VISIBILITY | STRING | Whether the share is `DISCOVERABLE` or `HIDDEN` |
| UPDATE | STRING | The share's update mode (`MANUAL` vs. `AUTOMATIC`) |
| CREATED_TS | TIMESTAMP | The share's creation time |
:::note
Shares are **region-scoped** based on your Organization's cloud region. Each MotherDuck Organization is currently scoped to a single cloud region that must be chosen at Org creation when signing up.
MotherDuck is currently available on AWS in two regions:
- **US East (N. Virginia):** `us-east-1`
- **Europe (Frankfurt):** `eu-central-1`
:::
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md-run-parameter
---
sidebar_position: 1
title: MD_RUN parameter
---
# MD_RUN parameter
For certain DuckDB **Table Functions**, MotherDuck now provides an additional parameter, `MD_RUN` that gives explicit control over where the query is executed.
This parameter is available to the following functions:
- `read_csv()`
- `read_csv_auto()`
- `read_json()`
- `read_json_auto()`
- `read_parquet()` and its alias `parquet_scan()`
To leverage the MD_RUN parameter, you can choose:
- `MD_RUN=LOCAL` executes the function in your local DuckDB environment
- `MD_RUN=REMOTE` executes the function in MotherDuck-hosted DuckDB runtimes in the cloud
- `MD_RUN=AUTO` executes remotely all s3://, http://, and https:// requests, except those to localhost/127.0.01. This is the default option.
The following is an example of evoking this parameter to execute the function remotely:
```sql
SELECT *
FROM read_csv_auto(
'https://github.com/duckdb/duckdb/raw/main/data/csv/ips.csv.gz',
MD_RUN=REMOTE)
LIMIT 100
```
In this example `MD_RUN=REMOTE` is redundant, because omitting it implies `MD_RUN=AUTO` and given that this is a non-local https:// resource, MotherDuck will automatically chose remote execution already.
One can force local execution with `MD_RUN=LOCAL`. Be aware that DuckDB-WASM does not support reading compressed files yet, so inside the Web Browser one would get an error for this particular file as it is ips.csv**.gz** (it does work locally from the CLI or e.g. a python notebook).
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/database_size
---
sidebar_position: 5
title: PRAGMA database_size
---
# Database Size
Database size can be fetched with `PRAGMA database_size;`. It contains attributes to allow insights into the sizes of MotherDuck databases.
### Alternative invocations
This Pragma can also be invoked as a tabular function with:
- `FROM pragma_database_size();`
## Schema
`PRAGMA database_size` has the following schema:
| Column Name | Data Type | Value |
|-----------------------|-------------|-----------------------------------|
| database_name | VARCHAR | name of the database |
| database_size | VARCHAR | database size in 1000 byte increments (i.e. Kilobytes) |
| block_size | BIGINT | _not currently returned_ |
| used_blocks | BIGINT | _not currently returned_ |
| total_blocks | BIGINT | _not currently returned_ |
| free_blocks | BIGINT | _not currently returned_ |
| wal_size | VARCHAR | _not currently returned_ |
| memory_usage | VARCHAR | _not currently returned_ |
| memory_limit | VARCHAR | _not currently returned_ |
## Example Usage
```sql
PRAGMA database_size;
```
Example result:
| database_name | database_size | block_size | used_blocks | total_blocks | free_blocks | wal_size | memory_usage | memory_limit |
|---------------|---------------|------------|-------------|--------------|-------------|----------|--------------|--------------|
| my_db | 153.9 GiB | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| another_database | 3.1 TiB | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
In some cases, you may want to filter the dataset, in which case you can use a tabular function in your `FROM` clause. An example is shown below:
```sql
SELECT *
FROM pragma_database_size()
WHERE database_name = 'my_db'
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/databases
---
sidebar_position: 1
title: DATABASES view
---
# DATABASES view
The `MD_INFORMATION_SCHEMA.DATABASES` view provides information about the current databases that the user created.
## Schema
When you query the `MD_INFORMATION_SCHEMA.DATABASES` view, the query results contain one row for each database that the current user created.
The `MD_INFORMATION_SCHEMA.DATABASES` view has the following schema:
| Column Name | Data Type | Value |
|-------------|-----------|-----------------------------------|
| NAME | STRING | The name or alias of the database |
| UUID | STRING | The UUID of the database |
| CREATED_TS | TIMESTAMP | The database’s creation time |
## Example usage
```sql
from MD_INFORMATION_SCHEMA.DATABASES;
```
| name | uuid | created_ts |
|----------------------|--------------------------------------|------------------------|
| tpch_sf1000_template | 2c80b37d-d307-44d8-aff6-33ea2294bd35 | 2024-10-21 14:26:30-04 |
| db1 | 445864c7-5758-42a2-9a5c-2f16620ebc9f | 2024-09-15 09:32:05-04 |
| foo | 4d829a9e-e0da-408c-aafa-0fc50186a588 | 2024-09-03 13:32:10-04 |
| tpch_sf1000 | fc4bf9f4-80d1-4fd9-b6fe-d6d71f40ef42 | 2024-10-21 14:26:30-04 |
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/introduction
---
title: Introduction to MD_INFORMATION_SCHEMA
description: Introduction to MD_INFORMATION_SCHEMA
---
# Introduction to MD_INFORMATION_SCHEMA
The MotherDuck `MD_INFORMATION_SCHEMA` views are read-only, system-defined views that provide metadata information
about your MotherDuck objects.
The following table lists all `MD_INFORMATION_SCHEMA` views that you can query to retrieve metadata information:
| Resource Type | MD_INFORMATION_SCHEMA View |
|-----------------|----------------------------------|
| Database | [DATABASES](databases.md) |
| Database Size | [PRAGMA database_size](database_size.md) |
| Database Shares | [OWNED_SHARES](owned_shares.md)
[SHARED_WITH_ME](shared_with_me.md)
|
| Storage | [STORAGE_INFO](storage_info.md) _includes history_ |
| Queries | [RECENT_QUERIES](recent_queries.md) |
| Queries | [QUERY_HISTORY](query_history.md) |
## Example usage
```sql
-- list all databases you created
from md_information_schema.databases;
-- list all shares you created
from md_information_schema.owned_shares;
-- select specific columns
select name, url, access, visibility from md_information_schema.owned_shares;
-- set md_information_schema as the current database
use md_information_schema;
-- list all the views in md_information_schema
show tables;
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/owned_shares
---
sidebar_position: 2
title: OWNED_SHARES view
---
# OWNED_SHARES view
The `MD_INFORMATION_SCHEMA.OWNED_SHARES` view provides information about shares created by the current user.
:::note
Shares are **region-scoped** based on your Organization's cloud region. Each MotherDuck Organization is currently scoped to a single cloud region that must be chosen at Org creation when signing up.
MotherDuck is currently available on AWS in two regions:
- **US East (N. Virginia):** `us-east-1`
- **Europe (Frankfurt):** `eu-central-1`
:::
## Schema
Querying the `MD_INFORMATION_SCHEMA.OWNED_SHARES` view will return query results that contain one row for each share created by the current user.
The `MD_INFORMATION_SCHEMA.OWNED_SHARES` view has the following schema:
| Column Name | Data Type | Value |
|-------------|-----------|-----------------------------------|
| NAME | STRING | The name of the share |
| URL | STRING | The share_url which can be used to attach the share |
| SOURCE_DB_NAME | STRING | The name of the database where this share was created from |
| SOURCE_DB_UUID | UUID | UUID of the database where this share was created from |
| ACCESS | STRING | Whether anyone (referred to as UNRESTRICTED) or only organization members (referred to as ORGANIZATION) can attach to the share by its share_url |
| GRANTS | STRUCT(username VARCHAR, access VARCHAR)[] | A list of all grants that are active for the share |
| VISIBILITY | STRING | Whether the share is DISCOVERABLE or HIDDEN |
| UPDATE | STRING | The share’s update mode (MANUAL vs. AUTOMATIC) |
| CREATED_TS | TIMESTAMP | The share’s creation time |
## Example usage
```sql
from MD_INFORMATION_SCHEMA.OWNED_SHARES;
select name, url, created_ts from MD_INFORMATION_SCHEMA.OWNED_SHARES;
```
| name | url | source_db_name |
|----------|---------------------------------------------------------|----------------|
| my_share | md:_share/my_share/2ef6b580-2445-4f4f-bce8-c13a85812464 | db1 |
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/query_history
---
sidebar_position: 1
title: QUERY_HISTORY view
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Admonition from '@theme/Admonition';
::::warning[Preview Feature]
This is a preview feature only available on Business plans. Note that preview features may be operationally incomplete and may offer limited backward compatibility. This feature is only available for organization admins.
::::
# QUERY_HISTORY view
The `MD_INFORMATION_SCHEMA.QUERY_HISTORY` view provides organization admins with a consolidated view of all queries run across their full organization.
## Schema
When you query the `MD_INFORMATION_SCHEMA.QUERY_HISTORY` view, the query results contain one row for each query that was run in the organization. Note that the information in this view will have some delays. A more realtime view of ongoing and recently completed queries that have not been captured in `QUERY_HISTORY` yet is provided via the [`MD_INFORMATION_SCHEMA.RECENT_QUERIES`](recent_queries.md) view.
The `MD_INFORMATION_SCHEMA.QUERY_HISTORY` view has the following schema:
| Column Name | Data Type | Value |
|-----------------------|-------------|-----------------------------------|
| QUERY_ID | UUID | A unique ID representing the particular query run |
| QUERY_TEXT | STRING | Query SQL text (up to 100k chars) |
| START_TIME | TIMESTAMPTZ | Start time of the query |
| END_TIME | TIMESTAMPTZ | End time of the query |
| EXECUTION_TIME | INTERVAL | Duration where the query is actively executing |
| WAIT_TIME | INTERVAL | Duration where the query is waiting on resources to become available. For example a query needs to wait because other queries are using all available execution threads, or a query might be waiting on data to become available (in case of data upload). |
| TOTAL_ELAPSED_TIME | INTERVAL | Total duration of the query |
| ERROR_MESSAGE | STRING | Error message, if the query returned an error |
| ERROR_TYPE | STRING | Error type, if the query returned an error |
| USER_AGENT | STRING | User agent of the client |
| USER_NAME | STRING | Identifier for the MotherDuck user in their organization |
| QUERY_NR | UBIGINT | ID of the query within the transaction that ran the query. Number that just increments for each query that is run within a given transaction |
| TRANSACTION_NR | UBIGINT | ID of the transaction that contained the query. Number that just increments for each new transaction on a given connection |
| CONNECTION_ID | UUID | Unique ID for the [client DuckDB connection](../connection-management/connection-duckdb-id.md) where the query was issued |
| DUCKDB_ID | UUID | Unique ID for the [client DuckDB instance](../connection-management/connection-duckdb-id.md) where the query was issued |
| DUCKDB_VERSION | STRING | Client DuckDB version that issued the query |
| INSTANCE_TYPE | STRING | The size of Duckling that the query was run on (Pulse / Standard / Jumbo / Mega / Giga / ...) |
| QUERY_TYPE | STRING | The nature of the query (DDL / DML / QUERY / ...) |
| BYTES_UPLOADED | UBIGINT | Number of bytes uploaded from client to server (relevant for hybrid queries) |
| BYTES_DOWNLOADED | UBIGINT | Number of bytes downloaded from server to client (relevant for hybrid queries) |
| BYTES_SPILLED_TO_DISK | UBIGINT | Total number of bytes [spilled to disk](https://duckdb.org/docs/stable/guides/performance/how_to_tune_workloads.html#spilling-to-disk) for "larger than in-memory" workloads |
| DUCKLING_ID | STRING | Identifies the duckling that ran the query. It is composed of the user name and a qualifier (`rw` for read-write ducklings, or `rs.0`, `rs.1`, ... for the respective read-scaling duckling) |
| SESSION_NAME | STRING | The [`session_hint`](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/?_gl=1*l0d2b8*_up*MQ..*_ga*MzY2OTE2Mjc4LjE3NTUxNjQ3MDY.*_ga_L80NDGFJTP*czE3NTUxNjQ3MDYkbzEkZzAkdDE3NTUxNjQ3MDYkajYwJGwwJGg3OTc4MzAwODU.#read-scaling-with-session-hints) that was supplied for connecting to the read-scaling duckling |
Note that the fields `START_TIME`, `END_TIME`, `TOTAL_ELAPSED_TIME`, `ERROR_MESSAGE`, and `ERROR_TYPE` are currently just captured on the server (i.e. when query starts and ends on server), in the future they will be based on client information too (taking better into account the full hybrid context).
## Example usage
```sql
from MD_INFORMATION_SCHEMA.QUERY_HISTORY limit 10;
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/recent_queries
---
sidebar_position: 1
title: RECENT_QUERIES view
---
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Admonition from '@theme/Admonition';
::::warning[Preview Feature]
This is a preview feature only available on Business plans. Note that preview features may be operationally incomplete and may offer limited backward compatibility. This feature is only available for organization admins.
::::
# RECENT_QUERIES view
The `MD_INFORMATION_SCHEMA.RECENT_QUERIES` view provides organization admins with a consolidated view of all currently running or recently completed queries across their full organization. It complements the [`MD_INFORMATION_SCHEMA.QUERY_HISTORY`](query_history.md) view, which is geared towards analytics of past events and has some delays, with a more realtime view of recent queries (active and completed) that are not yet exposed in `QUERY_HISTORY`.
## Schema
When you query the `MD_INFORMATION_SCHEMA.RECENT_QUERIES` view, the query results contain one row for each query that is running or has recently completed in the organization. Note that the information in this view is updated every couple of seconds.
The `MD_INFORMATION_SCHEMA.RECENT_QUERIES` view shares the same schema as the [`MD_INFORMATION_SCHEMA.QUERY_HISTORY`](query_history.md) view. The main difference is that for queries that have not completed yet the `END_TIME` field is null, and all other fields represent ongoing metrics that will be updated every few seconds.
Full schema:
| Column Name | Data Type | Value |
|-----------------------|-------------|-----------------------------------|
| QUERY_ID | UUID | A unique ID representing the particular query run |
| QUERY_TEXT | STRING | Query SQL text (up to 100k chars) |
| START_TIME | TIMESTAMPTZ | Start time of the query |
| END_TIME | TIMESTAMPTZ | End time of the query, if the query is completed |
| EXECUTION_TIME | INTERVAL | Duration where the query is actively executing |
| WAIT_TIME | INTERVAL | Duration where the query is waiting on resources to become available. For example a query needs to wait because other queries are using all available execution threads, or a query might be waiting on data to become available (in case of data upload). |
| TOTAL_ELAPSED_TIME | INTERVAL | Total duration of the query (the sum of execution time and wait time) |
| ERROR_MESSAGE | STRING | Error message, if the query returned an error |
| ERROR_TYPE | STRING | Error type, if the query returned an error |
| USER_AGENT | STRING | User agent of the client |
| USER_NAME | STRING | Identifier for the MotherDuck user in their organization |
| QUERY_NR | UBIGINT | ID of the query within the transaction that ran the query. Number that just increments for each query that is run within a given transaction |
| TRANSACTION_NR | UBIGINT | ID of the transaction that contained the query. Number that just increments for each new transaction on a given connection |
| CONNECTION_ID | UUID | Unique ID for the [client DuckDB connection](../connection-management/connection-duckdb-id.md) where the query was issued |
| DUCKDB_ID | UUID | Unique ID for the [client DuckDB instance](../connection-management/connection-duckdb-id.md) where the query was issued |
| DUCKDB_VERSION | STRING | Client DuckDB version that issued the query |
| INSTANCE_TYPE | STRING | The type of duckling that the query was run on (Pulse / Standard / Jumbo / Mega / Giga / ...) |
| QUERY_TYPE | STRING | The nature of the query (DDL / DML / QUERY / ...) |
| BYTES_UPLOADED | UBIGINT | Number of bytes uploaded from client to server (relevant for hybrid queries) |
| BYTES_DOWNLOADED | UBIGINT | Number of bytes downloaded from server to client (relevant for hybrid queries) |
| BYTES_SPILLED_TO_DISK | UBIGINT | Total number of bytes [spilled to disk](https://duckdb.org/docs/stable/guides/performance/how_to_tune_workloads.html#spilling-to-disk) for "larger than in-memory" workloads |
| DUCKLING_ID | STRING | Identifies the duckling that ran the query. It is composed of the user name and a qualifier (`rw` for read-write ducklings, or `rs.0`, `rs.1`, ... for the respective read-scaling duckling) |
| SESSION_NAME | STRING | The [`session_hint`](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/?_gl=1*l0d2b8*_up*MQ..*_ga*MzY2OTE2Mjc4LjE3NTUxNjQ3MDY.*_ga_L80NDGFJTP*czE3NTUxNjQ3MDYkbzEkZzAkdDE3NTUxNjQ3MDYkajYwJGwwJGg3OTc4MzAwODU.#read-scaling-with-session-hints) that was supplied for connecting to the read-scaling duckling |
Note that the fields `START_TIME`, `END_TIME`, `TOTAL_ELAPSED_TIME`, `ERROR_MESSAGE`, and `ERROR_TYPE` are currently just captured on the server (i.e. when query starts and ends on server), in the future they will be based on client information too (taking better into account the full hybrid context).
## Example usage
```sql
from MD_INFORMATION_SCHEMA.RECENT_QUERIES where end_time is null limit 10;
```
## Limitations
The `RECENT_QUERIES` view has been optimized for quickly answering questions such as "Which ongoing queries in my organization are taking a long time to complete". Query results of this view are therefore limited to 1000 rows, but support filter pushdowns so that this limit only applies after some basic filters. Take for example the following query:
```sql
from MD_INFORMATION_SCHEMA.RECENT_QUERIES where end_time is null and total_elapsed_time > '5 seconds';
```
The 1000 row limit only applies after the `end_time` and the `total_elapsed_time` filters have been applied, showing at most 1000 queries that are still ongoing and that are taking longer than 5 seconds. To check what filters are pushed down and apply before the row limit, the "filters" section of the MD_SERVER_RECENT_QUERIES table scan operator can be checked in the [query plan explain output](https://duckdb.org/docs/stable/guides/meta/explain).
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/shared_with_me
---
sidebar_position: 3
title: SHARED_WITH_ME view
---
# SHARED_WITH_ME view
The `MD_INFORMATION_SCHEMA.SHARED_WITH_ME` view provides information about all shares that the current user can attach to (excluding their own created shares).
:::note
Shares are **region-scoped** based on your Organization's cloud region. Each MotherDuck Organization is currently scoped to a single cloud region that must be chosen at Org creation when signing up.
MotherDuck is currently available on AWS in two regions:
- **US East (N. Virginia):** `us-east-1`
- **Europe (Frankfurt):** `eu-central-1`
:::
## Schema
When you query the `MD_INFORMATION_SCHEMA.SHARED_WITH_ME` view, the query results contain one row for each share that the current user can discover.
The `MD_INFORMATION_SCHEMA.SHARED_WITH_ME` view has the following schema:
| Column Name | Data Type | Value |
|-------------|-----------|-----------------------------------|
| NAME | STRING | The name of the share |
| URL | STRING | The share_url which can be used to attach the share |
| CREATED_TS | TIMESTAMP | The share’s creation time |
| UPDATE | STRING | The share’s update mode (MANUAL vs. AUTOMATIC) |
| ACCESS | STRING | Whether anyone (referred to as UNRESTRICTED) or only organization members (referred to as ORGANIZATION) can attach to the share by its share_url |
## Example usage
```sql
from MD_INFORMATION_SCHEMA.SHARED_WITH_ME;
```
| name | url | created_ts |update | access |
|-----------------------------|----------------------------------------------------------------------------|------------------------|----------|-----------|
| efs_ia_benchmark | md:_share/efs_ia_benchmark/11597119-359a-4e02-8e5c-bc2b9b8c1908 | 2024-07-16 15:09:11-04 | MANUAL | ORGANIZATION |
| hf_load_test_share | md:_share/hf_load_test_share/f76062a5-f1f5-4024-987d-fc2eea48311b | 2024-07-29 09:07:33-04 | MANUAL | ORGANIZATION |
| mdw | md:_share/mdw/87be4635-fbfd-4d4b-9cae-b629842733d5 | 2024-10-16 17:04:42-04 | AUTOMATIC | ORGANIZATION |
| my_sample_share | md:_share/my_sample_share/c4ee2a30-2fb6-4cb5-b664-9030ae43ffdc | 2024-09-24 17:53:23-04 | MANUAL | ORGANIZATION |
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/storage_info
---
sidebar_position: 1
title: STORAGE_INFO views
description: View storage footprint, billing, and lifecycle information for all databases in your MotherDuck organization
---
import Admonition from '@theme/Admonition';
# STORAGE_INFO views
:::note Admin Only Feature
This feature can only be used by Admin users.
:::
## Overview
MotherDuck provides two views to look at how much storage is used - a current snapshot (`STORAGE_INFO`) and the previous 30 days of history (`STORAGE_INFO_HISTORY`).
The `MD_INFORMATION_SCHEMA.STORAGE_INFO` view provides comprehensive storage information for all databases in your MotherDuck organization. This view is essential for understanding storage usage, billing calculations, and database lifecycle management.
The `MD_INFORMATION_SCHEMA.STORAGE_INFO_HISTORY` view provides storage information for up to the past 30 days of usage.
If you're an admin, you can view your organization's storage breakdown on the [databases page](https://app.motherduck.com/settings/databases). Here, you'll find the total breakdown of current bytes across all your databases, as well as a breakdown for each database. You can also click on a row to get a lifecycle breakdown for a given database.
## Syntax
To see the latest snapshot:
```sql
SELECT * FROM MD_INFORMATION_SCHEMA.STORAGE_INFO;
```
To see the history:
```sql
SELECT * FROM MD_INFORMATION_SCHEMA.STORAGE_INFO_HISTORY;
```
## Columns
The `MD_INFORMATION_SCHEMA.STORAGE_INFO` view returns one row for each database in your organization with the following columns:
| Column Name | Data Type | Description |
| ----------------------- | --------- | -------------------------------------------------------------------------------------------------------------------------------------- |
| `database_name` | VARCHAR | Name of the database |
| `database_id` | UUID | Unique ID for the database |
| `created_ts` | TIMESTAMP | Time when the database was created |
| `deleted_ts` | TIMESTAMP | Time when the database was deleted (NULL if not deleted) |
| `username` | VARCHAR | Username of the database owner |
| `active_bytes` | BIGINT | Actively referenced bytes of the database |
| `historical_bytes` | BIGINT | Non-active bytes that are referenced by a share of this database |
| `kept_for_cloned_bytes` | BIGINT | Bytes referenced by other databases (via zero-copy clone) that are no longer referenced by this database as active or historical bytes |
| `failsafe_bytes` | BIGINT | Bytes that are no longer referenced by any database or share |
| `computed_ts` | TIMESTAMP | Time at which active_bytes, historical_bytes, etc. were computed |
The `MD_INFORMATION_SCHEMA.STORAGE_INFO_HISTORY` view has the same schema, but will return results from up to the past 30 days, so a single database might have multiple entries reflecting its state at different points in time.
## Examples
### Basic Usage
View storage information for all databases in your organization:
```sql
-- Get storage information for all databases
SELECT * FROM MD_INFORMATION_SCHEMA.STORAGE_INFO;
```
**Sample results:**
| database_name | database_id | created_ts | deleted_ts | username | active_bytes | historical_bytes | kept_for_cloned_bytes | failsafe_bytes | results_ts |
| ------------- | ------------------------------------ | ------------------- | ---------- | -------- | ------------ | ---------------- | --------------------- | -------------- | ---------------------- |
| test_db_1 | 7ed1baf3-e4ff-42c9-a37b-9f683905ce45 | 2024-12-02 20:18:36 | NULL | bob | 82063360 | 0 | 268496896 | 0 | 2025-06-25 16:46:16.37 |
| test_db_2 | fcc16e53-d761-4e40-84ec-15570fab363e | 2024-11-12 03:38:52 | NULL | jim | 274432 | 0 | 0 | 0 | 2025-06-25 16:46:16.37 |
### Filtering and Analysis
Find databases with high storage usage:
```sql
-- Find databases using more than 1GB of active storage
SELECT
database_name,
username,
active_bytes,
ROUND(active_bytes / 1000.0 / 1000.0 / 1000.0, 2) as active_gb
FROM MD_INFORMATION_SCHEMA.STORAGE_INFO
WHERE active_bytes > 1000000000 -- 1GB in bytes
ORDER BY active_bytes DESC;
```
### Storage Cost Analysis
Analyze storage costs by user:
```sql
-- Calculate total storage usage per user
SELECT
username,
COUNT(*) as database_count,
SUM(active_bytes) as total_active_bytes,
SUM(historical_bytes) as total_historical_bytes,
SUM(kept_for_cloned_bytes) as total_cloned_bytes,
SUM(failsafe_bytes) as total_failsafe_bytes
FROM MD_INFORMATION_SCHEMA.STORAGE_INFO
GROUP BY username
ORDER BY total_active_bytes DESC;
```
Analyze active and failsafe storage footprint over the past week for a specific database:
```sql
SELECT active_bytes, failsafe_bytes, computed_ts
FROM MD_INFORMATION_SCHEMA.STORAGE_INFO_HISTORY
WHERE database_name = "my_database"
AND computed_ts >= NOW - INTERVAL 7 DAYS
ORDER BY computed_ts DESC;
```
## Notes
- **Data Refresh**: Information in this view refreshes every 1-6 hours
- **Retention**: STORAGE_INFO_HISTORY only returns one set of results per day, even though the latest results are re-computed multiple times per day
- **Billing Data**: This view returns the underlying data used to power MotherDuck storage billing
- **Permissions**: You must have appropriate permissions to access this view
- **Organization Scope**: Only shows databases within your current organization
## Troubleshooting
### Common Issues
**Outdated information**
- Data refreshes only happen periodically, so recent changes may not be immediately visible
**Permission denied errors**
- Contact your organization administrator to ensure you have the necessary permissions
- This feature is only for Admins
- Verify your authentication token is valid and has the required scope
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/print-md-token
---
sidebar_position: 1
title: PRINT_MD_TOKEN pragma
---
# PRINT_MD_TOKEN pragma
You can retrieve your MotherDuck authentication token using the `PRINT_MD_TOKEN` pragma.
In CLI or Python, to avoid having to re-authenticate every time, you can store your token as an environment variable; for example, by running `export motherduck_token='xxxx'` in the terminal. Be sure to replace 'xxxx' with your own token!
# Syntax
```sql
PRAGMA PRINT_MD_TOKEN;
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/refresh-database
---
sidebar_position: 1
title: REFRESH DATABASE
---
# REFRESH DATABASE
There are two types of databases that can be refreshed: **database shares** and databases attached to **read-scaling**
connections.
**Read-scaling** connections sync automatically every minute. To ensure maximum freshness, run `CREATE SNAPSHOT` on the
writer, followed by `REFRESH DATABASES` on the reader. This pulls the latest snapshot.
**Database shares** can also be refreshed—either automatically or manually. In this case, the writer uses `UPDATE SHARE`
instead of `CREATE SNAPSHOT`, followed by `REFRESH DATABASES` on the reader.
## Behavior by connection mode
The behavior of `REFRESH DATABASES` depends on how you connected to MotherDuck:
- **Workspace mode** (`ATTACH 'md:'`): Refreshes all databases in your workspace, including new databases created by other connections (e.g., R/W instances). This allows you to pick up databases that were created after your initial connection.
## Syntax
```sql
REFRESH { DATABASE | DATABASES } [];
```
## Examples
```sql
REFRESH DATABASES; -- Refreshes all connected databases and shares
┌─────────┬───────────────────┬──────────────────────────┬───────────┐
│ name │ type │ fully_qualified_name │ refreshed │
│ varchar │ varchar │ varchar │ boolean │
├─────────┼───────────────────┼──────────────────────────┼───────────┤
│ │ motherduck │ md: │ false │
│ │ motherduck share │ md:_share// │ true │
└─────────┴───────────────────┴──────────────────────────┴───────────┘
REFRESH DATABASE my_db; -- Alternatively, refresh a specific database
┌─────────┬──────────────────┬──────────────────────────┬───────────┐
│ name │ type │ fully_qualified_name │ refreshed │
│ varchar │ varchar │ varchar │ boolean │
├─────────┼──────────────────┼──────────────────────────┼───────────┤
│ │ motherduck share │ md:_share// │ false │
└─────────┴──────────────────┴──────────────────────────┴───────────┘
```
## Related Content
- [CREATE SNAPSHOT](/sql-reference/motherduck-sql-reference/create-snapshot.md)
- [UPDATE SHARE](/sql-reference/motherduck-sql-reference/update-share.md)
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/revoke-access
---
sidebar_position: 1
title: REVOKE READ ON SHARE
---
# REVOKE READ ON SHARE
For restricted shares, use the `REVOKE` command to explicitly remove share access from users that have an existing `GRANT`. After running a `REVOKE` command there may be a delay of a few minutes before access is fully removed if a user is currently querying the share. Only the owner of the share can use the `REVOKE` command to remove access from others. `GRANT` and `REVOKE` do not apply to `UNRESTRICTED` shares.
## Syntax
```sql
REVOKE READ ON SHARE FROM [, , ];
```
If a username contains special characters, such as '@', it must be enclosed in double quotes (`"`).
## Example usage
```sql
-- revokes access to the share 'birds' from the user with username 'duck'
REVOKE READ ON SHARE birds FROM duck;
-- revokes access to the share 'taxis' from the users with usernames 'usr1' and 'usr2'
REVOKE READ ON SHARE taxis FROM usr1, usr2;
-- revokes access from a user whose username contains special characters
REVOKE READ ON SHARE sensitive_data FROM "user@example-com";
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/show-databases
---
sidebar_position: 1
title: SHOW ALL DATABASES
---
# SHOW ALL DATABASES
The `SHOW ALL DATABASES` statement shows all databases, would it be MotherDuck database, DuckDB database or MotherDuck shares.
It returns:
* `alias` (`db_name` or `share_alias`)
* `is_attached` flag to mention if the database is attached or not.
* `type` (e.g. DuckDB, MotherDuck, MotherDuck share)
* `fully_qualified_name` (empty, md:_share/ or md:db_name)
To query specific columns, you can use the table function `MD_ALL_DATABASES()`.
# Syntax
```sql
SHOW ALL DATABASES;
```
or using the table function
```sql
select * from MD_ALL_DATABASES();
```
# Example usage
```sql
SHOW ALL DATABASES;
```
Example output:
```bash
┌──────────────────────────────────────────┬─────────────┬──────────────────┬─────────────────────────────────────────────────────────────────────────────────────────┐
│ alias │ is_attached │ type │ fully_qualified_name │
│ varchar │ boolean │ varchar │ varchar │
├──────────────────────────────────────────┼─────────────┼──────────────────┼─────────────────────────────────────────────────────────────────────────────────────────┤
│ TEST_DB_02d6fc2158094bd693b6f285dbd402f7 │ true │ motherduck │ md:TEST_DB_02d6fc2158094bd693b6f285dbd402f7 │
│ TEST_DB_62b53d968a4f4b6682ed117a7251b814 │ true │ motherduck │ md:TEST_DB_62b53d968a4f4b6682ed117a7251b814 │
│ base │ false │ motherduck │ md:base │
│ base2 │ true │ motherduck │ md:base2 │
│ db1 │ false │ motherduck │ md:db1 │
│ integration_test_001 │ false │ motherduck │ md:integration_test_001 │
│ my_db │ true │ motherduck │ md:my_db │
│ my_share_1 │ true │ motherduck share │ md:_share/integration_test_001/18d6dbdb-e130-4cdf-97c4-60782ed5972b │
│ sample_data │ false │ motherduck │ md:sample_data │
│ source_db │ true │ motherduck │ md:source_db │
│ test_db_115 │ false │ motherduck │ md:test_db_115 │
│ test_db_28d │ false │ motherduck │ md:test_db_28d │
│ test_db_cc9 │ false │ motherduck │ md:test_db_cc9 │
│ test_share │ true │ motherduck share │ md:_share/source_db/b990b424-2f9a-477a-b216-680a22c3f43f │
│ test_share_002 │ true │ motherduck share │ md:_share/integration_test_001/06cc5500-e49a-4f62-9203-105e89a4b8ae │
├──────────────────────────────────────────┴─────────────┴──────────────────┴─────────────────────────────────────────────────────────────────────────────────────────┤
│ 15 rows (15 shown) 4 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
```
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/temporary-tables
---
sidebar_position: 1
title: TEMPORARY TABLES
---
# TEMPORARY TABLES
The `CREATE TEMPORARY TABLE` statement creates a new a temporary from a sql query. This command is used to create a local temporary table. [More information can be found in the DuckDB documentation.](https://duckdb.org/docs/sql/statements/create_table.html#temporary-tables)
## Syntax
```sql
CREATE [ OR REPLACE ] TEMPORARY TABLE [ IF NOT EXISTS ] AS ...
```
Temporary Tables can be created traditionally with column names and types, or with `Create Table ... As Select` (CTAS).
### Shorthand Convention
The word `TEMP` can be used interchangably with `TEMPORARY`.
## Example Usage
```sql
CREATE TEMPORARY TABLE flights AS
FROM 'https://duckdb.org/data/flights.csv';
```
This will create a local table with data from the duckdb `flights.csv` file.
## Notes
- Temporary Tables in MotherDuck persist locally, not on the server. As such, local constraints should be considered when using them.
- Because they are bound to your session, when your session ends, any temporary tables will no longer be available.
---
Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/update-share
---
sidebar_position: 1
title: UPDATE SHARE
---
# UPDATE SHARE
Shares can either be manually or automatically updated by the share creator. All users of the share will automatically see share updates within 1 minute, containing both DDL (like CREATE TABLE) and DML (inserts, updates, or deletes) changes.
These updates are transactionally consistent snapshots, i.e. never partial database updates.
The share creator can have the share be automatically updated when the underlying database changes. This is done by specifying the `UPDATE AUTOMATIC` option during [share creation](create-share.md).
Alternatively the share creator can manually update the share with a new point-in-time snapshot of the database. This is done by running the `UPDATE SHARE` command.
# Syntax
```sql
UPDATE SHARE ;
```
---
Source: https://motherduck.com/docs/sql-reference/rest-api/ducklings-get-duckling-config-for-user.api
---
id: ducklings-get-duckling-config-for-user
title: "Get user Ducklings"
description: "Gets Duckling (instance) configuration for a user. Requires 'Admin' role."
sidebar_label: "Get user Ducklings"
hide_title: true
hide_table_of_contents: true
api: eJzlmEtv2zgQx78KoUtbwOtHm1x8c2sla6Br7yr2HtYIDFqibSYSqSWpPCrou+8MJUUPCykKG4sAOZkKh8OZP39kyEkdGTNFDZdiFjhjJ0j8+5CLvf5tz8y0+PgmxY7vr6RaaaacnhMw7Sse4yAYcs2MJlxoQ4XPiG9tk9wl2UlFKElgWJ947N+EK6bJh0kQcfGBKBmyPrgzdK+d8dopfWjntudo5ieKm2foSJ1JYg5S8R80n3J9m4FFTBWNmGFKWxuOscTUHMCjgA74wnltsx0ydpBHbg5cEHNgRKo9FaV7mNs/sIg649QxzzE60kaBDNAT0afvTOxhkvHny8tfcpv1HJUrADoblTDMAeSIpdCQMkz2eTjEn6bPm8QHRfQuCUlpDHGAyoYJg+Y0jkPu2zkGdxrHpMcJyO0d8w0MjBWut+H5jIrRYPMIMrOf25ars9H8B+vShokkwmWMk9DGiOYBVQE075JoK53brKHBuuXy1vZCQNqnSN3/E1LP2YXSv2+7gIFbyzqQyiP0MrSrn7cvXs+k4fMo65rorYTBFI0vujj4SgO7g5g251v/CMii+07pmrMvgWamFOzmcgiI+0SjOGSt2DIMLvgll9a+4W8y3XjuXyv3Zon+uNZJHm/hkSpFn2EESBjpE7Jsr0xpiFQ0g50IYiclckfycGCDU0MemWLlvuTbkNkDz5SZ1ZNaH3FQKWkFgNPAcNPWs5DoIzDx6RUJuYCJo/L8epk0LdaioWivEqRFVSn02oJoSRwdkzgTD4BrQL5BHsAgp6F+c0R2xXgOMlfzyWr5+8Kb/eNO3yWaHcJWiI5OQbQhbZ3Rbt46WP1yzOpK0OLqwII3B2kjuHPQebXwvs6mU3f+LtGsy1kx+eUUJitB60C2oOog8eKYxLk05Eom4u1hWEV2Dgbni+XmarGav8/j8UXLCsCLUwCs1KwDWGfpiL7LrtvjDICDp1BI3CK7t4VgK7xzcDibL11vPvm+uXG9v11v43rewnuXTDbFLcG8PO1C2S1v8992C7kWqRkawzGKVYc9szrj433sDB5GA3xJ60FaPuCzQVUbwNKAeihf/YkKYcjBmFiPBwMa834kQTGFdYy+LyMnq9USbpDqfBVbFYWXtURPZQUAv7eMKngHYrQoibUsdP3DToQ1EuLBvZpM/pzBSIwsF3PUH/aHSFwstYmonaUoTFwzY4sipJ5WYynSaoeeocCS52bYkxnEIeW2GGGVSwvR187DCAyt7PA7rlVOGlWZA6SC1mm6pZqtVJhl+Gd4QCis1EDzgSpOt5ZROIS4xjYs8Q5ubeyVHD8WwQefyE+KM53JlFtZ4EaG62KCX9C8Z8/1OlCGe/EAT25YUowv7574PotNbeDRiYgQvcB67eIbCq8ANW4KTnplA713BgXKWYulvGcC1CtjNPiNAWbZf01Ev3s=
sidebar_class_name: "get api-method"
info_path: sql-reference/rest-api/motherduck-rest-api
custom_edit_url: null
---
import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";
Gets Duckling (instance) configuration for a user. Requires 'Admin' role.
---
Source: https://motherduck.com/docs/sql-reference/rest-api/ducklings-set-duckling-config-for-user.api
---
id: ducklings-set-duckling-config-for-user
title: "Set user Ducklings"
description: "Configure account-specific settings, such as Duckling sizes for service accounts. See the Service Accounts Guide for context."
sidebar_label: "Set user Ducklings"
hide_title: true
hide_table_of_contents: true
api: eJztmE1v2zgQhv8KwUtbwI2dNrn45iQO1kDX6Sr2HtYIDFqibSYSqSWpfFTQf+8MJUUfFhIUNooA6SWRzOFw5p2HlDQpVTHXzAolJwEd0iDx70IhN+az4faiuDlXci02l0rPDde0RwNufC1inARTrrk1REhjmfQ58Z1tkrska6UJIwlMOyIe/z8RmhvyYRREQn4gWoUcvFm2MXS4oKULQ2961HA/0cI+wUBKR4ndKi1+sHzFxU0GFjHTLOKWa+NsBIYSM7sFjxIG4A6XdZftiHGAPAi7FZLYLSdKb5gs3cPa/pZHjA5Tap9idGSsBhVgJGKP37jcwCLDL6env+Q261GdCwAyW51wzAF/4caeqeAJl2sa9Choabm0OMTiOBS+c9W/NbhcuhunWt1y30KcscaqWgFawmhektftNGfB8gFU56/blsVaGvGDd0nFZRJhVeMkNFgBNA+YDuDyNolWit5kDUkWLZc3bhQCMj5DBn9PSD26DpV/13YBE1eOfOBWROhl4GDIr09ezqThcyfrmuithHdMizrC7/mIiZU0ee5fBgP819qYiQ+7yayTkJTG9GBQ/YHlrcGCxiddHJyxwB2+cNIcrv4RkMU2ndI1V5/BSci1hgdBOQXEfWRRHPJWbBkGF/ySS2ff8De6WHrjf+bj6xn6E8YkebyFR6Y1e4IZIGFk9siyXZnSEKloBjuSxC1K1Jrk4cDDgVnywDUv96VYhdw9K22ZWT2pxQ4HlZJOAHiSWGHbehYSfQQmPr0goZCwcFQ++54XTYtaNBTtVYK0qCqFXjgQHYnHuyRO5D3gGpBzyAMYFCw0b47IrhgPQeZ8OprP/rryJv+NL94lmh3CVoge74NoQ9o6o928dbD6dZfVuWTFaycP3hykjeAOQefllXc2ubgYT98lmnU5Kya/7sNkJWgdyBZUHSSe7JI4VZZcqkS+PQyryA7B4PRqtry8mk/f5/H4rGUF4Mk+AFZq1gGss7RD32nX2+MEgIPP6JCMi+zeFoKt8A7B4WQ6G3vT0bfl9dj7d+wtx5535b1LJpvilmCe7vdC2S1v87HdQq5FaobGcIxiwypOnM7Y+BnS/v1xH7swpp+WzZ+sX/WVsK2k78uOUaJDmLK1NjbDfp/F4ihSoJjGFtiRryKa1fpQ10h1XsVWN+q5luip7B7h/YozDd+BGC3uDa/q9YzrmpS9meYHdev7tPgM7fjM7TZsfpoOnGRYFxduUdy/XbbY4yMevNyT0fcJzEN58ooeHw2OBrhkrIyNmEu16Kxdc+uaeqSubYOHtDom9m8Q5vpa/mj7cciEa6a56qVF4Rf0/hgMXenh/7DW+Wt0FbeQCVqn6YoZPtdhluHPUBiNnUa4vGdasJXbJ3AQCoPXgNka3hz5Cyl+LGIPPpFXmoudyZTHicTDBF5ZE7yDyzv+VO9jZngebAEAwArjy4fP8yg+z9BJNX3nbM565YyR7/PYvmh7U9ti3+f45bcqmpRRvos1e8AeBPx1kSonSt4Zwt9SGjK5SfL9nLtEBvHtp7Zlii3SKy8wqU4toGDOYqbuuISildJYvEddsuwnVCzhWg==
sidebar_class_name: "put api-method"
info_path: sql-reference/rest-api/motherduck-rest-api
custom_edit_url: null
---
import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";
import Admonition from '@theme/Admonition';
::::info
This endpoint is used to configure user-specific settings, primarily for setting Duckling sizes for service accounts.
For a complete walkthrough of service account management, see Service Accounts Guide - Step 3.
::::
::::caution[Username Parameter]
When configuring a service account, ensure the `username` in the path (`/v1/users/:username/instances`) is the specific username defined when creating the service account. Note: The endpoint path uses "instances" for legacy reasons but configures Ducklings (compute instances).
::::
Set user specific config such as Duckling (instance) sizes
::::note
Authentication for this endpoint requires an Admin token. This configuration currently applies to Duckling sizes and will in the future include read scaling tokens.
::::
---
Source: https://motherduck.com/docs/sql-reference/rest-api/motherduck-rest-api.info
---
id: motherduck-rest-api
title: "MotherDuck REST API"
description: ""
sidebar_label: Introduction
sidebar_position: 0
hide_title: true
custom_edit_url: null
---
import ApiLogo from "@theme/ApiLogo";
import Admonition from '@theme/Admonition';
import Heading from "@theme/Heading";
import SchemaTabs from "@theme/SchemaTabs";
import TabItem from "@theme/TabItem";
import Export from "@theme/ApiExplorer/Export";
import DocCardList from '@theme/DocCardList';
::::warning[Preview Feature]
The REST API methods are in 'Preview' and may change in the future
::::
To better support scenarios that require some flexibility or dynamic configuration around
managing a MotherDuck organization we are exposing an OpenAPI endpoint with some new functionality.
At the moment it enables limited management of users and tokens via HTTP without requiring a
DuckDB + MotherDuck client to be running.
All of the methods are authenticated using a Read/Write token of a user with the `Admin` role within your MotherDuck Organization
and passing it via the `Authorization` header with a value of `Bearer {TOKEN}`.
::::info[Service Account Management]
You can use this REST API to programmatically manage service accounts, including their creation, token generation, and Duckling configuration.
For a detailed walkthrough, please see our [Service Accounts Guide](../../../key-tasks/service-accounts-guide).
::::
If you would like to generate your own OpenAPI client the spec file is located at https://api.motherduck.com/docs/specs
---
Source: https://motherduck.com/docs/sql-reference/rest-api/users-create-service-account.api
---
id: users-create-service-account
title: "Create new user (service account)"
description: "Create a new user, typically a service account, within your MotherDuck organization."
sidebar_label: "Create new user"
hide_title: true
hide_table_of_contents: true
api: eJzlmFFv2zYQx78KIQzoNrhx3CYvfnNmBTOQ2p1s76GOG9DSxWYjURpJOfEEfffdUVIkK163wR4QIC8JRR2Pd3/+KPOYOXECihsRy1Hg9J1Ug9LvfQXcwBTUVvgw8P04lcbpOAFoX4mEjNH0F2vEaAQTmvmpUiBNuGMKtFHCNxAwEzPrS8g144XpozAbbL/7BNEK1Dum4hDQt+Fr7fQXRQDOsuNoQI/C7LAzcwap2cRK/MmLuRfLHC0U/JHiVFdxgEaZfRQKMAujUug4fiwNBkSveJKEwreDu980ecgc7W8g4tQyuwQwn3j1DXxKM1GkiRGg6S0FJHkE1k8YTu5tQOUYSlSuaQw3Bu2w6+vi9jbJbvIl9kZC3oBcm43T7+ETf6qePlxe5p1/4+X2Nrhb/vyDQ/nuy1/FxaJUG7bChZAC9WBCMi5ZrNZcVnrleaehzqJOaZnnxTudxFIX+X44P6d/+5NNU98Hre/TkFXGzv+i8L4i/xT4xaFYr3jAvAKN08UYYfZ8fSDE9rLMNsBAqVixakjHgSceJSG0YsspuOA/ubT2e/4GwzvP/W3uTmfkT2idFvGWHrlSfIcjhIFIH5FleyEqw2XeDnaA+NGkLL5nRTjMbLhhj6CgYkesQmD3mI+pMmsmhZv7b2YrBcOtYIRp61lK9CMy8dN3JBQSJ46KfdGYNCvXYk/RTi1Ii6pK6IUF0ZLYe0niSG55KAKGX8oAGRQ81K+OyEMxnoLM+Xgwn/068UZf3OGbRPOAsDWivWMQ3ZO2yehh3g6w+vElq3PJy99YTOy1QboX3CnovJ54V6Ph0B2/STSbctZMfjyGyVrQJpAtqA6QePGSxHFs2DWeOF8fhnVkp2BwPJndXU/m47f5eXzWsgbw4hgAazWbADZZekHf5aHT40jSCZyHzC2ze10ItsI7BYej8cz1xoObu6nr/e56d67nTbw3yeS+uBWYl8cdKA/Lu/+z3UKuRWpOxvgZpfo8ie35EwtFrCKd7rbXLQpmqpfVllpUnaYqxLcbYxLd73Z5Is6iGNVRQeo/nPlxZMvJqsCeEsHFirXK7Od1I080hbXE5xVwBcquI+0Dry7E3Tr/uqx7XndMDdWyjkvJP9m4hhgX8/DIzQafRzgRJVLo3Ds7PzungZR4xG1QpdPy/kHCo71YaC9RVu/cU19VFKIYeDLdJORCUnxW8qxcmIWz7aHh813GhlYNe7NsxTXMVZjn1I2qKbrfwOaWK8FXJbA4AHiAKdFaPsCOMihyeT+juck8TO2ObH+R6FahGDHAij0x37VdNsD6PLEFz6q8TIkKeBV/xE7623ewEVttLSq2L3NCLtdpgXHhk5igH/0GPSUtnarRuD/hcteIEOWxFrP4ASRK1ClTMfSM4aLzvwAw4m74
sidebar_class_name: "post api-method"
info_path: sql-reference/rest-api/motherduck-rest-api
custom_edit_url: null
---
import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";
import Admonition from '@theme/Admonition';
::::info
This endpoint is used to create new users / service accounts. For a detailed guide on managing service accounts, see the [Service Accounts Guide](../../../key-tasks/service-accounts-guide).
::::
Create user is currently restricted to creating a user with a 'Member' role
---
Source: https://motherduck.com/docs/sql-reference/rest-api/users-create-token.api
---
id: users-create-token
title: "Create an access token for a user"
description: "Create an access token for a user, including service accounts."
sidebar_label: "Create an access token"
hide_title: true
hide_table_of_contents: true
api: eJzlWN9z4jYQ/lc0ms70rsMFuCR94I0kZMpMCncO9KGEMsIWoIstu5KchDL+37sr2/gHbno38JCZvARbXq12v+9bRdodDSOumBGhHHq0R2PNlf7kKs4Mn4SPXNIWNWytaW9GDb5rOm9Rzd1YCbOF0R3tx2YTKvGPdQIj8wQsIqZYwA04szYCPsCY2YA7CR+ylexji3pcu0pE6XxYTq25Ifj9Z00yE+1ueMBob0eZ749X1qnZRuhIGyXkmuKaBhZEF3/NHh6i3V0yh9FAyDsu17B0rwtv7CV/+3x5mbS+x8vDg7eY//ITTeZJiyr+dywUB6yMijmmiiNcm6vQ22J8VYMWdUNpuDQ29CjyhWtxan/TmOyulFgWSLj8xl2DgSikxgiu7Vfjl4xkHCy5SrMTQRzQ3nmnY7PL3rqX5792cKiGLVJI+EskUs6JkATIDKWnaZJTc4jJ6ximXhfppMPJXGJEMwCGeYtnkA3yaV+0y3y0mWOYKxb7gFLZLKngPUvDmydJOq6jUOoUnM+dDv5Uc72PXZdrvYp9khvT09Fha6Mh22oMzIZAUmuIWnhNc1ahChjmHsdg8J88wAfLHF8w8/9LT0TASYlvrglMAx9pcXsLo3/IyTPTBMp8LSRU4JZkXmiScRlKf1vytwxDnzOb89HqqMvAZPsSYFXJphxJZdlMMhdNKrliHnHSCj6dOgIgna0b863hu+GEKxUqkk9BjlkQ+bwWGxIXej/k0tpX/PVvFs7g63RwP7Fi1DrmZREwpRhCByQE+ogs64Tlhrh/VoPtS2IXJeGKpOEQs2GGPHPF86oVS58TKBH4kmVWTmp2II8CSQsAbC5GmDqeGUQfQBMfX4FQyLQ2cby06C7jooJoqwCkpqoc6JkVolVi91CJQ/kEgvfINeQBGhTM129OkU0xnkKZ01F/Ovlt7Az/HNy8S2k2AFtItHuMRCvQljXarLcGrZ4fanUqWXbq496bE2kluFOo83bsXA1vbgajdynNMpyFJs+P0WQBaFmQNVE1KPHiUImj0JDbMJZvT4ZFZKfQ4Gg8WdyOp6P3uT3usSwEeHGMAAs0ywIsa+lAfZdNp8ehxLsi88kgy+5tSbAW3il0OBxNBs6of7e4Hzh/DJzFwHHGzrvUZBXcXJiXxx0om+Gt/tuuSa6m1ANUru0NiTAAp3QbtRgw22Wx7mHjxf5PFNoTq23V9Gj7qdu2HaH2Lm/XJO2sDYRdIPWUN3hi5YP9xphI99ptFomzIASElRe7j2duGNCk1Da6xypIWa81j/bco6e87WPvk5wpiBSzw1pyip7LoMDQ9kg6+w7TXtzlG2ilvwDgAR92akbq7zbqG4iaOHCoJ/0vQ3CAaaZgds86Zx2ciEAFzIacLfc9OFeY2Sdr+ItpRz4T9r5sodxlFMzoUxfmWRLgt1fqmhXtuA2SBqa73ZJpPlV+kuAwQKSwRQePT0wJtrQKhy1MaHwGuldw5uMHUe33MPrBySrhI2nsyjXGn5e/xOKHI2aMb/D4yLfltp/tpm2ADAAGo0o/X6drf5qgk2L6wV6Knbt0Rh/AjsyrtvOSwL+M7VVtmXXrgrTsFHvG9gH8taGGFgsrUDu2oz6T6zgtwNQnKhGPK+WeR6rRVv5Q6lFWwQCerIVtsAJXOTZZmwjr+F94PGm8
sidebar_class_name: "post api-method"
info_path: sql-reference/rest-api/motherduck-rest-api
custom_edit_url: null
---
import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";
import Admonition from '@theme/Admonition';
Create an access token for a user
:::note
- For service account token creation, ensure you are using an **Admin token** for authentication. The token generated by this call will be the service account's own token for its operations.
- For detailed guidance on service account token creation and best practices, see Service Accounts Guide - Step 2.
- If a new service account is created through the admin API, make sure to connect to this service account manually with a read/write token before using read scaling tokens.
- Each token is tied to a specific user - make sure to use the exact `username` that was specified during service account creation is put into the path (`/v1/users/:username/tokens`).
- If the optional `ttl` parameter is not specified, the access token will remain valid indefinitely until revoked by an administrator.
:::
---
Source: https://motherduck.com/docs/sql-reference/rest-api/users-delete-token.api
---
id: users-delete-token
title: "Invalidate a user access token"
description: "Invalidate a user access token"
sidebar_label: "Invalidate an access token"
hide_title: true
hide_table_of_contents: true
api: eJzlmE1v4zYQhv8KQRTobuGN493k4pu3VlADqd1V7B42cQ1aGsfc6Ksk5SYV9N87Q0m2ZKldNM4hQHyxRJEzL18++hhmPE5ACSPjaOLzIU81KP3BhwAMzOMHiHiPG3Gv+fCWGzrXfNnjGrxUSfOErRkfpWYbK/m3DYItyxx7JEKJEGMobftIvIBtZovhIryAZzbaSvrYor0thIIPM26eErqmjZLRPc97XMGfqVSA0oxKIe91hiLR9rDHfdCekkkhBZWrezCMrv+oWdnlkE0EwWxj9TXzknyD2inEH7d3d0l2nS+xNZTRNUT3mHo4wDPxWJ19vLwkad+Pcnfnr5Y//cDzZXtuS2rRSRxp0KTu4/k5/TVndJN6Hmi9SQNWdcZEXhwZiIydU5IE0rNr0f+maUzW9jdefwPP8Bx/PX7Rleez8JmL+kCb58dHBxThZWQxoxCVi3toL/Txus23wECpWLFqSI/DowiTAI605STO/18hbf9GvNF45TpfFs7NnOJJrdNCbxlRKCWecIQ0EOoTZpk31vx235FYaIodRcwmZfGGFXKY2QrD/gIF1brLdQBsg/Mx1czqk8K78F+ylYYhbUaaYz9Li94hE+//w0IZYeKwuOFrSbNyLRqO9g6GHFFVGU1aSxIHbRIn0U4E0mc/4zyQQSkC/eqI7NL4EmQupqPF/JeZO/nqjN8kmh3GHhAdnIJow9o6o928dbD6qc3qIhLlyxD8VwdpQ9xL0Hk1cz9PxmNn+ibRrNt5YPLTKUweDK0DeQRVB4kXbRKnsWFXcRq9PgwPyl6CwelsvrqaLaZv8/G49/IA4MUpAB7crANYZ6lF32XX1+Mkou9eETCnnN3rQvBI3ktwOJnOHXc6ul7dOO7vjrtyXHfmvkkmm+ZWYF6e9kHZbW/ztX2E3BGpLVfK97wwwIStEJmwpRUzZdmL5es2ppq4qIaLeg6LPd7fDfq2Tu5nVeWZ94viuJ9VZW1OlSaoXVUBpyrAoVtjEj3s90Uiz8IYvVZ+6j2ceXHI81pdfUP3Q7H+R9X1ngKKVBWzdL4GoUBZKshM27NckV9tojEmYi5+kbPRbxMcScoKJwZn52fnxGoSaxMKm6WsrL9rUsPTvTgDj6afBAJrdQxrp56V7t3y3QDHWf/wf1ir3cv9BWzc7w2gJ1sURcOybC00LFSQ59SMRYSi/Qc83Aklxdpyig8iqekY122DX27QUrh/EvF3bsnze0bJu5RXt2xENyw6kdIZHj7AU30Hg2r/Z+Xt3J94hpK9iXZfYQvCRxTIjeLyCNcsMbWBrWcwwbfnfexcO3MHu9N3R21VS8R61UFt46SpC5fK9rAbSHneNIw05vk/O5pT7Q==
sidebar_class_name: "delete api-method"
info_path: sql-reference/rest-api/motherduck-rest-api
custom_edit_url: null
---
import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";
Invalidate a user access token
---
Source: https://motherduck.com/docs/sql-reference/rest-api/users-delete.api
---
id: users-delete
title: "Delete a user"
description: "Permanently delete a user and all of their data. THIS CANNOT BE UNDONE"
sidebar_label: "Delete a user"
hide_title: true
hide_table_of_contents: true
api: eJzlmE1z2kgQhv/K1JySKhZMYl+44SBXqHJEVsAelnK5BqkNE+srMyNiVqX/vt36QEJoU5vAwVU+IVDP9NvvPCOmlfIoBiWMjMKpx0c80aD0Hx74YID3uAfaVTKm23jzK6hAhBAaf8+KECYYjWAi9JjwfRY9MbMFqZgnjOizxefpnH0a2/ZswW4ttrQnM9vCaY3YaD5aFdn4Q49rcBMlzR5/TPk4MdtIyX9EkXb1kGFELJQIMKPSeYwkPbEwW5wtxBul9PyyLTtX+EOarQxJHYvURoTl9MxE7FCtdrcQCD5KudnHNKU2SoYbvBOIl3sIN5hu9OHm5jcTZD2u4HsiFaDTRiVAdSnQcRRq0JT2w9UVfRzPPk9cF7R+SnxWBaMiNwoNrgSFizj2pZtnG3zTNCY9LSVafwPX4MBY0YobWWQ8mHZSdHYkd1VHPmQZ3bvu0norPObgINDmchoDrF5sOiS212GB3oNSkWLVkB6HFxHEPrS0ZSTO+6Up8/ij+caTR8f6c2nNFzSf1Dop9JYzCqXEHkdIA4E+o8r2QlSBD1lb7DhkeVLahoUcxFEY9gMUVOzItQ/sCesxVWXNonCv/Ue20jAk1kjT9rO06B0y8f4nFsoQEwfFtm4kTcu1OHK0VxvSoqoyepWDmJM4PCVxGu6ELz32CetABqXw9asjskvjJchc2uPl4vPMmf5tTd4kmh3G1ogOz0H0yNomo928dbD68ZTVZSjKvzws7LVBeiTuEnTezZzb6WRi2W8SzaadNZMfz2GyNrQJZAuqDhKvT0m0I8PuoiR8fRjWyi7BIB5IH+9meB59kwwevKwBvD4HwNrNJoBNlk7ou+k6PU4RODxj+swqq3tdCLbkXYLDqb2wHHt8/zi3nL8s59FynJnzJpk8NrcC8+a8A2W3vcd/2y3kWqRmFIyPUeqND31i3neO+GA3HOQd7CCtmqOMukhQu6pNTZSPgVtjYj0aDEQs+0GEVikvcZ/7bhTwrNH8zgnnYvlaLfBhEWmmqlGl72sQClS+qORFHlka+iVPNMFEzMEDNRt/neJIUla4OOxf9a8ItTjSBtt6Glt20pNmb9+2P6135QVfCBTlGXgxg9gX2N2jsNy8tHR7xXdDDCzeGPT4qG5He3yLFVBEmq6FhqXys4x+xoZB0RsFvNwJJcU6ZxIfOlLTNS7pE57S4Cf1vXNKdt+z//0SobOUahOHtIXxoJjQN7x8hn3zzUVGu3ALwkPbSWlxe4z9f2waA0+ehUTRAdOJdW8tyFT6/2+wU7LSqy4oQacutDGPWETPEGbZQaah76Qxy/4F/Dk/SA==
sidebar_class_name: "delete api-method"
info_path: sql-reference/rest-api/motherduck-rest-api
custom_edit_url: null
---
import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";
import Admonition from '@theme/Admonition';
::::warning[This action can not be undone!]
Once you delete a user or service account, all their data will be erased from MotherDuck.
There is no possibility of recovery data or users deleted via the API.
::::
Permanently delete a user and all of their data.
---
Source: https://motherduck.com/docs/sql-reference/rest-api/users-list-tokens.api
---
id: users-list-tokens
title: "List a user's access tokens"
description: "List a user's access tokens"
sidebar_label: "List a user's access tokens"
hide_title: true
hide_table_of_contents: true
api: eJzlmE1v2zgQhv8Kocu2gDeO2+Tim1sruwZSu1XsPawRGLQ0sdlIpEpSTryG/vsOKcn6bIvAPgTIKQo1Mxy+85AW5+CIGCTVTPBJ4AydRIFUf4ZM6bl4BK6cnqPpRjnDpaOzgfueo8BPJNN7HD04o0RvhWT/2Rg4cp+iRUwljUBjLGvD8AWO6S2G4/gin8g+9pwAlC9ZnPnbF+SJ6S3jRG+BCLmhvAiPc/tbiKgzPDh6H5tASkvGN/gmos+3wDc4yfDD9fWLwqY9R8KPhElADbRMwKxBgooFV6DMZB8uL82fesy7xPdBqYckJIUx5uELroFrY07jOGS+naP/XRmfQ3sBYv0dfI2OsTS10CybMZe7tKNS0j2aMQ2R+r0/C7pEehAyotrokaBBWpSjaYgv4DlGPVZUd4Wp6zBnERCbL8m8FEE3jOFLoBqClVYvCvJEFdaHbRinYbgneZSsSjRYCR7uK/HWQoRAbRGt+yobb88HPIkMyDbIEwJsqmX/UT4Njc19WiNhaUSsraKaQW069Gy4FtsltW+uuvj5RAPioQsofT5uIiSSbjoFaAiOuwCkFJIULqboNIpDaORmKimCF4W09rV4o/HKc78t3Lu5iceUSuAEun+6ymYZCsP7tJnsiBM7KREPJEsHDwaqyRNIKPYzW4dAcM/YI8OurLqoZYuXUkkrAJ4imummnrlE75CJ97+QkPFss2bn3nHSQ16LmqK9UpAGVYXQSwuiJXHQJnHCd7gDAvIZ14EMMhqqV0dkV47nIHMxHS3mf8+8yb/u+E2i2SFsiejgFERr0lYZ7eatg9WPbVYXnOafHBC8OkhryZ2DzpuZ92kyHrvTN4lmVc6SyY+nMFkKWgWyAVUHiVdtEqdCkxuR8NeHYZnZORiczuarm9li+jaPx6OWJYBXpwBYqlkFsMpSi77rrq/HCQKHV6iQuPnqXheCjfTOweFkOne96eh2ded6/7jeyvW8mfcmmayLW4B5fdoHZbe89Z/tBnINUluq3DL83KXEXL7/wEuhvS4TXXQWIsAj13QdNmBrYhoEQ6e/G/RtG6J/KJoEaf/ogyO7oq2QyBDtt1rHatjv05hdRAKllUHiP174InLSSrPizuCflbvRsjgW3UQqWgz2ZglUgrQQGO2sZV6AL3aiMU5EPPwAJ6OvE/Q0mWULH1xcXlwaNGOhdETtLHnn49ea1PQ7ZqbhWffjkDJ7zbXrPuR6LZ3dAP2sYvh3WGmslB2bLWZhTA+HNVWwkGGammG8JEjTxcHHHZWMri2HeNAwZZ6xNA/4ZQatrI4njfPOy3l9T37TuOlcSbFdudms+EmYmP/w8RH21R5RavbbFm/eWA2TX/Z6hNLFuuLYOvVM/Y+Q/eWae5L5ma82D7IS94oHE70zKVTOWti2GKpX5Gg1Ngmm6f8fcaOl
sidebar_class_name: "get api-method"
info_path: sql-reference/rest-api/motherduck-rest-api
custom_edit_url: null
---
import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";
List a user's access tokens
---
Source: https://motherduck.com/docs/sql-reference/rest-api/zducklings-get-active-accounts.api
---
id: ducklings-get-active-accounts
title: "Get active accounts"
description: "[Preview] Get active accounts in an organization along with active Ducklings per account. Requires 'Admin' role"
sidebar_label: "Get active accounts"
hide_title: true
hide_table_of_contents: true
api: eJzlmFGP4jYQx7+KNS93lVJge3sveeOO7BWphWuAPhQQMslAfBvsnO3A0SjfvbKTkBC4dltW6kr7REj+mRn/5+ckdgYiQUk1E3wYggthGjzGjG/Vj1vU/UCzPfaDQKRcK3AgRBVIlhg1uDD/LHHP8LAkn1ATasWElmrCOKGcCLmlnP1pExAaC74lB6ajSj2o0pEEZXVvh/j4NWUSFXnTD3eMvyFSxAgOaLpV4M6hSgJLBxQGqWT6CO48g36qIyHLfODOl/nSAYkqEVyhAjeDn3o983M+kkkaBKjUJo1JJQYHAsE1cm3kNEliFtio3S/K3JOBCiLcUXOkjwmCC2L9BQMNDiTSmKpZkfFUbK2kUtIjOMA07tQ/R0gVSk532FAqLRnfQu7UHfvv8Vl4Gbnd7AXIwwKIkGQBUnVGCyCHCCWSEWEhcs02DBU5RCyIiEQaEhVQUxaRaK0zpRYZnpAJabg6SKbxlNGcKSMuwMRSmupUPSVagVoVKRAiDsWBmyi5YcOSFhqqWAhljafwy7wtOvWi6fylrCY0t9fur2H3gYYWdVT6+XDboVJ0+wSfpxESlFJIUt3iAH6juyTGVm25KS78VyGt/ixef7Dyvd9m3mRq4jGlUrwB2e+Ost2ISrjM28X2ObFJidiQohyiI6rJwVBdPgbYOkayEZLoamTNQc2X38tWGrZ0QDPd9rO06O19r/fD31jI+EbIXfEkayTNyl6cOerUhrSoqoyeWxAtiXeXJA75nsYsJB8l2slMY/XiiLxW43OQORv1Z9Ofx/7wD2/wKtG8YmyN6N0tiJ5Z22T0Om9XWH13yeqM0/Itj+GLg/SsuOeg82HsfxgOBt7oVaLZtLNm8t0tTNaGNoFsQXWFxPtLEkdCkweR8peHYV3ZczA4Gk9XD+PZ6HU+Hk9e1gDe3wJg7WYTwCZLF/S9v/b1OOTafIrGxCtH97IQbJX3HBwOR1PPH/V/WU08/3fPX3m+P/ZfJZPn5lZgvr/tg/K6veev7RZyLVJzI9aRMDsJW7Q+Ux2BC939XbdYh61ovZ2gUO5RKrtyT2UMLkRaJ8rtdmnCOjuhI5RmkdUJxA7yxlp/YlAuWtda8Z8aaCKZFFYJLqyRSpS2ocYHqyzN/NUmMlsRxPcmU9L/PAQHTGWFg3edXqdnMEuE0jtqsxTLcbiy8dFuQFbPy/9jx6RwQ+M33U1iyrgZh/U6K3szh/0dONDuztKBSChtrmfZmiqcyTjPzemvKUqz27J0YE8lo+sSXgcipCFK285HPJpJEQSYGAz2NE7tvGw/l0xXT8h88sxKxryIG40sG+dUByZ6Ncv5sRE7ywrFVDwiz3NwyiK0+Q+5IfQvzQyFNw==
sidebar_class_name: "get api-method"
info_path: sql-reference/rest-api/motherduck-rest-api
custom_edit_url: null
---
import MethodEndpoint from "@theme/ApiExplorer/MethodEndpoint";
import ParamsDetails from "@theme/ParamsDetails";
import RequestSchema from "@theme/RequestSchema";
import StatusCodes from "@theme/StatusCodes";
import OperationTabs from "@theme/OperationTabs";
import TabItem from "@theme/TabItem";
import Heading from "@theme/Heading";
[Preview] Get active accounts in an organization along with active Ducklings per account. Requires 'Admin' role
---
Source: https://motherduck.com/docs/sql-reference/sql-reference
---
title: SQL reference
sidebar_class_name: sql-reference-icon
description: SQL reference for MotherDuck & DuckDB
---
import DocCardList from '@theme/DocCardList';
---
Source: https://motherduck.com/docs/sql-reference/wasm-client
---
position: 3
---
# MotherDuck Wasm Client
[MotherDuck](https://motherduck.com/) is a managed DuckDB-in-the-cloud service.
[DuckDB Wasm](https://github.com/duckdb/duckdb-wasm) brings DuckDB to every browser thanks to WebAssembly.
The MotherDuck Wasm Client library enables using MotherDuck through DuckDB Wasm in your own browser applications.
## Examples
Example projects and live demos can be found [here](https://github.com/motherduckdb/wasm-client).
## Status
Please note that the MotherDuck Wasm Client library is in an early stage of active development. Its structure and API may change considerably.
Our current intention is to align more closely with the DuckDB Wasm API in the future, to make using MotherDuck with DuckDB Wasm as easy as possible.
## DuckDB Version Support
- The MotherDuck Wasm Client library uses the same version of DuckDB Wasm as the MotherDuck web UI. Since the DuckDB Wasm assets are fetched dynamically, and the MotherDuck web UI is updated weekly and adopts new DuckDB versions promptly, the DuckDB version used could change even without upgrading the MotherDuck Wasm Client library. Check `pragma version` to see which DuckDB version is in use.
## Installation
`npm install @motherduck/wasm-client`
## Requirements
To faciliate efficient communication across worker threads, the MotherDuck Wasm Client library currently uses advanced browser features, including [SharedArrayBuffer](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/SharedArrayBuffer).
Due to [security requirements](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/SharedArrayBuffer#security_requirements) of modern browsers, these features require applications to be [cross-origin isolated](https://developer.mozilla.org/en-US/docs/Web/API/crossOriginIsolated).
To use the MotherDuck Wasm Client library, your application must be in cross-origin isolation mode, which is enabled when it is served with the following headers:
```
Cross-Origin-Opener-Policy: same-origin
Cross-Origin-Embedder-Policy: require-corp
```
You can check whether your application is in this mode by examining the [crossOriginIsolated](https://developer.mozilla.org/en-US/docs/Web/API/crossOriginIsolated) property in the browser console.
Note that applications in this mode are restricted in [some](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Cross-Origin-Opener-Policy#same-origin) [ways](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Cross-Origin-Embedder-Policy#require-corp). In particular, resources from different origins can only be loaded if they are served with a [Cross-Origin-Resource-Policy (CORS)](https://developer.mozilla.org/en-US/docs/Web/HTTP/Headers/Cross-Origin-Resource-Policy) header with the value `cross-origin`.
## Dependencies
The MotherDuck Wasm Client library depends on `apache-arrow` as a peer dependency.
If you use `npm` version 7 or later to install `@motherduck/wasm-client`, then `apache-arrow` will automatically be installed, if it is not already.
If you already have `apache-arrow` installed, then `@motherduck/wasm-client` will use it, as long as it is a compatible version (`^14.0.x` at the time of this writing).
Optionally, you can use a variant of `@motherduck/wasm-client` that bundles `apache-arrow` instead of relying on it as a peer dependency.
Don't use this option if you are using `apache-arrow` elsewhere in your application, because different copies of this library don't work together.
To use this version, change your imports to:
```ts
import '@motherduck/wasm-client/with-arrow';
```
instead of:
```ts
import '@motherduck/wasm-client';
```
## Usage
The MotherDuck Wasm Client library is written in TypeScript and exposes full TypeScript type definitions. These instructions assume you are using it from TypeScript.
Once you have installed `@motherduck/wasm-client`, you can import the main class, `MDConnection`, as follows:
```ts
import { MDConnection } from '@motherduck/wasm-client';
```
### Creating Connections
To create a `connection` to a MotherDuck-connected DuckDB instance, call the `create` static method:
```ts
const connection = MDConnection.create({
mdToken: token
});
```
The `mdToken` parameter is required and should be set to a valid MotherDuck access token. You can create a MotherDuck access token in the MotherDuck UI. For more information, see [Authenticating to MotherDuck](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck#authentication-using-an-access-token).
The `create` call returns immediately, but starts the process of loading the DuckDB Wasm assets from `https://app.motherduck.com` and starting the DuckDB Wasm worker.
This initialization process happens asynchronously. Any query evaluated before initialization is complete will be queued.
To determine whether initialization is complete, call the `isInitialized` method, which returns a promise resolving to `true` when DuckDB Wasm is initialized:
```ts
await connection.isInitialized();
```
Multiple connections can be created. Connections share a DuckDB Wasm instance, so creating subsequent connections will not repeat the initialization process.
Queries evaluated on different connections happen concurrently; queries evaluated on the same connection are queued sequentially.
### Evaluating Queries
To evaluate a query, call the `evaluateQuery` method on the `connection` object:
```ts
try {
const result = await connection.evaluateQuery(sql);
console.log('query result', result);
} catch (err) {
console.log('query failed', err);
}
```
The `evaluateQuery` method returns a [promise](https://developer.mozilla.org/en-US/docs/Learn/JavaScript/Asynchronous/Promises) for the result. In an [async function](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/async_function), you can use the `await` syntax as above. Or, you can use the `then` and/or `catch` methods:
```ts
connection.evaluateQuery(sql).then((result) => {
console.log('query result', result);
}).catch((reason) => {
console.log('query failed', reason);
});
```
See [Results](#results) below for the structure of the result object.
### Prepared Statements
To create a [prepared](https://duckdb.org/docs/api/c/prepared) [statement](https://duckdb.org/docs/api/wasm/query#prepared-statements) for later evaluation, use the `prepareQuery` method:
```ts
const prepareResult = await this.prepareQuery('SELECT v + ? FROM generate_series(0, 10000) AS t(v);');
```
This returns an [AsyncPreparedStatement](https://shell.duckdb.org/docs/classes/index.AsyncPreparedStatement.html), which can be evaluated later using the `send` method:
```ts
const arrowStream = await prepareResult.send(234);
```
Note: The `query` method of the AsyncPreparedStatement should not be used, because it can lead to deadlock when combined with the MotherDuck extension.
To immediately evaluate a prepared statement, call the `evaluatePreparedStatement` method:
```ts
const result = await connection.evaluatePreparedStatement('SELECT v + ? FROM generate_series(0, 10000) AS t(v);', [234]);
```
This returns a materialized result, as described in [Results](#results) below.
### Canceling Queries
To evalute a query that can be canceled, use the `enqueueQuery` and `evaluateQueuedQuery` methods:
```ts
const queryId = connection.enqueueQuery(sql);
const result = await connection.evaluateQueuedQuery(queryId);
```
To cancel a query evaluated in this fashion, use the `cancelQuery` method, passing the `queryId` returned by `enqueueQuery`:
```ts
const queryWasCanceled = await connection.cancelQuery(queryId);
```
The `cancelQuery` method returns a promise for a boolean indicating whether the query was successfully canceled.
The result promise of a canceled query will be rejected with and error message. The `cancelQuery` method takes an optional second argument for controlling this message:
```ts
const queryWasCanceled = await connection.cancelQuery(queryId, 'custom error message');
```
### Streaming Results
The query methods above return fully materialized results. To evalute a query and return a stream of results, use `evaluateStreamingQuery` or `evaluateStreamingPreparedStatement`:
```ts
const result = await connection.evaluateStreamingQuery(sql);
```
See [Results](#results) below for the structure of the result object.
### Error Handling
The query result promises returned by `evaluateQuery`, `evaluatePreparedStatement`, `evaluateQueuedQuery`, and `evaluateStreamingQuery` will be rejected in the case of an error.
For convenience, "safe" variants of these three method are provided that catch this error and always resolve to a value indicating success or failure. For example:
```ts
const result = await connection.safeEvaluateQuery(sql);
if (result.status === 'success') {
console.log('rows', result.rows);
} else {
console.log('error', result.err);
}
```
### Results
A successful query result may either be fully materialized, or it may contain a stream.
Use the `type` property of the result object, which is either `'materialized'` or `'streaming'`, to distinguish these.
#### Materialized Results
A materialized result contains a `data` property, which provides several methods for getting the results.
The number of columns and rows in the result are available through the `columnCount` and `rowCount` properties of `data`.
Column names and types can be retrived using the `columnName(columnIndex)` and `columnType(columnIndex)` methods.
Individual values can be accessed using the `value(columnIndex, rowIndex)` method. See below for details about the forms values can take.
Several convenience methods also simplify common access patterns; see `singleValue()`, `columnNames()`, `deduplicatedColumnNames()`, and `toRows()`.
The `toRows()` method is especially useful in many cases. It returns the result as an array of row objects.
Each row object has one property per column, named after that column. (Multiple columns with the same name are dedupicated with suffixes.)
The type of each column property of a row object depends on the type of the corresponding column in DuckDB.
Many values are converted to a JavaScript primitive type, such as `boolean`, `number`, or `string`.
Some numeric values too large to fit in a JavaScript `number` (e.g a DuckDB [BIGINT](https://duckdb.org/docs/sql/data_types/numeric#integer-types)) are converted to a JavaScript `bigint`.
Some DuckDB types, such as [DATE](https://duckdb.org/docs/sql/data_types/date), [TIME](https://duckdb.org/docs/sql/data_types/time), [TIMESTAMP](https://duckdb.org/docs/sql/data_types/timestamp), and [DECIMAL](https://duckdb.org/docs/sql/data_types/numeric#fixed-point-decimals), are converted to JavaScript objects implementing an interface specific to that type. Nested types such as DuckDB [LIST](https://duckdb.org/docs/sql/data_types/list), [MAP](https://duckdb.org/docs/sql/data_types/map), and [STRUCT](https://duckdb.org/docs/sql/data_types/struct) are also exposed through speical JavaScript objects.
These objects all implement `toString` to return a string representation. For primitive, this representation is identical to DuckDB's string conversion (e.g. using [CAST](https://duckdb.org/docs/sql/expressions/cast.html) to VARCHAR). For nested types, the representation is equivalent to the syntax used to construct these types.
They also have properties exposing the underlying value. For example, the object for a DuckDB TIME has a `microseconds` property (of type `bigint`). See the TypeScript type definitions for details.
Note that these result types differ from those returned by DuckDB Wasm without the MotherDuck Wasm Client library. The MotherDuck Wasm Client library implements custom conversion logic to preserve the full range of some types.
#### Streaming Results
A streaming result contains three ways to consume the results, `arrowStream`, `dataStream`, and `dataReader`. The first two (`arrowStream` and `dataStream`) implement the async iterator protocol, and return items representing batches of rows, but return different kinds of batch objects. Batches correspond to DuckDB DataChunks, which are no more than 2048 rows. The third (`dataReader`) wraps `dataStream` and makes consuming multiple batches easier.
The `dataStream` iterator returns a sequence of `data` objects, each of which implements the same interface as the `data` property of a materialized query result, described above.
The `dataReader` implements the same `data` interface, but also adds useful methods such as `readAll` and `readUntil`, which can be used to read at least a given number of rows, possibly across multiple batches.
The `arrowStream` property provides access to the underlying Arrow RecordBatch stream reader. This can be useful if you need the underlying Arrow representation. Also, this stream has convenience methods such as `readAll` to materialize all batches.
Note, however, that Arrow performs sometimes lossy conversion of the underlying data to JavaScript types for certain DuckDB types, especially dates, times, and decimals.
Also, converting Arrow values to strings will not always match DuckDB's string conversion.
Note that results of remote queries are not streamed end-to-end yet.
Results of remote queries are fully materialized on the client upstream of this API.
So the first batch will not be returned from this API until all results have been received by the client.
End-to-end streaming of remote query results is on our roadmap.
### DuckDB Wasm API
To access the underlying DuckDB Wasm instance, use the `getAsyncDuckDb` function. Note that this function returns (a Promise to) a singleton instance of DuckDB Wasm also used by the MotherDuck Wasm Client.
---
Source: https://motherduck.com/docs/troubleshooting/aws-s3-secrets
---
sidebar_position: 5
title: AWS S3 Secrets Troubleshooting
keywords:
- AWS S3
- secrets
- authentication
- credentials
- troubleshooting
- IAM policy
- credential chain
---
# AWS S3 Secrets Troubleshooting
This page is for troubleshooting help with AWS S3 secrets in MotherDuck. For more information on creating a secret, see: [Create Secret](/documentation/sql-reference/motherduck-sql-reference/create-secret.md).
## Prerequisites
Before troubleshooting AWS S3 secrets, ensure you have:
- **Required**: [A valid MotherDuck Token](documentation/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck.md#creating-an-access-token) with access to the target database
- **Required**: [AWS credentials](https://docs.aws.amazon.com/cli/v1/userguide/cli-configure-files.html) (access keys, SSO, or IAM role)
- **Optional**: [DuckDB](https://duckdb.org/docs/stable/clients/cli/overview.html) CLI (for troubleshooting purposes, though any DuckDB client will work)
- **Optional**: [AWS CLI](https://docs.aws.amazon.com/cli/latest/userguide/cli-chap-welcome.html) (for bucket access verification)
:::note
**AWS CLI PATH**: If you installed AWS CLI manually, you may need to add it to your system PATH. Package managers like Homebrew (macOS) typically add it to PATH automatically. Verify with `which aws` (macOS/Linux) or `where aws` (Windows) - if it returns a path, you're all set!
:::
## Verify Secret Access
### Check that the secret is configured
First, make sure you're connected to MotherDuck:
```sql
-- Connect to MotherDuck (replace 'your_db' with your database name)
ATTACH 'md:your_db';
```
Then type in the following:
```sql
.mode line
SELECT secret_string, storage FROM duckdb_secrets();
```
The output should look something like this. Make sure that the output string includes values for: `key_id`, `region`, and `session_token`:
```
secret_string = name=aws_sso;type=s3;provider=credential_chain;serializable=true;scope=s3://,s3n://,s3a://;endpoint=s3.amazonaws.com;key_id=;region=us-east-1;secret=;session_token=
```
:::note
If you see no results, it means no secrets are configured. You'll need to create a secret first using [CREATE SECRET](/documentation/sql-reference/motherduck-sql-reference/create-secret.md).
:::
If your output is missing a value for `key_id`, `region`, or `session_token`, you can recreate your secret by following the directions for [CREATE OR REPLACE SECRET](/documentation/sql-reference/motherduck-sql-reference/create-secret.md).
If that output worked successfully, you can confirm you have access to your AWS bucket by running these commands **in your terminal** (not in DuckDB):
```bash
# Log into AWS by running:
aws sso login
# Check bucket access:
aws s3 ls
```
**Example Output:**
```
PRE lambda-deployments/
PRE raw/
PRE ducklake/
2025-05-29 07:03:26 14695690 sample-data.csv
```
:::note
**Understanding the output**: `PRE` indicates folders/prefixes, while files show their size and modification date. If you only see `PRE` entries, your bucket contains organized data in folders. To explore deeper, use `aws s3 ls s3:////` or `aws s3 ls s3:/// --recursive` to see all files.
:::
## Configure permissions in AWS
This is an example of an IAM policy that will allow MotherDuck to access your S3 bucket. Note: if you use KMS keys, the IAM policy should also have `kms:Decrypt` in `AllowBucketListingAndLocation`.
```json
{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "AllowBucketListingAndLocation",
"Effect": "Allow",
"Action": [
"s3:ListBucket",
"s3:GetBucketLocation"
],
"Resource": [
"arn:aws:s3:::your_bucket_name"
]
},
{
"Sid": "AllowObjectRead",
"Effect": "Allow",
"Action": [
"s3:GetObject"
],
"Resource": [
"arn:aws:s3:::your_bucket_name/*"
]
}
]
}
```
## AWS Credential Chain
MotherDuck automatically finds your AWS credentials using AWS's credential chain. This is the recommended approach, as it uses short-lived credentials (typically valid for 1 hour), which are more secure and reduce the risk of credential leakage. For most users, it works seamlessly with your existing AWS setup.
### Most Common: AWS SSO
If you use AWS SSO (like most users), run:
```bash
aws sso login
```
To create a secret using the credential chain, run:
```sql
CREATE OR REPLACE SECRET my_secret IN MOTHERDUCK (
TYPE s3,
PROVIDER credential_chain,
CHAIN 'env;config' --optional
);
```
### Other Credential Types
The credential chain also works with:
- **Access keys** stored in `~/.aws/credentials`
- **IAM roles** (if running on EC2)
- **Environment variables**
### Advanced: Role Assumption
:::note
**Only needed for**: Cross-account access, elevated permissions, or when you need to assume a different role than your current profile.
:::
If you need to assume a specific IAM role, create a profile in `~/.aws/config`:
```ini
[profile my_motherduck_role]
role_arn = arn:aws:iam::your_account_id:role/your_role_name
source_profile = your_source_profile
```
Then create a secret that uses this profile:
```sql
CREATE SECRET my_s3_secret (
TYPE S3,
PROVIDER credential_chain,
PROFILE 'my_motherduck_role',
REGION 'us-east-1' -- Use your bucket's region if different
);
```
## Common Challenges
### Scope
When using multiple secrets, the `SCOPE` parameter ensures MotherDuck knows which secret to use. You can validate which secret is being used with the `which_secret` function:
```sql
SELECT * FROM which_secret('s3://my-bucket/file.parquet', 's3');
```
### Periods in bucket name (url_style = path)
Because of SSL certificate verification requirements, S3 bucket names that contain dots (.) cannot be accessed using virtual-hosted style URLs. This is due to AWS's SSL wildcard certificate (`*.s3.amazonaws.com`) which only validates single-level subdomains.
If your bucket name contains dots, you have two options:
1. **Rename your bucket** to remove dots (e.g., use dashes instead)
2. **Use path-style URLs** by adding the `URL_STYLE 'path'` option to your secret:
```sql
CREATE OR REPLACE SECRET my_secret (
TYPE s3,
URL_STYLE 'path',
SCOPE 's3://my.bucket.with.dots'
);
```
For more information, see [Amazon S3 Virtual Hosting documentation](https://docs.aws.amazon.com/AmazonS3/latest/userguide/VirtualHosting.html).
## What's Next
After resolving your AWS S3 secret issues:
- **[Query your S3 data](/key-tasks/cloud-storage/querying-s3-files.md)** - Learn how to query files stored in S3
- **[Load data into MotherDuck](/key-tasks/loading-data-into-motherduck/)** - Set up data loading workflows
- **[Configure additional cloud storage](/integrations/cloud-storage/)** - Set up Azure, Google Cloud, or other providers
- **[Share data with your team](/key-tasks/sharing-data/)** - Collaborate using MotherDuck's sharing features
---
Source: https://motherduck.com/docs/troubleshooting/error_messages
---
sidebar_position: 1
title: Error Messages
---
## Connection Errors
### Disallowed connections with a different configuration
If you create different connections with the same connection database path (such as `md:my_db`) but a different configuration dictionary, you may encounter the following error:
```text
Connection Error: Can't open a connection to same database file with a different configuration than existing connections
```
This validation error prevents accidental retrieval of a previously cached database connection, and can happen only in DuckDB APIs that make use of a [database instance cache](/documentation/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck.md#multiple-connections-and-the-database-instance-cache).
In file-based DuckDB, this can only happen when the previous connection is still in scope.
With MotherDuck, the database instance cache is longer lived, so you may see this error even after the previous connections have been closed.
#### How To Recover
For multiple connections that are used sequentially:
* If the configuration does not need to differ, consider unifying it, which will allow the same underlying client-side database instance to be reused.
* If the configuration differs intentionally, [set the database instance TTL to zero](/documentation/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck.md#setting-custom-database-instance-cache-time-ttl) and close the previous connections.
For multiple connections whose lifecycles need to overlap, add a differentiating suffix to the connection string, so that these connections are no longer considered to be backed by the same database.
A good differentiating string is the [`session_hint`](/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck/#read-scaling-with-session-hints).
While it is meant to associate an individual end user to a dedicated backend when used with read scaling tokens, it can also be used to signal client-side intent for a distinct database instance when used with regular tokens.
---
Source: https://motherduck.com/docs/troubleshooting/faq
---
sidebar_position: 1
title: FAQ
keywords:
- MotherDuck version
- open vs attach
- database connection
- WAL file
- database cache
- compatibility
---
import Versions from '@site/src/components/Versions';
### What's the difference between .open md: & ATTACH 'md:' ?
`.open` initiates a new database connection (to a given database or `my_db` by default) and can be passed different parameters in the connection strings like `motherduck_token` or [saas_mode](/key-tasks/authenticating-and-connecting-to-motherduck/authenticating-to-motherduck.md#authentication-using-saas-mode) flag. If you have previous local database attached, it will be detached when using `.open`.
`ATTACH` keeps the current database connection and attaches a new motherduck (cloud) database(s) to the current connection. You'll need to use `USE` to select the database you want to query.
### How do I know which version of DuckDB I should be running ?
MotherDuck currently supports DuckDB .
- In **US East (N. Virginia) -** `us-east-1`, MotherDuck is compatible with client versions through .
- In **Europe (Frankfurt) -** `eu-central-1`, MotherDuck is compatible with client versions through .
Please check that you have a compatible version of DuckDB running locally.
### How do I know which version of DuckDB am I running?
You can use the `VERSION` pragma to find out which version of DuckDB you are running
```sql
PRAGMA VERSION;
```
### How do I know what's executed locally and what's executed remote ?
If you run an [EXPLAIN](/sql-reference/motherduck-sql-reference/explain/) on your query, you will see the phyical plan. Each operation is followed by either (L)= Local or (R)= Remote as shown in the query plan example below. More information can be found in the [documentation](/sql-reference/motherduck-sql-reference/explain/).
```sql
EXPLAIN [Your Query]
```
### I connect to both MotherDuck and a local database, why is there an uncheckpointed WAL left behind?
DuckDB keeps a [database instance cache](/documentation/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck.md#multiple-connections-and-the-database-instance-cache) for each unique connection path.
Connecting to MotherDuck extends the lifetime of the database instance to a default of 15 minutes.
If you observe a WAL file left behind for the local database after the process exits or run into the "File is already open" error when closing and reopening the connection, there are several workarounds:
* Run `CHECKPOINT "local-database-name"` in the application code.
* Run `DETACH "local-database-name"` in the application code
* Disable the cache lifetime extension by setting `motherduck_dbinstance_inactivity_ttl` setting to `0s` (see [Setting Custom Database Instance Cache TTL](/documentation/key-tasks/authenticating-and-connecting-to-motherduck/connecting-to-motherduck.md#setting-custom-database-instance-cache-time-ttl)).
### Why am I not in the same Organization as my team?
If you sign up to MotherDuck directly, you will create your own Organization as a part of the sign up flow.
To join your team's Organization, reach out to your team and request that they [invite you to their Organization](../key-tasks/managing-organizations/managing-organizations.mdx#inviting-users-to-your-organization).
As an alternative, you may reach out to [MotherDuck support](./support.md) and we can search for other users within your domain.
### How do I use my team's shared databases?
Some database shares are scoped at the `ORGANIZATION` level. To use those shares, you must be in the same Organization as the person who created the share.
In addition, some shares are marked as 'DISCOVERABLE`. This allows members of the same Organization to easily find those shares through the UI.
Follow the steps outlined in ["Why am I not in the same Organization as my team?"](#why-am-i-not-in-the-same-organization-as-my-team) to join your team!
### How do I delete my account?
You can delete your account and all associated information by following these steps:
1. Navigate to your personal Settings and select "Members" from the left sidebar
2. Click the three dots (⋮) next to your name
3. Select "Delete"
4. Confirm the account deletion
:::note
If you are the only member of your Organization, deleting your account will also delete the Organization.
:::
For additional assistance, please contact our [support team](./support.md).
### Why I am getting SSL errors when connecting to MotherDuck from a Docker image?
If you see SSL errors when trying to connect to MotherDuck from a Docker image, this is likely because the image does not have updated CA certificates. If the container was working and suddenly stopped, it is likely that the certificates in the image have expired. Please refer to [Docker's documentation](https://docs.docker.com/engine/network/ca-certs/) for best practices on updating CA certificates in Docker images.
Some common errors you might see indicating an issue with your CA certificates include:
* `Could not get default pem root certs.`
* `Failed to create security handshaker.`
* `Update handshaker factory failed.`
### Why don't COPY DATABASE statements work in the MotherDuck Web UI?
The MotherDuck Web UI has limitations with certain SQL statements that are implemented as multiple statement macros:
**COPY DATABASE statements** have limited support in the MotherDuck Web UI:
* The full `COPY FROM DATABASE` command is not supported when copying both schema and data simultaneously
* **Workaround**: Use the `COPY FROM DATABASE` command with specific options:
* `COPY FROM DATABASE source_db TO target_db (SCHEMA)` - copies only the database structure
* `COPY FROM DATABASE source_db TO target_db (DATA)` - copies only the database data
For full functionality with these commands, use the DuckDB CLI or other supported drivers. More information about database copying can be found in the [database operations documentation](/documentation/key-tasks/database-operations/copying-databases.md).
---
Source: https://motherduck.com/docs/troubleshooting/reinstall-md-extension
---
sidebar_position: 3
title: Reinstall MotherDuck extension
---
The MotherDuck extension will be automatically loaded and downloaded as soon as you connect to MotherDuck. However, you can force a reinstallation by following these steps:
```
FORCE INSTALL motherduck;
```
Next to that make sure you are running the current supported [version of DuckDB](../faq#how-do-i-know-which-version-of-duckdb-i-should-be-running-).
---
Source: https://motherduck.com/docs/troubleshooting/support
---
sidebar_position: 7
title: Support
---
Have a question that isn't answered in our [FAQ](./faq.md)? Join the [MotherDuck Slack Community](https://slack.motherduck.com/) or contact us at [support@motherduck.com](mailto:support@motherduck.com?subject=Support+question).
---
Source: https://motherduck.com/docs/troubleshooting/troubleshooting-access-policy
---
sidebar_position: 6
title: Troubleshooting Data Access Policy
---
In order to help you with certain kinds of MotherDuck issues, it can be helpful for us to access your MotherDuck account. For example, if a specific query on a specific dataset is triggering a bug, it may be necessary for us to access the data and SQL query, and possibly re-run a specific query, in order to reproduce the issue and diagnose the problem.
A MotherDuck employee may use our community Slack or
email to request your permission to access your MotherDuck account while troubleshooting an issue.
If you give us permission to access to your MotherDuck account for troubleshooting, here is what you need to know:
- Our goal is to understand the issue and resolve the problem. We will make every effort to minimize the amount of time we spend accessing your account and the amount of data we access. We will only access the data we need to investigate and troubleshoot the specific issue.
- Any access to your data will be strictly read-only.
- A MotherDuck employee may pull in other MotherDuck employees during the debugging process. By agreeing to allow us to access your account for troubleshooting an issue, other MotherDuck employees who are asked to help investigate the issue may also access your account, subject to the same terms of this policy, without requesting additional authorization from you.
- We will not share or disclose the data we access while troubleshooting the issue to any third party or non-MotherDuck employee.
- We may make temporary copies of your data while debugging the issue. Any such copies will be permanently deleted once the issue is resolved.
- We may use the data we access in your account to generate a redacted copy of the data to be used for creating a bug report or test.
- The permission you have granted to access your account lapses once this specific issue is resolved.
---
Source: https://motherduck.com/docs/troubleshooting/troubleshooting
---
title: Troubleshooting
sidebar_class_name: troubleshooting-icon
description: Troubleshooting
---
---
Source: https://motherduck.com/docs/troubleshooting/uninstall
---
sidebar_position: 2
title: Uninstall MotherDuck extension
---
### How do I uninstall MotherDuck?
* Remove `motherduck_*` from your environment variables (most likely only `motherduck_token`) [1]
* Remove any `motherduck*.duckdb_extension` file located into `~/.duckdb` [2]
[1] To view all your environment variables you may use:
```bash
$ env | grep -i motherduck
```
To unset in the current session:
```bash
$ unset motherduck_token
```
To unset the variable permanently, you may have to check your shell initialisation files (`~/.bashrc`, `~/.zshrc`, etc.)
[2] Note those files are generally under `~/.duckdb/extensions//`. Eg. `~/.duckdb/extensions/v0.9.1/osx_arm64`.
You may use this script:
```bash
$ find ~/.duckdb -name 'motherduck*.duckdb_extension' -exec rm {} \;
```
---
Source: https://motherduck.com/docs/troubleshooting/version-lifecycle-schedules
---
sidebar_position: 8
title: MotherDuck Version Lifecycle Schedules
---
import Versions from '@site/src/components/Versions';
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
MotherDuck supports DuckDB versions according to a predictable lifecycle so you always know which version is safe to use.
The lifecycle schedules below form a part of MotherDuck’s Support Policies. They include Major Releases and Minor Releases to support [DuckDB](/troubleshooting/faq/#how-do-i-know-which-version-of-duckdb-i-should-be-running-) and [DuckLake](/integrations/file-formats/ducklake/) versions and specify end of life dates for both.
## Currently Supported Versions
MotherDuck currently supports DuckDB .
- In **US East (N. Virginia) -** `us-east-1`, MotherDuck is compatible with client versions through .
- In **Europe (Frankfurt) -** `eu-central-1`, MotherDuck is compatible with client versions through .
MotherDuck strives to support current DuckDB minor versions in alignment with the [DuckDB](https://duckdb.org/release_calendar) and [DuckLake](https://ducklake.select/release_calendar) release calendars.
We recommend that users run the latest minor version when possible to take advantage of the most up-to-date features and functionality.
## MotherDuck Support Lifecycle Schedules
The following lifecycle schedules apply to DuckDB and DuckLake versions.
```mermaid
%%{init: {"gantt": {"fontSize": 13, "sectionFontSize": 15}}}%%
gantt
title MotherDuck Version Lifecycle Schedules
dateFormat YYYY-MM-DD
axisFormat %b %Y
section 1.0.x
DuckDB 1.0.0 :2024-06-01, 2025-07-31
section 1.1.x
DuckDB 1.1.0 :2024-09-01, 2025-07-31
DuckDB 1.1.1 :2024-09-15, 2025-07-31
DuckDB 1.1.2 :2024-10-01, 2025-07-31
DuckDB 1.1.3 :2024-11-01, 2025-07-31
section 1.2.x
DuckDB 1.2.0 :2025-02-01, 2026-01-31
DuckDB 1.2.1 :2025-03-01, 2026-01-31
DuckDB 1.2.2 :2025-04-01, 2026-01-31
section 1.3.x
DuckDB 1.3.0 :2025-06-01, 2026-03-31
DuckDB 1.3.1 :2025-06-15, 2026-03-31
DuckDB 1.3.2 :2025-07-01, 2026-03-31
section 1.4.x
DuckDB 1.4.0 :2025-09-01, 2026-05-31
DuckDB 1.4.1 :2025-10-01, 2026-05-31
DuckDB 1.4.2 :2025-11-01, 2026-05-31
section DuckLake
DuckLake 0.1 :2025-06-01, 2026-03-31
DuckLake 0.2 :2025-07-01, 2026-03-31
DuckLake 0.3 :2025-09-01, 2026-05-31
```
| DuckDB Release | DuckLake Release | Release Date | End of Life Date * |
|----------------|-------------------------------------|------------------|--------------------------|
| 1.0.0 | — | June 2024 | July 2025 |
| 1.1.0 | — | September 2024 | July 2025 |
| 1.1.1 | — | September 2024 | July 2025 |
| 1.1.2 | — | October 2024 | July 2025 |
| 1.1.3 | — | November 2024 | July 2025 |
| 1.2.0 | — | February 2025 | January 2026 |
| 1.2.1 | — | March 2025 | January 2026 |
| 1.2.2 | — | April 2025 | January 2026 |
| 1.3.0 | 0.1 (June 2025), 0.2 (July 2025) | June 2025 | March 2026 |
| 1.3.1 | 0.1 (June 2025), 0.2 (July 2025) | June 2025 | March 2026 |
| 1.3.2 | 0.1 (June 2025), 0.2 (July 2025) | July 2025 | March 2026 |
| 1.4.0 | 0.3 (September 2025) | September 2025 | May 2026 |
| 1.4.1 | 0.3 (September 2025) | October 2025 | May 2026 |
| 1.4.2 | 0.3 (September 2025) | November 2025 | May 2026 |
| 1.4.3 | 0.3 (September 2025) | December 2025 | May 2026 |
* Beginning with DuckDB 1.3.0, MotherDuck will support each Minor Release until the date specified above.
## End of Life (EoL) Policy
When a new minor version becomes available, the previous one enters Extended Support. While we don't offer support for new features, critical fixes may still be backported for the greater of:
- **6 months** after the version’s release, or
- **4 months** after the next minor version is released
When a minor version reaches its End of Life (EoL):
- Connections using that DuckDB version are blocked, requiring MotherDuck users to upgrade
- Ahead of scheduled End of Life (EoL) dates, MotherDuck provides in-app UI warnings, email communications, and targeted outreach to users about impacted versions slated for deprecation
## MotherDuck Extended Lifecycle Support Add-On
MotherDuck offers an **Extended Lifecycle Support Add-On** to provide customers with peace of mind and flexibility to upgrade at a later date by extending ongoing technical support for a minor DuckDB version after it reaches its End of Life (EOL) date.
For more information, please [get in touch with our team](https://motherduck.com/contact-us/product-expert/).
💁 If you have additional questions about our version lifecycle, please feel free connect with us directly in our [Community Slack support channel](https://slack.motherduck.com/) or send a note to support@motherduck.com.
---
Source: https://motherduck.com/docs/troubleshooting/windows-certs
---
sidebar_position: 4
title: Install certificate on Windows machines
---
In some circumstances, you may face an error that reads like `Http response at 400 or 500 level, http status code: 0`.
On Windows machine, this is usually due to [Let's Encrypt](https://letsencrypt.org/) certificate not being trusted.
To fix this, please follow the steps below:
* download this file https://letsencrypt.org/certs/isrgrootx1.der
* open it (double click on the file)
* click on "Install Certificate" and follow the instructions:
Then you should be able to try again.
If it still doesn't work, could you check if it was correctly installed by opening the certmgr (typing "`cert`" in the search box should show it)
And then it should be under `Trusted Root Certification Authorities\Certificates`:
.src})
.src})
.src})
.src})
.src})
.src})
- **Improved Boolean cell styling:** Boolean values in the data grid now have distinct visual weights to make it easier to visually scan result sets and prevent confusion with empty cells.
.src})
## June 18, 2025
- **DuckDB 1.3.1:** MotherDuck supports DuckDB 1.3.1, a bugfix release. Additional details are available in the changelog [here](https://github.com/duckdb/duckdb/releases/tag/v1.3.1).
- **`PIVOT` statements in MotherDuck UI:** The MotherDuck UI now supports [`PIVOT` statements](https://duckdb.org/docs/stable/sql/statements/pivot.html), with pivot columns also appearing in the Column Explorer. `PIVOT` transforms distinct column values into separate columns with aggregated data.
- **New `STORAGE_INFO` View in `MD_INFORMATION_SCHEMA`:** Organization admins can now review detailed storage breakdowns per database using the new [`STORAGE_INFO` view](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/storage_info/) in the [`MD_INFORMATION_SCHEMA`](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/md_information_schema/introduction/).
## June 12, 2025
- **Improved query execution UX:** After 5 seconds, the run button now displays a timer showing how long the query has been running. It also offers clearer visual cues for canceling a query on mouseover and focus.
## June 5, 2025
- **Overwrite a database with a zero-copy clone:** The new [`COPY FROM DATABASE (OVERWRITE)` command](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/copy-database-overwrite/) replaces all data in the target database with the source’s contents in a single atomic operation, waiting for active writes to finish and blocking new ones during the process.
- **Copy SQL definitions for views from the Object Explorer:** The dropdown menu for views in the left-hand panel of the MotherDuck UI now lets you copy the associated SQL definition without opening the table summary.
## May 29, 2025
MotherDuck now supports DuckDB version 1.3.0 🎉
DuckDB 1.3.0 improves performance in real-world scenarios for faster queries, new SQL syntax, and smarter Parquet file handling. Learn more in the [changelog](https://github.com/duckdb/duckdb/releases/tag/v1.3.0) here.