Python FastAPI Tutorial (Part 15): PostgreSQL and Alembic - Database Migrations for Production
TL;DR
This tutorial transitions a FastAPI application from SQLite to PostgreSQL for production readiness, implementing Alembic for database migrations to enable safe, version-controlled schema changes without data loss.
🐘 Production Database Infrastructure 3 insights
Why PostgreSQL beats SQLite
PostgreSQL handles concurrent writes and connections far better than SQLite, making it the industry standard for production FastAPI applications while helping developers catch database-specific issues locally before deployment.
Local PostgreSQL setup options
Developers can install PostgreSQL via Homebrew (Mac), apt (Linux), Windows installer, or Docker, with version 18 used as the example in the tutorial.
Creating database users
Setting up a dedicated user with password (e.g., 'blog_user'/'blog_pass') mimics production credential requirements rather than using passwordless local development.
🔌 Async Database Connectivity 3 insights
Modern Python PostgreSQL driver
The tutorial adopts psycopg (version 3) with the binary extra, which supports both synchronous and asynchronous operations and serves as the natural upgrade from psycopg2.
Environment-based configuration
Hardcoded SQLite paths are replaced with Pydantic settings that read DATABASE_URL from environment variables or .env files to securely manage credentials.
Async SQLAlchemy integration
SQLAlchemy automatically uses async mode with create_async_engine when paired with psycopg, maintaining the application's asynchronous architecture.
🔄 Database Migration Strategy 3 insights
The create_all problem
Using SQLAlchemy's create_all in application startup only creates tables if they don't exist, failing to add new columns to existing tables and requiring database deletion to sync schema changes.
Alembic fundamentals
Alembic, created by SQLAlchemy author Mike Bayer, provides version control for database schemas through migration files containing upgrade() and downgrade() functions.
Async initialization
Initializing Alembic with the -t async flag generates an async-compatible template, requiring configuration in alembic.ini (empty SQLAlchemy URL) and env.py (importing models, Base, and settings).
Bottom Line
Replace SQLite and create_all with PostgreSQL and Alembic migrations before deploying to production to ensure safe, incremental schema evolution without data loss.
More from Corey Schafer
View all
Python FastAPI Tutorial (Part 14): Password Reset - Email, Tokens, and Background Tasks
This tutorial demonstrates implementing a secure password reset flow in FastAPI using cryptographically secure hashed tokens stored in a database, asynchronous email sending via aiostmplib to prevent blocking, and Jinja2 templates for HTML emails, following security best practices like one-hour expiration and single-use validation.
Python FastAPI Tutorial (Part 13): Pagination - Loading More Data with Query Parameters
This tutorial demonstrates how to implement offset-based pagination in FastAPI using skip and limit query parameters, covering backend schema design with SQLAlchemy queries and frontend integration with a 'load more' button pattern.
Python FastAPI Tutorial (Part 12): File Uploads - Image Processing, Validation, and Storage
Corey Schafer demonstrates implementing secure profile picture uploads in FastAPI using Pillow for image resizing and format standardization, with proper handling of CPU-bound tasks in async contexts and safe file transaction patterns to prevent data loss.
Python FastAPI Tutorial (Part 11): Authorization - Protecting Routes and Verifying Current User
This tutorial demonstrates how to implement proper authorization in FastAPI by creating a reusable dependency that validates JWT tokens and retrieves the current user, enabling secure route protection and ownership verification while eliminating hard-coded user IDs.