SQLite Platform Adapter

The SQLite adapter provides lightweight testing and development capabilities with embedded database functionality.

Overview

SQLite is a self-contained, serverless, zero-configuration SQL database engine. It is part of the Python standard library, so the adapter needs no extra package. It offers:

Embedded database - No server process required
In-memory mode - Fast testing with no disk I/O
File-based mode - Persistent storage in single file
Zero configuration - No setup or administration
ACID compliant - Full transactional support
Cross-platform - Runs on all platforms

Common use cases:

Development and testing workflows
Small-scale benchmarks (< 10GB data)
CI/CD pipeline testing
Proof-of-concept work
Educational and learning purposes

Quick Start

In-Memory Mode

from benchbox.tpch import TPCH
from benchbox.platforms.sqlite import SQLiteAdapter

adapter = SQLiteAdapter(database_path=":memory:")

benchmark = TPCH(scale_factor=0.1)
benchmark.generate_data()
results = benchmark.run_with_platform(adapter)

The in-memory database is the fastest option and has no persistence.

run_with_platform does not generate data. Without generate_data() the tables are created empty and the queries succeed with zero rows.

File-Based Mode

from benchbox.tpch import TPCH
from benchbox.platforms.sqlite import SQLiteAdapter

adapter = SQLiteAdapter(
    database_path="./tpch.db",
    timeout=30.0
)

benchmark = TPCH(scale_factor=1.0)
benchmark.generate_data()
results = benchmark.run_with_platform(adapter)

The file-based database is persistent.

The directory that holds the database file must already exist; SQLite does not create directories (sqlite3.OperationalError: unable to open database file).

API Reference

SQLiteAdapter Class

benchbox.platforms.sqlite.SQLiteAdapter runs BenchBox benchmarks on a SQLite database, in memory or in a file.

Import: from benchbox.platforms.sqlite import SQLiteAdapter · Extras: none

Parameters

All parameters are keyword arguments (the signature is (**config)).

Name	Type	Default	Meaning
`database_path`	`str`	`":memory:"`	Database file path, or `":memory:"`.
`timeout`	`float`	`30.0`	Seconds a connection waits for a lock before `sqlite3.OperationalError: database is locked`. Passed to `sqlite3.connect`.
`check_same_thread`	`bool`	`False`	Passed to `sqlite3.connect`. `False` lets any thread use the connection; `True` makes another thread’s use raise `sqlite3.ProgrammingError`.
`force_recreate`	`bool`	`False`	Delete an existing database file when a connection is created.

The adapter also accepts the keys every BenchBox adapter takes: see Constructor Parameters. Keys it does not recognise are accepted and ignored: SQLiteAdapter(read_only=True) is valid and sets nothing.

Returns

A SQLiteAdapter. Construction does not validate or use the values and opens no connection; call create_connection().

Raises

Nothing the constructor raises itself. Bad values fail later, in create_connection(): a timeout that is not a number raises TypeError (must be real number, not str), a database_path that is not a path raises TypeError, and a path in a directory that does not exist raises sqlite3.OperationalError (unable to open database file).

Example

from benchbox.platforms.sqlite import SQLiteAdapter

adapter = SQLiteAdapter(timeout=5.0)
connection = adapter.create_connection()
connection.execute("CREATE TABLE customer (c_custkey INTEGER)")
connection.execute("INSERT INTO customer VALUES (1), (2)")
result = adapter.execute_query(connection, "SELECT COUNT(*) FROM customer", "count_customers")
print(result["status"], result["rows_returned"], result["first_row"])
print(adapter.get_query_plan(connection, "SELECT * FROM customer WHERE c_custkey = 1"))
print(adapter.get_platform_info(connection)["configuration"])

try:
    adapter.run_power_test(None)
except NotImplementedError as exc:
    print(exc)
print(SQLiteAdapter(read_only=True).timeout)

Output on 0.4.1:

SUCCESS 1 (2,)
QUERY PLAN
`--SCAN customer
{'database_path': ':memory:', 'timeout': 5.0, 'check_same_thread': False}
Power test not implemented for SQLite adapter
30.0

Compatibility

from benchbox.platforms import SQLiteAdapter returns the same class, although the name is not listed in benchbox.platforms.__all__.
SQLite needs no extra package: the adapter uses the sqlite3 module of the Python standard library.
The power, throughput and maintenance tests are not implemented (see the methods below).

Constructor Parameters

Every platform adapter accepts these keyword arguments. All are optional.

Name	Type	Default	Meaning
`force_recreate`	`bool`	`False`	Recreate an existing database instead of reusing it.
`show_query_plans`	`bool`	`False`	Print each query’s plan after it runs.
`capture_plans`	`bool`	`False`	Add `query_plan` and `plan_fingerprint` to each successful query result.
`analyze_plans`	`bool`	`False`	Capture plans with actual timings; each captured `SELECT` is executed once more.
`tuning_enabled`	`bool`	`False`	Apply the tuning configuration given in `tuning_config`.
`tuning_config`	`UnifiedTuningConfiguration` or `None`	`None`	The tuning configuration (`unified_tuning_configuration` is read as a fallback).
`enable_validation`	`bool`	`False`	The benchmark runner passes `validate_row_count=True` to `execute_query`.
`dry_run`	`bool`	`False`	Makes `is_dry_run` true.
`verbose_enabled`, `very_verbose`, `quiet`	`bool`	`False`	Logging verbosity. `verbose_level` (0, 1 or 2) sets the first two.

The values are stored as attributes of the same name (SQLiteAdapter().force_recreate is False).

Methods and attributes

Construction and configuration

__init__(**config): Creates the adapter from keyword arguments. See Parameters above. It stores the settings and opens no connection.

from_config(config: dict[str, Any]) (class method): Builds an adapter from a unified configuration dictionary and returns it. The database path is the first of database_path, connection_string, or a generated path <output_dir>/databases/<benchmark>_sf<token>/<benchmark>_sf<token>_notuning_noconstraints.sqlite (for example runs/databases/tpch_sf001/tpch_sf001_notuning_noconstraints.sqlite; the directory is created, and benchmark_runs/databases/... is used when output_dir is absent). The generated path needs benchmark and a scale_factor that is not None. With none of the three it raises ConfigurationError (benchbox.core.exceptions) with the two-line message SQLite requires database path configuration. followed by Either pass --platform-option database_path=<path> or provide --benchmark and --scale. timeout (default 30.0) and check_same_thread (default False) are copied. force_recreate is true if any of force_recreate, options['force_recreate'] or force is true. The tuning and verbosity keys (tuning_config, tuning_enabled, unified_tuning_configuration, tuning_source, tuning_source_file, verbose_enabled, very_verbose) pass through; other keys are dropped.

add_cli_arguments(parser) -> None (static method): Adds three options to an argparse parser and returns None: --sqlite-database (stored as database_path, default None), --sqlite-timeout (float, stored as timeout, default 30.0) and --sqlite-check-same-thread (flag, stored as check_same_thread, default False). Errors while adding them are swallowed.

get_database_path(**connection_config) -> str | None: Returns the database path. A database_path keyword that is not None wins; otherwise the instance’s database_path is returned (':memory:' by default).

platform_name (property): Always the string 'SQLite'.

get_target_dialect() -> str: Returns 'sqlite', the SQL dialect BenchBox translates queries into.

get_platform_info(connection: Any = None) -> dict[str, Any]: Returns a dict: platform_type ('sqlite'), platform_name, connection_mode ('memory' for :memory:, else 'file'), configuration (database_path, timeout, check_same_thread), client_library_version ('builtin') and platform_version (the SQLite library version of the Python build). The connection argument is accepted and not used.

Connection and schema

create_connection(**connection_config) -> Any: Opens a sqlite3 connection and returns it. Existing-database handling comes first: force_recreate=True deletes the file, and otherwise the file is validated and kept if it passes (database_was_reused becomes True). The connection uses the adapter’s timeout and check_same_thread and runs PRAGMA foreign_keys = ON, journal_mode = WAL, synchronous = NORMAL, cache_size = 10000 and temp_store = MEMORY. An in-memory database reports journal_mode memory, since WAL needs a file. It also registers SQL functions that SQLite lacks: REGEXP_REPLACE(value, pattern, replacement), the aggregates STDDEV and STDDEV_SAMP (sample standard deviation) and the two-argument aggregate PERCENTILE_CONT(fraction, value). Calling it registers a sqlite3 adapter that stores decimal.Decimal values as float; this is process-wide. A bad timeout raises TypeError and an unusable path raises sqlite3.OperationalError (unable to open database file).

configure_for_benchmark(connection: Any, benchmark_type: str) -> None: Sets session pragmas for a benchmark type and returns None. The comparison is exact and lower-case: 'olap' runs PRAGMA query_only = false and PRAGMA read_uncommitted = true; 'oltp' runs PRAGMA synchronous = FULL. Any other value, including 'OLAP' and 'tpch', changes nothing.

create_schema(benchmark, connection: Any) -> float: Creates the benchmark’s tables and returns the elapsed time in seconds (float). The benchmark’s standard SQL DDL is translated to SQLite, run as one script and committed.

Loading data into tables

load_data(benchmark, connection: Any, data_dir: Path) -> tuple[dict[str, int], float, dict[str, Any] | None]: Loads the benchmark’s generated files into the tables and returns (table_row_counts, seconds, None); table_row_counts maps lower-case table names to row counts (TPC-H at scale factor 0.01 gives customer 1500, lineitem 60175). For TPC-H and Join Order it then builds helper indexes named idx_bb_tpch_* and idx_bb_job_*, and their time is included in seconds. Create the tables first with create_schema.

Query execution and plans

execute_query(connection: Any, query: str, query_id: str, benchmark_type: str | None = None, scale_factor: float | None = None, validate_row_count: bool = True, stream_id: int | None = None) -> dict[str, Any]: Runs one query and returns a result dict; it does not raise for SQL errors. On success status is 'SUCCESS' and the dict holds query_id, execution_time_seconds, rows_returned, first_row and results (every row, as a list of tuples). On an error status is 'FAILED' with error (message), error_type (for example 'OperationalError'), execution_time_seconds and rows_returned of 0. When benchmark_type is given and validate_row_count is true, a row-count comparison is added to the result.

get_query_plan(connection: Any, query: str) -> str | None: Returns the plan as text built from EXPLAIN QUERY PLAN: the line QUERY PLAN followed by an indented tree, for example QUERY PLAN then `--SCAN nation. Returns None if the statement fails (for example for a missing table) or returns no rows.

get_query_plan_parser(): Returns a SQLiteQueryPlanParser (from benchbox.core.query_plans.parsers.sqlite) for the text that get_query_plan returns.

plan_capture_phase_eligible (class attribute): True. Query plans for SQLite are captured in a separate pass after the timed run, not inline with the timed queries.

TPC test drivers

run_power_test(benchmark, **kwargs) -> dict[str, Any]: Always raises NotImplementedError('Power test not implemented for SQLite adapter').

run_throughput_test(benchmark, **kwargs) -> dict[str, Any]: Always raises NotImplementedError('Throughput test not implemented for SQLite adapter').

run_maintenance_test(benchmark, **kwargs) -> dict[str, Any]: Always raises NotImplementedError('Maintenance test not implemented for SQLite adapter').

Tuning

generate_tuning_clause(table_tuning: TableTuning) -> str: Returns '' for every input. SQLite has no CREATE TABLE tuning syntax.

apply_table_tunings(table_tuning: TableTuning, connection: Any) -> None: Does nothing and returns None: it issues no SQL for any TableTuning.

apply_unified_tuning(unified_config: UnifiedTuningConfiguration, connection: Any) -> None: Does nothing and returns None: it issues no SQL for any UnifiedTuningConfiguration.

apply_platform_optimizations(platform_config: PlatformOptimizationConfiguration, connection: Any) -> None: Does nothing and returns None.

apply_constraint_configuration(primary_key_config: PrimaryKeyConfiguration, foreign_key_config: ForeignKeyConfiguration, connection: Any) -> None: Switches foreign-key enforcement and returns None: it runs PRAGMA foreign_keys = ON when foreign_key_config.enabled is true and PRAGMA foreign_keys = OFF when it is false. primary_key_config is not used, and the call does nothing when foreign_key_config is None.

Capabilities

driver_isolation_capability (class attribute): DriverIsolationCapability.NOT_APPLICABLE (from benchbox.platforms.base): SQLite is part of the Python standard library, so there is no driver version to isolate.

stream_connection_capability (read-only attribute): StreamConnectionCapability.SHARED_CURSOR (from benchbox.platforms.base), declared in the platform manifest rather than on the class: concurrent throughput streams use cursors of the one connection instead of opening more connections.

Configuration Examples

Development Testing

This example uses an in-memory database and a tiny scale factor for fast development testing and quick benchmark validation.

adapter = SQLiteAdapter(database_path=":memory:")

from benchbox.tpch import TPCH
benchmark = TPCH(scale_factor=0.01)
benchmark.generate_data()
results = benchmark.run_with_platform(adapter)

print(f"Validation complete in {results.total_execution_time:.2f}s")

Persistent Storage

This example stores results in a file for later analysis. The longer timeout allows for file I/O.

from pathlib import Path

db_path = Path("./data/benchmarks.db")
db_path.parent.mkdir(parents=True, exist_ok=True)

adapter = SQLiteAdapter(
    database_path=str(db_path),
    timeout=60.0
)

CI/CD Pipeline

The CI branch is a fast in-memory test. Otherwise the example uses local development with a larger dataset. The assertions check benchmark quality, and the 60 second limit is the CI time limit.

import os
from benchbox.platforms.sqlite import SQLiteAdapter
from benchbox.tpch import TPCH

if os.getenv("CI"):
    adapter = SQLiteAdapter(database_path=":memory:")
    benchmark = TPCH(scale_factor=0.01)
else:
    adapter = SQLiteAdapter(database_path="./dev_benchmark.db")
    benchmark = TPCH(scale_factor=0.1)

benchmark.generate_data()
results = benchmark.run_with_platform(adapter)

assert results.successful_queries == results.total_queries
assert results.total_execution_time < 60.0

Multi-threaded Access

Enable this for multi-threaded applications. check_same_thread=False allows access from multiple threads, and the higher timeout allows for concurrent access.

adapter = SQLiteAdapter(
    database_path="./benchmark.db",
    check_same_thread=False,
    timeout=120.0
)

Connection Management

Basic Connection

from benchbox.platforms.sqlite import SQLiteAdapter

adapter = SQLiteAdapter(database_path="./benchmark.db")

conn = adapter.create_connection()

The connection is configured automatically with these optimizations: WAL journal mode, NORMAL synchronous mode, foreign keys enabled, a cache size of 10000 pages, and temporary storage in memory.

Query Execution

The example first creates a small table to query. See Data Loading for loading benchmark tables.

from benchbox.platforms.sqlite import SQLiteAdapter

adapter = SQLiteAdapter(database_path=":memory:")
conn = adapter.create_connection()

conn.execute("CREATE TABLE customer (c_custkey INTEGER)")
conn.execute("INSERT INTO customer VALUES (1), (2)")

result = adapter.execute_query(
    conn,
    "SELECT COUNT(*) FROM customer",
    "count_customers"
)

print(f"Status: {result['status']}")
print(f"Execution time: {result['execution_time_seconds']:.3f}s")
print(f"Rows: {result['rows_returned']}")

On 0.4.1 this prints Status: SUCCESS, a time of a few milliseconds, and Rows: 1. If the query fails, status is FAILED and result['error'] holds the message; execute_query does not raise.

Data Loading

From Generated Data

The example generates the data, connects to SQLite, creates the schema, and then loads the data.

from benchbox.platforms.sqlite import SQLiteAdapter
from benchbox.tpch import TPCH
from pathlib import Path

data_dir = Path("./tpch_data")
benchmark = TPCH(scale_factor=0.1, output_dir=data_dir)
benchmark.generate_data()

adapter = SQLiteAdapter(database_path="./tpch.db")
conn = adapter.create_connection()

adapter.create_schema(benchmark, conn)

table_stats, load_time, _ = adapter.load_data(benchmark, conn, data_dir)

print(f"Loaded {sum(table_stats.values()):,} rows in {load_time:.2f}s")
for table, count in table_stats.items():
    print(f"  {table}: {count:,} rows")

Performance Optimization

Connection Pragmas

SQLite adapter automatically applies these optimizations:

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA cache_size = 10000;
PRAGMA temp_store = MEMORY;
PRAGMA foreign_keys = ON;

journal_mode = WAL: Write-Ahead Logging for better concurrency.
synchronous = NORMAL: normal durability, which is faster than FULL.
cache_size = 10000: a large cache (10K pages) that reduces disk I/O.
temp_store = MEMORY: temporary tables are kept in memory.
foreign_keys = ON: foreign key constraints are enforced.

Query Optimization Tips

Use appropriate indexes:

CREATE INDEX idx_orders_custkey ON orders(o_custkey);
CREATE INDEX idx_lineitem_orderkey ON lineitem(l_orderkey);

Analyze statistics after data load:
```
conn.execute("ANALYZE")
conn.commit()
```
Vacuum to reclaim space and defragment:
```
conn.execute("VACUUM")
conn.commit()
```

Scale Factor Guidelines

Recommended scale factors for SQLite:

Development/Testing: SF = 0.01 to 0.1 (~10MB to 100MB)
CI/CD Pipelines: SF = 0.01 (~10MB, completes in seconds)
Local benchmarking: SF = 0.1 to 1.0 (~100MB to 1GB)
Maximum practical: SF = 10 (~10GB, slow queries)

Best Practices

Use Case Selection

When to use SQLite adapter:

Development and testing
CI/CD pipeline validation
Learning and education
Small datasets (< 1GB)
Single-user applications

When NOT to use SQLite adapter:

Production benchmarking
Large-scale data (> 10GB)
Concurrent multi-user workloads
Performance-critical comparisons

Testing Strategy

The test uses a small scale factor for speed. It validates that all queries succeeded, and then validates reasonable performance: an average of under 1 second per query at SF=0.01.

from benchbox.platforms.sqlite import SQLiteAdapter
from benchbox.tpch import TPCH

def test_benchmark_queries():
    adapter = SQLiteAdapter(database_path=":memory:")
    benchmark = TPCH(scale_factor=0.01)
    benchmark.generate_data()

    results = benchmark.run_with_platform(adapter)

    assert results.successful_queries == results.total_queries
    assert results.average_query_time < 1.0

    return results

test_benchmark_queries()

Development Workflow

Start with an in-memory database for quick iterations.
Validate query logic with test queries.
Move to a file-based database for persistent testing.
Graduate to a production platform (DuckDB, ClickHouse, and so on).

The two adapters below cover steps 1 and 3.

from benchbox.platforms.sqlite import SQLiteAdapter

adapter = SQLiteAdapter(database_path=":memory:")

adapter = SQLiteAdapter(database_path="./dev_test.db")

Resource Management

Run the benchmark operations inside the try block. The finally block closes the connection and, optionally, deletes the temporary database.

from benchbox.platforms.sqlite import SQLiteAdapter

adapter = SQLiteAdapter(database_path="./benchmark.db")

try:
    conn = adapter.create_connection()
finally:
    conn.close()

    if adapter.database_path != ":memory:":
        import os
        if os.path.exists(adapter.database_path):
            os.remove(adapter.database_path)

Common Issues

Database Locked Error

Problem: “database is locked” error during concurrent access

Solutions: increase the timeout (the example waits up to 2 minutes), use WAL mode, and avoid concurrent writes. WAL mode is already enabled by default and allows concurrent reads. SQLite only supports one writer at a time.

adapter = SQLiteAdapter(
    database_path="./benchmark.db",
    timeout=120.0
)

Memory Error

Problem: Out of memory with large datasets

Solutions: use a smaller scale factor, such as 0.1 and not 1.0 or higher, and use a file-based database instead of an in-memory one (not ":memory:"). Processing data in chunks is not directly supported. Consider using DuckDB instead.

benchmark = TPCH(scale_factor=0.1)

adapter = SQLiteAdapter(database_path="./benchmark.db")

Slow Query Performance

Problem: Queries take longer than expected

Solutions: reduce the scale factor, add indexes for common joins, and consider using DuckDB for analytical queries. DuckDB uses columnar storage and is optimized for OLAP workloads.

benchmark = TPCH(scale_factor=0.1)

conn.execute("CREATE INDEX idx_lineitem_orderkey ON lineitem(l_orderkey)")
conn.execute("ANALYZE")

from benchbox.platforms.duckdb import DuckDBAdapter
adapter = DuckDBAdapter()

Missing Tables

Problem: “no such table” error

Solutions: ensure that the schema is created before data loading. If you use an existing database with an old schema, recreate the database with force_recreate=True, which deletes the existing database file on connect.

adapter.create_schema(benchmark, conn)
adapter.load_data(benchmark, conn, data_dir)

adapter = SQLiteAdapter(
    database_path="./benchmark.db",
    force_recreate=True
)

Feature Not Supported

Problem: “Power test not implemented for SQLite adapter”

Explanation: SQLiteAdapter.run_power_test, run_throughput_test and run_maintenance_test always raise NotImplementedError.

Solution: for the TPC power, throughput and maintenance tests, use another platform. DuckDB does not override these tests with NotImplementedError.

from benchbox.platforms.duckdb import DuckDBAdapter
adapter = DuckDBAdapter()

SQLite Platform Adapter

Overview

Quick Start

In-Memory Mode

File-Based Mode

API Reference

SQLiteAdapter Class

Parameters

Returns

Raises

Example

Compatibility

Constructor Parameters

Methods and attributes

Construction and configuration

Connection and schema

Loading data into tables

Query execution and plans

TPC test drivers

Tuning

Capabilities

Configuration Examples

Development Testing

Persistent Storage

CI/CD Pipeline

Multi-threaded Access

Connection Management

Basic Connection

Query Execution

Data Loading

From Generated Data

Performance Optimization

Connection Pragmas

Query Optimization Tips

Scale Factor Guidelines

Best Practices

Use Case Selection

Testing Strategy

Development Workflow

Resource Management

Common Issues

Database Locked Error

Memory Error

Slow Query Performance

Missing Tables

Feature Not Supported

See Also

Platform Documentation

API Reference

Benchmarks

External Resources