On this page

SQLModel CRUD with FastAPI: Create, Read, Update, and Delete

Published on August 10, 2026

Enrico Hochkoeppler Profile

By Enrico Hochkoeppler

Web Developer & TutoriaLibre Founder

CRUD — create, read, update, delete — is the foundation of every database-backed API, and getting each operation right in SQLModel depends on a handful of specific patterns: a session dependency that actually closes its connections, model_dump(exclude_unset=True) for partial updates, parameterized filtering instead of raw SQL strings, and pagination that's built in from the start rather than bolted on after a table grows too large to return in full. This article builds all five CRUD operations for a Task Manager API, with each pattern explained as production-correct rather than simplified for the sake of a shorter example.


The Correct Session Dependency​

Before any CRUD route gets written, the session dependency in db.py needs to be right, because getting it wrong here breaks every route built on top of it. A version written as return Session(engine) carries a critical bug: the session it returns is never closed. Every single request leaks an open database connection, and the application eventually exhausts its connection pool and stops accepting requests entirely.

The correct implementation uses yield inside a with block:


from sqlmodel import SQLModel, Session, create_engine



DATABASE_URL = "sqlite:///database.db"



engine = create_engine(

    DATABASE_URL,

    connect_args={"check_same_thread": False},

    echo=False,

)



def create_db_and_tables():

    SQLModel.metadata.create_all(engine)



def get_session():

    with Session(engine) as session:

        yield session

with Session(engine) as session: opens a session and guarantees it closes when the block exits — whether the request completes normally or an exception is raised partway through it. yield session is what hands that open session to the route handler; once the handler returns, execution resumes right after the yield, and the with block closes the session automatically. This is what FastAPI calls a generator dependency, and it's the standard approach for any resource that needs to be opened before a request and reliably closed after it.


The Task Model​


from sqlmodel import SQLModel, Field



class Task(SQLModel, table=True):

    id: int | None = Field(default=None, primary_key=True)

    title: str

    description: str | None = Field(default=None)

    completed: bool = Field(default=False)

This model serves double duty — the database table definition and the Pydantic validation schema for the API are the same class. Route handlers use it directly for database work and return it as the API response, with response_model on each route controlling exactly which fields make it into the serialized JSON.


Router Setup​


from fastapi import APIRouter, Depends, HTTPException, Query

from sqlmodel import Session, select

from db import get_session

from models.task import Task



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

Every CRUD endpoint lives on this router under the /tasks prefix, and each one declares session: Session = Depends(get_session) — FastAPI calls get_session(), injects the yielded session, and cleans it up automatically once the handler returns.


Create — POST /tasks​


@router.post("/", status_code=201, response_model=Task)

def create_task(task: Task, session: Session = Depends(get_session)):

    session.add(task)

    session.commit()

    session.refresh(task)

    return task

session.add(task) registers the object — already built by FastAPI from the validated request body — with the session. At this point it exists only in memory; nothing has reached the database yet. session.commit() writes every pending change and is the moment the actual INSERT SQL runs; if a database constraint is violated — a missing NOT NULL value, a UNIQUE collision — an exception is raised and the transaction rolls back automatically. session.refresh(task) re-reads the row from the database afterward, which is necessary to populate task.id, since the database assigns that value during the insert — without this call, task.id stays None and the response would contain "id": null. response_model=Task is the boundary controlling exactly which fields reach the client, serializing only what Task declares.

Sending:


{

  "title": "Write tests",

  "description": "Cover all endpoints",

  "completed": false

}

returns 201 Created with {"id": 1, "title": "Write tests", "description": "Cover all endpoints", "completed": false}.


Read All — GET /tasks​

Returning every row with no limit at all is a performance problem waiting to surface the moment a table grows large, so filtering and pagination are built in from the start rather than added later:


@router.get("/", response_model=list[Task])

def get_all_tasks(

    completed: bool | None = Query(default=None),

    limit: int = Query(default=20, ge=1, le=100),

    offset: int = Query(default=0, ge=0),

    session: Session = Depends(get_session),

):

    query = select(Task)

    if completed is not None:

        query = query.where(Task.completed == completed)

    query = query.offset(offset).limit(limit)

    return session.exec(query).all()

How select() works​

select(Task) builds a query object equivalent to SELECT * FROM task, but building it doesn't execute anything — it's a description of the query, only run once passed to session.exec(). .where(Task.completed == completed) adds a WHERE completed = ? clause, expressed through Python comparisons on model fields rather than a raw SQL string — which means the value is always passed as a parameterized argument, immune to SQL injection regardless of what it contains. .offset(offset).limit(limit) adds the SQL clauses that implement pagination, and the ge=1, le=100 constraints on limit stop a client from requesting zero rows or more than a hundred at once.

session.exec() vs session.execute()​

session.exec() is SQLModel's own method: it accepts a select() statement and returns results as typed Python objects — here, a sequence of Task instances. session.execute() is SQLAlchemy's lower-level method, returning raw row tuples instead. For SQLModel queries, session.exec() is the one to reach for.

Example requests: GET /tasks/ returns the first 20 tasks; GET /tasks/?completed=false returns only incomplete ones; GET /tasks/?completed=true&limit=5 returns the first five completed tasks; GET /tasks/?limit=10&offset=20 returns tasks 21 through 30 — the third page at that page size.


Read One — GET /tasks/{task_id}​


@router.get("/{task_id}", response_model=Task)

def get_task(task_id: int, session: Session = Depends(get_session)):

    task = session.get(Task, task_id)

    if not task:

        raise HTTPException(status_code=404, detail="Task not found")

    return task

session.get(Task, task_id) fetches a single row by primary key, equivalent to SELECT * FROM task WHERE id = task_id, returning the object if found or None otherwise. For primary key lookups specifically, this is more efficient than session.exec(select(Task).where(Task.id == task_id)).first(), since SQLAlchemy can check its identity map cache first rather than always issuing a query. A None result becomes a 404 response with {"detail": "Task not found"} in the body.


Full Update — PUT /tasks/{task_id}​

PUT replaces the entire resource — the client sends every field, and the server updates all of them:


@router.put("/{task_id}", response_model=Task)

def update_task(

    task_id: int,

    updated: Task,

    session: Session = Depends(get_session),

):

    task = session.get(Task, task_id)

    if not task:

        raise HTTPException(status_code=404, detail="Task not found")

    task.title = updated.title

    task.description = updated.description

    task.completed = updated.completed

    session.commit()

    session.refresh(task)

    return task

The task is fetched first; if it exists, its fields are updated directly on the already-attached Python object — since task came from session.get(), it's already tracked by the session, and calling session.add() again isn't necessary. session.commit() writes the UPDATE SQL. updated.id is deliberately never used: the ID comes from the task_id path parameter, not the request body, since allowing a client to change a resource's own ID through the body would produce unpredictable results.


Partial Update — PATCH /tasks/{task_id}​

PUT requires every field to be resent even when only one is actually changing. PATCH solves that: the client sends only the fields it wants to update, and everything else stays untouched. The mechanism behind this is model_dump(exclude_unset=True), which returns only the fields explicitly included in the request body, skipping anything the client omitted entirely.

A dedicated schema with every field optional makes any subset of fields valid to send:


from pydantic import BaseModel



class TaskPatch(BaseModel):

    title: str | None = None

    description: str | None = None

    completed: bool | None = None

@router.patch("/{task_id}", response_model=Task)

def patch_task(

    task_id: int,

    patch: TaskPatch,

    session: Session = Depends(get_session),

):

    task = session.get(Task, task_id)

    if not task:

        raise HTTPException(status_code=404, detail="Task not found")

    patch_data = patch.model_dump(exclude_unset=True)

    for field, value in patch_data.items():

        setattr(task, field, value)

    session.commit()

    session.refresh(task)

    return task

patch.model_dump(exclude_unset=True) produces a dictionary containing only the fields the client actually sent — a field absent from the request is absent from patch_data entirely, so setattr is never called for it and that database column is never touched. Sending {"completed": true} updates only completed, leaving title and description exactly as they were. Sending {"title": "Revised title", "description": null} updates both of those fields — including setting description to null explicitly — while leaving completed alone.


Delete — DELETE /tasks/{task_id}​


@router.delete("/{task_id}", status_code=204)

def delete_task(task_id: int, session: Session = Depends(get_session)):

    task = session.get(Task, task_id)

    if not task:

        raise HTTPException(status_code=404, detail="Task not found")

    session.delete(task)

    session.commit()

session.delete(task) marks the object for deletion, and the actual DELETE SQL runs on session.commit(). The endpoint returns 204 No Content — the conventional status for a successful delete with nothing meaningful to return — so no return statement is needed. Fetching the task before deleting it also means attempting to delete a nonexistent ID correctly returns 404 rather than silently succeeding on a row that was never there.


Complete Endpoint Summary​


from fastapi import APIRouter, Depends, HTTPException, Query

from pydantic import BaseModel

from sqlmodel import Session, select

from db import get_session

from models.task import Task



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



class TaskPatch(BaseModel):

    title: str | None = None

    description: str | None = None

    completed: bool | None = None



@router.post("/", status_code=201, response_model=Task)

def create_task(task: Task, session: Session = Depends(get_session)):

    session.add(task)

    session.commit()

    session.refresh(task)

    return task



@router.get("/", response_model=list[Task])

def get_all_tasks(

    completed: bool | None = Query(default=None),

    limit: int = Query(default=20, ge=1, le=100),

    offset: int = Query(default=0, ge=0),

    session: Session = Depends(get_session),

):

    query = select(Task)

    if completed is not None:

        query = query.where(Task.completed == completed)

    query = query.offset(offset).limit(limit)

    return session.exec(query).all()



@router.get("/{task_id}", response_model=Task)

def get_task(task_id: int, session: Session = Depends(get_session)):

    task = session.get(Task, task_id)

    if not task:

        raise HTTPException(status_code=404, detail="Task not found")

    return task



@router.put("/{task_id}", response_model=Task)

def update_task(

    task_id: int,

    updated: Task,

    session: Session = Depends(get_session),

):

    task = session.get(Task, task_id)

    if not task:

        raise HTTPException(status_code=404, detail="Task not found")

    task.title = updated.title

    task.description = updated.description

    task.completed = updated.completed

    session.commit()

    session.refresh(task)

    return task



@router.patch("/{task_id}", response_model=Task)

def patch_task(

    task_id: int,

    patch: TaskPatch,

    session: Session = Depends(get_session),

):

    task = session.get(Task, task_id)

    if not task:

        raise HTTPException(status_code=404, detail="Task not found")

    patch_data = patch.model_dump(exclude_unset=True)

    for field, value in patch_data.items():

        setattr(task, field, value)

    session.commit()

    session.refresh(task)

    return task



@router.delete("/{task_id}", status_code=204)

def delete_task(task_id: int, session: Session = Depends(get_session)):

    task = session.get(Task, task_id)

    if not task:

        raise HTTPException(status_code=404, detail="Task not found")

    session.delete(task)

    session.commit()

MethodEndpointActionStatusPOST/tasks/Create a task201 CreatedGET/tasks/List tasks (filtering, pagination)200 OKGET/tasks/{id}Get a single task200 OK / 404PUT/tasks/{id}Full update — replace all fields200 OK / 404PATCH/tasks/{id}Partial update — only provided fields200 OK / 404DELETE/tasks/{id}Delete a task204 No Content / 404


Testing in Swagger UI​

Starting the server and visiting http://127.0.0.1:8000/docs shows interactive documentation generated for all six endpoints — GET /tasks/ lists completed, limit, and offset as optional query parameters with their defaults and constraints already reflected. Sending only {"completed": true} to the PATCH endpoint and confirming that title and description come back unchanged in the response is a direct, practical check that exclude_unset=True is behaving correctly.


Common Mistakes to Avoid​

Using return Session(engine) instead of the yield-based generator leaves every session open indefinitely, leaking a connection per request until the pool is exhausted and the application stops accepting requests. Skipping session.refresh() after session.commit() leaves id as None in the response, since the database assigns that value and nothing re-reads it back into the Python object without the refresh. Calling model_dump() without exclude_unset=True inside a PATCH handler defeats the entire point of PATCH: every field the client omitted gets included with its default value — typically None — and ends up overwriting existing data with nulls instead of leaving it untouched. Returning every row with no pagination works fine at a small scale and fails badly at a large one, since a table with a million rows and no LIMIT will exhaust available memory and time out. Concatenating user input directly into SQL strings for filtering — f"WHERE title = '{title}'" — is a SQL injection vulnerability regardless of how the input is sanitized beforehand; SQLModel's expression-based filtering, Task.title == title, passes values as parameterized arguments and is safe by construction.


Extending the API: Sorting and Bulk Updates​

Sorting with a validated order_by parameter​

Adding a created_at field to Task — a datetime column defaulting to the current UTC time — makes sorting by creation order possible. order_by and desc() from SQLAlchemy build the actual ORDER BY clause, but the field name itself needs validating before being used, since passing an unchecked string straight into getattr() would let a client attempt to sort by any attribute on the model, including ones that were never meant to be exposed that way:


from sqlalchemy import asc, desc



ALLOWED_SORT_FIELDS = {"created_at", "title"}



@router.get("/", response_model=list[Task])

def get_all_tasks(

    completed: bool | None = Query(default=None),

    limit: int = Query(default=20, ge=1, le=100),

    offset: int = Query(default=0, ge=0),

    order_by: str | None = Query(default=None),

    session: Session = Depends(get_session),

):

    query = select(Task)

    if completed is not None:

        query = query.where(Task.completed == completed)

    if order_by:

        descending = order_by.startswith("-")

        field_name = order_by.lstrip("-")

        if field_name not in ALLOWED_SORT_FIELDS:

            raise HTTPException(status_code=400, detail=f"Cannot sort by '{field_name}'")

        column = getattr(Task, field_name)

        query = query.order_by(desc(column) if descending else asc(column))

    query = query.offset(offset).limit(limit)

    return session.exec(query).all()

order_by="-created_at" sets descending = True and strips the leading - to get "created_at" as the actual field name. Checking that name against ALLOWED_SORT_FIELDS before calling getattr(Task, field_name) is what keeps this safe — without that check, any attribute name on Task could be passed through, including ones with no reason to be sortable from a public API. GET /tasks/?order_by=-created_at returns the newest tasks first; GET /tasks/?order_by=title returns them alphabetically.

Bulk-completing every task​


@router.patch("/complete-all", response_model=list[Task])

def complete_all_tasks(session: Session = Depends(get_session)):

    incomplete_tasks = session.exec(select(Task).where(Task.completed == False)).all()

    for task in incomplete_tasks:

        task.completed = True

    session.commit()

    for task in incomplete_tasks:

        session.refresh(task)

    return incomplete_tasks

This route sits at /tasks/complete-all, alongside /tasks/{task_id} on the same router. The two don't collide even though "complete-all" could in principle look like it might match {task_id}: task_id is typed as int, so FastAPI only matches that route against URL segments made entirely of digits — "complete-all" never matches that pattern at all, and falls through correctly regardless of which route is registered first. All incomplete tasks are fetched in a single query, updated in memory, and committed together in one transaction rather than one commit per task, which keeps this a single round trip to the database instead of one per row.

Key Takeaways​

Every CRUD operation here follows from getting the session dependency right first — a yield-based generator that closes cleanly is what every route depends on, and a return-based version that never closes will eventually take the whole application down regardless of how correct the routes themselves are. PUT and PATCH diverge specifically in how much of the resource the client is required to send, with exclude_unset=True being the exact mechanism that makes a partial update actually partial rather than silently nulling out anything left unmentioned. Filtering and sorting stay safe by construction as long as they're built from SQLModel's expression syntax — Task.completed == completed, getattr(Task, field_name) guarded by an allow-list — rather than by interpolating client input directly into a query string, and pagination limits are worth having from the very first version of a list endpoint, not added later once a table has already grown too large to return in one response.

Frequently Asked Questions​

What is CRUD and why does every database API need it?

CRUD stands for Create, Read, Update, and Delete — the four fundamental operations that any database-backed API must support. Create adds new records, Read retrieves existing ones, Update modifies them, and Delete removes them. Every application that stores persistent data, from a simple task list to a complex e-commerce platform, is built on these four operations.

Why is using return Session(engine) in a FastAPI dependency wrong?

Using return Session(engine) means the session is never closed after the request finishes. Every request leaks an open database connection. Over time, the connection pool is exhausted and the application stops accepting new requests. The correct pattern is a generator function that uses yield inside a with block: with Session(engine) as session: yield session. This guarantees the session is closed whether the request succeeds or raises an exception.

What is a generator dependency in FastAPI?

A generator dependency is a dependency function that uses yield instead of return. FastAPI calls the function, receives the yielded value, injects it into the route handler, and then resumes execution after the yield once the handler returns. This makes generator dependencies ideal for resources that must be opened before a request and closed after it, such as database sessions, file handles, and network connections.

What does session.add() do in SQLModel?

session.add(task) registers the object with the session and marks it as pending insertion. Nothing is written to the database at this point — the object is tracked in memory only. The actual INSERT SQL runs when you call session.commit(). You must call session.add() for any new object you want the session to persist.

Why do I need to call session.refresh() after session.commit()?

The database assigns the primary key value during the INSERT operation, not Python. After session.commit(), the Python object's id field is still None because the assignment happened in the database. session.refresh(task) re-reads the row from the database and updates the Python object with the database-assigned values, including the id. Without it, the response contains id: null instead of the real integer.

What does session.get() do and when should I use it?

session.get(Task, task_id) fetches a single row by its primary key and returns the corresponding Python object, or None if no row with that ID exists. It is equivalent to SELECT * FROM task WHERE id = task_id but is more efficient than a full select().where() query for primary key lookups because SQLAlchemy can use its identity map cache. Use session.get() whenever you need one specific row by ID.

What is the difference between session.exec() and session.execute() in SQLModel?

session.exec() is SQLModel's method. It accepts a SQLModel select() statement and returns results as typed Python objects — for example, a sequence of Task instances. session.execute() is SQLAlchemy's lower-level method that returns raw row tuples without SQLModel's type handling. For SQLModel queries, always use session.exec() to get properly typed objects back.

How does select() work in SQLModel?

select(Task) builds a query object equivalent to SELECT * FROM task. It does not execute anything — it describes the query. The query runs only when you pass it to session.exec(). You can chain .where(), .offset(), and .limit() onto the select() object to filter, paginate, and sort results before executing. This approach uses parameterised values automatically, which prevents SQL injection.

What is the difference between PUT and PATCH in a REST API?

PUT is a full update — the client sends all fields and the server replaces the entire resource. If a field is omitted, it is set to its default or null. PATCH is a partial update — the client sends only the fields it wants to change and everything else is left untouched. Use PUT when the client always has the complete resource state. Use PATCH when the client needs to change a specific field without resending the entire object.

How do I implement PATCH correctly in FastAPI with SQLModel?

Define a separate schema with all fields optional, then use model_dump(exclude_unset=True) to get only the fields the client actually sent. Iterate over those fields and apply them to the database object with setattr(). Commit and refresh. The exclude_unset=True flag is essential — without it, model_dump() includes every field with its default value, nulling out all fields the client did not send, which defeats the purpose of PATCH.

What does model_dump(exclude_unset=True) do?

model_dump(exclude_unset=True) returns a dictionary containing only the fields that were explicitly included in the request body. Fields that were omitted by the client are absent from the dictionary entirely. In a PATCH handler, this means only the provided fields are updated in the database. Fields the client did not send are never touched, regardless of what their default values are in the schema.

Why should PATCH use a separate schema instead of the main model?

The main Task model has required fields like title — fields with no default value that must be present. If you use Task as the PATCH body, FastAPI requires the client to send all required fields even for a partial update, which defeats the purpose. A dedicated TaskPatch schema with all fields optional allows the client to send any subset of fields. Making every field optional is the correct design for a PATCH request body.

What HTTP status code should a DELETE endpoint return?

204 No Content. This is the HTTP standard for a successful operation that has no response body to return. Set status_code=204 on the route decorator and do not include a return statement in the handler. If the resource does not exist, return 404 Not Found before attempting the deletion.

How do I add filtering to a GET endpoint in FastAPI SQLModel?

Declare the filter value as a Query parameter on the route function, then apply it to the select() statement conditionally. For example: if completed is not None: query = query.where(Task.completed == completed). SQLModel builds the WHERE clause using Python expressions on model fields rather than raw SQL strings, which ensures the values are parameterised and safe from SQL injection.

How do I add pagination to a SQLModel query in FastAPI?

Add limit and offset as Query parameters to the route function, then chain .offset(offset).limit(limit) onto the select() statement before executing it. Use Query(ge=1, le=100) to constrain the limit to a safe range. Pagination must be added from the beginning of the project — returning all rows with no limit will cause the API to time out and consume all available memory as the table grows.

Is it safe to use Task.completed == completed directly in a SQLModel where clause?

Yes. SQLModel's expression-based filtering passes the comparison value as a parameterised query argument, not as a raw string inserted into the SQL. This means SQL injection is impossible regardless of what value the user provides. Never build SQL filters by concatenating user input into strings — always use SQLModel's expression syntax or SQLAlchemy's text() with bound parameters.

What does response_model do in a FastAPI endpoint?

response_model tells FastAPI which Pydantic schema to use when serializing the return value into JSON. Only the fields defined on the response model appear in the response — any additional fields on the returned object are stripped out. This acts as a safety boundary between the internal data model and what the API exposes to clients. It also ensures FastAPI generates accurate response schemas in the OpenAPI documentation.

Why should I not allow clients to set the resource ID in a PUT or PATCH request body?

The resource ID is the authoritative identifier for a record and comes from the URL path parameter, not the request body. Allowing a client to send a different ID in the body creates ambiguity about which record to update, could allow a client to accidentally overwrite a different record's ID, and violates REST conventions. In the update handler, always use the path parameter task_id to look up the record and ignore any id field present in the request body.

What happens if I try to delete a task that does not exist in SQLModel?

session.get(Task, task_id) returns None when no row with that ID exists. The handler should check for None and raise HTTPException(status_code=404, detail='Task not found') before calling session.delete(). Without this check, calling session.delete(None) raises an error. Always verify the resource exists before attempting to delete it.

Found this helpful? Share the knowledge...

... or support this project to keep it alive!

Buy Me a Coffee

Previous

Creating Models and Tables

Next

Dependency Injection