Alembic is a lightweight database migration tool for usage with SQLAlchemy. The term migration can be a little misleading, because in this context it doesn't mean to migrate to a different database in the sense of using a different version or a different type of database. In this context, migration refers to changes to the database schema: add a new column to a table, modify the type of an existing column, create a new index, etc..
Alembic "provides for the creation, management, and invocation of change management scripts for a relational database, using SQLAlchemy as the underlying engine". It is designed to handle changes to the database schema over time, allowing for a version-controlled approach to database evolution, so you can keep track of changes and revert back to previous versions if necessary.
Steps to use it
- Install Alembic.
$ pip install alembic # with pip
$ poetry add alembic # with poetry
- Initialize Alembic in the project. Use the
-t async
flag for asynchronous support.
$ alembic init alembic
$ alembic init -t async alembic # for asynchronous support
- The init command creates the following project structure.
├── alembic
│ ├── README
│ ├── env.py
│ ├── script.py.mako
│ └── versions
└── alembic.ini
-
alembic.ini
is a configuration file initialized with default generic values. You need to update thesqlalchemy.url
value with your database URL. If the value depends on environment variables (so it isn’t a fixed value that can be directly written in thealembic.ini
file), thealembic/env.py
can be modified to update the Alembic configuration based on the environment variables.
from app.core.settings import get_settings
# update database URL
settings = get_settings()
config.set_main_option("sqlalchemy.url", settings.get_db_url())
Autogenerate Migrations
One of Alembic's key features is its ability to auto-generate migration scripts. By analyzing the current database state and comparing it with the application's table metadata, Alembic can automatically generate the necessary migration scripts using the --autogenerate
flag in the alembic revision
command. Note that autogenerate does not detect all database changes and it is always necessary to manually review (and correct if needed) the candidate migrations that autogenerate produces.
To support autogeneration, the target_metadata
variable in the alembic/env.py
file must be set to your tables metadata.
from app.models import * # noqa: F403
from app.models.database import Base
# all models must have been previously imported
# (from app.models import *)
target_metadata = Base.metadata
Now the autogeneration command can be run. Once it is executed, Alembic will generate a new migration script with the necessary changes based on the comparison between the database and the table metadata in the application. The migration script will be created in the alembic/versions directory.
alembic revision --autogenerate -m "Initial tables"
The generated migration script can be applied to the database using the upgrade
command.
$ alembic upgrade head
Manually Create Migrations
There are times when you will need to manually create migrations, such as when making complex changes that autogeneration might miss. To accomplish this, you can use the revision
command.
$ alembic revision -m "Add new column to User table"
Then open the generated migration script in the alembic/versions
directory and add the necessary changes to the upgrade
and downgrade
functions.
def upgrade():
op.add_column('user', sa.Column('new_column', sa.String()))
def downgrade():
op.drop_column('user', 'new_column')
Top comments (3)
Looking like another good one here, Mati! 🙌
Thanks Michael! 💪🏼
PLEASE SOMEONE HELP ME! this is my question on stackoverflow!
stackoverflow.com/questions/787422...