GuideAdvanced
SQL Patterns for Production APIs Guide
Master the SQL patterns every production multi-tenant API needs: cursor pagination (17x faster than OFFSET on deep pages), correct soft deletes, audit logs, Row-Level Security multi-tenancy, zero-downtime migrations, optimistic locking, schema versioning, and bulk operations with COPY — fully integrated with FastAPI and SQLAlchemy 2.0 async.
- 64
- lessons
- 8
- modules
- English · Spanish
- available in
- Free
- access
NIEVA
Outcomes
What you'll be able to do
- Implement cursor and keyset pagination with SQLAlchemy 2.0 (17x faster than OFFSET on deep pages) with composite cursors for secondary sort
- Model soft deletes with partial indexes and avoid the anti-patterns that destroy performance (plus knowing when to use archive tables or partitioning instead)
- Design audit logs with PostgreSQL triggers, history tables, or lightweight event sourcing, choosing the right approach per case
- Implement multi-tenancy correctly with Row-Level Security (RLS), schema-per-tenant, or shared schema — with quantified trade-offs
- Execute zero-downtime migrations with expand-contract, `CREATE INDEX CONCURRENTLY`, `lock_timeout`, and `statement_timeout` using Alembic in production
- Handle concurrent updates with optimistic locking (version columns) and translate `StaleDataError` to HTTP 409 with useful client feedback
- Version schemas with backward-compatible changes, deprecation strategies, and contract testing
- Run bulk operations at PostgreSQL speed: `COPY` with `copy_expert`, batch inserts, upserts with `ON CONFLICT DO UPDATE`, with measured benchmarks
- Build a production-ready multi-tenant SaaS API consolidating all seven patterns with a live zero-downtime migration
Before you start
What you need to bring
It's for you if...
- Senior backend Python developers with FastAPI apps in production who have hit real problems: slow deep-page pagination, leaky multi-tenancy, migrations that broke deploys
- Engineers who completed the PostgreSQL & SQLAlchemy and Database Performance guides and need the patterns that come after "you know how to optimize queries"
- Engineers building multi-tenant SaaS who need to choose an isolation architecture (RLS vs schema-per-tenant vs shared schema)
- Tech leads writing team guidelines for how pagination, soft deletes, and migrations should be done
- Developers preparing for senior backend interviews at SaaS companies where cursor pagination, RLS, and zero-downtime migrations are asked
- Anyone who has had to run an `ALTER TABLE` in production at 3am and would rather not repeat the experience
Requirements and materials
- PostgreSQL & SQLAlchemy guide completed (or equivalent: SQLAlchemy 2.0 ORM with relationships, basic Alembic, ACID, isolation levels)
- Database Performance & Query Tuning guide completed (or equivalent: EXPLAIN ANALYZE, advanced indexing, N+1 with SQLAlchemy)
- Functional FastAPI app with real data (>100k rows in at least one table) for hands-on practice
- PostgreSQL 14+ installed locally or in Docker (some RLS features require 14+)
- Familiarity with `psql`, Alembic CLI, and FastAPI dependency injection
- Comfort with async/await in Python
Content
The syllabus, module by module
Open any of them to see its lessons.
- Introduction: Pagination Patterns for Production APIs
- OFFSET pagination and its limits
- Cursor pagination: fundamentals
- Cursor pagination in FastAPI
- Keyset pagination and when to use it
- Bidirectional pagination and opaque cursors
- Pagination with filters and dynamic ordering
- Module 1 deliverable: Cursor Pagination in TaskFlow
- Correct Soft Deletes — Module introduction
- Soft delete vs hard delete: trade-offs
- Implementing `deleted_at` in PostgreSQL
- Soft delete in SQLAlchemy: mixins and events
- Partial indexes for soft deletes
- Anti-patterns: `WHERE deleted_at IS NULL` everywhere
- Alternatives: archive tables and partitioning
- Module 2 deliverable: Soft Delete in TaskFlow
- Audit Logs and History Tables — Module introduction
- What to audit and why
- PostgreSQL triggers for auditing
- The history tables pattern
- Lightweight vs full event sourcing
- Auditing with SQLAlchemy event listeners
- Retention and partitioning of audit logs
- Module 3 deliverable: Audit Trail in TaskFlow (standalone)
- Module 5: Zero-Downtime Migrations
- Why migrations break production
- The expand-contract pattern
- `CREATE INDEX CONCURRENTLY` and other non-blocking operations
- `lock_timeout` and `statement_timeout` in migrations
- Alembic in production: safe patterns
- Blue-green and database rollback strategies
- Module 5 deliverable: a live zero-downtime migration
- Module 6: Optimistic Locking + Schema Versioning
- Optimistic vs pessimistic locking: when each one wins
- Version columns in SQLAlchemy 2.0: the native implementation
- `StaleDataError` → HTTP 409 with an informative body
- The `If-Match` header and HTTP 412: optimistic concurrency at the HTTP level
- Schema versioning: backward-compatible vs breaking changes
- Deprecation strategy: headers, metrics, communication
- Mini-project: TaskFlow with optimistic locking + 3 schema versions
- Module 7: Bulk Operations
- The 4 approaches with real benchmarks
- `COPY` in depth with asyncpg
- `bulk_insert_mappings` and its limitations
- Atomic upserts with `ON CONFLICT`
- Bulk upserts with a temp table: the canonical pattern
- Robust error handling + streaming for gigantic datasets
- Mini-project: `POST /tasks/bulk` with benchmarks
- Module 8: Final Project — TaskFlow API
- Initial setup: FastAPI + SQLAlchemy + Alembic
- Multi-tenancy with Row-Level Security + mock auth
- Task CRUD + cursor pagination + soft delete
- Audit log with triggers + optimistic locking
- Bulk endpoint + live zero-downtime migration
- Complete tests: isolation, integration, benchmarks
- Final documentation + guide wrap-up
Where it fits
This guide is part of something bigger
It's studied inside these programs, with support and dates.
Common questions
What people usually ask
No limit. It's a free guide: come in whenever you like, as often as you like.
No. Modules run from easier to harder, but you can jump to the one you need. Progress is saved per lesson.
Whatever is needed is listed under “What you need to bring”, above. If nothing is listed there, you can start from zero.
In the Club's WhatsApp group, and every two weeks there's a live with an instructor where questions get worked through.
No. This guide is self-paced with no dates. The bootcamp is live, by cohort, with work someone reviews.
Start whenever you like
Reviews
What students say
These reviews are from enrolled students who completed at least 50% of the course. We moderate reviews only on content grounds (spam, offensive language, personal data), never for being critical or negative.
No approved reviews yet.
Be the first to share your experience!