Alembic is a database migration tool used by SQLAlchemy. Database migration is something similar to a version control system for databases. It’s worth recalling that SQLAlchemy’s create_all() method only creates missing tables from models. When a table is already created, it doesn’t change its schema based on changes in the model. When developing an application, it is common practice to change the table schema. This is where Alembic comes in. It, like other similar tools, allows you to change the database schema as your application evolves. It also keeps track of changes to the database itself, so you can move back and forth. If you don’t use Alembic, you have to keep track of all the changes manually and change the schema with Alter. Flask-Migrate is an extension that integrates Alembic into the Flask application. You can install it using the following command.
(env) [email protected]:~/flask_app$ pip install flask-migrate
To integrate Flask-Migrate with the application, you need to import the Migrate and MigrateCommand classes from the flask_package package, and create an instance of the Migrate class by passing an application instance(app) and a SQLAlchemy object(db):
#...
from flask_migrate import Migrate, MigrateCommand
app = Flask(__name__)
app.debug = True
app.config['SECRET_KEY'] = 'a really really really long secret key'
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:[email protected]/flask_app_db'
manager = Manager(app)
db = SQLAlchemy(app)
migrate = Migrate(app, db)
manager.add_command('db', MigrateCommand)
#...
The MigrateCommand class defines some of the database migration commands available in Flask-Script. On line 12, these commands are rendered using the db command line argument. To see the commands created, you have to go back to the terminal and enter the following command:
(env) [email protected]:~/flask_app$ python main2.py
positional arguments:
{db,faker,foo,shell,runserver}
db Perform database migrations
faker A command to add fake data to the tables
foo Just a simple command
shell Runs a Python shell inside Flask application context.
runserver Runs the Flask development server i.e. app.run()
optional arguments:
-?, --help show this help message and exit
(env) [email protected]:~/flask_app$
So, you can see that the new db command is used for database migrations. To see the full list of subcommands for dv, enter the following:
(env) [email protected]:~/flask_app$ python main2.py db -?
Perform database migrations
positional arguments:
{init,revision,migrate,edit,merge,upgrade,downgrade,show,history,heads,branche
s,current,stamp}
init Creates a new migration repository
revision Create a new revision file.
migrate Alias for 'revision --autogenerate'
edit current revision.
merge Merge two revisions together. Creates a new migration
file
upgrade Upgrade to a later version
downgrade Revert to a previous version
show the revision denoted by the given symbol.
history List changeset scripts in chronological order.
heads Show current available heads in the script directory
branches Show current branch points
current Display the current revision for each database.
stamp 'stamp' the revision table with the given revision;
don't run any migrations
optional arguments:
-?, --help show this help message and exit
These are the actual commands that will be used for database migrations. Before Alembic starts tracking changes, you need to install the migration repository. The migration repository is just a folder that contains Alembic settings and migration scripts. To create a repository you need to execute the init command:
(env) [email protected]:~/flask_app$ python main2.py db init
Creating directory /home/gvido/flask_app/migrations ... done
Creating directory /home/gvido/flask_app/migrations/versions ... done
Generating /home/gvido/flask_app/migrations/README ... done
Generating /home/gvido/flask_app/migrations/env.py ... done
Generating /home/gvido/flask_app/migrations/alembic.ini ... done
Generating /home/gvido/flask_app/migrations/script.py ... done
Please edit configuration/connection/logging settings in
/home/gvido/flask_app/migrations/alembic.ini' before proceeding.
(env) [email protected]:~/flask_app$
This command will create a “migrations” folder inside the flask_app folder. The structure of the migrations folder is as follows:
migrations
├── alembic.ini
├── env.py
├─── README
├── script.py.mako
└─── versions
A brief description of each folder and file:
alembic.ini– file with Alembic settings.env.py– Python file, which is run every time Alembic is called. It connects to the database, starts a transaction and calls the migration engine.README– README file.script.py.mako– Mako template file, which will be used to create migration scripts.version– the folder for storing migration scripts.
Table of Contents
Creating a migration script
Alembic stores database migrations in migration scripts, which are regular Python files. The migration script defines two functions: upgrade() and downgrade(). The job of upgrade() is to apply the changes to the database, and downgrade() is to roll them back. When the migration is applied, the upgrade() function is called. When it is rolled back, downgrade() is called. Alembic offers two options for creating migrations:
- Manually with the command
revision. - Automatically with the command
migrate.
Manual migration
Manual or empty migration creates a migration script with empty upgrade() and downgrade() functions. The task is to fill them with Alembic instructions, which will apply the changes to the database. Manual migration is used when you need full control over the migration process. To create an empty migration, enter the following command:
(env) [email protected]:~/flask_app$ python main2.py db revision -m "Initial migration"
This command will create a new migration script in the migrations/version folder. The file name should be in the format someid_initial_migration.py. The file should look like this:
"""Initial migration
Revision ID: 945fc7313080
Revises:
Create Date: 2019-06-03 14:39:27.854291
"""
from alembic import op
import sqlalchemy as sa
# change identifiers used by Alembic.
revision = '945fc7313080'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
pass
def downgrade():
pass
It starts with the commented part, which contains the message specified with the -m label, the change ID and the time the file was created. The next important part is the change IDs. Each migration script gets a unique change ID, which is stored in the revision variable. On the next line there is a down_revision variable with the value None. Alembic uses the down_revision variable to determine which migration to run and in what order. The down_revision variable points to the change identifier of the parent migration. In this case, its value is None, because it is only the first migration script. At the end of the file, there are empty upgrade() and downgrade() functions.
Now we need to edit the migration file to add table creation and deletion operations for upgrade() and downgrade(), respectively. The upgrade() function uses Alembic’s create_table() instruction. The create_table() instruction uses the CREATE TABLE statement. The drop_table() instruction uses the DROP TABLE operator in the downgrade() function. The first migration will create users table, and on rollback the same migration will delete users table. Now you can perform the first migration. To do this, enter the following command:
(env) [email protected]:~/flask_app$ python main2.py db upgrade
] This command will execute the upgrade() function of the migration script. The db upgrade command will return the database to the last migration. It is worth noting that db upgrade not only runs the last migration, but all the migrations that have not yet been run. This means that if several migrations have been created, db upgrade will run them all together in the order they were created. Instead of running the last migration, you can also pass the change ID of the desired migration. In this case, db upgrade will stop after running a particular migration and will not run subsequent migrations.
(env) [email protected]:~/flask_app$ python main2.py db upgrade 945fc7313080
Since the migration is run for the first time, Alembic will also create a table alembic_version. It consists of a single column version_num, which stores the change identifier of the last migration run. This is how Alembic knows the current state of the migration, and where to execute it from. Right now the alembic_version table looks like this:
You can determine the last migration applied by using the db current command. This will return the change identifier of the last migration. If there was none, nothing will be returned.
(env) [email protected]:~/flask_app$ python main2.py db current
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
945fc7313080 (head)
(env) [email protected]:~/flask_app$
The output shows that the current migration is 945fc7313080. You should also pay attention to the line(head) after the change identifier, which indicates that 945fc7313080 is the last migration. Let’s create another empty migration with the db revision command:
(env) [email protected]:~/flask_app$ python main2.py db revision -m "Second migration"
Next, run the db current command again. This time the revision ID will be displayed without the line(head), because the migration 945fc7313080 is not the last one.
(env) [email protected]:~/flask_app$ python main2.py db current
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
945fc7313080
(env) [email protected]:~/flask_app$
To see a complete list of migrations (running and not), you should use the command db history. It will return a list of migrations in reverse chronological order (the last migration will be displayed first).
(env) [email protected]:~/flask_app$ python main2.py db history
945fc7313080 -> b0c1f3d3617c (head), Second migration
<base> -> 945fc7313080, Initial migration
(env) [email protected]:~/flask_app$
The output shows that 945fc7313080 is the first migration, followed by b0c1f3d3617, the last migration. As usual, (head) indicates the last migration. The users table was created for testing purposes only. To get the database back to its original state, which was before the db upgrade command was executed, you can use rollback migration. To roll back to the last migration, the db downgrade command is used.
(env) [email protected]:~/flask_app$ python main2.py db downgrade
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running downgrade 945fc7313080 -> , Initial mi
gration
(env) [email protected]:~/flask_app$
It will execute the downgrade() migration method 945fc7313080, which will remove the users table from the database. As with the db upgrade command, you can pass the identifier of the migration change you want to roll back to. For example, to roll back to migration 645fc5113912, use the following command.
(env) [email protected]:~/flask_app$ python main2.py db downgrade 645fc5113912
To roll back all accepted migrations, you must use the following command:
(env) [email protected]:~/flask_app$ python main2.py db downgrade base
No migrations have been applied to the database at this time. You can verify this by running the db current command:
(env) [email protected]:~/flask_app$ python main2.py db current
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
(env) [email protected]:~/flask_app$
As you can see, the output does not return a change identifier. Note that rolling back the migration only undoes the changes to the database, but does not remove the migration script itself. As a result, the db history command will show two migration scripts.
(env) [email protected]:~/flask_app$ python main2.py db history
945fc7313080 -> b0c1f3d3617c (head), Second migration
<base> -> 945fc7313080, Initial migration
(env) [email protected]:~/flask_app$
What happens if you run the db upgrade command now? The db upgrade command will run migration 945fc7313080 first, followed by b0c1f3d3617. The database is back in its original state, and since no changes to the migration scripts are required, they can be removed.
Automatic migration
Note: before moving on, you need to make sure that the migrations from the previous section have been removed. Automatic migration creates the code for the upgrade() and downgrade() functions after comparing the models with the current version of the database. The migrate command is used to create an automatic migration, which essentially replicates what revision --autogenerate does. You must enter the migrate command in the terminal: It is important to note that the last line of the output says "No changes in schema detected." This means that the models are synchronized with the database. Let’s open main2.py to add the Employee model after the Feedback model:
#...
class Employee(db.Model):
__tablename__ = 'employees'
id = db.Column(db.Integer(), primary_key=True)
name = db.Column(db.String(255), nullable=False)
designation = db.Column(db.String(255), nullable=False)
doj = db.Column(db.Date(), nullable=False)
#...
Next we need to run the db migrate command again. This time Alembic will detect that a new employees table has been added and create a migration script with functions to later create and delete the employees table.
(env) [email protected]:~/flask_app$ python main2.py db migrate -m "Adding employees table"
The migration script created with the previous command should look like this:
"""Adding employees table
Revision ID: 6e059688f04e
Revises:
Create Date: 2019-06-03 16:01:28.030320
"""
from alembic import op
import sqlalchemy as sa
# change identifiers used by Alembic.
revision = '6e059688f04e'
down_revision = None
branch_labels = None
depends_on = None
def upgrade():
### auto-generated commands - please customize! ###
op.create_table('employees',
sa.Column('id', sa.Integer(), nullable=False),
sa.Column('name', sa.String(length=255), nullable=False),
sa.Column('designation', sa.String(length=255), nullable=False),
sa.Column('doj', sa.Date(), nullable=False),
sa.PrimaryKeyConstraint('id')
)
### end of Alembic commands ###
def downgrade():
### automatically generated Alembic commands - please customize! ###
op.drop_table('employees')
### end of Alembic commands ###
Nothing new here. The upgrade() function uses the create_table instruction to create the table, and the downgrade() function uses the drop_table instruction to delete it. Let’s start the migration with the db upgrade command:
(env) [email protected]:~/flask_app$ python main2.py db upgrade
INFO [alembic.runtime.migration] Context impl MySQLImpl.
INFO [alembic.runtime.migration] Will assume non-transactional DDL.
INFO [alembic.runtime.migration] Running upgrade -> 6e059688f04e, Adding emplo
yees table
(env) [email protected]:~/flask_app$
This will add the employees table to the database.
Limitations of automatic migration
Automatic migration is not perfect. It does not detect every possible change. The operations that Alembic is able to detect
- Adding and removing tables
- Adding and removing columns
- Changing foreign keys
- Changes in column types
- Changes in indexes and unique constraints used
Changes that Alembic does not detect:
- Table name
- Column name
- Restrictions with separately specified names
To create migration scripts for operations that Alembic cannot detect, you need to create an empty migration script and fill out the upgrade() and downgrade() functions accordingly.
