How to Write .sqltest Format Tests for Turso: A Complete Guide

Turso uses a custom domain-specific language called .sqltest to define database-level integration tests that are parsed by the test-runner crate and executed against SQLite-compatible databases in parallel.

The tursodatabase/turso repository includes a powerful testing framework for validating SQL behavior. Writing .sqltest format tests allows you to declaratively specify database state, execute queries, and assert results using a custom DSL that runs in isolated environments with full parallelism support.

Understanding the .sqltest DSL Architecture

The .sqltest format is processed by a dedicated test-runner that transforms plain text files into executable test suites. Understanding the architecture helps you write efficient tests and debug failures effectively.

CLI Entry and Backend Selection

The testing pipeline begins at src/main.rs where the clap parser handles run or check subcommands. The runner creates a CliBackend instance that knows how to launch a tursodb binary and issue SQL statements against it. This abstraction allows the same test files to run against different database implementations.

Parsing and Validation Pipeline

In test-runner/src/parser/lexer.rs, the Logos crate tokenizes the file content, while test-runner/src/parser/parser.rs builds a TestFile AST. The parser enforces strict validation rules: every file must declare at least one @database, cannot mix read-only and writable databases, and must reference existing setup names. These checks catch structural errors before any SQL executes.

Execution and Comparison Strategies

The test-runner/src/runner/ module executes each TestCase or SnapshotCase in its own isolated database instance using tokio and a job-pool for concurrency. Tests apply declared @setup blocks first, then compare results using one of four strategies: exact match, pattern matching (regex), unordered set comparison, or error expectation. Results are formatted via the OutputFormat trait supporting pretty or json output for CI integration.

Essential .sqltest Syntax and Concepts

The .sqltest DSL uses specific declarations to control database state, test execution, and result validation.

Database Declarations

Every test file must start with at least one database declaration. Most tests use an in-memory database:

@database :memory:

Multiple declarations cause the same tests to execute against each database sequentially.

Setup Blocks for Reusable State

Define reusable SQL fragments with setup blocks. These are only allowed for writable databases:

setup users {
    CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
    INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob');
}

Reference setups in tests using the @setup decorator.

Test Cases and Assertions

The core unit executes SQL and validates output:

@setup users
test select-users {
    SELECT id, name FROM users ORDER BY id;
}
expect {
    1|Alice
    2|Bob
}

Snapshot Cases for Query Plans

Capture EXPLAIN output for regression testing. These run only on the Rust backend:

snapshot plan {
    SELECT * FROM users WHERE id = 1;
}

Decorators for Conditional Execution

Control test execution with decorators:

• @skip – Always skip this test
• @skip-if mvcc – Skip only in MVCC mode
• @backend rust – Run only on the Rust implementation
• @requires trigger – Skip if the database lacks trigger support

Expect Modifiers for Flexible Comparison

When output varies, use alternative comparison strategies:

• expect error { ... } – Validate error messages
• expect pattern { ... } – Use regex matching
• expect unordered { ... } – Compare as sets ignoring row order

Writing Your First .sqltest File

Follow these steps to create valid integration tests for Turso.

Start with the database declaration:

@database :memory:

Define any reusable setup blocks:

setup users {
    CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
    INSERT INTO users VALUES (1, 'Alice'), (2, 'Bob');
}

Write the test case with setup decoration:

@setup users
test select-users {
    SELECT id, name FROM users ORDER BY id;
}
expect {
    1|Alice
    2|Bob
}

Handle nondeterministic output with pattern matching:

test random-value {
    SELECT random();
}
expect pattern {
    ^-?\d+$
}

Expect errors when testing invalid syntax:

test invalid-syntax {
    SELECT * FROM ;
}
expect error {
    syntax error
}

Use unordered comparison when row order does not matter:

@setup users
test unordered-list {
    SELECT name FROM users;
}
expect unordered {
    Bob
    Alice
}

Add conditional skips for database-specific limitations:

@skip-if mvcc "total_changes not supported in MVCC"
test total-changes {
    CREATE TABLE t (id INTEGER PRIMARY KEY);
    INSERT INTO t VALUES (1), (2), (3);
    SELECT total_changes();
}
expect {
    3
}

Save the file with a .sqltest extension (e.g., my-feature.sqltest) in testing/sqltests/ or any subdirectory.

Running and Validating Tests

Build the test-runner binary before executing tests:

cargo build --release

Execute all .sqltest files under a directory:

./target/release/test-runner run testing/sqltests/ \
   --binary ./target/release/tursodb \
   --output json > results.json

Validate syntax without running:

./target/release/test-runner check testing/sqltests/my-feature.sqltest

Control execution with additional options:

• Use --filter to run a subset of tests matching a pattern
• Set -j to control parallelism (defaults to available CPUs)
• Specify --output pretty for human-readable terminal output

Summary

• .sqltest is a custom DSL parsed by the test-runner crate using Logos for tokenization and a custom parser for AST generation.
• Every file must declare at least one @database, typically :memory: for isolated testing.
• Setup blocks define reusable SQL state that tests can attach via the @setup decorator.
• Test cases execute SQL and compare results using exact, pattern, unordered, or error matching strategies.
• Snapshot cases capture query plans for regression testing against the Rust backend.
• Decorators like @skip, @skip-if, and @requires control conditional execution based on database capabilities.
• The runner executes tests in parallel using tokio with isolated database instances per test case.
• Reference the full specification in testing/sqltests/docs/dsl-spec.md and examples in testing/sqltests/examples/basic.sqltest.

Frequently Asked Questions

What is the difference between test and snapshot cases in .sqltest files?

Test cases validate SQL execution results against expected output, while snapshot cases specifically capture EXPLAIN query plans for regression testing. Snapshot tests run only on the Rust backend and compare execution plans rather than data rows, making them ideal for verifying that optimizer changes do not negatively affect query performance.

How does the test-runner handle database isolation between tests?

The test-runner creates a separate isolated database instance for each test case, applying any declared @setup blocks before execution. This isolation ensures that tests do not interfere with each other, allowing the framework to run tests concurrently using tokio and a job-pool without transaction conflicts or side effects.

Can I use .sqltest files with databases other than Turso?

While the .sqltest format is designed for Turso's testing framework, the CLI backend abstraction in test-runner/src/runner/ allows connecting to any SQLite-compatible binary. However, specific decorators like @requires trigger or @skip-if mvcc depend on Turso-specific capabilities, so portable tests should minimize vendor-specific features.

Where can I find examples of production .sqltest files?

The repository includes comprehensive examples in testing/sqltests/examples/basic.sqltest demonstrating all DSL features, and real-world tests in testing/sqltests/tests/trigger.sqltest showing advanced usage with capability requirements. The complete grammar specification resides in testing/sqltests/docs/dsl-spec.md, with CLI usage documented in testing/sqltests/docs/cli-usage.md.

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