> ## Documentation Index
> Fetch the complete documentation index at: https://dbhub.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# 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**: 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` to limit SELECT query results

## Single Query

Execute a single SELECT, INSERT, UPDATE, or DELETE statement.

<CodeGroup>
  ```sql SELECT theme={null}
  SELECT * FROM users WHERE status = 'active' LIMIT 10;
  ```

  ```sql INSERT theme={null}
  INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');
  ```

  ```sql UPDATE theme={null}
  UPDATE users SET last_login = NOW() WHERE id = 123;
  ```

  ```sql DELETE theme={null}
  DELETE FROM sessions WHERE expires_at < NOW();
  ```
</CodeGroup>

## Multiple Statements

Execute multiple SQL statements in sequence by separating them with semicolons.

```sql theme={null}
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 theme={null}
  BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
  COMMIT;
  ```

  ```sql Rollback Transaction theme={null}
  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 theme={null}
  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 theme={null}
  ALTER TABLE products ADD COLUMN category VARCHAR(100);
  ```

  ```sql CREATE INDEX theme={null}
  CREATE INDEX idx_products_category ON products(category);
  ```

  ```sql DROP TABLE theme={null}
  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 theme={null}
  [[sources]]
  id = "production"
  dsn = "postgres://user:pass@localhost:5432/mydb"

  [[tools]]
  name = "execute_sql"
  source = "production"
  readonly = true
  ```

  ```bash Command Line (Deprecated) theme={null}
  # 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, DBHub enforces read-only access in two layers:

**1. Keyword classifier.** Each statement is checked against an [allow-list of read-only keywords](https://github.com/bytebase/dbhub/blob/main/src/utils/allowed-keywords.ts) after stripping comments and strings:

* `SELECT` queries
* `SHOW` commands
* `DESCRIBE` commands
* `EXPLAIN` queries
* Other read-only operations

**2. Engine-level enforcement.** The statement is then executed under the database's own read-only mechanism, so the database rejects writes even if the classifier is evaded:

* **PostgreSQL** — runs inside a `BEGIN READ ONLY` transaction
* **SQLite** — runs with `PRAGMA query_only = ON`
* **MySQL / MariaDB** — runs inside a `START TRANSACTION READ ONLY`

<Note>
  Engine-level enforcement was added in **0.22.6**. Earlier versions relied on the keyword classifier alone.
</Note>

<Warning>
  Read-only mode prevents data modification (DML/DDL), but it cannot constrain everything a **privileged database role** can do through *functions*. On PostgreSQL, a superuser or specially-privileged role can still read or write server files and run commands via `pg_read_file`, `lo_export`, `dblink`, and `COPY ... TO PROGRAM` — these are not blocked by a read-only transaction. On MySQL/MariaDB, DDL performs an implicit commit and is stopped only by the keyword classifier.

  For untrusted or agent-driven environments, **always connect DBHub with a least-privilege, read-only database user** scoped to the data it needs. Do not point a read-only tool at an admin/superuser DSN and rely on read-only mode alone.
</Warning>

## Row Limiting

Limit the number of rows returned from SELECT queries to prevent accidentally retrieving too much data:

<CodeGroup>
  ```toml TOML Configuration theme={null}
  [[sources]]
  id = "production"
  dsn = "postgres://..."

  [[tools]]
  name = "execute_sql"
  source = "production"
  max_rows = 1000
  ```

  ```bash Command Line (Deprecated) theme={null}
  # 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 [TOML configuration](/config/toml)

## 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 theme={null}
[[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 theme={null}
[[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>