09 · SQLAlchemy, SQLite, and the Request-Scoped Session
In the previous lesson, our service performed the document-creation workflow but stored the result in a Python dictionary:
documents_by_id[document_id] = documentThat storage was temporary:
- Restarting the application erased it.
- Multiple server processes would have separate dictionaries.
- Concurrent requests could generate conflicting IDs.
- Other tools could not reliably access the data.
Today we replace that dictionary with SQLite and SQLAlchemy.
The lifecycle becomes:
Browser→ middleware→ router→ dependencies→ Pydantic→ route→ service→ SQLAlchemy Session→ SQLite→ SQLAlchemy object→ response model→ browserThis is the first time a request will travel all the way into a real database and back.
The user dependency still returns the dictionary introduced earlier. Today we are replacing document storage, so current_user["firm_id"] remains correct. The creation example assumes that user belongs to a firm.
The old dictionary records do not move into SQLite automatically. IDs such as 203 below are illustrative; an empty table will ordinarily assign 1 to its first row.
The new structure
Section titled “The new structure”Our application now includes database and ORM model modules:
app/├── main.py├── database.py├── dependencies.py├── models/│ ├── __init__.py│ └── documents.py├── schemas/│ └── documents.py├── services/│ └── documents.py└── routers/ └── documents.pyThe responsibilities are:
database.py└── Engine, session factory, and database dependency
models/documents.py└── SQLAlchemy mapping for the documents table
schemas/documents.py└── Pydantic input and output contracts
services/documents.py└── Document-creation business operation
routers/documents.py└── HTTP request and response handlingThe Pydantic model and SQLAlchemy model represent related data, but they remain separate objects with separate jobs.
Configuring SQLite
Section titled “Configuring SQLite”Here is app/database.py:
from collections.abc import Generatorfrom typing import Annotated
from fastapi import Dependsfrom sqlalchemy import create_enginefrom sqlalchemy.orm import ( DeclarativeBase, Session, sessionmaker,)
DATABASE_URL = "sqlite:///./review.db"
engine = create_engine( DATABASE_URL, connect_args={ "check_same_thread": False, },)
SessionLocal = sessionmaker( bind=engine, autoflush=False, expire_on_commit=False,)
class Base(DeclarativeBase): pass
def get_db() -> Generator[Session, None, None]: with SessionLocal() as db: yield db
DbSession = Annotated[ Session, Depends(get_db),]There are several different objects here:
engineSessionLocalBaseget_dbDbSessionThey are connected, but they are not interchangeable.
The database URL
Section titled “The database URL”The connection URL is:
DATABASE_URL = "sqlite:///./review.db"The first part selects SQLite:
sqliteThe three slashes indicate a relative file path:
./review.dbThe database will therefore be stored in a file named:
review.dbrelative to the directory from which the application process is started.
That final detail matters. The path is not automatically relative to database.py.
For a deployed application, configuration often supplies an absolute database path so the location does not depend on the process’s working directory.
The SQLAlchemy engine
Section titled “The SQLAlchemy engine”We create the engine:
engine = create_engine( DATABASE_URL, connect_args={ "check_same_thread": False, },)The engine is SQLAlchemy’s main interface to a particular database configuration.
It knows:
- Which database system is being used
- How to construct connections
- Which SQL dialect to generate
- How connections may be reused
The engine is not the SQLite database itself.
It is also not the database session used by one request.
A useful mental model is:
Engine└── Long-lived application-level connection manager
Session└── Short-lived unit of database workCreating the engine does not necessarily open the SQLite file immediately. SQLAlchemy usually establishes a database connection when one is actually needed.
SQLAlchemy describes the engine as a factory for database connections that can also manage reusable connections through a pool. SQLAlchemy ORM quick start
check_same_thread
Section titled “check_same_thread”SQLite connections ordinarily restrict their use to the thread in which they were created.
Our configuration includes:
connect_args={ "check_same_thread": False,}FastAPI may handle related portions of a synchronous request using worker threads. This setting allows the SQLite connection to operate in that environment.
It does not mean one SQLAlchemy Session should be shared freely across requests or threads.
We still create a separate session for each request.
The rule remains:
Engine→ shared across the application
Session→ scoped to one request or one unit of workThe session factory
Section titled “The session factory”Next we create:
SessionLocal = sessionmaker( bind=engine, autoflush=False, expire_on_commit=False,)sessionmaker() creates a factory.
That means SessionLocal is not yet one active database session.
Calling it creates one:
db = SessionLocal()The relationship is:
SessionLocal└── Factory capable of creating Session objects
SessionLocal()└── One actual Session objectThe factory is bound to:
bind=engineSessions it creates therefore know which engine to use when they need a database connection.
autoflush=False
Section titled “autoflush=False”The setting:
autoflush=Falseprevents SQLAlchemy from automatically flushing pending changes before certain queries.
A flush means:
Send pending SQL statements to the database inside the current transaction.
A flush is not the same as a commit.
flush→ Send SQL to the database, but keep the transaction open
commit→ Finalize the transactionWe will explicitly call commit() in our service, so disabling autoflush makes the timing somewhat easier to reason about.
Both approaches can be valid. What matters is understanding when pending Python-side changes become SQL.
expire_on_commit=False
Section titled “expire_on_commit=False”By default, SQLAlchemy may expire ORM object attributes after a commit so that subsequent access reloads current values from the database.
We configure:
expire_on_commit=FalseThis allows us to continue reading the returned document’s attributes after committing without automatically triggering reloads for every attribute.
We will still call:
db.refresh(document)explicitly after the commit. That makes a database round trip visible and reloads the stored values. The generated primary key will already have been obtained during the insert; refresh() is not what creates or first retrieves it.
The declarative base
Section titled “The declarative base”We define:
class Base(DeclarativeBase): passBase becomes the parent class for our SQLAlchemy ORM models.
A mapped model will look like:
class Document(Base): ...Inheriting from Base connects the class to SQLAlchemy’s mapping system and shared metadata.
The metadata becomes a Python description of the database schema:
Table namesColumnsData typesPrimary keysUnique constraintsIndexesThe metadata describes the schema. It is not the physical schema inside SQLite until SQL is executed to create or migrate those tables.
The database dependency
Section titled “The database dependency”Now look at:
def get_db() -> Generator[Session, None, None]: with SessionLocal() as db: yield dbThis is a FastAPI dependency using yield.
When a request needs a database session, FastAPI enters the dependency and runs:
with SessionLocal() as db:SessionLocal() creates a new Session.
The with statement establishes a context manager that will close the session after the operation finishes.
The dependency then pauses at:
yield dbFastAPI takes that db object and injects it into the route.
With the default request scope used here, FastAPI resumes cleanup after the response has been sent. The with block exits and closes the session. If an uncommitted transaction remains, closing the session releases it through rollback; it does not silently commit our changes.
Conceptually:
get_db() begins ↓Create Session ↓Yield Session to route ↓Route calls service ↓Service uses Session ↓Route returns response ↓FastAPI serializes and sends response ↓Resume get_db() ↓Close SessionSQLAlchemy recommends treating the session as an active database resource with a clearly limited lifecycle. SQLAlchemy session basics
FastAPI also supports other cleanup scopes. Our declaration uses the default request scope, which is why a later streaming lesson must avoid retaining this dependency for a long-lived response. FastAPI dependency cleanup
This is not the browser session
Section titled “This is not the browser session”We now have two objects called “session” in the application:
request.sessionand:
db: SessionThey are completely different.
request.session└── Authentication-related browser session└── Reconstructed from a signed cookie└── Contains values such as user_id
db Session└── SQLAlchemy database session└── Manages queries, ORM objects, and transactions└── Exists for one database unit of workThe browser session helps answer:
Who made this request?
The SQLAlchemy session helps answer:
What database work belongs to this request?
The shared word “session” is unfortunate, but the systems are unrelated.
The reusable dependency alias
Section titled “The reusable dependency alias”We define:
DbSession = Annotated[ Session, Depends(get_db),]This is the same dependency-alias pattern we used for:
CurrentUserDepA route can now declare:
db: DbSessionFastAPI sees that declaration, calls get_db(), and injects its yielded Session.
The value inside the route is an actual SQLAlchemy session:
isinstance(db, Session)# TrueIt is not a Depends object and not the SessionLocal factory.
The SQLAlchemy model
Section titled “The SQLAlchemy model”Here is app/models/documents.py:
from sqlalchemy import String, UniqueConstraintfrom sqlalchemy.orm import Mapped, mapped_column
from ..database import Base
class Document(Base): __tablename__ = "documents"
__table_args__ = ( UniqueConstraint( "firm_id", "normalized_title", name="uq_documents_firm_normalized_title", ), )
id: Mapped[int] = mapped_column( primary_key=True, )
firm_id: Mapped[int] = mapped_column( index=True, )
created_by_user_id: Mapped[int]
title: Mapped[str] = mapped_column( String(200), )
normalized_title: Mapped[str] = mapped_column( String(200), )
document_type: Mapped[str] = mapped_column( String(30), )
priority: Mapped[int]
status: Mapped[str] = mapped_column( String(30), default="not_started", )This class serves two roles:
- It describes the
documentsdatabase table. - It gives Python an ORM class representing rows from that table.
The document begins with status="not_started": no review has been requested yet. Later, starting a review will move it into an active state. This keeps a newly created document separate from work that is already waiting to run.
SQLAlchemy calls this a declarative mapping: one class describes both a Python object model and database table metadata. SQLAlchemy declarative models
The table name
Section titled “The table name”The class declares:
__tablename__ = "documents"This tells SQLAlchemy that the mapped database table is named:
documentsThe Python class uses the singular name:
DocumentThe database table uses the plural name:
documentsThat is a convention, not a technical requirement. Consistency is more important than singular versus plural.
Mapped and mapped_column
Section titled “Mapped and mapped_column”Consider:
id: Mapped[int] = mapped_column( primary_key=True,)The annotation:
Mapped[int]means this is a SQLAlchemy-mapped attribute whose Python value will be an integer.
The right side:
mapped_column(primary_key=True)adds database-column configuration.
SQLAlchemy uses these declarations to infer a column conceptually resembling:
id INTEGER NOT NULL PRIMARY KEYFor strings, we give the database type more detail:
title: Mapped[str] = mapped_column( String(200),)This describes a string column with a declared maximum length of 200.
One practical warning: SQLite does not enforce every declared string length the way some other databases do. Pydantic still enforces the title length at the HTTP boundary.
This is an example of layered responsibility:
Pydantic→ Validates normal browser submissions
Database constraints→ Protect persistent data regardless of callerThe primary key
Section titled “The primary key”The ID column is:
id: Mapped[int] = mapped_column( primary_key=True,)The primary key uniquely identifies each database row.
Previously, our service calculated:
max(documents_by_id) + 1We no longer need that.
SQLite assigns the integer primary key when the row is inserted.
This is safer because the database coordinates ID generation even when requests arrive close together.
The unique constraint
Section titled “The unique constraint”The model declares:
__table_args__ = ( UniqueConstraint( "firm_id", "normalized_title", name="uq_documents_firm_normalized_title", ),)This creates a combined uniqueness rule:
firm_id + normalized_title must be uniqueThese two rows are allowed:
Firm 10 | september website reviewFirm 20 | september website reviewThese are not:
Firm 10 | september website reviewFirm 10 | september website reviewIn the previous lesson, Python searched the dictionary for a duplicate.
That check alone has a concurrency problem:
Request A checks→ No duplicate exists
Request B checks→ No duplicate exists
Request A inserts
Request B insertsBoth requests could pass the Python check before either inserts.
A database unique constraint makes SQLite the final authority. Even if two requests race, only one conflicting row can be committed.
Creating the table
Section titled “Creating the table”For this first database installment, imagine the application temporarily performs:
from .database import Base, enginefrom .models.documents import Document
Base.metadata.create_all(bind=engine)Importing Document causes its table definition to be registered with Base.metadata.
Then:
Base.metadata.create_all(bind=engine)asks SQLAlchemy to create tables that do not yet exist.
SQLAlchemy generates SQL conceptually resembling:
CREATE TABLE documents ( id INTEGER NOT NULL, firm_id INTEGER NOT NULL, created_by_user_id INTEGER NOT NULL, title VARCHAR(200) NOT NULL, normalized_title VARCHAR(200) NOT NULL, document_type VARCHAR(30) NOT NULL, priority INTEGER NOT NULL, status VARCHAR(30) NOT NULL, PRIMARY KEY (id), CONSTRAINT uq_documents_firm_normalized_title UNIQUE (firm_id, normalized_title));This keeps today’s example self-contained, but create_all() is not a complete schema-migration system.
It can create a missing table. It does not safely describe a history such as:
Add a columnRename a columnTransform existing dataRemove an old constraintThat is Alembic’s job, which we will introduce after understanding the database request flow.
Updating the Pydantic response model
Section titled “Updating the Pydantic response model”Our service will now return a SQLAlchemy Document object rather than a dictionary.
Keep DocumentCreate and the DocumentType alias from lesson 7 in app/schemas/documents.py. Replace its DocumentRead definition with:
from pydantic import BaseModel, ConfigDict
class DocumentRead(BaseModel): model_config = ConfigDict( from_attributes=True, )
id: int firm_id: int title: str document_type: DocumentType priority: int status: strThe key configuration is:
from_attributes=TrueIt tells Pydantic that values may come from object attributes:
document.iddocument.titledocument.statusrather than only dictionary keys:
document["id"]document["title"]document["status"]This lets FastAPI serialize a SQLAlchemy object through the Pydantic response model.
The updated service
Section titled “The updated service”Here is the database-backed service:
import sqlite3
from sqlalchemy.exc import IntegrityErrorfrom sqlalchemy.orm import Session
from ..models.documents import Document
class DuplicateDocumentTitleError(Exception): pass
def create_document( *, db: Session, firm_id: int, created_by_user_id: int, title: str, document_type: str, priority: int,) -> Document: document = Document( firm_id=firm_id, created_by_user_id=created_by_user_id, title=title, normalized_title=title.casefold(), document_type=document_type, priority=priority, status="not_started", )
db.add(document)
try: db.commit()
except IntegrityError as exc: db.rollback()
is_title_conflict = ( isinstance(exc.orig, sqlite3.IntegrityError) and exc.orig.sqlite_errorcode == sqlite3.SQLITE_CONSTRAINT_UNIQUE and str(exc.orig) == ( "UNIQUE constraint failed: " "documents.firm_id, documents.normalized_title" ) )
if not is_title_conflict: raise
raise DuplicateDocumentTitleError( f'A document named "{title}" already exists' ) from exc
db.refresh(document)
return documentThe service now requires:
db: SessionThat makes persistence an explicit dependency of the business operation.
Creating an ORM object
Section titled “Creating an ORM object”The service constructs:
document = Document( firm_id=firm_id, created_by_user_id=created_by_user_id, title=title, normalized_title=title.casefold(), document_type=document_type, priority=priority, status="not_started",)At this moment, Python has created an ORM object.
SQLite does not yet contain the row.
The object is in a transient state:
Python object existsDatabase row does notIts ID is not yet known:
document.id# Usually None at this stageSQLite will assign the ID when SQLAlchemy performs the insert.
Adding the object to the session
Section titled “Adding the object to the session”The service calls:
db.add(document)This associates the object with the SQLAlchemy session.
The object becomes pending:
Here, pending is SQLAlchemy’s name for an object’s persistence state. The object’s business field still says status="not_started". Those two uses describe different things.
Transient→ Python object only
Pending→ Attached to Session, waiting to be inserted
Persistent→ Corresponding database row has been inserteddb.add() does not necessarily execute the INSERT immediately.
It tells the session:
Include this object in the current unit of work.
The session tracks the change until it flushes.
Committing the transaction
Section titled “Committing the transaction”The service calls:
db.commit()This is the critical persistence operation.
SQLAlchemy flushes pending changes, generating SQL resembling:
INSERT INTO documents ( firm_id, created_by_user_id, title, normalized_title, document_type, priority, status)VALUES (?, ?, ?, ?, ?, ?, ?);The question marks are parameter placeholders.
The values are sent separately:
101September Website Reviewseptember website reviewwebsite4not_startedThis parameterized structure is important. SQLAlchemy does not build the statement by pasting untrusted title text directly into the SQL string.
SQLite executes the insert inside a transaction.
If it succeeds, SQLAlchemy commits:
COMMIT;The record is now durable in review.db.
Restarting FastAPI will not erase it.
SQLAlchemy documents that Session.commit() flushes pending changes and commits the active database transaction. SQLAlchemy persistence flow
Handling a database conflict
Section titled “Handling a database conflict”The commit is inside:
try: db.commit()
except IntegrityError as exc:If the unique constraint is violated, SQLite rejects the insert.
SQLAlchemy represents the database failure as:
IntegrityErrorThe transaction is now in a failed state, so the service must call:
db.rollback()Rollback tells the database:
Discard the uncommitted work in this transaction and return the session to a usable state.
An IntegrityError can also represent a different problem, such as a required value being missing. We should not describe every rejected insert as a duplicate title.
That is why the service checks the original SQLite exception:
is_title_conflict = ( isinstance(exc.orig, sqlite3.IntegrityError) and exc.orig.sqlite_errorcode == sqlite3.SQLITE_CONSTRAINT_UNIQUE and str(exc.orig) == ( "UNIQUE constraint failed: " "documents.firm_id, documents.normalized_title" ))The error code identifies a uniqueness failure. The diagnostic text identifies the two columns involved. This small check is deliberately specific to our SQLite table; a different database would need its own diagnostic handling. Python exposes SQLite’s error code on the original exception. Python sqlite3 documentation
If this is a different integrity failure:
if not is_title_conflict: raisethe bare raise lets that original failure propagate after rollback. We do not relabel it as a business conflict.
Only the matching duplicate-title failure becomes our application-level error:
raise DuplicateDocumentTitleError( f'A document named "{title}" already exists') from excThe route can translate that into the same 409 Conflict response introduced previously.
The translation chain is now:
SQLite unique-constraint failure→ SQLAlchemy IntegrityError→ service DuplicateDocumentTitleError→ route HTTPException→ HTTP 409→ browserEach layer communicates in its own language.
Refreshing the object
Section titled “Refreshing the object”After a successful commit:
db.refresh(document)This asks SQLAlchemy to reload the row from SQLite.
Conceptually, it performs a query resembling:
SELECT id, firm_id, created_by_user_id, title, normalized_title, document_type, priority, statusFROM documentsWHERE id = ?;The refreshed object contains the stored values, including the primary key that SQLAlchemy already obtained during the insert:
document.id# 203The service returns the ORM object:
return documentIt does not convert it into a dictionary.
The updated route
Section titled “The updated route”The route now receives three prepared objects:
The excerpt below retains the existing router and adds the database dependency import. It isolates persistence, as the previous creation lesson did. Cookie-authenticated writes also need CSRF protection; the explicit write-protection dependency is introduced in lesson 14.
from fastapi import HTTPException, status
from ..database import DbSessionfrom ..dependencies import CurrentUserDepfrom ..schemas.documents import DocumentCreate, DocumentReadfrom ..services.documents import ( DuplicateDocumentTitleError, create_document,)
@router.post( "/", response_model=DocumentRead, status_code=status.HTTP_201_CREATED,)def create_document_route( payload: DocumentCreate, current_user: CurrentUserDep, db: DbSession,): try: document = create_document( db=db, firm_id=current_user["firm_id"], created_by_user_id=current_user["id"], title=payload.title, document_type=payload.document_type, priority=payload.priority, )
except DuplicateDocumentTitleError as exc: raise HTTPException( status_code=status.HTTP_409_CONFLICT, detail=str(exc), ) from exc
return documentFastAPI prepares:
payload→ Pydantic request-body validation
current_user→ Authentication dependency
db→ Database-session dependencyThe route then passes them into the service.
Why this route uses def
Section titled “Why this route uses def”Notice that the route is now:
def create_document_route(rather than:
async def create_document_route(We are using synchronous SQLAlchemy and SQLite calls.
Database operations such as:
db.commit()db.refresh(document)can block while waiting for database work.
FastAPI runs normal def path operations in a worker thread, preventing this synchronous database work from directly blocking the main event loop.
A production application should choose a coherent approach:
Synchronous SQLAlchemy→ normal def routes/services→ simpler SQLite setup
Asynchronous SQLAlchemy→ async def routes/services→ async engine and async driverMixing synchronous database work directly into an async def route can block the event loop.
For a small SQLite-backed modular monolith, the synchronous approach is boring, understandable, and entirely reasonable.
The response journey
Section titled “The response journey”The route returns:
documentThat is a SQLAlchemy Document object.
FastAPI applies:
response_model=DocumentReadPydantic reads:
document.iddocument.firm_iddocument.titledocument.document_typedocument.prioritydocument.statusIt creates the public response representation:
{ "id": 203, "firm_id": 10, "title": "September Website Review", "document_type": "website", "priority": 4, "status": "not_started"}FastAPI serializes it into JSON and returns:
HTTP/1.1 201 CreatedContent-Type: application/jsonThe browser parses the JSON and can update the page.
The complete request lifecycle
Section titled “The complete request lifecycle”For a successful document submission:
- The browser sends JSON and its signed session cookie.
- Session middleware reconstructs the browser session.
- The router matches
POST /documents/. - The authentication dependency retrieves the current user.
get_db()creates a SQLAlchemySession.- Pydantic validates the parsed JSON as
DocumentCreate. - FastAPI injects all three objects into the route.
- The route calls the document service.
- The service constructs a
DocumentORM object. db.add()attaches it to the session.db.commit()causes SQLAlchemy to generate anINSERT.- SQLite validates the unique constraint.
- SQLite writes the row and commits the transaction.
db.refresh()reloads the persisted row.- The service returns the SQLAlchemy object.
- The route returns that object.
- Pydantic reads its attributes through
DocumentRead. - FastAPI serializes the response as JSON.
- Middleware processes the outgoing response.
- The server sends
201 Created. - The browser receives the persisted document.
- The database session closes after the request finishes.
The database session exists only for this unit of work. The engine remains available for future requests.
The list follows this route’s successful path. The framework may parse the JSON bytes before resolving dependencies; the important guarantee is that authentication and validation both succeed before the service is called.
The central idea
Section titled “The central idea”The SQLAlchemy model, engine, and session solve different parts of persistence:
Model└── Describes a table and represents its rows as Python objects
Engine└── Knows how to connect and communicate with SQLite
Session└── Tracks one unit of database work and its transactionThe service uses those components without becoming an HTTP endpoint.
The route remains the translation layer:
HTTP and Pydantic objects→ service call→ SQLAlchemy result→ HTTP responseWe now have durable data, but the table was created using Base.metadata.create_all().
The next lesson introduces Alembic and answers the next production question:
What happens when the application evolves and an existing database table must evolve with it?