AWS, Azure, Cloud Computing, Google Cloud (GCP)

4 Mins Read

Database Schema Migration using Alembic

Voiced by Amazon Polly

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.

Customized Cloud Solutions to Drive your Business Success

  • Cloud Migration
  • Devops
  • AIML & IoT
Know More

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.
  1. Create and initialize a python virtual environment that ensures that the dependencies we will use for a specific project remain specific to that project.
  1. 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

  1. 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

  1. 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.

Get your new hires billable within 1-60 days. Experience our Capability Development Framework today.

  • Cloud Training
  • Customized Training
  • Experiential Learning
Read More

About CloudThat

CloudThat is a leading provider of Cloud Training and Consulting services with a global presence in India, the USA, Asia, Europe, and Africa. Specializing in AWS, Microsoft Azure, GCP, VMware, Databricks, and more, the company serves mid-market and enterprise clients, offering comprehensive expertise in Cloud Migration, Data Platforms, DevOps, IoT, AI/ML, and more.

CloudThat is the first Indian Company to win the prestigious Microsoft Partner 2024 Award and is recognized as a top-tier partner with AWS and Microsoft, including the prestigious ‘Think Big’ partner award from AWS and the Microsoft Superstars FY 2023 award in Asia & India. Having trained 850k+ professionals in 600+ cloud certifications and completed 500+ consulting projects globally, CloudThat is an official AWS Advanced Consulting Partner, Microsoft Gold Partner, AWS Training PartnerAWS Migration PartnerAWS Data and Analytics PartnerAWS DevOps Competency PartnerAWS GenAI Competency PartnerAmazon QuickSight Service Delivery PartnerAmazon EKS Service Delivery Partner AWS Microsoft Workload PartnersAmazon EC2 Service Delivery PartnerAmazon ECS Service Delivery PartnerAWS Glue Service Delivery PartnerAmazon Redshift Service Delivery PartnerAWS Control Tower Service Delivery PartnerAWS WAF Service Delivery PartnerAmazon CloudFront Service Delivery PartnerAmazon OpenSearch Service Delivery PartnerAWS DMS Service Delivery PartnerAWS Systems Manager Service Delivery PartnerAmazon RDS Service Delivery PartnerAWS CloudFormation Service Delivery PartnerAWS ConfigAmazon EMR and many more.

FAQs

1. How does alembic keep track of Revision IDs?

ANS: – Alembic will create a revision_id table in the database as soon as the baseline script is executed. In the future, it will update the revision_id table with the latest revision ID; that way, it keeps track of revision IDs. faq

2. Can two or more migration scripts have the same revision IDs?

ANS: – No, it is impossible to have the same revision id for even two migration scripts. Alembic will throw an error if it happens.

3. Can we keep the downgrade() or upgrade() function empty in a revision?

ANS: – Yes, we can empty any or both functions by just passing the pass

WRITTEN BY Sahil Kumar

Sahil Kumar works as a Subject Matter Expert - Data and AI/ML at CloudThat. He is a certified Google Cloud Professional Data Engineer. He has a great enthusiasm for cloud computing and a strong desire to learn new technologies continuously.

Share

Comments

    Click to Comment

Get The Most Out Of Us

Our support doesn't end here. We have monthly newsletters, study guides, practice questions, and more to assist you in upgrading your cloud career. Subscribe to get them all!