Mastering Database Development in the Backend: ORM Best Practices from Real-World Incidents

In today’s backend systems, the use of Object Relational Mappers (ORMs) like SQLAlchemy, Django ORM, Prisma, or Entity Framework has become the standard for interacting with relational databases. While they offer a powerful abstraction, real-world experience teaches us that misusing ORMs can lead to severe production issues, particularly around database connections, session handling, and performance bottlenecks.

In this blog, I’ll walk through real incidents I faced in production systems and distill those lessons into actionable best practices.

⚠️ The Production Incident: Stuck Requests and Open DB Connections

Scenario:

We started noticing that API requests were intermittently timing out or getting stuck. Upon further investigation, the PostgreSQL database had reached the max_connections limit. Every new request resulted in an error: too many connections.

Root Cause:

SQLAlchemy sessions were not being closed explicitly.

Sessions were being created in routes and long-lived background tasks without proper teardown.

The connection pool was exhausted due to leaked connections.

Resolution:

# Use context manager to ensure automatic session close
from contextlib import contextmanager

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

Then in FastAPI route:

@app.get("/users")
def read_users(db: Session = Depends(get_db)):
    return db.query(User).all()

✅ Best Practice: Always use a context-managed session lifecycle. Never leave sessions open!

🔁 Connection Pool Misconfiguration

Scenario:

Under peak load, our web app randomly failed with:

sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached

Root Cause:

We were using the default pool size (usually 5). Our application handled concurrent requests (100+), but the pool couldn’t handle more than 15 simultaneous connections (5 base + 10 overflow).

Resolution:

from sqlalchemy import create_engine

engine = create_engine(
    DATABASE_URL,
    pool_size=20,
    max_overflow=10,
    pool_timeout=30,
    pool_pre_ping=True  # avoids stale connections
)

✅ Best Practice: Tune pool_size, max_overflow, and pool_timeout based on your app's concurrency profile. Use pool_pre_ping to prevent stale connection usage.

🧼 Zombie Sessions in Background Jobs

Scenario:

We had a batch job (running hourly) that performed customer invoice aggregation. Over time, performance degraded and connections were left hanging in idle in transaction state.

Root Cause:

Sessions were created in job workers and never closed.

Background workers don’t get the same dependency injection lifecycle as FastAPI routes.

Resolution:

Wrap job logic with session management:

def run_invoice_batch():
    db = SessionLocal()
    try:
        # Business logic
        ...
    finally:
        db.close()

✅ Best Practice: Explicitly manage session lifecycle in async jobs, Celery tasks, or custom threads.

🐢 Lazy Loading Pitfalls

Scenario:

Our customer dashboard suddenly slowed down after a schema change. We moved some user metadata into a related table, assuming ORM joins would handle it.

Root Cause:

The ORM defaulted to lazy-loading for relationships. A list of 100 users with 3 related tables = 300 SQL queries.

Resolution:

# Use joinedload to eager load relationships
from sqlalchemy.orm import joinedload

users = db.query(User).options(
    joinedload(User.profile),
    joinedload(User.permissions)
).all()

✅ Best Practice: Analyze query plans for N+1 issues. Use eager loading for related data that’s always needed.

🚦 Health Monitoring & Logging

Incident:

Database load randomly spiked and we had no visibility.

Resolution:

Enabled SQL query logging with query time metrics.

Set up Prometheus exporters for DB metrics.

Added logging of session creation/destruction in custom middlewares.

✅ Best Practice: Monitor query count, average latency, connection pool status, and long-running transactions.

Summary: ORM Best Practices

Area Best Practice Session Management Always close sessions using context managers or finally blocks Connection Pooling Tune pool_size, max_overflow, pool_timeout, enable pool_pre_ping Async Jobs Manually handle session lifecycle in tasks or threads Query Optimization Use eager loading to avoid N+1 query problems Monitoring Log queries, monitor pool and transaction metrics

Final Thoughts

ORMs offer powerful abstractions, but they hide complex database interactions under the hood. Real-world production issues often stem from invisible misuse of sessions and pools. By following these best practices, and treating your ORM like a database driver, not just a magic box, you can ensure your backend stays stable, performant, and scalable.

If you’ve faced your own ORM horror stories, feel free to share in the comments.