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 19): Deploy with Docker - Serverless Containers and Custom Domain
This tutorial demonstrates how to deploy a FastAPI application to Google Cloud Run using Docker containers, implementing security headers via middleware, leveraging multi-stage builds with the UV package manager for optimization, and configuring the container for serverless deployment with proper signal handling and non-root user privileges.
Python FastAPI Tutorial (Part 18): Deploy to a VPS - Security, Nginx, SSL, and Custom Domain
Corey Schafer demonstrates how to deploy a production-ready FastAPI application to a Virtual Private Server (VPS), emphasizing fundamental deployment concepts including security hardening, SSH key authentication, and health check implementation before moving to managed cloud solutions.
Python FastAPI Tutorial (Part 17): Testing the API - Pytest, Fixtures, and Mocking External Services
This tutorial demonstrates how to implement comprehensive testing for FastAPI applications using pytest with async support, covering critical setup patterns like environment variable configuration before app imports, using AsyncClient for async endpoints, mocking AWS S3 with Moto, and maintaining a separate PostgreSQL test database to ensure production parity.
Python FastAPI Tutorial (Part 16): AWS S3 and Boto3 - Moving File Uploads to the Cloud
This tutorial demonstrates how to migrate a FastAPI application from local disk storage to AWS S3 for production file uploads, covering S3 bucket setup, IAM security configuration, and Boto3 integration while maintaining separation between image processing and storage layers.