Schema Mapping¶
By default, select, select_one, select_one_or_none, and their fetch* aliases 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¶
Use msgspec.Struct for typed row objects. Install the msgspec extra before
running this example:
import msgspec
from sqlspec import SQLSpec
from sqlspec.adapters.sqlite import SqliteConfig
class User(msgspec.Struct):
"""Typed user row projection."""
id: int
name: str
def fetch_users(session: Any) -> tuple[list[User], User, User | None]:
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,
)
return users, alice, nobody
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')")
users, alice, nobody = fetch_users(session)
Notice how schema_type=User narrows each method's static return type automatically:
session.select(..., schema_type=User)returnslist[User].session.select_one(..., schema_type=User)returnsUser.session.select_one_or_none(..., schema_type=User)returnsUser | None.
Supported Schema Libraries¶
SQLSpec's schema dispatcher supports five model families out of the box without requiring adapter-specific glue code:
| Model Family | Install Extra | Best When |
|---|---|---|
msgspec.Struct |
sqlspec[msgspec] |
High-throughput APIs, Litestar MsgspecDTO endpoints, low GC pressure, strict C-speed decoding |
typing.TypedDict |
(built-in) | Lightweight static typing over plain dict rows at zero runtime conversion cost |
dataclasses.dataclass |
(built-in) | Standard-library domain value objects with zero external dependencies |
pydantic.BaseModel |
sqlspec[pydantic] |
FastAPI applications or rich field validators (EmailStr, custom @field_validator) |
attrs (@define) |
sqlspec[attrs] |
Codebases standardized on attrs and cattrs converters |
Choose the extra matching your model library; they can be combined with a driver
extra in the same requirement. The pydantic extra includes pydantic-extra-types;
the attrs extra includes cattrs. TypedDict and dataclasses need no model-library extra.
Comparing Schema Definitions¶
All supported schema types plug into the exact same schema_type= keyword argument:
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, andADBCover Oracle/Db2) enabledriver_features={"enable_lowercase_column_names": True}in SQLSpec soSELECT id, email FROM accountsmaps cleanly onto lowercaseidandemailPython fields without quoting every alias. - For camelCase JSON APIs backed by
snake_caseSQL columns, configurerename="camel"on yourmsgspec.Structor an alias generator onBaseModelwhile keeping SQL column projections insnake_case. By default, msgspec requires encoded field names inside nested values.
For trusted SQL projections with nested values that use Python field names, fetch
the raw rows and pass normalize_nested=True to to_schema(). For example, DuckDB
returns item_name inside the struct in this query.
import msgspec
from sqlspec.utils.schema import to_schema
class Item(msgspec.Struct, rename="camel"):
item_name: str
class InventoryRow(msgspec.Struct):
item: Item
row = session.select_one("SELECT struct_pack(item_name := ?) AS item", "Pencil")
inventory = to_schema(row, schema_type=InventoryRow, normalize_nested=True)
The option follows declared Struct fields through optional fields, lists, tuples, and mapping values. Use encoded names inside tagged unions. When the Python and encoded names both appear, the encoded name wins.
Recap¶
- Start with
msgspec.Structfor fast, typed row objects, orTypedDictwhen you want static types with zero runtime allocation beyond the driver dictionary. - Pass
schema_type=directly toselect,select_one,select_one_or_none,select_with_total, orSQLResult.all(schema_type=...). - Use explicit
AS field_nameSQL aliases for calculated columns so projections remain decoupled from underlying expression text.