skip to content

For a team's FastAPI services, how would you decide between SQLModel table models and plain SQLAlchemy models with separate Pydantic schemas?

level: principalimportance: should knowfreq 20%

answer

  1. one declaration versus two
  2. coupling of columns and contract
  3. escape hatches into SQLAlchemy
  4. a 0.0.x dependency

basics

~20 s

SQLModel declares each field once for the table and the API models and matches FastAPI's documented recipe. Plain SQLAlchemy models with Pydantic schemas duplicate fields but decouple storage from the contract. Decide on schema complexity, team SQLAlchemy depth and tolerance for a 0.0.x dependency.

solid answer

~50 s

SQLModel's draw is one class tree: a data-only base shares fields between the table model and the Create, Public and Update models, and it is the recipe FastAPI's own docs teach. Its costs are a 0.0.x version line, table models that skip Pydantic validation when built directly, and advanced SQLAlchemy features reached through escape hatches such as `sa_column` and `sa_relationship_kwargs`. Plain SQLAlchemy models with separate Pydantic schemas duplicate fields (FastAPI reads the ORM objects' attributes into the response model), but storage and contract evolve separately and the team works from SQLAlchemy's own documentation. I'd choose SQLModel for CRUD-shaped services and teams new to SQLAlchemy, and plain SQLAlchemy for rich mappings, shared model packages or contracts that must not move with a column. Either way the conventions matter more: no table models as bodies, a public response model on every endpoint, migrations instead of `create_all`.

go deeper

for a junior

Know that SQLModel classes are both Pydantic and SQLAlchemy models, while the alternative declares SQLAlchemy tables and Pydantic schemas separately.

for a middle

Explain what each style duplicates or shares, and how FastAPI fills a plain Pydantic response model from a returned ORM object's attributes.

for a senior

Show the operational differences: escape hatches for advanced mapping, the validation trap with table models, and contract drift through shared bases.

for a principal

Make the call per service from schema complexity, team depth, contract stability and appetite for a 0.0.x dependency, and write down the triggers for revisiting it.

## What is being decided A team building several FastAPI services on a relational database has to choose how it declares its models, and the choice is hard to reverse once dozens of endpoints depend on it: - **SQLModel**: one class tree where a data-only base shares fields between a table model (`table=True`) and the Create, Public and Update data models. This is the recipe FastAPI's own SQL tutorial teaches. - **Plain SQLAlchemy plus Pydantic**: SQLAlchemy declarative models for the tables and separate Pydantic models for the API. FastAPI validates a returned value with `from_attributes=True`, so a Pydantic response model reads an ORM object's attributes without extra configuration; code that converts objects itself calls `model_validate(obj, from_attributes=True)` or sets it in the model's config. Neither is wrong. They trade duplication against coupling and maturity. ## The case for SQLModel - **Fields are declared once.** `title: str = Field(index=True)` on `BookBase` serves the column, the index and every API schema that inherits it. - **It is the documented FastAPI path.** New engineers find the same models, `SessionDep` and `response_model` patterns in FastAPI's and SQLModel's docs. - **Editor support is good.** Table models are typed classes, and `select(Book)` and `session.exec()` return typed results. - **It is still SQLAlchemy underneath.** The engine, session, `select()` and Alembic all work; `sa_type`, `sa_column`, `sa_column_kwargs` and `sa_relationship_kwargs` reach SQLAlchemy features that `Field()` and `Relationship()` do not expose. ## The case for plain SQLAlchemy plus Pydantic - **Storage and contract evolve separately.** Renaming a column never renames a JSON field by accident, because the API models declare their own fields. - **The whole of SQLAlchemy, with its own documentation.** Rich mappings (inheritance hierarchies, composite types, custom loaders) are written directly instead of through escape hatches. - **Maturity and versioning.** SQLModel's releases are still numbered 0.0.x, and its docs describe some behaviour as current and expected to change, such as table models skipping Pydantic validation when built directly. SQLAlchemy and Pydantic each follow their own stable major lines. - **The cost is duplication.** Each field appears in the table and in one or more API models, and mapping code or `model_validate()` calls connect them. | Concern | SQLModel table and data models | SQLAlchemy models plus Pydantic schemas | |---|---|---| | Field declarations | once, shared through a base | twice or more | | API and column coupling | tight if bases are shared | none by default | | Advanced mapping | through `sa_*` escape hatches | direct | | Validation trap | table models skip it on direct construction | ORM objects are never request bodies | | Version line | 0.0.x | stable majors | | Learning curve for FastAPI newcomers | lowest | higher | ## Signals that should drive the call 1. **Schema shape.** Mostly CRUD tables with simple relationships favour SQLModel; heavy use of advanced mapping favours plain SQLAlchemy. 2. **Team depth.** A team fluent in SQLAlchemy loses little by writing it directly; a team new to it gains from SQLModel's narrower surface. 3. **Contract stability.** Public APIs consumed by other teams want models that do not move when a column does; that pushes toward separate declarations, even inside SQLModel. 4. **Existing code.** Services that share a SQLAlchemy model package with workers or scripts usually keep that package rather than wrap it. 5. **Risk appetite for a 0.0.x dependency.** Pin it, read its release notes on upgrade, and keep tests that would catch a behaviour change. ## Conventions that hold either way - Table or ORM models never appear as request bodies; input is always a data or Pydantic model, and rows are built with `model_validate()` or explicit mapping. - Every endpoint declares a public `response_model`, so internal columns never leak. - PATCH uses an all-optional update model and `exclude_unset=True`. - Schema changes go through migrations; `create_all` is for tests and prototypes. ## Revisiting the decision Record the reasons with the choice. If SQLModel's escape hatches start to dominate a schema, or the public contract keeps moving with column renames, those are the triggers to move a service to plain SQLAlchemy; the session-per-request wiring in FastAPI stays the same either way.

  • Does SQLModel's shared base couple the database to the public API?
    It can. When `BookPublic` and `Book` both inherit `title` from `BookBase`, renaming the column renames the JSON field unless someone adds an alias or stops sharing it. Teams that need a stable contract declare the public models' fields separately, which gives back part of the duplication SQLModel saves.
  • If a team picks SQLModel, what do they do when a column needs a SQLAlchemy type or option that `Field()` does not expose?
    Use the escape hatches: `Field(sa_type=...)` for a column type, `Field(sa_column=Column(...))` for a full column definition, `sa_column_kwargs` for extra column options, and `Relationship(sa_relationship_kwargs=...)` for relationship options. Each is SQLAlchemy written inside SQLModel, so a schema full of them suggests plain SQLAlchemy would read more clearly.

  • SQLModel is a separate ORM, so SQLAlchemy knowledge does not carry over.
  • With SQLModel a team no longer needs separate request and response models.
  • SQLAlchemy ORM objects cannot be returned through a FastAPI response_model.
  • create_all at startup replaces migrations whichever model style is chosen.