Skip to content

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:

uv add "sqlspec[msgspec]"
docs/examples/querying/schema_mapping.py
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) 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:

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:

import msgspec


class AccountRow(msgspec.Struct):
    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 an alias generator on BaseModel while keeping SQL column projections in snake_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.Struct for fast, typed 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.