How DBX Handles Query Execution Across Different Databases

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:

• 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.

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 (approximately lines 1330–1380), while dialect-aware utilities live in 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:

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 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 (approximately lines 1460–1520):

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	Splits scripts into statements with optional transaction wrapping
execute_statements	crates/dbx-core/src/query.rs	Executes independent statements sequentially
execute_statements_in_transaction	crates/dbx-core/src/query.rs	Runs statements atomically within a transaction
Manual transaction API	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:

// 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:

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 accepts the same parameters:

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:

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, 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, 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. 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}