GuideIntermediate
PostgreSQL & SQLAlchemy Guide
Learn PostgreSQL from installation to optimized queries, model your data with the SQLAlchemy ORM, manage your schema with Alembic, and integrate everything asynchronously with FastAPI. This guide replaces in-memory data with real, professional persistence, exactly as it is used in production. Built for developers who have already built APIs with FastAPI and need to take the next step: a well-designed relational database that scales.
- 48
- lessons
- 6
- modules
- English · Spanish
- available in
- Yes
- certificate
- Included in the Club
- access
Outcomes
What you'll be able to do
- Install and configure PostgreSQL locally and with Docker, master psql and pgAdmin
- Design schemas with advanced types (UUID, JSONB, TIMESTAMP) and constraints
- Write complex SQL queries with JOINs, subqueries, CTEs and aggregations
- Model 1:N and N:M relationships with foreign keys and junction tables
- Optimize performance with B-tree indexes and EXPLAIN ANALYZE
- Ensure data integrity with ACID transactions and isolation levels
- Model data in Python with SQLAlchemy 2.0 ORM (Mapped, mapped_column, relationships)
- Run CRUD with Sessions and queries with eager/lazy loading
- Version your schema with Alembic: autogenerate, upgrade/downgrade, data migrations
- Integrate PostgreSQL with FastAPI using AsyncSession, dependency injection and connection pooling
Before you start
What you need to bring
It's for you if...
- Developers who completed FastAPI Fundamentals and FastAPI Advanced and need real persistence
- Backend developers from other languages (Node, Java, Go) who want to master the PostgreSQL + Python stack
- Programmers who have used basic SQL but never designed schemas with complex relationships or versioned migrations
- Students of the Backend Python Developer Path building the Blog Platform API project
- Developers who want to learn SQLAlchemy 2.0 (the modern, typed API), not the legacy one
Requirements and materials
- Intermediate Python (classes, async/await, type hints, decorators)
- FastAPI Fundamentals completed (path operations, Pydantic, basic dependency injection)
- FastAPI Advanced Features completed (advanced Depends, APIRouter, response models)
- Familiarity with the command line (terminal/shell)
- Docker installed (recommended to skip local install friction)
Content
The syllabus, module by module
Open any of them to see its lessons.
- Module 1 Introduction: PostgreSQL Fundamentals
- Installation: PostgreSQL local and with Docker
- psql: the CLI you'll use every day
- pgAdmin: exploring the DB visually
- PostgreSQL data types
- CREATE TABLE and constraints
- INSERT, SELECT, UPDATE, DELETE
- Module Project: the Blog's `users` table
- Module 2 Introduction: SQL Queries and Relationships
- Foreign keys and 1:N relationships
- JOINs: INNER, LEFT, RIGHT, FULL
- N:M relationships with junction tables
- Subqueries
- CTEs (Common Table Expressions)
- Aggregations: GROUP BY and HAVING
- Module Project: the Blog's complete schema
- Introduction to Module 3: Indexes and Transactions
- What is an index? B-tree and types
- EXPLAIN ANALYZE: measuring queries
- Indexes in practice
- Transactions: BEGIN, COMMIT, ROLLBACK
- ACID in real-world scenarios
- Isolation levels and concurrency
- Module Project: indexes and transactions in the Blog
- Introduction to Module 4: SQLAlchemy ORM
- Setup: Engine, Session, DeclarativeBase
- Basic models: Mapped and mapped_column
- Relationships: relationship() and back_populates
- CRUD with Session: add, commit, refresh, delete
- Queries with select(): filter, join, group_by
- Eager vs Lazy Loading: the N+1 problem
- Module Project: the blog in SQLAlchemy + Repository pattern
- Introduction to Module 5: Alembic Migrations
- Initializing Alembic in the project
- The initial migration: the "point zero"
- Autogenerate: the day-to-day flow
- Manual migrations: when autogenerate falls short
- Data migrations: migrating data alongside the schema
- Team workflow and CI/CD
- Module Project: the versioned blog with migrations
- Introduction to Module 6: FastAPI + Async SQLAlchemy
- Async SQLAlchemy: AsyncEngine and AsyncSession
- FastAPI + dependency injection
- CRUD endpoints with async repositories
- Connection pooling and performance
- Error handling and validation with Pydantic
- Async testing with pytest-asyncio
- Final Project: the complete blog API
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
As long as your Club subscription is active. If you cancel and come back later, you get the access and your progress back.
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.
Yes. It's issued automatically once you finish every lesson, with a verifiable code you can share on LinkedIn.
No. This guide is self-paced with no dates. The bootcamp is live, by cohort, with work someone reviews.
Start whenever you like
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!