DBHub

--- 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**: When enabled with `--readonly` flag, only SELECT and read-only operations are allowed - **Row limiting**: Configure `--max-rows` to limit SELECT query results ## Single Query Execute a single SELECT, INSERT, UPDATE, or DELETE statement. <CodeGroup> ```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(); ``` </CodeGroup> ## 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'); ``` <Note> Each statement is executed sequentially. If one statement fails, subsequent statements may not be executed depending on the database error handling. </Note> ## Transactions Wrap multiple operations in a transaction to ensure atomicity. Use `BEGIN`/`COMMIT` for successful transactions or `ROLLBACK` to undo changes. <CodeGroup> ```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; ``` </CodeGroup> ## DDL Operations Create, alter, or drop database objects. <CodeGroup> ```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; ``` </CodeGroup> ## Read-Only Mode Restrict SQL execution to safe, read-only operations by configuring the `execute_sql` tool with `readonly = true`: <CodeGroup> ```toml TOML Configuration [[sources]] id = "production" dsn = "postgres://user:pass@localhost:5432/mydb" [[tools]] name = "execute_sql" source = "production" readonly = true ``` ```bash Command Line (Deprecated) # This flag is deprecated - use TOML configuration instead npx @bytebase/dbhub@latest --readonly --dsn "postgres://user:pass@localhost:5432/mydb" ``` </CodeGroup> In read-only mode, only [allowed SQL keywords](https://github.com/bytebase/dbhub/blob/main/src/utils/allowed-keywords.ts) are permitted, including: - `SELECT` queries - `SHOW` commands - `DESCRIBE` commands - `EXPLAIN` queries - Other read-only operations ## Row Limiting Limit the number of rows returned from SELECT queries to prevent accidentally retrieving too much data: <CodeGroup> ```toml TOML Configuration [[sources]] id = "production" dsn = "postgres://..." [[tools]] name = "execute_sql" source = "production" max_rows = 1000 ``` ```bash Command Line (Deprecated) # This flag is deprecated - use TOML configuration instead npx @bytebase/dbhub@latest --max-rows 1000 --dsn "..." ``` </CodeGroup> - 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 [multi-database TOML configuration](/config/multi-database) ## 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" ``` <Note> If no `[[tools]]` entries are defined for a source, both `execute_sql` and `search_objects` are enabled by default for backward compatibility. </Note>