Testing

SQLSpec provides tools for both unit and integration testing of database code.

Pytest Fixture Tips

  • Use tmp_path (pytest built-in) for SQLite file databases

  • Use :memory: for fast in-memory tests

  • Create factory functions for reusable test setup

  • Use execute_many for bulk fixture data

  • Use select_value for assertion checks on counts

import pytest
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig

@pytest.fixture
def db(tmp_path):
    spec = SQLSpec()
    config = spec.add_config(
        SqliteConfig(connection_config={"database": str(tmp_path / "test.db")})
    )
    with spec.provide_session(config) as session:
        session.execute("create table users (id integer primary key, name text)")
        yield session

Integration Test Patterns

For integration tests against real databases, use the standard SQLSpec + adapter config pattern with temporary databases.

integration test fixtures
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig

def create_test_spec(db_path: Path) -> tuple[SQLSpec, SqliteConfig]:
    """Factory for test database setup."""
    spec = SQLSpec()
    config = spec.add_config(SqliteConfig(connection_config={"database": str(db_path)}))
    return spec, config

def seed_database(spec: SQLSpec, config: SqliteConfig) -> None:
    """Populate test fixtures."""
    with spec.provide_session(config) as session:
        session.execute("create table users (id integer primary key, name text, active boolean)")
        session.execute_many(
            "insert into users (name, active) values (?, ?)", [("Alice", True), ("Bob", False), ("Charlie", True)]
        )

# In your test
spec, config = create_test_spec(tmp_path / "test.db")
seed_database(spec, config)

with spec.provide_session(config) as session:
    active_count = session.select_value("select count(*) from users where active = 1")
    assert active_count == 2

    user = session.select_one("select name from users where id = ?", 1)
    assert user["name"] == "Alice"