SQLAlchemy 2.x and AsyncSession for FastAPI APIs (Part 2)

In the previous article, we learned how SQLAlchemy integrates with FastAPI, how AsyncSession is created and injected, and how to perform CRUD operations using the SQLAlchemy 2.x API.

In this article, we will explore how SQLAlchemy behaves in production applications and learn the best practices for building scalable, reliable, and high-performance FastAPI APIs.

Understanding Transactions

Every database operation occurs inside a transaction, whether developers explicitly create one or not.

Suppose a user registration process inserts a user, creates a profile, and records an audit entry. All three operations must either succeed together or fail together. A transaction guarantees exactly that.

The lifecycle of a transaction looks like this:

BEGIN
   ↓
Execute SQL
- Insert User
- Insert Profile
- Insert Audit Record
   ↓
COMMIT

If all operations complete successfully, the transaction is committed and the changes become permanent. If an error occurs before the commit, SQLAlchemy performs a rollback, ensuring that none of the partial changes are persisted.

Understanding commit()

Consider:

user = User(
    name="John",
    email="john@example.com"
)
db.add(user)

At this point, the User object exists only inside the session. The database has not yet been modified. The changes are permanently persisted to the database only after:

await db.commit()

Until commit() executes successfully, every modification remains part of the current transaction.

Understanding flush()

Calling flush() sends SQL statements to the database without committing the transaction.

db.add(user)
await db.flush()

The INSERT statement executes immediately, but the transaction remains open. If a rollback occurs later, the inserted row disappears because the transaction was never committed.

This makes flush() particularly useful when database-generated values such as primary keys are required before the transaction completes.

Understanding autoflush

By default, SQLAlchemy automatically calls flush() before executing certain database operations, such as queries. This behavior is known as autoflush.

Consider the following example:

user = User(
    name="John",
    email="john@example.com"
)

db.add(user)

result = await db.execute(
    select(User)
)

Before executing the SELECT statement, SQLAlchemy automatically flushes any pending changes to the database. This ensures that the query sees the most up-to-date state of the current transaction.

It is important to understand that autoflush does not commit the transaction. The transaction remains active, and all changes can still be rolled back until commit() is called.

flush() synchronizes pending changes with the database, while commit() permanently persists those changes. Autoflush simply invokes flush() automatically when required.

Understanding refresh()

Suppose the database automatically generates values such as a primary key or timestamp.

await db.commit()
await db.refresh(user)

refresh() reloads the latest state of the ORM object from the database.

This ensures that values generated by the database, such as primary keys, timestamps, or trigger-generated fields, are available inside the Python object.

Rollback and Error Recovery

Consider:

try:
    db.add(user)
    await db.commit()

except Exception:
    await db.rollback()
    raise

If commit() fails because of a constraint violation, deadlock, or network interruption, rollback() restores the database to its previous consistent state.

Without rollback(), the session cannot continue executing additional database operations.

Relationship Mapping

Suppose one user owns multiple orders.

User → One-To-Many → Orders

SQLAlchemy models these relationships using relationship().

class User(Base):
    __tablename__ = "users"

    id: Mapped[int] = mapped_column(primary_key=True)

    orders = relationship(
        "Order",
        back_populates="user"
    )

Relationships allow developers to navigate related objects using Python attributes instead of writing manual joins for every query.

Lazy Loading

By default, relationships use lazy loading. Suppose we retrieve a User.

user = await db.get(User, 1)

The related orders are not retrieved immediately. Instead, SQLAlchemy loads them only when accessed.

user.orders

Although this reduces unnecessary work, it can also introduce performance problems.

The N+1 Query Problem

Consider retrieving 100 users. If each user's orders are loaded separately, SQLAlchemy executes one query to retrieve the users, followed by 100 additional queries to load each user's orders.

This results in a total of 101 SQL queries, a problem known as the N+1 Query Problem. It is one of the most common ORM performance issues because it significantly increases the number of database round trips.

Eager Loading

Instead of loading relationships one at a time, SQLAlchemy can retrieve everything using a single optimized query.

from sqlalchemy.orm import selectinload

result = await db.execute(
    select(User).options(
        selectinload(User.orders)
    )
)

Instead of issuing a separate query for every user's orders, SQLAlchemy loads the related objects efficiently, significantly reducing the number of database round trips.

Connection Pooling

Creating a database connection is an expensive operation. Instead of opening a new connection for every request, SQLAlchemy maintains a pool of reusable connections.

When a request requires database access, SQLAlchemy borrows a connection from the pool. After the request completes, the connection is returned to the pool for reuse.

Reusing existing connections avoids the overhead of repeatedly opening and closing database connections, significantly improving application performance.

Connection Leaks

One of the most common production problems is connection leakage. A leaked connection is a connection that is never returned to the connection pool.

Eventually, the connection pool becomes exhausted, forcing new requests to wait until a connection becomes available.

Even if the database is healthy, the application may become slow or stop processing requests because no connections are available.

Using Dependency Injection together with yield-based sessions prevents most connection leaks.

Conclusion

SQLAlchemy 2.x provides much more than object-relational mapping. It offers a complete framework for transaction management, relationship mapping, connection pooling, and efficient database access.

Understanding concepts such as transactions, commit(), flush(), autoflush, refresh(), relationship loading, and connection pooling is essential for building scalable and reliable FastAPI applications.