Write SQL with AI

Automatically Edit SQL Queries in the WebUI

Edit is a MotherDuck AI-powered UI feature which allows you to edit SQL queries in the WebUI. The AI is aware of DuckDB-specific SQL features and relevant database schemas to provide effective suggestions.

Select the specific part of the query you want to edit, then press the keyboard shortcut to open the Edit dialog:

• Windows/Linux: Ctrl + K
• macOS: ⌘ + K

In the Edit dialog, enter your prompt (e.g., "extract the domain from the url, using a regex") and click Suggest edit.

If the suggestion is not as desired, it can be further clarified with follow-up prompts.

When happy with the change, click 'Apply edit', and the change will be applied to the query.

Automatically Fix SQL Errors in the WebUI

FixIt is a MotherDuck AI-powered UI feature that helps you resolve common SQL errors by offering fixes in-line. Read more about it in our blog post. FixIt can also be called programmatically using the prompt_fix_line . Find more information here.

How FixIt works

By default, FixIt is enabled for all users. If you run a query that has an error, FixIt will automatically analyze the query and suggest in-line fixes. When accepting a fix, MotherDuck will automatically update your query and re-execute it.

When 'Auto-suggest' is un-toggled, FixIt will not automatically suggest fixes anymore. FixIt can still be manually triggered by clicking 'Suggest fix' at the bottom of the error message.

Access SQL Assistant functions

MotherDuck provides built-in AI features to help you write, understand and fix DuckDB SQL queries more efficiently. These features include:

• Answer questions about your data using the prompt_query pragma.
• Generate SQL for you using the prompt_sql table function.
• Correct and fix up your SQL query using the prompt_fixup table function.
• Correct and fix up your SQL query line-by-line using the prompt_fix_line table function.
• Help you understand a query using the prompt_explain table function.
• Help you understand contents of a database using the prompt_schema table function.

Example usage of prompt_sql

We use MotherDuck's sample Hacker News dataset from MotherDuck's sample data database.

CALL prompt_sql('what are the top domains being shared on hacker_news?');

Output of this SQL statement is a single column table that contains the AI-generated SQL query.

query
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