--- title: "execute_sql" --- Execute SQL queries and statements on your database with support for transactions, multiple statements, and safety controls. ## Features - **Single statements**: Execute a single SQL query or command - **Multiple statements**: Separate multiple statements with semicolons (`;`) - **Transactions**: Wrap operations in `BEGIN`/`COMMIT` blocks for atomic execution - **[Read-only mode](/config/read-only-mode)**: Configure a tool with `readonly = true` to restrict execution to read-only operations, enforced by both a keyword classifier and the database's own read-only mode - **Row limiting**: Configure `max_rows` per tool via TOML `[[tools]]` to limit SELECT query results ## Single Query Execute a single SELECT, INSERT, UPDATE, or DELETE statement. ```sql SELECT SELECT * FROM users WHERE status = 'active' LIMIT 10; ``` ```sql INSERT INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'); ``` ```sql UPDATE UPDATE users SET last_login = NOW() WHERE id = 123; ``` ```sql DELETE DELETE FROM sessions WHERE expires_at < NOW(); ``` ## Multiple Statements Execute multiple SQL statements in sequence by separating them with semicolons. ```sql INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com'); INSERT INTO users (name, email) VALUES ('Bob', 'bob@example.com'); INSERT INTO users (name, email) VALUES ('Charlie', 'charlie@example.com'); ``` Each statement is executed sequentially. If one statement fails, subsequent statements may not be executed depending on the database error handling. ## Transactions Wrap multiple operations in a transaction to ensure atomicity. Use `BEGIN`/`COMMIT` for successful transactions or `ROLLBACK` to undo changes. ```sql Successful Transaction BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; ``` ```sql Rollback Transaction BEGIN; UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- Something went wrong, undo changes ROLLBACK; ``` ## DDL Operations Create, alter, or drop database objects. ```sql CREATE TABLE CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, price DECIMAL(10, 2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); ``` ```sql ALTER TABLE ALTER TABLE products ADD COLUMN category VARCHAR(100); ``` ```sql CREATE INDEX CREATE INDEX idx_products_category ON products(category); ``` ```sql DROP TABLE DROP TABLE IF EXISTS temp_data; ``` ## Read-Only Mode Restrict SQL execution to safe, read-only operations by configuring the `execute_sql` tool with `readonly = true`: ```toml TOML Configuration [[sources]] id = "production" dsn = "postgres://user:pass@localhost:5432/mydb" [[tools]] name = "execute_sql" source = "production" readonly = true [[tools]] name = "search_objects" source = "production" ``` In read-only mode, each statement must pass a keyword classifier (`SELECT`, `WITH` and `EXPLAIN` everywhere, plus a few read-only statements specific to each database), and is then executed under the database's own read-only mechanism where the engine has one. See [Read-Only Mode](/config/read-only-mode) for how it is enforced on each database and what it does not cover. Read-only mode is a guardrail, not a permission boundary. Always connect DBHub with a least-privilege, read-only database user. ## Row Limiting Limit the number of rows returned from SELECT queries to prevent accidentally retrieving too much data: ```toml TOML Configuration [[sources]] id = "production" dsn = "postgres://..." [[tools]] name = "execute_sql" source = "production" max_rows = 1000 ``` - Only applied to SELECT statements, not INSERT/UPDATE/DELETE - If your query already has a `LIMIT` or `TOP` clause, DBHub uses the smaller value - Can be configured per-tool in [TOML configuration](/config/toml) - When the cap actually cuts off rows, the statement's result carries `"truncated": true` alongside `count`, so a capped result is distinguishable from a table that genuinely has exactly `max_rows` rows (detection is exact — DBHub fetches one probe row past the cap; the flag is omitted for complete results). Run `COUNT(*)` to get the true total when you see it. ## Response Format The result is a JSON object. Each statement in the call gets its own entry in `statements`, in order — a single query is a `statements` array of length one: ```json { "success": true, "data": { "statements": [ { "sql": "SELECT id, name FROM users LIMIT 2", "rows": [ { "id": 1, "name": "Alice" }, { "id": 2, "name": "Bob" } ], "count": 2 } ], "source_id": "production" } } ``` - `count` is the number of rows returned, or the number of rows affected for a write. - `truncated: true` appears on a statement only when `max_rows` cut its rows off (see [Row Limiting](#row-limiting)). - `messages` appears next to `statements` when the database sent informational messages (SQL Server `PRINT` output, for example, or the rows and bytes a ClickHouse statement read). - Columns that share a name within one result are suffixed `_1`, `_2`, … so no value is lost. Errors use the same envelope with `success: false`: ```json { "success": false, "error": "relation \"userz\" does not exist", "code": "EXECUTION_ERROR" } ``` ## Selective Tool Exposure Control which tools are available for each database source. By default, both `execute_sql` and `search_objects` are enabled. You can: - Disable built-in tools entirely - Configure specific tools with custom settings - Expose only custom tools for restricted access **Example: Disable execute_sql, keep search_objects:** ```toml [[sources]] id = "production" dsn = "postgres://..." # Only enable search_objects - execute_sql will not be available [[tools]] name = "search_objects" source = "production" ``` **Example: Read-only execute_sql with row limit:** ```toml [[sources]] id = "production" dsn = "postgres://..." [[tools]] name = "execute_sql" source = "production" readonly = true max_rows = 100 [[tools]] name = "search_objects" source = "production" ``` If no `[[tools]]` entries are defined for a source, both `execute_sql` and `search_objects` are enabled by default for backward compatibility. ## See Also - [search_objects](/tools/search-objects) - Database schema exploration tool - [explain_sql](/tools/explain-sql) - Query execution plan tool (opt-in) - [health_check](/tools/health-check) - Connection pool and buffer cache metrics tool (opt-in) - [Custom Tools](/tools/custom-tools) - Reusable, parameterized SQL operations