Skip to content

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] = document

That 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
→ browser

This 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.


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.py

The 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 handling

The Pydantic model and SQLAlchemy model represent related data, but they remain separate objects with separate jobs.


Here is app/database.py:

from collections.abc import Generator
from typing import Annotated
from fastapi import Depends
from sqlalchemy import create_engine
from 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:

engine
SessionLocal
Base
get_db
DbSession

They are connected, but they are not interchangeable.


The connection URL is:

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

The first part selects SQLite:

sqlite

The three slashes indicate a relative file path:

./review.db

The database will therefore be stored in a file named:

review.db

relative 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.


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 work

Creating 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


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 work

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 object

The factory is bound to:

bind=engine

Sessions it creates therefore know which engine to use when they need a database connection.


The setting:

autoflush=False

prevents 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 transaction

We 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.


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=False

This 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.


We define:

class Base(DeclarativeBase):
pass

Base 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 names
Columns
Data types
Primary keys
Unique constraints
Indexes

The metadata describes the schema. It is not the physical schema inside SQLite until SQL is executed to create or migrate those tables.


Now look at:

def get_db() -> Generator[Session, None, None]:
with SessionLocal() as db:
yield db

This 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 db

FastAPI 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 Session

SQLAlchemy 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


We now have two objects called “session” in the application:

request.session

and:

db: Session

They 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 work

The 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.


We define:

DbSession = Annotated[
Session,
Depends(get_db),
]

This is the same dependency-alias pattern we used for:

CurrentUserDep

A route can now declare:

db: DbSession

FastAPI sees that declaration, calls get_db(), and injects its yielded Session.

The value inside the route is an actual SQLAlchemy session:

isinstance(db, Session)
# True

It is not a Depends object and not the SessionLocal factory.


Here is app/models/documents.py:

from sqlalchemy import String, UniqueConstraint
from 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:

  1. It describes the documents database table.
  2. 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 class declares:

__tablename__ = "documents"

This tells SQLAlchemy that the mapped database table is named:

documents

The Python class uses the singular name:

Document

The database table uses the plural name:

documents

That is a convention, not a technical requirement. Consistency is more important than singular versus plural.


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 KEY

For 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 caller

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) + 1

We 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 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 unique

These two rows are allowed:

Firm 10 | september website review
Firm 20 | september website review

These are not:

Firm 10 | september website review
Firm 10 | september website review

In 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 inserts

Both 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.


For this first database installment, imagine the application temporarily performs:

from .database import Base, engine
from .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 column
Rename a column
Transform existing data
Remove an old constraint

That is Alembic’s job, which we will introduce after understanding the database request flow.


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: str

The key configuration is:

from_attributes=True

It tells Pydantic that values may come from object attributes:

document.id
document.title
document.status

rather than only dictionary keys:

document["id"]
document["title"]
document["status"]

This lets FastAPI serialize a SQLAlchemy object through the Pydantic response model.


Here is the database-backed service:

import sqlite3
from sqlalchemy.exc import IntegrityError
from 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 document

The service now requires:

db: Session

That makes persistence an explicit dependency of the business operation.


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 exists
Database row does not

Its ID is not yet known:

document.id
# Usually None at this stage

SQLite will assign the ID when SQLAlchemy performs the insert.


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 inserted

db.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.


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:

10
1
September Website Review
september website review
website
4
not_started

This 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


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:

IntegrityError

The 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:
raise

the 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 exc

The 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
→ browser

Each layer communicates in its own language.


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,
status
FROM documents
WHERE id = ?;

The refreshed object contains the stored values, including the primary key that SQLAlchemy already obtained during the insert:

document.id
# 203

The service returns the ORM object:

return document

It does not convert it into a dictionary.


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 DbSession
from ..dependencies import CurrentUserDep
from ..schemas.documents import DocumentCreate, DocumentRead
from ..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 document

FastAPI prepares:

payload
→ Pydantic request-body validation
current_user
→ Authentication dependency
db
→ Database-session dependency

The route then passes them into the service.


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 driver

Mixing 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 route returns:

document

That is a SQLAlchemy Document object.

FastAPI applies:

response_model=DocumentRead

Pydantic reads:

document.id
document.firm_id
document.title
document.document_type
document.priority
document.status

It 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 Created
Content-Type: application/json

The browser parses the JSON and can update the page.


For a successful document submission:

  1. The browser sends JSON and its signed session cookie.
  2. Session middleware reconstructs the browser session.
  3. The router matches POST /documents/.
  4. The authentication dependency retrieves the current user.
  5. get_db() creates a SQLAlchemy Session.
  6. Pydantic validates the parsed JSON as DocumentCreate.
  7. FastAPI injects all three objects into the route.
  8. The route calls the document service.
  9. The service constructs a Document ORM object.
  10. db.add() attaches it to the session.
  11. db.commit() causes SQLAlchemy to generate an INSERT.
  12. SQLite validates the unique constraint.
  13. SQLite writes the row and commits the transaction.
  14. db.refresh() reloads the persisted row.
  15. The service returns the SQLAlchemy object.
  16. The route returns that object.
  17. Pydantic reads its attributes through DocumentRead.
  18. FastAPI serializes the response as JSON.
  19. Middleware processes the outgoing response.
  20. The server sends 201 Created.
  21. The browser receives the persisted document.
  22. 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 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 transaction

The service uses those components without becoming an HTTP endpoint.

The route remains the translation layer:

HTTP and Pydantic objects
→ service call
→ SQLAlchemy result
→ HTTP response

We 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?