Databases Highlights Summary

Every highlight bullet from the 11 pages in this section, gathered on one page and grouped by the page it came from.

The Python Database Layering Model

• PEP 249 (DB-API 2.0) is the common low-level contract nearly every Python database driver implements
• SQLAlchemy Core is a dialect-aware SQL layer over a driver; the ORM is a further layer over Core, not a replacement for it
• Async access isn't 'the same driver but faster' - it's a genuinely different driver implementation, sometimes bridged with greenlet
• A connection pool's size is a concurrency ceiling you choose, and it multiplies across every worker process you run

Databases Basics

• Parameterized queries prevent SQL injection in database operations
• Keep transactions short; handle exceptions with database rollback
• Declarative models use Mapped annotations in SQLAlchemy 2.0 style
• Session context manager closes; select() replaces legacy query API
• Review Alembic migrations manually before running in production
• Treat cache as optional; database remains single source of truth

SQLAlchemy Core

• MetaData, Table, Column objects define schemas without ORM
• Use engine.begin() context to execute insert/select/update
• Core avoids ORM overhead for bulk SQL and performance-critical queries
• Access columns via Table.c to build where and values clauses
• insert().values(), update().where() compose SQL expressions directly
• metadata.create_all(engine) creates tables from table definitions

SQLAlchemy ORM 2.0

• Use selectinload to eager load relationships and prevent N+1 queries
• Define relationships with Mapped, relationship, back_populates
• Session implements unit of work for ORM object persistence
• Type relationships with Mapped annotations for type safety
• Lazy loading relationships causes N+1 queries; eager load instead
• selectinload with unique() removes duplicate rows from joins

SQLModel

• SQLModel combines Pydantic validation with SQLAlchemy ORM
• Inherit SQLModel and set table=True for database tables
• Share one model for both API validation and database
• Maintain Alembic migrations for schema changes
• Create separate read schemas for public API responses
• Pass engine to Session context manager for CRUD operations

Migrations with Alembic

• Autogenerate compares metadata against live database before human review
• Use alembic revision --autogenerate to create tracked schema changes
• Run alembic upgrade head to apply pending schema revisions to database
• Keep migrations backward compatible to support zero-downtime deployments
• Add columns with nullable=True for backward compatible schema changes
• Review autogenerated revisions before merging to catch unintended changes

Async Database Access

• Use AsyncSession with await in FastAPI async handlers and WebSocket servers
• Create one AsyncSession per request and never share sessions across concurrent tasks
• Match connection pool size to your database maximum connection limit
• High fan-out read operations achieve best performance with AsyncSession pooling
• AsyncPG dialect provides the async database driver for PostgreSQL access
• Execute queries with await then fetch results using the scalars method

Query Patterns & N+1

• N+1 queries occur when loops trigger lazy-loaded relationships
• Use selectinload for collections to batch load relationships
• Use joinedload to load single-value foreign keys
• Eager loading fetches relationships in fewer queries
• List endpoints with relations perform better with eager loading
• Profile eager-loading effectiveness with SQL logging in staging

NoSQL & Caching

• Cache-aside pattern: read cache, miss reads DB, then cache result
• Redis caches sessions, rate limits, and pub/sub notifications
• Set Redis key TTL to match your acceptable data freshness tolerance
• Use motor.motor_asyncio.AsyncIOMotorClient for async MongoDB access
• Connect Redis via redis.Redis.from_url with redis://localhost:6379/0
• MongoDB handles document structure; Redis accelerates hot key queries

Connection Management

• Set pool_size and max_overflow to handle peak load and connection storms
• Enable pool_pre_ping to drop dead connections before executing queries
• Keep transactions short with engine.begin() to return connections quickly
• Multiply pool_size by worker count to ensure adequate database capacity
• Size connection pools cautiously in serverless environments with many instances

Databases Best Practices

• Parameterize SQL queries to prevent injection attacks
• Run all schema changes through Alembic or Django migrations
• Index only the columns your queries actually filter or join
• Eager-load relationships used in list endpoints
• Paginate large result sets at the database level
• Use least-privilege database roles per service