By WebNest Studio Engineering Team
Python Tutorial
Building a CRUD API with FastAPI and SQLAlchemy
Real APIs store data in a database. This lesson combines everything so far — path and query parameters, Pydantic models, response models, status codes and dependencies — with the SQLAlchemy ORM from the Database Connectivity module to build a complete CRUD API for a task tracker.
You will see the standard structure used in production FastAPI projects: an engine and a session dependency, ORM models for tables, separate Pydantic schemas for input and output, and endpoints that translate between them. The example uses SQLite, but switching to PostgreSQL or MySQL only means changing the database URL.
Why it matters
An API that keeps its data in a Python list loses everything when it restarts. Connecting endpoints to a database is the step that turns the examples so far into a real back end, and the create, list, read, update and delete endpoints built here are the pattern behind most business applications.
The Moving Parts
- Engine —
create_engine(DATABASE_URL), created once. SQLite needsconnect_args={"check_same_thread": False}because FastAPI may use the session from another thread. - Session dependency — a
yielddependency that opens aSessionper request and always closes it. - ORM models — classes mapped to tables (
models.py). - Schemas — Pydantic models for requests and responses (
schemas.py):TaskCreate,TaskUpdate,TaskOut. - from_attributes —
model_config = ConfigDict(from_attributes=True)lets a response schema read attributes straight from an ORM object.
Endpoint Patterns
Create: build an ORM object from payload.model_dump(), add, commit, refresh (to load generated ids and defaults) and return it with 201. Read one: session.get(Model, id), raising 404 when it is None. List: select() with filters, order_by, offset and limit. Update: apply model_dump(exclude_unset=True) with setattr. Delete: session.delete() and return 204. Catch IntegrityError to turn duplicate values into 409 Conflict.
Async Drivers and SQLModel
This lesson uses synchronous SQLAlchemy in plain def endpoints, which FastAPI runs in a thread pool — simple and perfectly fast for most apps. For very high concurrency you can use SQLAlchemy's asyncio extension (create_async_engine, AsyncSession) with async drivers such as asyncpg or aiosqlite in async def endpoints. SQLModel, by FastAPI's author, merges SQLAlchemy models and Pydantic schemas into one class — convenient for small projects.
Examples
A complete task-tracker CRUD API
from datetime import datetime, timezone
from typing import Annotated
from fastapi import Depends, FastAPI, HTTPException, Query, status
from fastapi.testclient import TestClient
from pydantic import BaseModel, ConfigDict, Field
from sqlalchemy import String, create_engine, select
from sqlalchemy.exc import IntegrityError
from sqlalchemy.orm import DeclarativeBase, Mapped, Session, mapped_column
from sqlalchemy.pool import StaticPool
# --- database.py ---------------------------------------------------------
DATABASE_URL = "sqlite://" # in-memory; use "sqlite:///tasks.db" or a PostgreSQL URL
engine = create_engine(DATABASE_URL, connect_args={"check_same_thread": False}, poolclass=StaticPool)
class Base(DeclarativeBase):
pass
def get_session():
with Session(engine) as session: # closed automatically after the response
yield session
SessionDep = Annotated[Session, Depends(get_session)]
# --- models.py -------------------------------------------------------------
class Task(Base):
__tablename__ = "tasks"
id: Mapped[int] = mapped_column(primary_key=True)
title: Mapped[str] = mapped_column(String(200), unique=True)
priority: Mapped[int] = mapped_column(default=3)
done: Mapped[bool] = mapped_column(default=False)
created_at: Mapped[datetime] = mapped_column(default=lambda: datetime.now(timezone.utc))
Base.metadata.create_all(engine)
# --- schemas.py ------------------------------------------------------------
class TaskCreate(BaseModel):
title: str = Field(min_length=1, max_length=200)
priority: int = Field(default=3, ge=1, le=5)
class TaskUpdate(BaseModel):
title: str | None = Field(default=None, min_length=1, max_length=200)
priority: int | None = Field(default=None, ge=1, le=5)
done: bool | None = None
class TaskOut(BaseModel):
model_config = ConfigDict(from_attributes=True) # read from ORM objects
id: int
title: str
priority: int
done: bool
# --- main.py ---------------------------------------------------------------
app = FastAPI(title="Tasks API")
def get_task_or_404(session: Session, task_id: int) -> Task:
task = session.get(Task, task_id)
if task is None:
raise HTTPException(status.HTTP_404_NOT_FOUND, detail="Task not found")
return task
@app.post("/tasks", status_code=status.HTTP_201_CREATED)
def create_task(payload: TaskCreate, session: SessionDep) -> TaskOut:
task = Task(**payload.model_dump())
session.add(task)
try:
session.commit()
except IntegrityError:
session.rollback()
raise HTTPException(status.HTTP_409_CONFLICT, detail="A task with this title already exists")
session.refresh(task)
return task
@app.get("/tasks")
def list_tasks(session: SessionDep, done: bool | None = None,
offset: Annotated[int, Query(ge=0)] = 0,
limit: Annotated[int, Query(ge=1, le=100)] = 20) -> list[TaskOut]:
query = select(Task).order_by(Task.priority, Task.id)
if done is not None:
query = query.where(Task.done == done)
return session.scalars(query.offset(offset).limit(limit)).all()
@app.get("/tasks/{task_id}")
def read_task(task_id: int, session: SessionDep) -> TaskOut:
return get_task_or_404(session, task_id)
@app.patch("/tasks/{task_id}")
def update_task(task_id: int, changes: TaskUpdate, session: SessionDep) -> TaskOut:
task = get_task_or_404(session, task_id)
for field, value in changes.model_dump(exclude_unset=True).items():
setattr(task, field, value)
session.commit()
session.refresh(task)
return task
@app.delete("/tasks/{task_id}", status_code=status.HTTP_204_NO_CONTENT)
def delete_task(task_id: int, session: SessionDep):
session.delete(get_task_or_404(session, task_id))
session.commit()
# --- trying it out ---------------------------------------------------------
client = TestClient(app)
for title, priority in [("Write report", 2), ("Review PR", 1), ("Plan sprint", 3)]:
print(client.post("/tasks", json={"title": title, "priority": priority}).json())
print(client.post("/tasks", json={"title": "Review PR"}).status_code)
print(client.patch("/tasks/2", json={"done": True}).json())
print([t["title"] for t in client.get("/tasks").json()])
print([t["title"] for t in client.get("/tasks?done=false").json()])
print(client.delete("/tasks/3").status_code, client.get("/tasks/3").json())
{'id': 1, 'title': 'Write report', 'priority': 2, 'done': False}
{'id': 2, 'title': 'Review PR', 'priority': 1, 'done': False}
{'id': 3, 'title': 'Plan sprint', 'priority': 3, 'done': False}
409
{'id': 2, 'title': 'Review PR', 'priority': 1, 'done': True}
['Review PR', 'Write report', 'Plan sprint']
['Write report', 'Plan sprint']
204 {'detail': 'Task not found'}
Switching databases is a one-line change
# .env
DATABASE_URL=postgresql+psycopg://tasks_app:secret@localhost:5432/tasks
# database.py
import os
from sqlalchemy import create_engine
DATABASE_URL = os.environ.get("DATABASE_URL", "sqlite:///tasks.db")
connect_args = {"check_same_thread": False} if DATABASE_URL.startswith("sqlite") else {}
engine = create_engine(DATABASE_URL, connect_args=connect_args, pool_pre_ping=True)
(no output — the rest of the application is unchanged)
Common Mistakes
- Creating a global Session shared by all requests instead of one session per request via a dependency.
- Returning ORM objects without from_attributes=True in the response schema.
- Forgetting session.refresh() after commit and returning objects without generated ids or defaults.
- Using the same Pydantic model for create, update and output.
- Calling Base.metadata.create_all() to change existing tables in production — use Alembic migrations.
Exercise
Using the in-memory SQLite database that is already set up, add POST /tasks, which inserts a task and returns it with its id and status 201, and GET /tasks, which returns all tasks.
Expected output: {'id': 1, 'title': 'write tests'} then [{'id': 1, 'title': 'write tests'}]
import sqlite3
from fastapi import FastAPI
from fastapi.testclient import TestClient
from pydantic import BaseModel
# check_same_thread=False lets the request handlers, which run in worker threads, share it
connection = sqlite3.connect(":memory:", check_same_thread=False)
connection.execute("CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT)")
app = FastAPI()
class TaskIn(BaseModel):
title: str
# TODO: POST /tasks (status 201): insert the task and return {"id": ..., "title": ...}
# TODO: GET /tasks: return a list of {"id": ..., "title": ...} for every row
client = TestClient(app)
print(client.post("/tasks", json={"title": "write tests"}).json())
print(client.get("/tasks").json())
Show hints
- Insert with a placeholder and read the new id from
cursor.lastrowid; commit afterwards. - Build the list with a comprehension over
fetchall().
Show solution
import sqlite3
from fastapi import FastAPI
from fastapi.testclient import TestClient
from pydantic import BaseModel
# check_same_thread=False lets the request handlers, which run in worker threads, share it
connection = sqlite3.connect(":memory:", check_same_thread=False)
connection.execute("CREATE TABLE tasks (id INTEGER PRIMARY KEY, title TEXT)")
app = FastAPI()
class TaskIn(BaseModel):
title: str
@app.post("/tasks", status_code=201)
def create_task(task: TaskIn):
cursor = connection.execute("INSERT INTO tasks (title) VALUES (?)", (task.title,))
connection.commit()
return {"id": cursor.lastrowid, "title": task.title}
@app.get("/tasks")
def list_tasks():
rows = connection.execute("SELECT id, title FROM tasks ORDER BY id").fetchall()
return [{"id": row[0], "title": row[1]} for row in rows]
client = TestClient(app)
print(client.post("/tasks", json={"title": "write tests"}).json())
print(client.get("/tasks").json())
Quick quiz
1. How should a database session normally be given to an endpoint in FastAPI?
- A. As a global variable used directly
- B. Through a dependency that opens it and closes it after the request
- C. By opening a new connection in every line
- D. Through the URL
Show answer
B. A yield dependency guarantees the session is closed even when the endpoint fails.
2. Why are the Pydantic schema and the database model usually separate classes?
- A. FastAPI requires it
- B. To make the code longer
- C. One describes what the API accepts and returns; the other describes how data is stored
- D. They cannot share field names
Show answer
C. Keeping them apart lets the stored form and the public form change independently.
3. What should an endpoint return when the requested row does not exist?
- A. An empty 200 response
- B. null with status 201
- C. A 500 error
- D. A 404 error
Show answer
D. Check the result of the query and raise HTTPException(status_code=404).
Interview questions
How do you manage database sessions in a FastAPI application?
Show answer
Create the engine and a session factory once at start-up. Write a dependency that opens a session, yields it to the endpoint, and closes it in a finally block, and declare it in each endpoint with Depends. Every request then gets its own session, which is always released, and tests can override the dependency to use a different database.
Should database endpoints be defined with def or async def?
Show answer
It depends on the driver. With a blocking driver, such as the standard synchronous SQLAlchemy session, use def: FastAPI runs such endpoints in a thread pool, so they do not block the event loop. Use async def only with an asynchronous driver and await. Calling a blocking database function inside an async def endpoint stalls every other request.
Key Points to Remember
- Engine once, Session per request through a yield dependency.
- ORM models describe tables; Pydantic schemas describe the API.
- from_attributes=True lets response models read ORM objects.
- CRUD: add/commit/refresh, session.get, select with filters and pagination, setattr updates, delete.
- Translate database errors (IntegrityError) into proper HTTP status codes.
Practice in the playground
Write and run your own code for this topic, or start from the exercise or an example. A layered FastAPI project with a SQLite database, running in your browser. Your code for each topic is kept in this browser.
Run the examples
Change an input, predict the result, then compare it with the output. Explain why the result changes.
Written by
WebNest Studio Engineering Team
Practical lessons from developers working with Java, Python, React, databases and production systems.
Led by Vansh Duggal, Founder, WebNest Studio · Full-stack & software engineering