FastAPI + SQLModel CRUD Patterns Skill

When to use this Skill

Use this Skill whenever you are:

• Creating or modifying CRUD (Create, Read, Update, Delete) endpoints in a FastAPI application that uses SQLModel for persistence.
• Designing new resources (e.g. Task, UserProfile, Project, Order) and their REST endpoints.
• Refactoring existing API code to be more consistent and reliable.
• Adding tests or changing database access patterns related to CRUD.

This Skill must work for any FastAPI + SQLModel project, not just a single repository.

Core goals

• Keep CRUD code consistent, predictable, and easy to reuse across many projects.
• Separate concerns:
  • Models (SQLModel) in one place.
  • Routers (FastAPI endpoints) in another.
  • Database session management in a dedicated module.
• Use clear REST semantics (HTTP verbs, status codes, resource paths).
• Provide strong typing via SQLModel and Pydantic models.
• Handle errors and not-found cases cleanly, without crashes. [web:53][web:59]

Architecture assumptions

• Web framework: FastAPI.
• ORM: SQLModel (sync or async, but pick one style per project).
• Database: Any SQL database supported by SQLModel (e.g. PostgreSQL). [web:53][web:57]
• Structure:

  • db.py

    or similar: session creation and engine.

  • models.py

    or

    models/

    : SQLModel models.

  • routers/

    or

    routes/

    : FastAPI routers per resource.

  • main.py

    : FastAPI app entrypoint registering routers.

The exact filenames can differ between projects; the patterns stay the same.

Resource and endpoint conventions

• Each logical resource (e.g.

  Task

  ,

  Item

  ,

  User

  ) should have its own router module, for example:

  • routers/tasks.py

    with a

    APIRouter(prefix="/tasks", tags=["tasks"])

    .

• Typical REST endpoints per resource:

  • GET /<resource>

    → list items.

  • POST /<resource>

    → create item.

  • GET /<resource>/{id}

    → get single item.

  • PUT /<resource>/{id}

    → replace item.

  • PATCH /<resource>/{id}

    → partial update (optional).

  • DELETE /<resource>/{id}

    → delete item. [web:53][web:59]

• Resource names should be plural in paths (

  /tasks

  ,

  /users

  ), with singular nouns used in model/type names (

  Task

  ,

  User

  ).

Models and schemas

• Use SQLModel models for database tables, with:

  • Primary key fields (

    id

    or similar).
  • Optional timestamps (e.g.

    created_at

    ,

    updated_at

    ) when useful.
  • Reasonable defaults and constraints (e.g.

    nullable

    ,

    max_length

    ).

• When needed, define separate Pydantic/SQLModel schemas for:

  • Create input (e.g.

    TaskCreate

    ) – fields required for creation.
  • Update input (e.g.

    TaskUpdate

    ) – optional fields for partial updates.
  • Response model (e.g.

    TaskRead

    ) – what the API returns.

• Avoid exposing internal-only fields (e.g. secrets) in response models.

Database session handling

• Provide a shared dependency for DB sessions, for example:

  • get_session()

    in

    db.py

    that yields a

    Session

    object.

• Use

  Depends(get_session)

  in routers to access the database.

• Do not create database engines or sessions directly inside routers or endpoint functions. Keep connection logic centralized. [web:53][web:57]

CRUD behaviour patterns

For each resource, the default CRUD behaviour should follow this pattern:

• Create (

  POST

  ):
  • Validate input using a dedicated schema if needed.
  • Construct the SQLModel instance from the validated data.
  • Add and commit the instance using the shared session.
  • Refresh the instance to return updated fields (e.g. autoincrement id).

• List (

  GET

  collection):
  • Return a list of items, optionally with pagination, filtering, or sorting based on query parameters.
  • Avoid returning unbounded, huge result sets when possible.

• Get (

  GET

  single):
  • Fetch the item by primary key.
  • If not found, raise

    HTTPException(status_code=404)

    with a clear message.

• Update (

  PUT

  /

  PATCH

  ):
  • Load the existing item; if not found, return 404.
  • Apply allowed changes from the input schema.
  • Commit and refresh before returning the updated item.

• Delete (

  DELETE

  ):
  • Load the existing item; if not found, return 404.
  • Either hard-delete or soft-delete depending on the project’s rules.
  • Return appropriate status (e.g. 204 No Content for hard delete).

Error handling

• Never let database or Python exceptions leak directly to clients. Use

  HTTPException

  with appropriate status codes and simple, safe error messages. [web:53][web:59]

• Common error cases:

  • Resource not found → 404.
  • Validation errors → 422 (FastAPI will handle many of these).
  • Unauthorized/forbidden (if auth is applied) → 401/403.

• Log details server-side if needed, but keep responses simple.

Typing and response models

• Always declare response models in router decorators when practical:

  • response_model=TaskRead

    or

    List[TaskRead]

    .

• This improves:

  • OpenAPI docs.
  • Type checking in clients.
  • Clarity of what each endpoint returns.

• Avoid returning raw dicts or mixing data shapes; keep responses consistent.

Filtering, sorting, and pagination (optional)

• When adding filtering/sorting:

  • Use query parameters (e.g.

    status

    ,

    sort_by

    ,

    order

    ).
  • Document default behaviours and limits in the resource spec.

• For pagination:

  • Use standard patterns like

    limit

    and

    offset

    or page/size pairs.
  • Enforce maximum limits to avoid performance issues.

Things to avoid

• Creating engines or sessions inside endpoint functions.
• Mixing business logic, validation, and database operations in large monolithic functions; prefer small helpers where appropriate.
• Returning raw SQLModel instances that include internal fields that should not be exposed.
• Using inconsistent status codes for the same error conditions.

References inside the repo

When present, this Skill should align with:

• db.py

  or equivalent – engine and

  get_session

  dependency.

• models.py

  or

  models/

  – SQLModel models for resources.

• routers/

  or

  routes/

  – resource-specific routers.

If these files are missing, propose creating them using these patterns rather than inventing a new, ad-hoc CRUD style for each resource.