🌳 C:\Users\soura\Documents\mine\fastapi\blog
├── 📁 alembic
| ├── 📄 env.py
| ├── 📄 README
| ├── 📄 script.py.mako
| └── 📁 versions
├── 📄 alembic.ini
├── 📁 apis
| └── 📄 deps.py
├── 📁 core
| └── 📄 config.py
├── 📁 database
| ├── 📄 db.py
| └── 📁 models
| ├── 📄 __init__.py # new
| ├── 📄 base.py
| ├── 📄 blog.py
| ├── 📄 user.py
├── 📄 docker-compose.yaml
├── 📄 Dockerfile
├── 📄 main.py
└── 📄 requirements.txt
Have you ever heard of horror story when someone accidentally messes up the production database table? 😱
Let's imagine we built a fastapi app and we have database all running in production server. However, now need to update the datatype of a column or we need to drop a column from a table. I can't tell you how scary is running raw SQL ALTER statement on a production system. For this reason we use migration tools like alembic. They are like GIT for databases. They help in applying new changes in a smooth way and roll back changes too.
What the Heck is Alembic?
Alembic is like a version control system for your database schema. Just like how Git tracks changes in your code, Alembic tracks changes in your database structure.
Setting Up Alembic in Our FastAPI Project
Let's add Alembic to our existing project.
First, let's add alembic to our requirements.txt:
fastapi==0.115.12
uvicorn==0.27.1
python-dotenv==1.1.1
psycopg2-binary==2.9.10
sqlmodel==0.0.24
alembic==1.16.4
Now, let's initialize Alembic in our project. Intializing alembic will create new files and folders which are useful to alembic. To initilize it, execute the command:
docker compose exec -it web alembic init alembic
This command creates the alembic directory structure. It's like setting up the foundation of our migration system.
Before we move further let's bring all our models in one single file so that alembic can import the models and get an understanding of their relationship. We should create a file database/models/__init__.py
from database.models.base import BaseModel
from database.models.user import User
from database.models.blog import Blog
The alembic/env.py file is the heart of our migration system. We can define the sqlalchemy.url in alembic.ini too but its not safe as directly hardcoding database secrets in code can lead to security vulnerability. In the env.py file we are going to tell alembic of our database configuration and also tell it about our models(tables).
from logging.config import fileConfig
from sqlalchemy import engine_from_config
from sqlalchemy import pool
from alembic import context
#new imports
from core.config import settings
from sqlmodel import SQLModel
from database.models import * #imports all the models from __init__.py
config = context.config
config.set_main_option("sqlalchemy.url", settings.DATABASE_URL) #new
target_metadata = SQLModel.metadata #changed
The target_metadata = SQLModel.metadata line is crucial. This tells Alembic, "Hey, these are all the models you need to know about. Use them to generate migrations."
Pro tip: Make sure to import ALL your models in this file by doing from database.models import *, even if they seem unused. I learned this the hard way when Alembic couldn't detect my new table because I forgot to import it! 🤦♂️
One last thing, in the alembic/script.py.mako file, add the below import of sqlmodel.sql.sqltypes. Otherwise alembic will try to use sqlmodel but it will be undefined in the versions files.
from typing import Sequence, Union
from alembic import op
import sqlalchemy as sa
#import sqlmodel even if unused
import sqlmodel.sql.sqltypes
Creating Your First Migration
A migration file is like a leizure of what changes are to be applied to the database. It also has information of how to revert the changes.
docker compose exec -it web alembic revision --autogenerate -m "user and blog model"
Let me break this down:
alembic revision: Creates a new migration file--autogenerate: Automatically detects changes by comparing your models with the current database-m "user and blog model": The message describing what this migration does
The output shows Alembic detected:
- Added table 'user'
- Added index 'ix_user_email' on '('email',)'
- Added index 'ix_user_username' on '('username',)'
- Added table 'blog'
This is Alembic being smart and figuring out what needs to be created in our database!
The upgrade() function contains the SQL commands to apply this migration, and downgrade() contains commands to undo it. It's like having an undo button for our database!
Running the Migration
To apply the migration to your database execute:
docker compose exec -it web alembic upgrade head
This command says, "Apply all pending migrations up to the latest one."
How to verify that the tables are created in the database?
Let's add a new service named PgAdmin to have a GUI of our postgres database. We have to define the PgAdmin service in our docker-compose.yaml file.
services:
web:
build: .
db:
image: postgres:16
pgadmin:
image: dpage/pgadmin4
environment:
PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL}
PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD}
ports:
- "5050:80"
depends_on:
- db
volumes:
- pgadmin_data:/var/lib/pgadmin
volumes:
postgres_data:
pgadmin_data:
Keep the secrets safe in the .env file:
[email protected]
PGADMIN_DEFAULT_PASSWORD=testpass
Once you do docker compose up --build and restart the services. You will be able to visit: http://127.0.0.1:5050 and login and register a new server with our database credentials from .env file:
Common Gotchas (I've Made Them All!) 😅
- Forgetting to import models in
env.py: Alembic won't detect tables if you don't import them. - Running migrations without backing up: Always backup your production database before running migrations!
- Not reviewing auto-generated migrations: Sometimes Alembic gets confused. Always review the generated migration files before applying them.
- Mixing manual database changes with migrations: Don't create tables manually and then try to run migrations. It gets messy!
The Bottom Line 💯
Alembic might seem intimidating at first, but it's a lifesaver. Trust me, future you will thank present you for setting this up properly. No more database horror stories, no more manual SQL commands on production!
Remember, software development is 20% writing code and 80% maintaining it. Tools like Alembic make that 80% much more manageable.
Now go forth and migrate with confidence! And remember, when in doubt, backup first! 🛡️
P.S. - If you found this helpful, drop a comment below! I love hearing about your migration adventures (and misadventures) 😄