SQLAlchemy setup with FastAPI - Performance & Optimization

Performance: SQLAlchemy setup with FastAPI

MEDIUM IMPACT

This affects the server response time and database query efficiency, impacting how fast the API can send data to the user.

Setting up database sessions in FastAPI to handle requests

FastAPI

from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine
from sqlalchemy.orm import sessionmaker

engine = create_async_engine('sqlite+aiosqlite:///./test.db')
AsyncSessionLocal = sessionmaker(engine, class_=AsyncSession, expire_on_commit=False)

async def get_db():
    async with AsyncSessionLocal() as session:
        yield session

Uses async SQLAlchemy engine and session to avoid blocking FastAPI's async event loop.

📈 Performance GainNon-blocking DB calls improve throughput and reduce API response latency

Setting up database sessions in FastAPI to handle requests

FastAPI

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine

engine = create_engine('sqlite:///./test.db')
SessionLocal = sessionmaker(bind=engine)

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

Using a synchronous SQLAlchemy session in an async FastAPI app blocks the event loop during database calls.

📉 Performance CostBlocks async event loop causing slower response times under load

Performance Comparison

Pattern	DB Connection Type	Event Loop Blocking	Response Latency	Verdict
Synchronous SQLAlchemy session	Sync	Blocks event loop	Higher latency under load	[X] Bad
Async SQLAlchemy session with async driver	Async	Non-blocking	Lower latency, better throughput	[OK] Good

Rendering Pipeline

In FastAPI, the SQLAlchemy setup affects how quickly the server can process and respond to requests by managing database connections efficiently.

→Request Handling

→Database Query Execution

→Response Sending

⚠️ BottleneckSynchronous DB sessions block async request handling, delaying responses.

Optimization Tips

1Avoid synchronous SQLAlchemy sessions in async FastAPI apps to prevent blocking.

2Use async SQLAlchemy engine and sessions with async drivers like aiosqlite or asyncpg.

3Create and close database sessions per request using FastAPI dependencies to manage resources efficiently.

Performance Quiz - 3 Questions

Why is using a synchronous SQLAlchemy session in FastAPI problematic for performance?

A It causes the browser to re-render unnecessarily.

B It increases the size of the database.

C It blocks the async event loop causing slower API responses.

D It reduces the number of database connections.

DevTools: Network and Performance panels

How to check: Use Network panel to measure API response times; use Performance panel to check if server responses are delayed due to blocking calls.

What to look for: Look for long server response times and blocked async tasks indicating synchronous DB calls.