Debugging Turso Bytecode vs SQLite Using EXPLAIN: Complete Technical Guide

Turso implements SQLite-compatible EXPLAIN and EXPLAIN QUERY PLAN by parsing the TK_EXPLAIN token into distinct query modes and generating human-readable bytecode output through the virtual database engine (VDBE) to enable direct comparison with SQLite's execution plans.

Turso is an edge database built in Rust that maintains binary compatibility with SQLite. When troubleshooting query execution differences or optimizing performance, developers use EXPLAIN statements to inspect the underlying bytecode instructions. This guide examines the complete implementation pipeline—from token parsing to row generation—using the actual source code from the tursodatabase/turso repository.

How Turso Parses EXPLAIN Statements

The parsing pipeline begins in the tokenizer where the EXPLAIN keyword is identified as a distinct token type. In parser/src/token.rs, the TokenType enum defines TK_EXPLAIN = 2 with its string representation mapped to "EXPLAIN" in the name() method.

When the parser encounters this token in parser/src/parser.rs, it examines the subsequent tokens to determine the execution mode. If the parser sees QUERY PLAN following EXPLAIN, it sets the mode to QueryMode::ExplainQueryPlan; otherwise, it uses QueryMode::Explain. This mode selection happens at parse time and propagates through the statement preparation phase.

Query Mode Handling and Column Metadata

The Statement struct in core/statement.rs uses the query mode to determine the result set schema. When column_count() is called, it matches against self.query_mode to return the appropriate column count: EXPLAIN_COLUMNS.len() for standard EXPLAIN output or EXPLAIN_QUERY_PLAN_COLUMNS.len() for query plan mode.

Similarly, the column_name() method returns Cow::Borrowed references to the static column name arrays defined in core/vdbe/explain.rs. This ensures that EXPLAIN queries return the exact same column headers as SQLite—addr, opcode, p1, p2, p3, p4, p5, and comment for standard EXPLAIN, and id, parent, notused, detail for query plan output.

Generating Bytecode Output in the VDBE

During execution, the virtual database engine checks the query mode and routes EXPLAIN statements through a specialized output path. The output_explain function in core/vdbe/mod.rs iterates over the PreparedProgram instructions, converting each bytecode operation into a display row.

Instruction Formatting and Comments

The actual conversion logic resides in core/vdbe/explain.rs. The insn_to_row_with_comment function takes a program reference and an instruction, then extracts the opcode, parameters (p1 through p5), and comment. It handles type conversion for the p4 operand, formatting Value::Integer as digits, Value::Text as strings, and representing blobs as "<blob>" to match SQLite's output format.

The function returns a tuple containing the opcode name, parameter values, and a descriptive comment, which output_explain packages into Value::Integer and Value::Text objects for the result row.

Snapshot Testing for Compatibility

To guarantee that Turso's EXPLAIN output remains identical to SQLite across releases, the test suite uses snapshot testing. The ExplainSnapshot struct in testing/sqltests/src/snapshot/mod.rs captures the textual rows from EXPLAIN queries and performs line-by-line comparisons against reference SQLite outputs.

This infrastructure ensures that any changes to the bytecode generator or VDBE formatting logic are immediately flagged if they diverge from the reference implementation.

Practical Debugging Examples

Command-Line EXPLAIN Output

You can compare Turso and SQLite bytecode directly using the CLI. Run an EXPLAIN query against Turso:

cargo run --quiet --bin tursodb -- -q "EXPLAIN SELECT id FROM users WHERE age > 30"

This produces tabular output showing the instruction address, opcode, parameters, and comments:

addr | opcode   | p1 | p2 | p3 | p4        | p5 | comment
---- | -------- | -- | -- | -- | --------- | -- | ---------------------------------
0    | Init     | 0  | 9  | 0  |           | 0  | Start at 9
1    | OpenRead | 0  | 1  | 0  | users     | 0  | table users
2    | Rewind   | 0  | 13 | 0  |           | 0  | cursor 0 is at start

Compare this against SQLite's output to verify identical bytecode generation.

Programmatic EXPLAIN Analysis in Rust

For automated testing, use the internal limbo_exec_rows function to capture EXPLAIN output programmatically:

use turso_core::limbo::exec::limbo_exec_rows;

// Assuming `conn` is a valid database connection
let rows = limbo_exec_rows(&conn, "EXPLAIN QUERY PLAN SELECT name FROM city WHERE pop > 100000")?;

// Rows contain: id, parent, notused, detail
for row in rows {
    println!(
        "id:{} parent:{} detail:{}",
        row[0].as_i64(),
        row[1].as_i64(),
        row[3].as_str()
    );
}

This approach allows you to integrate bytecode verification into your test suite, comparing Turso's output against a SQLite reference connection.

Summary

• Token Recognition: Turso identifies EXPLAIN via TK_EXPLAIN in parser/src/token.rs and branches into QueryMode::Explain or QueryMode::ExplainQueryPlan in parser/src/parser.rs.
• Schema Selection: The Statement implementation in core/statement.rs selects between EXPLAIN_COLUMNS and EXPLAIN_QUERY_PLAN_COLUMNS based on the query mode to match SQLite's output schema.
• Row Generation: The VDBE generates output via output_explain in core/vdbe/mod.rs, using insn_to_row_with_comment from core/vdbe/explain.rs to format instructions with parameters and comments.
• Compatibility Verification: Snapshot testing in testing/sqltests/src/snapshot/mod.rs ensures EXPLAIN output remains byte-for-byte compatible with SQLite across releases.

Frequently Asked Questions

What is the difference between EXPLAIN and EXPLAIN QUERY PLAN in Turso?

EXPLAIN displays the raw virtual machine bytecode instructions—showing opcodes like OpenRead, Column, and Next with their numeric parameters—while EXPLAIN QUERY PLAN shows the high-level query plan with id, parent, and detail columns describing the logical operations. Turso implements both modes via distinct QueryMode variants that select different column sets in core/statement.rs.

How does Turso ensure EXPLAIN output matches SQLite exactly?

Turso uses snapshot testing through the ExplainSnapshot struct in testing/sqltests/src/snapshot/mod.rs to capture EXPLAIN output and compare it line-by-line against SQLite reference output. Additionally, the insn_to_row_with_comment function in core/vdbe/explain.rs implements specific formatting rules—such as representing blobs as "<blob>" and nulls as "NULL"—to match SQLite's textual representation.

Can I use EXPLAIN output to compare Turso and SQLite performance?

While EXPLAIN shows the bytecode instructions and EXPLAIN QUERY PLAN shows the logical plan, the presence of specific opcodes (like Seek vs FullScan) can indicate performance differences. However, for precise timing comparisons, you should use the query timer features rather than the EXPLAIN output itself, as the bytecode instructions may be identical while execution speeds differ due to implementation details.

Where are the EXPLAIN column definitions stored in the Turso source?

The column name arrays are defined in core/vdbe/explain.rs as static slices: EXPLAIN_COLUMNS contains ["addr", "opcode", "p1", "p2", "p3", "p4", "p5", "comment"] for standard EXPLAIN output, and EXPLAIN_QUERY_PLAN_COLUMNS contains ["id", "parent", "notused", "detail"] for query plan output. These are referenced by core/statement.rs when determining the result set schema.

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