Database Schema Migration using Alembic

Overview

Database schema migrations are difficult in agile development and continuous deployment model. Some migrations can be simple, like adding a column to the existing table or a new one. But sometimes, it could be difficult when it involves changing data types of existing columns or denormalizing existing tables.

Here comes the Alembic, which eliminates all this complexity and makes things easy.

Introduction of Alembic

Alembic is a database schema migration tool that uses SQLAlchemy as the underlying engine. Whenever there is a need to modify the existing schema of the database, we have to create an Alembic script that the Alembic can invoke to update the original database schema. It uses ALTER statements to do the same. Alembic has full support for transactional DDL, ensuring no change in database schema upon failure. The most important point to remember is that Alembic executes the scripts sequentially, which is later discussed in the article.

Setting up Alembic Environment

1. Create an empty directory and move it into the directory using the cd command.

2. Create and initialize a python virtual environment that ensures that the dependencies we will use for a specific project remain specific to that project.

3. Install all the required dependencies –
   1. SQLAlchemy
   2. Postgress/SQL Driver (whichever database is required)
   3. Alembic

These prerequisites can be installed individually or can be installed in a single go by putting all in one requirements.txt file by executing the command pip install -r requirements.txt

The requirements.txt file will include the following –

SQLAlchemy==1.4.43

Alembic==1.8.1

4. Initialize the migrations folder, which will include all the migrations related to this particular project.

This command will create a folder named migrations and will also create a configuration file for alembic as alembic.ini

5. Edit the alembic.ini so that it can point to the required database. Inside alembic.ini, find the variable named url

Initially, it will be like –

Once everything is set up as per the above steps, we are ready to perform the following steps.

Steps to Generate Migration Versions

1. Generating Baseline Script

The baseline script can generate a database schema from scratch. The revision command is used to generate a new revision for a database. The -m flag is used to specify the revision name. Also, alembic will generate a unique id for each migration to ensure the uniqueness of scripts. All the scripts will be stored in the migrations/versions sub-directory.

This will generate an empty script with upgrade() and downgrade() functions.

Let’s add some code in the script to create a user table.

Note the revision id ‘bc25b5f3949f’. With the help of revision id and down_revision, alembic keeps track of the sequence of revisions. Since it is the first revision(baseline), it has no down_revision.

To run the revision –

This command ensures that the database is updated with the latest available revision.

The output will be like this –

As it can be seen, alembic successfully ran the baseline script and created the user table according to the defined schema.

2. Upgrading revision

Let’s create one more revision to alter the schema of the table by adding one more column to the existing schema.

Note that there is now a unique revision id for this revision. Also, for this revision, we have down_revision as the revision id of the baseline script.

To run the revision –

The output will look like this –

The script ran successfully & added mobile_number col to the table.

3. Downgrading revision

Suppose we no more need the mobile number col in the user table, we can run the downgrade command to revert the changes we made in the previous revision.

Note – The downgrade functionality will only work if something is written under the downgrade() function in the revision.

In our latest revision, we have added op.drop_column(‘user’, ‘mobile_number’), which means it will drop the column mobile_number from the user table.

To downgrade the revision –

OR

Here -1 specifies that we only want to downgrade 1 revision. We can also specify the revision id which needs to be downgraded.

The output will look like this –

The script ran successfully and removed mobile_number col from the user table.

Conclusion

Database schema migrations are an integral part of a large-scale system. They must be appropriately handled to prevent data loss & system availability. Alembic is an excellent solution for SQLAlchemy based systems.

About CloudThat