Database session management in FastAPI - Step-by-Step Execution

Concept Flow - Database session management

Start API request

↓

Create DB session

↓

Use session in endpoint

↓

Commit or rollback transaction

↓

Close DB session

↓

Return response

↓

End API request

This flow shows how a database session is created at the start of a request, used during the request, then committed or rolled back, and finally closed before sending the response.

Execution Sample

FastAPI

from fastapi import Depends
from sqlalchemy.orm import Session

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

This code creates a database session for each request, yields it for use, and ensures it closes after the request finishes.

Execution Table

Step	Action	Session State	Result
1	API request starts	No session	Prepare to create session
2	Create DB session with SessionLocal()	Session open	Session ready for queries
3	Yield session to endpoint	Session open	Endpoint uses session
4	Endpoint runs DB operations	Session open	Queries executed, changes staged
5	Commit or rollback transaction	Session open	Changes saved or discarded
6	Close DB session in finally block	Session closed	Resources freed
7	Return response to client	Session closed	Request complete

💡 Session closed after request ends to avoid resource leaks

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 6	Final
db	None	Session object	Session object (yielded)	Closed session	Closed session

Key Moments - 3 Insights

Why do we use 'yield' in the get_db function?

Using 'yield' allows the session to be used during the request and ensures the 'finally' block runs after, closing the session as shown in steps 3 and 6 of the execution_table.

What happens if we forget to close the session?

If the session is not closed (step 6), database connections stay open, causing resource leaks and possible errors in future requests.

When do we commit or rollback the transaction?

After endpoint operations (step 4), before closing the session (step 5), to save or discard changes made during the request.

Visual Quiz - 3 Questions

Look at the execution_table, what is the session state right after creating the DB session?

A Session open

B Session closed

C No session

D Session yielded

💡 Hint

Check Step 2 in the execution_table for session state after creation

At which step does the session get closed?

A Step 4

B Step 6

C Step 5

D Step 7

💡 Hint

Look for the step mentioning 'Close DB session' in the execution_table

If we remove the 'finally' block, what likely changes in the variable_tracker?

A db variable closes earlier at Step 4

B db variable becomes None after Step 3

C db variable stays as 'Session object' after Step 6

D db variable is never created

💡 Hint

Refer to variable_tracker and key_moments about session closing

Concept Snapshot

Database session management in FastAPI:
- Create session at request start
- Use 'yield' to provide session to endpoints
- Commit or rollback after DB operations
- Close session in 'finally' to free resources
- Prevents connection leaks and ensures safe DB access

Full Transcript

In FastAPI, database session management means creating a session when a request starts, using it during the request, then committing or rolling back changes, and finally closing the session to free resources. The get_db function uses 'yield' to provide the session to endpoints and ensures the session closes after the request finishes. This prevents resource leaks and keeps the database connections healthy. The session state changes from no session, to open, to closed as the request progresses. Closing the session is critical and happens in the 'finally' block. Forgetting to close the session can cause errors and resource exhaustion.