Testing

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

Mock Adapter for Unit Tests

MockSyncConfig and MockAsyncConfig use an in-memory SQLite backend with optional dialect transpilation. Write SQL in your production dialect (PostgreSQL, MySQL, Oracle) and it gets transpiled to SQLite before execution.

mock adapter for unit tests
from sqlspec import SQLSpec
from sqlspec.adapters.mock import MockSyncConfig

# MockSyncConfig uses SQLite :memory: internally
# but can transpile SQL from other dialects
config = MockSyncConfig(target_dialect="postgres")
spec = SQLSpec()
spec.add_config(config)

with spec.provide_session(config) as session:
    # Write SQL in PostgreSQL dialect - it gets transpiled to SQLite
    session.execute("CREATE TABLE users (  id INTEGER PRIMARY KEY,  name VARCHAR(100) NOT NULL)")
    session.execute("INSERT INTO users (name) VALUES ('Alice')")

    users = session.select("SELECT name FROM users")
    print(users)  # [{"name": "Alice"}]

    count = session.select_value("SELECT COUNT(*) FROM users")
    print(count)  # 1

Key features:

  • target_dialect accepts "postgres", "mysql", "oracle", or "sqlite"

  • SQL is automatically transpiled to SQLite for execution

  • No external database required – runs entirely in-memory

  • Supports initial_sql parameter for schema setup on connection create

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"

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