Query Execution The execute_query tool executes SQL queries using DuckDB’s SQL dialect and returns results in JSON format.
Simple SELECT Query { "name" : "execute_query" , "arguments" : { "sql" : "SELECT 1 as num, 'hello' as greeting" } }
INSERT Data INSERT, UPDATE, DELETE, and CREATE operations require the server to be started with --read-write flag.
{ "name" : "execute_query" , "arguments" : { "sql" : "INSERT INTO users VALUES (1, 'Alice', '[email protected] ')" } }
UPDATE Data { "name" : "execute_query" , "arguments" : { "sql" : "UPDATE users SET email = '[email protected] ' WHERE id = 1" } }
DELETE Data { "name" : "execute_query" , "arguments" : { "sql" : "DELETE FROM users WHERE id = 1" } }
Exploring Database Schemas List All Databases The list_databases tool shows all available databases in your connection.
{ "name" : "list_databases" , "arguments" : {} }
The system excludes internal databases (system and temp) from results.
List Tables and Views The list_tables tool shows all tables and views in a database.
{ "name" : "list_tables" , "arguments" : { "database" : "my_db" } }
Filter by Schema { "name" : "list_tables" , "arguments" : { "database" : "my_db" , "schema" : "main" } }
Use Current Database Omit the database parameter to use the current database:
{ "name" : "list_tables" , "arguments" : {} }
List Table Columns The list_columns tool shows detailed column information including types and constraints.
{ "name" : "list_columns" , "arguments" : { "table" : "users" , "database" : "my_db" } }
Real-World Query Examples Aggregations Count total rows and calculate averages:
{ "name" : "execute_query" , "arguments" : { "sql" : "SELECT COUNT(*) as total_users, AVG(age) as avg_age FROM users" } }
Filtering and Sorting SELECT name , email, created_at FROM users WHERE created_at > '2024-01-01' ORDER BY created_at DESC LIMIT 10
GROUP BY with HAVING SELECT country, COUNT ( * ) as user_count, AVG (purchase_amount) as avg_purchase FROM users JOIN purchases ON users . id = purchases . user_id GROUP BY country HAVING COUNT ( * ) > 100 ORDER BY user_count DESC
JOINs Across Tables { "name" : "execute_query" , "arguments" : { "sql" : "SELECT u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.name ORDER BY order_count DESC LIMIT 5" } }
Window Functions SELECT name , sales_amount, ROW_NUMBER () OVER ( ORDER BY sales_amount DESC ) as rank, SUM (sales_amount) OVER ( ORDER BY sales_amount DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) as running_total FROM sales_people LIMIT 10
Query Result Limits Query results are automatically limited to prevent excessive data transfer.
Default Limits
Max rows : 1,024 rows (configurable with --max-rows)Max characters : 50,000 characters (configurable with --max-chars)
Truncated Results When results exceed limits, you’ll receive a truncation warning:
{ "success" : true , "columns" : [ "id" , "name" ], "rows" : [[ 1 , "Alice" ], [ 2 , "Bob" ], ... ], "rowCount" : 1024 , "truncated" : true , "warning" : "Results limited to 1,024 rows. Query returned more data." }
To work with large datasets:
Use LIMIT clauses in your SQL
Filter data with WHERE conditions
Use aggregations instead of returning raw rows
Increase limits when starting the server: --max-rows 10000 --max-chars 100000
Error Handling Query Errors Errors return a structured JSON response:
Tool Call (Invalid)
Error Response
{ "name" : "execute_query" , "arguments" : { "sql" : "SELECT * FROM nonexistent_table" } }
Syntax Errors { "success" : false , "error" : "Parser Error: syntax error at or near 'FORM'" , "errorType" : "ParserException" }
Read-Only Mode Errors Write operations fail when server runs in read-only mode (default):
{ "success" : false , "error" : "Cannot execute statement of type INSERT in read-only mode" , "errorType" : "PermissionException" }
Start the server with --read-write to enable INSERT, UPDATE, DELETE, and CREATE operations.