Quickstart¶

Install sqlspec in your virtual environment (the built-in sqlite3 adapter requires no extra drivers):

uvpippdmpoetry

uv add sqlspec

pip install sqlspec

pdm add sqlspec

poetry add sqlspec

Run your first parameterized query with zero external setup using Python's built-in SQLite driver:

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

spec = SQLSpec()
config = spec.add_config(SqliteConfig(connection_config={"database": ":memory:"}))

with spec.provide_session(config) as session:
    session.execute(
        """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT,
            points INTEGER
        )
        """
    )
    session.execute(
        """
        INSERT INTO users (name, points)
        VALUES (:name, :points)
        """,
        name="Ada",
        points=42,
    )
    count = session.select_value("SELECT COUNT(*) FROM users", value_type=int)
    row = session.select_one(
        """
        SELECT id, name, points
        FROM users
        WHERE name = :name
        """,
        name="Ada",
    )

Execute the script with uv:

uv run python -m docs.examples.quickstart.first_query

Expected output:

{'id': 1, 'name': 'Ada', 'points': 42}

Recap¶

Key steps in first_query.py:

SQLSpec(): Creates the central registry that manages database configurations, connection pools, and named SQL files.
spec.add_config(...): Registers a SqliteConfig pointing at an in-memory SQLite database and returns a typed handle.
with spec.provide_session(config) as session:: Acquires a pooled connection and yields a SqliteDriver session that cleans up automatically on block exit.
session.execute(..., name="Ada", points=42): Compiles :name and :points placeholders for SQLite and binds keyword arguments safely without string interpolation.
select_value and select_one: select_value extracts a single scalar column (int), while select_one returns the single matching row as a dict[str, Any] (raising NotFoundError if zero rows match).

Positional vs Named Parameters¶

SQLSpec accepts both named keyword parameters (:name) and positional placeholders (? or $1) on any adapter, translating them to the underlying driver's wire syntax automatically:

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

spec = SQLSpec()
config = spec.add_config(SqliteConfig(connection_config={"database": ":memory:"}))

with spec.provide_session(config) as session:
    session.execute("CREATE TABLE notes (id INTEGER PRIMARY KEY, body TEXT)")
    session.execute("INSERT INTO notes (body) VALUES (?)", "Hello, SQLSpec")
    rows = session.select("SELECT id, body FROM notes")

Use positional parameters for quick single-column statements and named parameters whenever a query binds multiple fields or composes StatementFilter objects.

Asynchronous Sessions¶

Switching from synchronous sqlite3 to asyncio uses the exact same session methods with await and async with. Install sqlspec[aiosqlite] and swap SqliteConfig for AiosqliteConfig:

from sqlspec import SQLSpec
from sqlspec.adapters.aiosqlite import AiosqliteConfig


async def main() -> list[dict[str, Any]]:
    """Insert and read a note using an asynchronous SQLite session."""
    spec = SQLSpec()
    config = spec.add_config(AiosqliteConfig(connection_config={"database": ":memory:"}))

    try:
        async with spec.provide_session(config) as session:
            await session.execute(
                """
                CREATE TABLE notes (
                    id INTEGER PRIMARY KEY,
                    body TEXT
                )
                """
            )
            await session.execute(
                """
                INSERT INTO notes (body)
                VALUES (?)
                """,
                "Hello, Async SQLSpec",
            )
            rows = await session.select("SELECT id, body FROM notes")
            assert rows == [{"id": 1, "body": "Hello, Async SQLSpec"}]
            return rows
    finally:
        await config.close_pool()

Always call await config.close_pool() during application shutdown (or let a framework plugin manage lifespan cleanup automatically).

Typed Row Mapping¶

By default, select and select_one return dict[str, Any] rows. Pass schema_type= to decode rows directly into msgspec.Struct, TypedDict, pydantic.BaseModel, dataclass, or attrs instances (msgspec ships with the sqlspec[msgspec] extra):

import msgspec

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


class User(msgspec.Struct):
    """Typed user row returned from SQLite."""

    id: int
    name: str
    points: int


def get_user(session: Any) -> User:
    """Fetch Ada's record decoded into a msgspec.Struct."""
    return session.select_one(
        """
        SELECT id, name, points
        FROM users
        WHERE name = :name
        """,
        name="Ada",
        schema_type=User,
    )


spec = SQLSpec()
config = spec.add_config(SqliteConfig(connection_config={"database": ":memory:"}))

with spec.provide_session(config) as session:
    session.execute(
        """
        CREATE TABLE users (
            id INTEGER PRIMARY KEY,
            name TEXT,
            points INTEGER
        )
        """
    )
    session.execute(
        """
        INSERT INTO users (name, points)
        VALUES (:name, :points)
        """,
        name="Ada",
        points=42,
    )
    user = get_user(session)

user is a User(id=1, name='Ada', points=42) instance.

Defining a msgspec.Struct model gives you typed attribute access, static type checking, and lower allocation overhead than plain dictionaries (see Performance for optional garbage-collector tuning on hot paths).

Moving from In-Memory to a Local File¶

Replace ":memory:" with a file path to persist SQLite state across process restarts while keeping the rest of your query code identical:

from pathlib import Path

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

spec = SQLSpec()
config = spec.add_config(
    SqliteConfig(connection_config={"database": str(Path("app.db"))})
)

When you are ready to point the same queries at PostgreSQL, Oracle, DuckDB, Spanner, or MySQL, swap SqliteConfig for the corresponding adapter configuration without rewriting your session.select(...) or session.execute(...) calls.

Next Steps¶

Goal	Recommended Guide
Register multiple pools, `bind_key` names, or custom statement settings	Configuration
Choose between `select`, `fetch` aliases, `select_with_total`, `execute_many`, and `select_stream`	Querying
Move multi-line SQL out of Python strings into `-- name:` `.sql` files	SQL Files
Manage schema revisions with the `sqlspec` CLI	Migrations
Connect to PostgreSQL, Oracle, DuckDB, Spanner, BigQuery, MySQL, SQL Server, or Db2	Databases
Wire request-scoped sessions into Litestar, FastAPI, Google ADK, or Dishka	Integrations