Quickstart#

SQLSpec quickstart demo

Get running with SQLSpec in a few minutes using SQLite. These examples are short on purpose so you can copy them into a scratch file and experiment.

Step 1: Connect#

Create a SQLSpec registry, register an adapter configuration (using SQLite in-memory for this walkthrough), and open a session with provide_session(). Sessions manage connection lifecycle and transaction boundaries automatically. Choose sync or async:

sync connection (sqlite)#
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 if not exists notes (id integer primary key, body text)")
    session.execute("insert into notes (body) values (?)", "Hello, SQLSpec!")
    result = session.execute("select id, body from notes")
    print(result.all())
async connection (aiosqlite)#
    from sqlspec import SQLSpec
    from sqlspec.adapters.aiosqlite import AiosqliteConfig

    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 if not exists notes (id integer primary key, body text)")
            await session.execute("insert into notes (body) values (?)", "Hello, Async SQLSpec!")
            result = await session.execute("select id, body from notes")
            print(result.all())
    finally:
        await config.close_pool()

Step 2: Run Your First Query#

Execute statements with parameter placeholders (:param for named parameters or ? for positional). SQLSpec binds parameters safely to prevent SQL injection and returns structured results accessible via .one(), .all(), or .scalar():

named parameters and query execution#
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 if not exists 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.execute("select count(*) from users").scalar()
    result = session.execute("select id, name, points from users where name = :name", name="Ada")
    print(f"Count: {count}, Record: {result.one()}")

Step 3: Map Rows to Typed Models#

SQLSpec is a type-safe query mapper. You can map query results directly into standard library @dataclass, msgspec.Struct, or pydantic.BaseModel instances using the schema_type parameter:

type-safe model mapping#

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

@dataclass
class User:
    id: int
    name: str
    points: int

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

with spec.provide_session(config) as session:
    session.execute("create table if not exists users (id integer primary key, name text, points integer)")
    session.execute("insert into users (name, points) values (:name, :points)", name="Ada", points=42)

    result = session.execute("select id, name, points from users where name = :name", name="Ada")
    user = result.one(schema_type=User)
    print(f"Loaded {user.name} with {user.points} points")

Next Steps#