Production Python database engineering
Build SQLAlchemy data layers that hold up in production.
Practical guidance for teams moving to SQLAlchemy 2.0, async execution, tuned connection pools, and query patterns that stay predictable under real traffic.
In-depth guides
82
Event-loop aware
async
SQLAlchemy-first
2.0
Core reading paths
Choose the work you need to unblock.
Master SQLAlchemy 2.0 ArchitectureModern Core and ORM patterns, typed mappings, session scope, transactions, and the test suite that keeps them honest.Mapped[] typingSession lifecycleTesting async codeBuild Reliable Async EnginesAsync drivers, engine configuration, pool sizing, instrumentation, and failure modes for FastAPI, Starlette and worker workloads.asyncpg vs psycopgPool exhaustionQuery instrumentationOptimize Queries and Bulk DataRelationship loading, analytical SQL, recursive CTEs, concurrent-safe upserts, and high-throughput writes without accidental N+1 behavior.selectinloadUpserts & conflictsBulk insertsRun Async Alembic MigrationsConfigure Alembic for async engines, autogenerate clean revisions, resolve multiple heads, and ship zero-downtime schema changes in CI/CD.Async env.pyMultiple headsZero-downtime DDL
Start here
Popular guides to read first.
- Async EnginesSet up an async engine from scratchThe end-to-end walkthrough for wiring create_async_engine, an async session factory, and clean startup and shutdown.
- Async EnginesFix GreenletSpawnError in async workflowsWhy lazy attribute access blows up under async, and the loading patterns that keep your FastAPI handlers safe.
- ObservabilityCount queries per request to catch N+1sA ContextVar counter that turns a silent forty-one-query endpoint into a failing test instead of a slow page.
- TestingRoll back database state between async testsThe nested-transaction fixture that resets the database in microseconds, without truncating a single table.
- Query PatternsWrite ON CONFLICT DO UPDATE upsertsThe single statement that replaces select-then-insert, and the excluded reference that silently does nothing if you get it wrong.
- ArchitectureUse mapped_column() instead of Column()Convert a legacy declarative class attribute by attribute, and prove the change produced no schema diff at all.
- Query Patternsselectinload vs joinedload for N+1Pick the right relationship loading strategy and stop your endpoints from firing a query per row.
- Alembic MigrationsResolve Alembic multiple head revisionsWhy two heads appear, what a merge revision actually does, and the CI check that stops it happening again.
- Alembic MigrationsSet up Alembic env.py for asyncpgConfigure migrations to run against an async engine so autogenerate and upgrades work with your real database.