SQLModel combines typed models, validation, and persistence on top of Pydantic and SQLAlchemy. It removes repetition in APIs, but it does not remove decisions about transactions, indexes, relationships, and migrations.

Create a table and session

from sqlmodel import Field, Session, SQLModel, create_engine, select


class Product(SQLModel, table=True):
    id: int | None = Field(default=None, primary_key=True)
    name: str = Field(index=True, min_length=2, max_length=120)
    price_cents: int = Field(gt=0)


engine = create_engine("sqlite:///store.db")
SQLModel.metadata.create_all(engine)

with Session(engine) as session:
    product = Product(name="Keyboard", price_cents=15900)
    session.add(product)
    session.commit()
    session.refresh(product)

    matches = session.exec(select(Product).where(Product.price_cents < 20000)).all()

Represent money as minor-unit integers or configure Decimal consistently. See Decimal for monetary calculations.

Separate database and API contracts

Define ProductCreate without table=True, ProductUpdate with optional fields, and ProductPublic with only safe response fields. This prevents clients from assigning internal IDs or reading sensitive columns. With FastAPI, provide a short-lived session per request through dependency injection.

create_all() is convenient in examples and tests, but it cannot safely describe changes to an existing production database. Use Alembic migrations, inspect generated SQL, and maintain backups.

The official SQLModel tutorial, accessed July 22, 2026, covers CRUD, relationships, and FastAPI. Treat SQLModel as a convenient layer, not a replacement for understanding constraints, transactions, and generated queries.

Design separate database and API models

A production CRUD service must distinguish client input, stored data, and public output. A shared base can remove repetition while keeping the table out of the public contract:

class ProductBase(SQLModel):
    name: str = Field(min_length=2, max_length=120)
    price_cents: int = Field(gt=0)

class Product(ProductBase, table=True):
    id: int | None = Field(default=None, primary_key=True)
    active: bool = True

class ProductCreate(ProductBase):
    pass

class ProductUpdate(SQLModel):
    name: str | None = Field(default=None, min_length=2, max_length=120)
    price_cents: int | None = Field(default=None, gt=0)
    active: bool | None = None

class ProductPublic(ProductBase):
    id: int
    active: bool

This prevents a POST from selecting an internal ID. It also lets a partial update distinguish an omitted field from a supplied value. For PATCH, obtain supplied fields with model_dump(exclude_unset=True) and apply only those values. Do not automatically use exclude_none=True: in another model, an explicit null may legitimately clear an optional value.

Implement each CRUD operation deliberately

Keep transaction ownership close to the operation. A create function can accept a session and validated input, build Product.model_validate(data), add it, commit, and call refresh() before returning it. Refreshing loads the generated ID and database defaults.

For a single read, query by primary key and return 404 when no row exists. A successful response containing null creates an ambiguous API contract. List endpoints need a bounded page and deterministic ordering:

def list_products(session: Session, offset: int = 0, limit: int = 50):
    statement = (
        select(Product)
        .where(Product.active.is_(True))
        .order_by(Product.id)
        .offset(offset)
        .limit(min(limit, 100))
    )
    return session.exec(statement).all()

Offset pagination is understandable for modest catalogs. Large or frequently changing datasets may need cursor pagination. Index columns that support real filters, but remember that every index costs storage and write time. Inspect the database query plan rather than adding indexes by intuition.

During an update, load and modify the record in the same session. If a unique constraint fails, catch the relevant database exception, call rollback(), and translate the conflict into a 409 response. A failed session cannot safely execute more work until it has been rolled back.

Physical deletion suits disposable data. A product referenced by orders is usually better deactivated with active=False. Soft deletion creates an extra responsibility: every applicable query must consistently exclude inactive rows. Centralize that policy instead of relying on every endpoint author to remember it.

Manage sessions in FastAPI

Use a dependency that scopes one session to one request:

def get_session():
    with Session(engine) as session:
        yield session

An endpoint receives session: Session = Depends(get_session). A session is neither a global cache nor an object to share across threads. SQLite test settings can differ from production settings; do not copy check_same_thread=False to another database without understanding why it was introduced.

Declare response_model=ProductPublic or the equivalent return annotation. OpenAPI then documents the public representation and internal columns are less likely to leak. Output filtering is not authorization, however. Verify that the current principal may access or change the requested record before performing the operation.

Handle transactions and concurrency

A commit marks a unit of work. If creating an order and decrementing inventory must succeed together, perform both changes in one transaction. Committing halfway and attempting compensation later leaves room for incomplete state.

Concurrent requests may read the same value and race to update it. Database constraints remain the final defense for uniqueness and referential integrity. Inventory and balance changes may require an atomic update, row locking, or optimistic concurrency with a version column. The right option depends on the database and isolation level, not only on ORM syntax.

Watch for N+1 queries when walking relationships. Inspect emitted SQL during development and choose explicit loading strategies when related data is required. Likewise, avoid an unbounded .all() merely because it is easy to write.

Test behavior, not just model construction

Override the session dependency with a temporary database and isolate test state. Cover successful creation, validation errors, missing records, uniqueness conflicts, pagination, authorization, and response filtering. Calling a repository function alone does not prove that the HTTP endpoint returns the intended status and public schema.

SQLite is useful for fast tests, but its typing and concurrency differ from PostgreSQL or MySQL. Keep integration tests against the production database engine for critical queries, constraints, and migrations. Never treat create_all() as a deployment migration system: it can create missing tables but does not safely describe or apply the history of schema changes.

Production checklist

Before release, review input bounds, pagination, indexes, constraints, rollback paths, authorization, and exposed fields. Log meaningful operations without recording sensitive payloads. Monitor query count and latency, maintain backups, and test restoration. SQLModel reduces boilerplate; reliable CRUD still depends on explicit contracts and invariants enforced by the database.

Decide where business rules belong. Shape and range validation fits input models, while a rule that depends on current state, such as refusing to deactivate a product used by an active promotion, must query the database inside the service transaction. Avoid hiding consequential behavior in automatic hooks that are difficult to trace and test.

Plan compatible schema changes. Adding a nullable column, deploying code that understands both states, backfilling rows, and only then enforcing a required constraint is safer than changing everything at once. Measure slow queries with data volumes that resemble production because an empty test table cannot expose weak query plans, expensive sorting, or unstable pagination.

Keep functions focused enough to test, but resist a generic repository that erases meaningful differences. Products, payments, and user accounts may all offer an “update” operation while requiring completely different authorization, audit, and concurrency rules. A little explicit code is often easier to operate than an abstraction with many hidden switches.

Document those decisions beside the service and revisit them when requirements change.