# How DBX Handles Query Execution Across Different Databases

> DBX centralizes query execution via dbx_core::query, unifying database type detection, connection pooling, and driver delegation for normalized results.

- Repository: [skyler/dbx](https://github.com/t8y2/dbx)
- Tags: internals
- Published: 2026-07-02

---

**DBX centralizes all query execution in the `dbx_core::query` module, using a unified entry point that detects the target database type, manages connection pools, and delegates to driver-specific implementations while normalizing results into a common format.**

The open-source DBX project (`t8y2/dbx`) provides a cross-platform database client that abstracts query execution across disparate database engines. Whether you are querying PostgreSQL, MySQL, SQLite, or MongoDB, DBX handles query execution across different databases through a single, consistent Rust core that manages dialect detection, connection pooling, and driver delegation.

## Centralized Query Architecture

Every query request—whether originating from the desktop UI via Tauri, an HTTP API call, or the JDBC plugin—converges on two core functions in [`crates/dbx-core/src/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query.rs):

- **`execute_sql_statement`** – A thin wrapper that supplies default execution options.
- **`execute_sql_statement_with_options`** – The full implementation that orchestrates cross-database query processing.

Both functions serve as the single source of truth for DBX query execution across different databases, ensuring consistent behavior regardless of the entry point.

## Database Type Detection and Dialect Handling

When a query arrives, DBX first determines which driver should handle the request by resolving the connection configuration.

```rust
let db_type = connection_database_type(state, connection_id).await;

```

The `connection_database_type` function looks up the stored `ConnectionConfig` for the provided `connection_id` and extracts the `DatabaseType` enum variant (e.g., `Postgres`, `MySQL`, `SQLite`, `SQLServer`, `DuckDB`, `MongoDB`).

This detection influences three critical steps:

1. **SQL parsing**: `crate::sql::has_executable_sql_for_database(sql, db_type)` validates whether the statement contains executable SQL for the specific dialect.
2. **Statement splitting**: For multi-statement scripts, `split_sql_statements_for_database` selects the appropriate tokenizer (MySQL, PostgreSQL, etc.).
3. **Special-case handling**: Certain databases trigger dedicated paths, such as `execute_postgres_drop_database` for PostgreSQL-specific operations.

The detection logic resides in [`crates/dbx-core/src/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query.rs) (approximately lines 1330–1380), while dialect-aware utilities live in [`crates/dbx-core/src/sql.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/sql.rs).

## Connection Pool Management

DBX maintains a per-connection-id pool map in `state.connections`. When executing a query, DBX generates a pool key that uniquely identifies the physical connection resources:

```rust
let pool_key = if database.is_empty() {
    state.get_or_create_pool_for_session(connection_id, None, options.client_session_id.as_deref()).await?
} else {
    state.get_or_create_pool_for_session(connection_id, Some(database), options.client_session_id.as_deref()).await?
};

```

The pool key encodes:
- The connection identifier
- The target database name (or `None` for server-level queries)
- An optional client session ID that isolates stateful sessions (e.g., MySQL user variables)

The `get_or_create_pool_for_session` implementation in [`crates/dbx-core/src/connection.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/connection.rs) selects the appropriate driver crate (`db::postgres`, `db::mysql`, etc.) based on the `DatabaseType` and either opens a new connection or reuses an existing one from the pool.

## Driver-Specific Execution Flow

Once a pool key is established, DBX delegates to the driver-specific execution helper `do_execute`, located in [`crates/dbx-core/src/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query.rs) (approximately lines 1460–1520):

```rust
do_execute(state, &pool_key, mysql_dialect, Some(database), sql, schema,
           cancel_token.clone(), options.clone()).await

```

The `do_execute` function performs four critical operations:

1. **Read-only protection**: `check_read_only_for_connection` aborts any DML operations on connections marked as read-only.
2. **Cancellation handling**: A `CancellationToken` is polled before and after each driver call to enable query cancellation.
3. **Driver invocation**: Executes database-specific logic such as `db::postgres::execute_query_with_max_rows`, `db::mysql::execute_query`, or the MongoDB shell executor.
4. **Result normalization**: Converts driver-specific outputs into the common `db::QueryResult` structure containing rows, column metadata, and execution time.

If a driver returns a pool error (e.g., lost TCP connection), DBX implements `PoolErrorAction` logic (around lines 1360–1380) to decide whether to reconnect and retry or discard the stale pool.

## Transaction and Batch Execution APIs

Beyond single-statement execution, DBX provides higher-level APIs for complex workloads, all ultimately routing through `do_execute`:

| API | Implementation File | Purpose |
|-----|---------------------|---------|
| `execute_multi_core_with_options` | [`crates/dbx-core/src/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query.rs) | Splits scripts into statements with optional transaction wrapping |
| `execute_statements` | [`crates/dbx-core/src/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query.rs) | Executes independent statements sequentially |
| `execute_statements_in_transaction` | [`crates/dbx-core/src/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query.rs) | Runs statements atomically within a transaction |
| Manual transaction API | [`crates/dbx-core/src/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query.rs) | Client-controlled sessions via `begin_manual_transaction`, `execute_in_manual_transaction`, `commit_manual_transaction`, and `rollback_manual_transaction` |

The manual transaction API uses UUID-based session identifiers to maintain state across multiple requests:

```rust
// Begin transaction
let txn_id = dbx_core::query::begin_manual_transaction(&state, "pg-conn-1", "public", None).await?;

// Execute within transaction context
let rows = dbx_core::query::execute_in_manual_transaction(
    &state,
    &txn_id,
    "UPDATE accounts SET balance = balance - 100 WHERE id = 1",
    "public",
    None,
    Some(100),
).await?;

// Commit
let commit_result = dbx_core::query::commit_manual_transaction(&state, &txn_id).await?;

```

## Cross-Platform Integration Examples

### Desktop Application (Tauri Command)

When using the DBX desktop application, the Vue/TypeScript frontend invokes the Tauri command defined in [`src-tauri/src/commands/query.rs`](https://github.com/t8y2/dbx/blob/main/src-tauri/src/commands/query.rs):

```javascript
await invoke('execute_query', {
  connection_id: 'pg-conn-1',
  database: 'sales',
  sql: 'SELECT * FROM orders LIMIT 10',
  schema: null,
  execution_id: null,
  max_rows: 100,
  fetch_size: null,
  page_size: null,
  result_session_id: null,
  client_session_id: null,
  timeout_secs: 30,
});

```

This command forwards to `execute_sql_statement_with_options` in the core module.

### HTTP API Endpoint

For programmatic access, the HTTP route in [`crates/dbx-web/src/routes/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-web/src/routes/query.rs) accepts the same parameters:

```bash
curl -X POST https://dbx.mycompany.com/api/query/execute \
  -H 'Content-Type: application/json' \
  -d '{
        "connection_id":"mysql-01",
        "database":"inventory",
        "sql":"SELECT name, qty FROM products WHERE qty < 5"
      }'

```

### Multi-Statement Transaction (Rust)

To execute a script with automatic transaction wrapping:

```rust
let result = dbx_core::query::execute_multi_core_with_options(
    &state,
    "pg-conn-1",
    "analytics",
    "INSERT INTO events VALUES (1); INSERT INTO events VALUES (2);",
    None,
    None,
    dbx_core::query::QueryExecutionOptions {
        use_transaction: Some(true),
        ..Default::default()
    },
).await?;

```

This ensures both INSERT statements execute atomically using the same driver-selection and pooling logic as single queries.

## Summary

DBX achieves database-agnostic query execution through a layered architecture:

- **Unified entry points**: All requests flow through `execute_sql_statement_with_options` in [`crates/dbx-core/src/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query.rs), ensuring consistent behavior across Tauri, HTTP, and JDBC interfaces.
- **Dynamic dialect detection**: The `DatabaseType` enum and `connection_database_type` function enable runtime selection of SQL parsers and tokenizers.
- **Session-aware pooling**: `get_or_create_pool_for_session` manages physical connections with support for stateful sessions via client session IDs.
- **Driver abstraction**: The `do_execute` function normalizes results from PostgreSQL, MySQL, SQLite, and other drivers into a common `db::QueryResult` format.
- **Transaction support**: Comprehensive APIs for both automatic and manual transaction management reuse the same core execution path.

## Frequently Asked Questions

### How does DBX determine which database driver to use for a query?

DBX determines the driver by calling `connection_database_type` in [`crates/dbx-core/src/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query.rs), which looks up the connection configuration and returns a `DatabaseType` enum variant (such as `Postgres`, `MySQL`, or `MongoDB`). This type then selects the appropriate driver crate and SQL dialect tokenizer for the execution.

### What happens if a database connection drops during query execution?

When a driver returns a pool error (such as a lost TCP connection), DBX evaluates the `PoolErrorAction` around lines 1360–1380 in [`crates/dbx-core/src/query.rs`](https://github.com/t8y2/dbx/blob/main/crates/dbx-core/src/query.rs). Depending on the error type, DBX either automatically reconnects and retries the query or discards the invalid pool and creates a fresh connection for subsequent requests.

### Can DBX execute multiple SQL statements in a single transaction across different databases?

Yes, DBX supports multi-statement transaction execution through `execute_multi_core_with_options` by setting `use_transaction: Some(true)`, or via the manual transaction API (`begin_manual_transaction`, `commit_manual_transaction`). However, transactions are isolated to a single database connection; DBX does not support distributed transactions across multiple database instances simultaneously.

### How does DBX prevent write operations on read-only connections?

Before executing any statement, the `do_execute` function calls `check_read_only_for_connection` to verify the connection's permissions. If the connection is marked as read-only and the SQL contains DML operations (INSERT, UPDATE, DELETE), DBX aborts the execution immediately and returns an error, preventing accidental data modifications.