This article will introduce basic usage about alembic for migration about SQLAlchemy.
I’m in windows 10 and python 3.11.2
Setup
I’m currently in a new folder named alembic-example folder.
Create a virtual environment for python.
1
python -m venv venv
Activate virtual environment
1
.\venv\Scripts\activate
Install dependencies.
1
pip install alembic psycopg2-binary
Run cmd alembic init {alembic_folder_name}
1
alembic init alembic
It will create a folder named alembic and a file named alembic.ini.
In alembic-example folder, create an app folder and create a file named database.py inside app folder.
In database.py, you need to configure your connection string CONNECTION_STR
1
2
3
4
5
6
7
8
9
10
import sqlalchemy
from sqlalchemy.orm import sessionmaker, declarative_base
# Config your connction string //{username}:{password}@{host}:{port}/{database}
CONNECTION_STR = 'postgresql+psycopg2://postgres:mysecretpassword@localhost:5432/postgres'
engine = sqlalchemy.create_engine(url=CONNECTION_STR)
SessionLocal = sessionmaker(bind=engine, autocommit=False, autoflush=False)
Base = declarative_base()
In app folder, create a file named models.py.
In models.py, I simply import database.py’s Base and create a simple table.
1
2
3
4
5
6
7
8
from app.database import Base
import sqlalchemy as sa
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True, nullable=False)
And I also create a __init__.py in app folder.
Now, the folder’s structure should be similar like this:
1
2
3
4
5
6
7
8
alembic-example/
alembic/
...
alembic.ini
app/
__init__.py
database.py
models.py
Configure alembic
In alembic.ini, change sqlalchemy.url
from
1
sqlalchemy.url = driver://user:pass@localhost/dbname
to
1
sqlalchemy.url = postgresql+psycopg2://postgres:mysecretpassword@localhost:5432/postgres
In alembic/env.py, change
from
1
target_metadata = None
to
1
2
from app.models import Base
target_metadata = Base.metadata
Run alembic
Create a new version
Run alembic revision --autogenerate -m "{your simple description}"
1
alembic revision --autogenerate -m "First revision"
This will generate a migration script in alembic/revision/ folder and a table named alembic_version in database which will be used for controlling database version.
Migrate up
Run alembic upgrade +1 will upgrade database 1 version,
or run alembic upgrade head will upgrade database to newest version
1
alembic upgrade +1
Now the database create a table named user, a column id and an index for id
Modify table and migrate up
Add a new column username for user
Here is updated models.py
1
2
3
4
5
6
7
8
9
from app.database import Base
import sqlalchemy as sa
class User(Base):
__tablename__ = 'user'
id = sa.Column(sa.Integer, primary_key=True, nullable=False)
username = sa.Column(sa.String, index=True)
Run
1
2
alembic revision --autogenerate -m "Add username for user"
alembic upgrade +1
refresh the database and see the result, it works.
Migrate down
Migrade down 1 version
1
alembic downgrade -1
Migrade down to the base
1
alembic downgrade base
Takeaway
I think the best pratice is:
- when you migrate up you
git committhe related model file - when you migrate down you
rollback to related model file
1
2
3
4
5
alembic revision --autogenerate -m "First revision"
alembic upgrade +1
alembic upgrade head
alembic downgrade -1
alembic downgrade base