Schema Mapping¶

By default, select, select_one, and select_one_or_none return dict[str, Any] rows. Passing schema_type= hydrates rows directly into typed Python structures while preserving static type inference in mypy and pyright.

Mapping Rows to msgspec.Struct¶

msgspec.Struct is the fastest and lowest-memory target for typed row hydration in SQLSpec (uv add "sqlspec[msgspec]"). Declaring frozen=True, gc=False omits CPython cyclic garbage-collection headers on every row object:

"""Map query results automatically into typed msgspec.Struct models with schema_type."""

import msgspec

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

__all__ = ("User", "main")


class User(msgspec.Struct, frozen=True, gc=False):
    """Typed user row projection."""

    id: int
    name: str


def main() -> tuple[list[User], User, User | None]:
    """Fetch dictionary rows and typed User structs via select, select_one, and select_one_or_none."""
    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)")
        session.execute("INSERT INTO users (name) VALUES ('Alice'), ('Bob')")

        raw_rows = session.select("SELECT id, name FROM users ORDER BY id")
        assert raw_rows == [{"id": 1, "name": "Alice"}, {"id": 2, "name": "Bob"}]

        users = session.select("SELECT id, name FROM users ORDER BY id", schema_type=User)
        alice = session.select_one("SELECT id, name FROM users WHERE name = ?", "Alice", schema_type=User)
        nobody = session.select_one_or_none("SELECT id, name FROM users WHERE name = ?", "Nobody", schema_type=User)

    assert isinstance(users[0], User)
    assert alice.name == "Alice"
    assert nobody is None
    return users, alice, nobody


if __name__ == "__main__":
    print(main())

Notice how schema_type=User narrows each method's static return type automatically:

• session.select(..., schema_type=User) returns list[User].
• session.select_one(..., schema_type=User) returns User.
• session.select_one_or_none(..., schema_type=User) returns User | None.

Supported Schema Libraries¶

SQLSpec's schema dispatcher supports five model families out of the box without requiring adapter-specific glue code:

Comparing Schema Definitions¶

All supported schema types plug into the exact same schema_type= keyword argument:

import msgspec


class AccountRow(msgspec.Struct, frozen=True, gc=False):
    id: int
    email: str
    is_active: bool = True

from typing import TypedDict


class AccountRow(TypedDict):
    id: int
    email: str
    is_active: bool

from pydantic import BaseModel


class AccountRow(BaseModel):
    id: int
    email: str
    is_active: bool = True

from dataclasses import dataclass


@dataclass(frozen=True, slots=True)
class AccountRow:
    id: int
    email: str
    is_active: bool = True

Column Naming and Case Normalization¶

Schema mapping matches SQL result column names to schema field names:

• Alias computed expressions explicitly in SQL (SELECT COUNT(*) AS total_orders) so column names match your struct or model attributes.
• Enterprise engines that return unquoted column identifiers in uppercase (Oracle, IBM Db2, and ADBC over Oracle/Db2) enable driver_features={"enable_lowercase_column_names": True} in SQLSpec so SELECT id, email FROM accounts maps cleanly onto lowercase id and email Python fields without quoting every alias.
• For camelCase JSON APIs backed by snake_case SQL columns, configure rename="camel" on your msgspec.Struct or alias generator on BaseModel while keeping SQL column projections in snake_case.

Recap¶

• Start with msgspec.Struct(..., frozen=True, gc=False) for fast, immutable row objects, or TypedDict when you want static types with zero runtime allocation beyond the driver dictionary.
• Pass schema_type= directly to select, select_one, select_one_or_none, select_with_total, or SQLResult.all(schema_type=...).
• Use explicit AS field_name SQL aliases for calculated columns so projections remain decoupled from underlying expression text.