Multi-statement execution in QoreDB: running a whole SQL script

A developer pastes a three-statement script into the editor, separated by semicolons: a CREATE TABLE, an INSERT, then a SELECT. They hit run. What should the client do? Send the whole block to the engine in one go, or split it and execute each statement one after the other? QoreDB opts for splitting, and that choice pulls in a whole chain: a dialect-aware SQL parser, sequential execution and a tabbed result view. Here is how it works, and where the deliberate limits are.

The split is decided at the command layer

It all starts in the execute_query command on the backend (src-tauri/src/commands/query.rs). For a SQL driver, it calls split_sql_statements on the query. If the split returns more than one statement, the batch is handed to the service layer; otherwise the query stays a None and follows the single-statement path. The semicolon alone triggers nothing: what decides is the number of statements the parser recognizes.

let sql_statements = if is_sql_driver {
    match sql_safety::split_sql_statements(driver.driver_id(), &query) {
        Ok(statements) if statements.len() > 1 => Some(statements),
        _ => None,
    }
} else if matches!(
    driver.driver_id().to_ascii_lowercase().as_str(),
    "elasticsearch" | "opensearch"
) {
    // Multi-request console: several `METHOD /path` blocks run
    // sequentially, each producing its own result tab.
    let blocks = qore_drivers::drivers::search_compat::split_requests(&query);
    (blocks.len() > 1).then_some(blocks)
} else {
    None
};

Two details are worth noting. First, Elasticsearch and OpenSearch reuse the exact same path: their console splits METHOD /path blocks via split_requests, each producing its own result. Second, a multi-statement script disables streaming: the should_stream flag requires sql_statements to be empty. You can't stream rows while chaining several queries.

Splitting cleanly: sqlparser and the engine's dialect

The split_sql_statements function lives in src-tauri/crates/qore-sql/src/safety.rs. It does not naively cut on the semicolon: it feeds the script to sqlparser with the right dialect, then re-serializes each recognized statement with statement.to_string(). A semicolon inside a string literal or a comment cuts nothing, because it is the syntax tree that gets scanned, not the raw text.

fn split_sql_statements_uncached(driver_id: &str, trimmed: &str) -> Result<Vec<String>, String> {
    if driver_id.eq_ignore_ascii_case("clickhouse") {
        // sqlparser cannot reliably round-trip CH syntax, so split
        // on top-level `;` outside string literals.
        return Ok(split_ch_statements(trimmed));
    }

    let dialect = dialect_for_driver(driver_id);
    let statements = Parser::parse_sql(&*dialect, trimmed).map_err(|err| err.to_string())?;

    let mut rendered = Vec::with_capacity(statements.len());
    for statement in statements {
        let statement_sql = statement.to_string();
        if !statement_sql.trim().is_empty() {
            rendered.push(statement_sql);
        }
    }

    Ok(rendered)
}

The dialect is picked by dialect_for_driver: PostgreSQL and CockroachDB get the PostgreSqlDialect, MySQL the MySqlDialect, DuckDB and MotherDuck the DuckDbDialect, SQL Server the MsSqlDialect, and everything else falls back to the GenericDialect. The result is memoized in a bounded LRU cache (128 entries), keyed on the (driver, trimmed SQL) pair: the same script re-run with F5 doesn't pay the parsing cost again.

ClickHouse: splitting by hand

ClickHouse is the exception. Its syntax is too specific for sqlparser to round-trip reliably, so QoreDB switches to split_ch_statements, a character-by-character scanner. It cuts on top-level ; while respecting '…' and "…" strings, -- … and /* … */ comments, and the \' escape. Lighter and safer than forcing a parser onto dialect-heavy SQL.

';' if !in_single && !in_double => {
    let s = buf.trim().to_string();
    if !s.is_empty() {
        out.push(s);
    }
    buf.clear();
    i += 1;
    continue;
}

Sequential execution, no implicit transaction

Once the batch is handed down, the service layer (qore-service/src/query.rs) loops over the statements and runs each one via driver.execute_in_namespace, in order. It opens no transaction around the loop: each statement is sent as-is, and a statement that succeeded stays applied even if a later one fails.

if let Some(statements) = sql_statements {
    let mut results = Vec::with_capacity(statements.len());
    for (idx, statement) in statements.iter().enumerate() {
        match driver
            .execute_in_namespace(session, namespace.clone(), statement, query_id)
            .await
        {
            Ok(result) => results.push(result),
            Err(e) => {
                return Err(EngineError::execution_error(format!(
                    "Statement {} failed after {} succeeded: {}",
                    idx + 1,
                    results.len(),
                    e
                )));
            }
        }
    }
    // ...
}

The error message is explicit: Statement 3 failed after 2 succeeded. It states exactly how many statements were applied before the failure, which matters when nothing is rolled back. The timeout, meanwhile, applies to the whole script: it is the entire loop that sits under the duration limit, not each statement in isolation.

One result tab per query

On the results side, the first statement becomes the primary result and the others are returned in extra_results. The frontend (QueryPanel.tsx) turns each result set into a tab. To label each tab with its statement, it re-splits locally on ; — but only if that naive split lands on the same number of sets as the backend. Otherwise it falls back to a generic label. An honest tradeoff: never a wrong label.

// A multi-statement query returns one result set per statement.
const extraResults = isFederated
  ? []
  : ((response as { extra_results?: QueryResult[] }).extra_results ?? []);

// Best-effort per-tab labels: only when the local split matches
// the number of result sets returned by the backend.
const statementParts = queryToRun
  .split(';')
  .map(part => part.trim())
  .filter(Boolean);
const labels =
  statementParts.length === extraResults.length + 1 ? statementParts : null;

Migrations get their own splitter

The query panel's splitter re-renders SQL, which is fine for ad-hoc queries. For migrations it would be unacceptable: a migration script must reach the database exactly as authored — comments, casing and formatting included. So QoreDB keeps a second splitter, migration_split.rs, which returns borrowed slices of the original text (&input[span]) instead of re-serializing. Where sqlparser would tolerate, this splitter rejects what it cannot cut without risk.

pub enum SplitErrorCode {
    UnterminatedString,
    UnterminatedComment,
    UnterminatedDollarQuote,
    UnterminatedBracket,
    /// MySQL `DELIMITER`.
    UnsupportedDelimiter,
    /// T-SQL `GO <n>` repeat count.
    UnsupportedGoCount,
    /// Procedural body whose inner `;` cannot be told
    /// apart from separators.
    UnsupportedProceduralBlock,
}

Two splitters for two requirements is the throughline: one favors reliable safety classification and a tabbed view for interactive exploration; the other favors fidelity to the text for migrations. In both cases, QoreDB refuses the shortcut of a global split(';'), which would break on the first semicolon hidden inside a string. Running a full script looks trivial from the editor; the guarantee that it behaves as expected rests precisely on the details you never see.