Request-Scoped Database Sessions

1. The "Session-per-Request" Pattern

Before we write code, let's understand the problem we're solving. To interact with a database, your application needs to establish a connection. A naive approach might be to create one global connection when the app starts. This is problematic because concurrent requests would all try to use the same connection, leading to conflicts, data corruption, and resource contention.

The standard solution is the session-per-request pattern:

1. For every incoming API request, a new, unique database session is created.
2. The endpoint function uses this session to perform its database operations (e.g., query for users, create a post).
3. Once the endpoint has finished and a response is sent, the session is closed, and its connection is returned to a connection pool.

This ensures that each request has its own isolated "conversation" with the database, preventing interference. Critically, the session must be closed even if an error occurs in the endpoint, to avoid leaking connections.

2. FastAPI's Dependency Injection System

Manually managing this open/use/close lifecycle in every single endpoint would be repetitive and error-prone. This is where FastAPI's dependency injection system shines.

We will create a special function (a "dependency") whose only job is to provide a database session. Our endpoint functions will then simply declare that they depend on this function. FastAPI takes care of the rest:

• It calls our dependency function before running the endpoint.
• It passes the result of the dependency (the database session) to the endpoint.
• It handles the cleanup logic after the endpoint is done.

The following video provides an excellent conceptual overview of creating this dependency. Although the implementation shown is synchronous, the core idea of using a generator with yield to manage the resource is exactly what we will be doing.

3. Implementing the Asynchronous Database Setup

Given your proficiency with async/await, we will use SQLAlchemy's modern asynchronous capabilities. This is highly efficient for web applications as it allows the server to handle other requests while waiting for database operations to complete.

First, you'll need to install the necessary asynchronous driver for SQLite:

pip install aiosqlite

Now, create a new file in your project's root directory named database.py. This file will contain all our database connection logic.

We will add three key components:

1. DATABASE_URL: A string that tells SQLAlchemy how to connect to our database.
2. create_async_engine: The core source of database connectivity. It manages a pool of low-level database connections.
3. async_sessionmaker: A factory that we'll use to create new AsyncSession instances. We configure it once here.

The final and most important piece is the dependency function itself, which we'll call get_async_session. It uses an async with block and yield to implement the session-per-request pattern elegantly and safely.

The following resource clearly demonstrates this exact setup.

4. Hands-On: Creating database.py

Let's put it all together. Create the database.py file and add the following code.

# database.py

from collections.abc import AsyncGenerator
from sqlalchemy.ext.asyncio import AsyncSession, async_sessionmaker, create_async_engine
from models import Base # Import Base from your models.py file




# Define the database URL for SQLite.
# 'sqlite+aiosqlite:///' means we're using the aiosqlite driver
# and the database file is named 'blog.db' in the root directory.
DATABASE_URL = "sqlite+aiosqlite:///./blog.db"




# Create the async engine.
# echo=True will log all SQL statements, which is useful for debugging.
engine = create_async_engine(DATABASE_URL, echo=True)




# Create a configured "Session" class.
# expire_on_commit=False prevents attributes from being expired after commit.
async_session_maker = async_sessionmaker(engine, expire_on_commit=False)





# This function will create the database tables based on your models.
# It's an async function that you can call when your app starts up.
async def create_db_and_tables():
    async with engine.begin() as conn:
        await conn.run_sync(Base.metadata.create_all)





# This is our dependency. It's an async generator.
async def get_async_session() -> AsyncGenerator[AsyncSession, None]:
    """
    Dependency that provides an SQLAlchemy async session per request.
    """
    async with async_session_maker() as session:
        yield session

Let's break down the get_async_session function:

• async def ... -> AsyncGenerator[...]: It's defined as an asynchronous generator.
• async with async_session_maker() as session:: This line gets a new session from our factory. The async with statement ensures that the session is automatically closed when the block is exited.
• yield session: This is the magic. The dependency provides the session object to the endpoint. It then pauses here, waiting for the endpoint to finish its work. Once the response is sent, FastAPI resumes the function, the async with block exits, and the session is closed. This happens for every single request, guaranteed.

5. A Glimpse of How It's Used

You now have a robust, reusable dependency for database access. In our upcoming lessons, you will use it like this:

from fastapi import APIRouter, Depends
from sqlalchemy.ext.asyncio import AsyncSession
from database import get_async_session

router = APIRouter()

@router.get("/posts")
async def get_all_posts(db: AsyncSession = Depends(get_async_session)):



    # You can now use the 'db' object to run queries!
    # result = await db.execute(...)
    ...

Notice the db: AsyncSession = Depends(get_async_session) part in the function signature. This tells FastAPI: "Before you run get_all_posts, please run get_async_session and pass its yielded value to me as the db argument."

Conclusion

Fantastic work! You have built the essential plumbing that connects your application logic to your database. This session management dependency is a cornerstone of any production-ready FastAPI application.

Key Takeaways:

• The session-per-request pattern is a best practice for managing database connections in a web application.
• FastAPI's dependency injection system (Depends) automates resource management, making code cleaner and safer.
• An async dependency using a generator (yield) within an async with block is the modern, idiomatic way to manage asynchronous resources like database sessions.
• Our database.py file now centralizes all our database connection configuration.

In the very next lesson, we will immediately put this dependency to use. We will implement our first two database-driven endpoints: one to list all records from a table and another to retrieve a single record by its ID.