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
154
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[] typingCascades & relationshipsTesting async codeBuild Reliable Async EnginesAsync drivers, concurrent sessions, pool sizing for cloud and serverless, instrumentation, and failure modes for FastAPI and worker workloads.Concurrent queriesPool exhaustionServerless & IAMOptimize Queries and Bulk DataRelationship loading, analytical SQL, JSONB and full-text search, concurrent-safe upserts, and high-throughput writes without accidental N+1 behavior.Hybrid propertiesJSONB & arraysBulk COPYRun Async Alembic MigrationsConfigure Alembic for async engines, autogenerate clean revisions, manage enums and indexes, and ship zero-downtime schema changes in CI/CD.Async env.pyEnums & indexesZero-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.
Recently added
Relationships, expressions on the model, and production migrations.
- ArchitectureConfigure cascade delete and delete-orphanThe ORM cascade and the database ON DELETE are different mechanisms; pick one per relationship and know which loads rows.
- Query PatternsWrite hybrid properties that work in SQL tooOne expression that serves attribute access and a WHERE clause, instead of a property you cannot filter by.
- Async EnginesUse one async engine across threads and loopsWhy a pooled asyncpg connection cannot move between event loops, and the three arrangements that avoid it.
- TransactionsBuild a job queue with SKIP LOCKEDRow locks hand each worker a different job with no coordination — plus the visibility timeouts a queue really fails on.
- Alembic MigrationsMigrate an integer primary key to bigintA shadow column, a batched backfill and a swap measured in seconds, instead of an ALTER that locks the table.
- Cloud DatabasesSize pools behind PgBouncer and RDS ProxyTwo pools in series, transaction-mode pooling, and the asyncpg setting that prepared statements require.