Alembic is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python. It started as a small utility to keep track of changes in a project's database schema, but as it grew it became useful to other projects as well, for example to keep track of changes in a project's internal data format, or as a backup tool. It aims to be simple to use, easy to extend, and to support database changes without the need for a full ORM model definition.
- Can emit ALTER statements to a database in order to change the structure of tables and other constructs
- Provides a system whereby "migration scripts" may be constructed; each script indicates a particular series of steps that can "upgrade" a target database to a new version, and optionally a series of steps that can "downgrade" similarly, doing the same steps in reverse.
- Can also output changes as SQL text files
- A system of "stamping" the database with a revision marker, so that it's possible to detect if a database is out of date
- A command-line script to manage the creation, running and listing of changesets
- A system to generate stub files that define a new migration series
- A system to detect changes between two models
$ pip install alembicWith a basic understanding of what the environment is, we can create one using alembic init. This will create an environment using the “generic” template:
$ cd /path/to/yourproject
$ source /path/to/yourproject/.venv/bin/activate # assuming a local virtualenv
$ alembic init alembicWhere above, the init command was called to generate a migrations directory called alembic:
Creating directory /path/to/yourproject/alembic...done
Creating directory /path/to/yourproject/alembic/versions...done
Generating /path/to/yourproject/alembic.ini...done
Generating /path/to/yourproject/alembic/env.py...done
Generating /path/to/yourproject/alembic/README...done
Generating /path/to/yourproject/alembic/script.py.mako...done
Please edit configuration/connection/logging settings in
'/path/to/yourproject/alembic.ini' before proceeding. After initializing Alembic, you'll find an alembic.ini file in your project directory. This file contains configuration settings for Alembic. You'll typically need to adjust the database URL in this file to match your database setup. Open alembic.ini in a text editor and locate the sqlalchemy.url setting. Modify it to match your database connection string. For example:
# Example configuration file for Alembic.
# [alembic] section is required in all cases.
[alembic]
# This is the url used when performing migrations etc.
# sqlalchemy.url = driver://user:pass@localhost/dbname
sqlalchemy.url = driver://user:pass@localhost/mydatabaseReplace driver, user, pass, localhost, and mydatabase with your actual database connection details.
The env.py file is generated by Alembic and is used to locate and run migration scripts. Open env.py in a text editor and locate the target_metadata variable. This variable should be set to the metadata object of your SQLAlchemy models. If you're using declarative models, this will typically involve importing your model classes and accessing their metadata attribute. For example:
# Import the base class for declarative SQLAlchemy models
from sqlalchemy.ext.declarative import declarative_base
# Import your model classes
from myapp.models import Base
# Set the target metadata
target_metadata = Base.metadataReplace myapp.models with the appropriate import path for your application's models.
With these configurations in place, Alembic will be able to locate your models and generate migration scripts accurately based on the differences between the current state of the database and your SQLAlchemy models.
To create a new migration script, use the revision command:
$ alembic revision -m "create user table"This will create a new file in the versions directory. The file will contain a Python script that includes the migration directives to apply the changes to the database.
and then edit the file to include the migration directives:
"""empty message
Revision ID: 27c6a30d7c24
Revises: None
Create Date: 2011-11-08 11:40:27.089406
"""
# revision identifiers, used by Alembic.
revision = '27c6a30d7c24'
down_revision = None
from alembic import op
import sqlalchemy as sa
def upgrade():
### commands auto generated by Alembic - please adjust! ###
op.create_table(
'account',
sa.Column('id', sa.Integer()),
sa.Column('name', sa.String(length=50), nullable=False),
sa.Column('description', sa.VARCHAR(200)),
sa.Column('last_transaction_date', sa.DateTime()),
sa.PrimaryKeyConstraint('id')
)
### end Alembic commands ###
def downgrade():
### commands auto generated by Alembic - please adjust! ###
op.drop_table("account")
### end Alembic commands ###- The
upgrade()function contains the migration directives to apply the changes to the database. - The
downgrade()function contains the migration directives to revert the changes, if necessary. - The
opobject is a proxy object that will dispatch the operations to the correct backend. - The
saobject is the SQLAlchemy schema object, which is used to define the schema of the table. - The directives are generated automatically by Alembic, but they may need to be adjusted to match the requirements of the application. They are generated based on the differences between the current state of the database and the models, as defined in the SQLAlchemy models.
To apply the changes to the database, use the upgrade command:
$ alembic upgrade headThis will apply all the changes in the migration scripts to the database.
To revert the changes, use the downgrade command:
$ alembic downgrade -1This will revert the last migration.
To generate a migration script, use the revision command:
$ alembic revision --autogenerate -m "create user table"This will create a new file in the versions directory. The file will contain a Python script that includes the migration directives to apply the changes to the database based on the differences between the current state of the database and the models, as defined in the SQLAlchemy models.
To apply the changes to the database, use the upgrade command:
$ alembic upgrade +1This will apply the next migration script to the database, the +1 is a relative reference to the current migration, so +1 means the next migration, -1 means the previous migration, and so on.