# MotherDuck Documentation - SQL Reference > Scoped full Markdown content for SQL Reference. For other areas, start from https://motherduck.com/docs/llms.txt instead of loading unrelated documentation. ## Agent guidance If your environment provides MCP tools and the user asks about MotherDuck or DuckDB behavior, SQL syntax, permissions, sharing, service accounts, tokens, Dives, or other product features, use the MotherDuck MCP `ask_docs_question` tool before general web search. It answers from official DuckDB and MotherDuck documentation. For broad context, prefer the most specific scoped `llms-full.txt` file listed in https://motherduck.com/docs/llms.txt before loading the root `llms-full.txt`. The root file contains the complete public documentation corpus and is intended for bulk indexing or large-context workflows. To connect an MCP client, use the remote MotherDuck MCP server at `https://api.motherduck.com/mcp`. Setup instructions: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup. Tool reference: https://motherduck.com/docs/sql-reference/mcp/ask-docs-question. --- Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/ai-functions # AI Functions > MotherDuck AI SQL functions for text generation, embeddings, and SQL assistance. 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 ## Included pages - [SQL Assistant](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant) - [EMBEDDING](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/embedding): Generate vector embeddings for text using the EMBEDDING function for semantic search. - [PROMPT](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/prompt): Generate AI responses directly in SQL with the PROMPT function. - [Dives Functions](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/dives): SQL table functions for creating, reading, updating, and deleting MotherDuck Dives. --- Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/dives/dives # Dives Functions > SQL table functions for creating, reading, updating, and deleting MotherDuck Dives. SQL table functions for managing [Dives](/key-tasks/ai-and-motherduck/dives), the interactive React data apps that query live MotherDuck data. These functions let you create, read, update, and delete Dives directly from SQL. Dives use the [`useSQLQuery` hook](use-sql-query) to query data from within their React components and the [`useDiveState` hook](use-dive-state) to sync shareable UI state to the URL. :::note These functions are executed server-side on MotherDuck. They are not available on local-only DuckDB connections. ::: Create your first Dive assisted by your AI-tool of choice using our [MCP server](/key-tasks/ai-and-motherduck/mcp-setup/). Or try out a minimal working example using only SQL. ```sql SELECT * FROM MD_CREATE_DIVE( title = 'PokeDuck', content = ' import { useSQLQuery } from "@motherduck/react-sql-query"; export default function Dive() { const { data } = useSQLQuery( `SELECT PROMPT(''Suggest a duck type or pokemon and tell a fun fact about them'')`, { select: (rows) => Object.values(rows[0])[0] } ); return

FUN FACT:

{JSON.stringify(data)}; }' ); ``` ## Available functions ## Included pages - [MD_LIST_DIVES](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/dives/md-list-dives): List all Dives in your MotherDuck account with pagination support. - [useSQLQuery hook](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/dives/use-sql-query): React hook for querying MotherDuck data from within Dives. - [useDiveState hook](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/dives/use-dive-state): React hook for storing shareable UI state in MotherDuck Dives. - [MD_GET_DIVE](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/dives/md-get-dive): Retrieve a Dive by ID including its full React component content. - [MD_CREATE_DIVE](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/dives/md-create-dive): Create a new Dive in your MotherDuck account. - [MD_UPDATE_DIVE_METADATA](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/dives/md-update-dive-metadata): Update a Dive's title or description without creating a new version. - [MD_UPDATE_DIVE_CONTENT](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/dives/md-update-dive-content): Update a Dive's React component code, creating a new version. - [MD_DELETE_DIVE](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/dives/md-delete-dive): Permanently delete a Dive by ID. - [MD_LIST_DIVE_VERSIONS](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/dives/md-list-dive-versions): List all versions of a specific Dive with pagination support. - [MD_GET_DIVE_VERSION](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/dives/md-get-dive-version): Retrieve a specific historical version of a Dive including its content. --- Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/flights/flights # Flights functions > SQL table functions for creating, scheduling, running, and inspecting MotherDuck Flights. SQL table functions for managing [Flights](/concepts/flights), MotherDuck's scheduled Python execution. Use these functions from any MotherDuck client (DuckDB CLI, BI tool, or another Flight) to create, schedule, list, and monitor Flights without leaving SQL. The MCP server exposes the same operations to AI agents through `create_flight`, `list_flights`, `run_flight`, and so on. The MCP and SQL surfaces use slightly different parameter names — see each function page for the details, or read the [Flights MCP reference](/sql-reference/mcp/) for the agent-facing tools. :::note These functions execute server-side on MotherDuck. They are not available on local-only DuckDB connections. ::: A minimal end-to-end Flight from SQL: ```sql -- Create the Flight SELECT flight_id, current_version FROM MD_CREATE_FLIGHT( name := 'heartbeat', access_token_name := '', source_code := $$ import duckdb def main(): con = duckdb.connect("md:") con.execute("CREATE DATABASE IF NOT EXISTS flights_demo") print("ok") $$, requirements_txt := 'duckdb==1.5.2' ); -- Trigger an on-demand run CALL MD_RUN_FLIGHT(flight_id := ''); -- Inspect the run SELECT run_number, status, created_at FROM MD_FLIGHT_RUNS(flight_id := '') ORDER BY run_number DESC LIMIT 1; ``` ## Available functions ## Included pages - [MD_CREATE_FLIGHT](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/flights/md-create-flight): Create a new Flight in your MotherDuck account. - [MD_UPDATE_FLIGHT](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/flights/md-update-flight): Update a Flight's source code, requirements, config, token, secrets, name, or schedule. - [MD_DELETE_FLIGHT](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/flights/md-delete-flight): Delete a Flight, its versions, runs, and logs. - [MD_GET_FLIGHT](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/flights/md-get-flight): Fetch the summary metadata for a single Flight. - [MD_GET_FLIGHT_VERSION](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/flights/md-get-flight-version): Fetch the full content (source, requirements, config, token, secrets) for a specific Flight version. - [MD_FLIGHTS](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/flights/md-flights): List the Flights you own with summary metadata. - [MD_FLIGHT_VERSIONS](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/flights/md-flight-versions): List the version history of a Flight. - [MD_RUN_FLIGHT](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/flights/md-run-flight): Trigger an on-demand execution of a Flight using its current version. - [MD_FLIGHT_RUNS](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/flights/md-flight-runs): List the execution history of a Flight, newest first. - [MD_FLIGHT_LOGS](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/flights/md-flight-logs): Read the combined stdout and stderr captured during a Flight run. - [MD_CANCEL_FLIGHT_RUN](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/flights/md-cancel-flight-run): Cancel an in-progress Flight run. --- Source: https://motherduck.com/docs/sql-reference/mcp/mcp # MotherDuck MCP server > Connect AI assistants to MotherDuck using the remote (fully managed) or local (fully customizable) MCP server MotherDuck offers a **remote MCP server** (fully managed, read-write) and a [**local MCP server**](#local-mcp-server) (fully customizable, self-hosted) that let AI assistants query and explore your MotherDuck databases using the [Model Context Protocol (MCP)](https://modelcontextprotocol.io/). :::info Connection URL The remote MCP server is hosted at `https://api.motherduck.com/mcp`. Most clients connect through OAuth automatically; clients that need a manual configuration use this URL with an HTTP transport. ::: For step-by-step setup instructions for all supported clients (Claude, ChatGPT, Cursor, Claude Code, and others), see [Connect to the MotherDuck MCP Server](/key-tasks/ai-and-motherduck/mcp-setup/). ## Server capabilities With the remote MCP server, your agent can: - Execute read-only and read-write SQL against your databases - Explore database schemas, tables, and columns - Attach and detach [shares](/key-tasks/sharing-data/sharing-overview) - Ask questions about DuckDB and MotherDuck documentation - Create and manage [Dives](/key-tasks/ai-and-motherduck/dives) (interactive data visualizations) - Render Dives inline in supported clients with the Dive Viewer MCP App, so you iterate against live data instead of a sample-data preview - Create, schedule, run, and monitor [Flights](/concepts/flights) (scheduled Python jobs on MotherDuck compute) For clients that [support MCP instructions](https://modelcontextprotocol.io/clients#feature-support-matrix), the remote MCP 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). ### Regional availability The remote 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 remote MCP server. If you have strict data residency requirements, ensure your MCP client runs within your region. ### Restricting to read-only access The remote MCP server exposes both read-only and read-write tools. To restrict your AI assistant to read-only access, see [Restricting to read-only access](/key-tasks/ai-and-motherduck/securing-read-only-access/). ## Local MCP server For local DuckDB databases, custom configurations, or self-hosted scenarios, use the **local MCP server** ([mcp-server-motherduck](https://github.com/motherduckdb/mcp-server-motherduck)). For a comparison of remote vs local and when to use each, see the [setup guide](/key-tasks/ai-and-motherduck/mcp-setup/#remote-vs-local-mcp-server). [📦 **Local MCP Server GitHub Repository** – Self-host the open-source MCP server for DuckDB and MotherDuck](https://github.com/motherduckdb/mcp-server-motherduck) ## Related resources - [Connect to the MCP Server](/key-tasks/ai-and-motherduck/mcp-setup/) - Setup instructions for all supported AI clients - [MCP Workflows 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 ## Included pages - [list_databases](https://motherduck.com/docs/sql-reference/mcp/list-databases): List all databases in your MotherDuck account - [list_shares](https://motherduck.com/docs/sql-reference/mcp/list-shares): List database shares that have been shared with you - [list_tables](https://motherduck.com/docs/sql-reference/mcp/list-tables): List tables and views in a MotherDuck database - [list_columns](https://motherduck.com/docs/sql-reference/mcp/list-columns): List columns of a table or view with types and comments - [search_catalog](https://motherduck.com/docs/sql-reference/mcp/search-catalog): Fuzzy search across databases, schemas, tables, columns, and shares - [query](https://motherduck.com/docs/sql-reference/mcp/query): Execute SQL queries against MotherDuck databases - [query_rw](https://motherduck.com/docs/sql-reference/mcp/query-rw): Execute SQL queries that can modify data or schema in MotherDuck - [ask_docs_question](https://motherduck.com/docs/sql-reference/mcp/ask-docs-question): Ask questions about DuckDB or MotherDuck documentation - [get_dive_guide](https://motherduck.com/docs/sql-reference/mcp/get-dive-guide): Load instructions for creating MotherDuck Dives - [list_dives](https://motherduck.com/docs/sql-reference/mcp/list-dives): List all Dives in your MotherDuck workspace - [read_dive](https://motherduck.com/docs/sql-reference/mcp/read-dive): Read a specific Dive by ID, including its full component code - [view_dive](https://motherduck.com/docs/sql-reference/mcp/view-dive): Render a MotherDuck Dive as a live, interactive MCP app inside the host client. - [save_dive](https://motherduck.com/docs/sql-reference/mcp/save-dive): Save a new Dive to your MotherDuck workspace - [update_dive](https://motherduck.com/docs/sql-reference/mcp/update-dive): Update an existing Dive's title, description, or content - [share_dive_data](https://motherduck.com/docs/sql-reference/mcp/share-dive-data): Share the data for a Dive with your organization - [delete_dive](https://motherduck.com/docs/sql-reference/mcp/delete-dive): Permanently delete a Dive by ID - [get_flight_guide](https://motherduck.com/docs/sql-reference/mcp/get-flight-guide): Load the canonical instructions for authoring, scheduling, running, and troubleshooting MotherDuck Flights. - [list_flights](https://motherduck.com/docs/sql-reference/mcp/list-flights): List the Flights you own with summary metadata, optionally filtered by keywords. - [get_flight](https://motherduck.com/docs/sql-reference/mcp/get-flight): Fetch a Flight's metadata and version snapshot by UUID, optionally at a specific historical version. - [list_flight_versions](https://motherduck.com/docs/sql-reference/mcp/list-flight-versions): List the version history of a Flight, newest first. - [create_flight](https://motherduck.com/docs/sql-reference/mcp/create-flight): Create a new Flight from Python source code, requirements, token, and an optional schedule. - [update_flight](https://motherduck.com/docs/sql-reference/mcp/update-flight): Update a Flight's source, requirements, config, token, secrets, name, or schedule. - [edit_flight_source](https://motherduck.com/docs/sql-reference/mcp/edit-flight-source): Edit a Flight's source code with one or more find-and-replace operations, producing a new version. - [delete_flight](https://motherduck.com/docs/sql-reference/mcp/delete-flight): Permanently delete a Flight, its versions, schedule, and run history. - [run_flight](https://motherduck.com/docs/sql-reference/mcp/run-flight): Trigger an on-demand execution of a Flight using its current version. - [list_flight_runs](https://motherduck.com/docs/sql-reference/mcp/list-flight-runs): List the execution history of a Flight, newest first. - [get_flight_run_logs](https://motherduck.com/docs/sql-reference/mcp/get-flight-run-logs): Fetch the logs and run record for a single Flight run. - [cancel_flight_run](https://motherduck.com/docs/sql-reference/mcp/cancel-flight-run): Cancel an in-progress Flight run. --- Source: https://motherduck.com/docs/sql-reference/rest-api/motherduck-rest-api # MotherDuck REST API > REST API reference for managing MotherDuck resources including databases, users, and access tokens. # MotherDuck REST API ::::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 through 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 through 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, see [Create and configure service accounts](/key-tasks/service-accounts-guide/create-and-configure-service-accounts/). :::: If you would like to generate your own OpenAPI client the spec file is located at https://api.motherduck.com/docs/specs ## Included pages - [Create a Dive embed session](https://motherduck.com/docs/sql-reference/rest-api/dashboards-create-embed-session): Creates an embed session for the specified Dive. - [Create an access token for a user](https://motherduck.com/docs/sql-reference/rest-api/users-create-token): Create an access token for a user - [Create new user](https://motherduck.com/docs/sql-reference/rest-api/users-create-service-account): Create user is restricted to creating a user with a 'Member' role - [Delete a user](https://motherduck.com/docs/sql-reference/rest-api/users-delete): Permanently delete a user and all of their data. THIS CANNOT BE UNDONE - [Get active accounts](https://motherduck.com/docs/sql-reference/rest-api/ducklings-get-active-accounts): [Preview] Get active accounts in an organization along with active Ducklings per account. Requires 'Admin' role - [Get user Duckling configuration](https://motherduck.com/docs/sql-reference/rest-api/ducklings-get-duckling-config-for-user): Gets Duckling (instance) configuration for a user. Requires 'Admin' role. - [Invalidate a user access token](https://motherduck.com/docs/sql-reference/rest-api/users-delete-token): Invalidate a user access token - [List a user's access tokens](https://motherduck.com/docs/sql-reference/rest-api/users-list-tokens): List a user's access tokens - [Set user Duckling configuration](https://motherduck.com/docs/sql-reference/rest-api/ducklings-set-duckling-config-for-user): Sets Duckling (instance) configuration for a user. Requires 'Admin' role --- Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/index # 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 the [MCP Server setup guide](/key-tasks/ai-and-motherduck/mcp-setup/) (or the [local MCP server](/key-tasks/ai-and-motherduck/mcp-setup/#remote-vs-local-mcp-server) for self-hosted, read-write use). ## Available functions ## Included pages - [PROMPT_QUERY](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-query): Answer natural language questions about your data using the PROMPT_QUERY function. - [PROMPT_SQL](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-sql): Generate SQL queries from natural language descriptions using the PROMPT_SQL function. - [PROMPT_EXPLAIN](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-explain): Get AI-generated explanations of SQL queries using the PROMPT_EXPLAIN function. - [PROMPT_FIX_LINE](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-fix-line): Fix SQL query errors line by line using the PROMPT_FIX_LINE function. - [PROMPT_FIXUP](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-fixup): Automatically fix SQL query errors using the PROMPT_FIXUP function. - [PROMPT_SCHEMA](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-schema): Describe database contents using the PROMPT_SCHEMA function for AI-generated schema summaries. ## 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 | US West | |----------|--------|--------|---------| | SQL Assistant Functions | ✓ | ✓ | ✓ | ### Data usage The data processed by MotherDuck's AI functionality is **not** used for model training. --- Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/ai-functions/sql-assistant/prompt-query # PROMPT_QUERY > Answer natural language questions about your data using the PROMPT_QUERY function. ## 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-sql # PROMPT_SQL > Generate SQL queries from natural language descriptions using the PROMPT_SQL function. ## 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/ai-functions/sql-assistant/prompt-explain # PROMPT_EXPLAIN > Get AI-generated explanations of SQL queries using the PROMPT_EXPLAIN function. ## 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 # PROMPT_FIX_LINE > Fix SQL query errors line by line using the PROMPT_FIX_LINE function. ## 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 # PROMPT_FIXUP > Automatically fix SQL query errors using the PROMPT_FIXUP function. ## 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-schema # PROMPT_SCHEMA > Describe database contents using the PROMPT_SCHEMA function for AI-generated schema summaries. ## 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/alter-database # ALTER DATABASE > Update storage-related settings on a MotherDuck database. The `ALTER DATABASE` statement updates storage-related settings for an existing MotherDuck database. ## Syntax ```sql ALTER DATABASE SET