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

> Learn to write .sqltest format tests for Turso with this comprehensive guide. Master database-level integration testing for SQLite-compatible databases in parallel.

- Repository: [Turso Database/turso](https://github.com/tursodatabase/turso)
- Tags: how-to-guide
- Published: 2026-06-22

---

**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`](https://github.com/tursodatabase/turso/blob/main/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`](https://github.com/tursodatabase/turso/blob/main/test-runner/src/parser/lexer.rs), the **Logos** crate tokenizes the file content, while [`test-runner/src/parser/parser.rs`](https://github.com/tursodatabase/turso/blob/main/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:

```sql
@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:

```sql
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:

```sql
@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:

```sql
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:

```sql
@database :memory:

```

Define any reusable setup blocks:

```sql
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:

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

```

Handle nondeterministic output with pattern matching:

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

```

Expect errors when testing invalid syntax:

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

```

Use unordered comparison when row order does not matter:

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

```

Add conditional skips for database-specific limitations:

```sql
@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:

```bash
cargo build --release

```

Execute all `.sqltest` files under a directory:

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

```

Validate syntax without running:

```bash
./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`](https://github.com/tursodatabase/turso/blob/main/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`](https://github.com/tursodatabase/turso/blob/main/testing/sqltests/docs/dsl-spec.md), with CLI usage documented in [`testing/sqltests/docs/cli-usage.md`](https://github.com/tursodatabase/turso/blob/main/testing/sqltests/docs/cli-usage.md).