No history yet

Async Engine Setup

The Asynchronous Engine

When moving from synchronous to asynchronous database operations in SQLAlchemy, the standard create_engine is replaced by its async counterpart, create_async_engine. This function requires a database driver that is compatible with Python's asyncio framework. For PostgreSQL, the top performer is asyncpg.

The connection string format is slightly different. You must specify the async driver in the URL, like postgresql+asyncpg://.

from sqlalchemy.ext.asyncio import create_async_engine

# The database URL specifies the asyncpg driver
DATABASE_URL = "postgresql+asyncpg://user:password@host/dbname"

# Create the asynchronous engine
engine = create_async_engine(DATABASE_URL)

A key role of the engine is managing a pool of database connections. In a high-traffic web application, creating a new connection for every request is slow and inefficient. A connection pool maintains a set of open connections that can be reused, which is critical for performance.

In an async environment, this pooling must be non-blocking. We configure it with two main parameters:

  • pool_size: The number of connections to keep open in the pool at all times.
  • max_overflow: The number of additional connections that can be temporarily created if the pool is exhausted during a traffic spike. This prevents new requests from failing, but it's a temporary measure.
# Configuring the engine with a connection pool
engine = create_async_engine(
    DATABASE_URL,
    pool_size=10,      # Keep 10 connections ready
    max_overflow=5     # Allow 5 extra connections under load
)

Crafting Async Sessions

With the engine configured, the next step is to create a factory for producing asynchronous sessions. Instead of the standard sessionmaker, we use async_sessionmaker. This factory will create AsyncSession instances that use our non-blocking engine and connection pool.

from sqlalchemy.ext.asyncio import async_sessionmaker

# Create a session factory bound to the engine
async_session = async_sessionmaker(
    bind=engine,
    expire_on_commit=False
)

The parameter expire_on_commit=False is crucial for async applications. By default, SQLAlchemy expires all instances in a session after a commit. This means the next time you access an attribute on an object that came from that session, SQLAlchemy will automatically issue a new SELECT statement to refresh its state from the database. This is called lazy loading.

In an async context, this lazy-loading operation would be a synchronous network call, which is forbidden. It would block the entire asyncio event loop, defeating the purpose of asynchronous code. Setting expire_on_commit=False tells SQLAlchemy not to expire objects after a commit. This prevents it from ever attempting an illegal synchronous lazy-load, making your objects safely accessible after the session is closed.

expire_on_commit=False is a non-negotiable setting for async SQLAlchemy. It prevents synchronous lazy-loading calls that would otherwise break the async event loop.

This combination of create_async_engine with a non-blocking pool and an async_sessionmaker configured with expire_on_commit=False gives you a robust, production-ready foundation for database interactions in a FastAPI application. Every session created from your factory will be fully asynchronous, from connection to commit.

Time to check your understanding of these core components.

Quiz Questions 1/5

When switching from synchronous to asynchronous operations in SQLAlchemy, which function should be used instead of create_engine?

Quiz Questions 2/5

What is the primary reason for setting expire_on_commit=False when creating an async_sessionmaker?

With these pieces in place, you're ready to start building asynchronous database queries.