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
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
- Create an empty directory and move it into the directory using the cd command.
1 2 |
$ mkdir alembic $ cd alembic |
- 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 2 |
$ virtualenv virtual_evn_name $ source virtual_env_name/bin/activate |
- Install all the required dependencies –
- SQLAlchemy
- Postgress/SQL Driver (whichever database is required)
- 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
- Initialize the migrations folder, which will include all the migrations related to this particular project.
1 |
$ alembic init migrations |
This command will create a folder named migrations and will also create a configuration file for alembic as alembic.ini
- 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 –
1 |
sqlalchemy = driver://user:pass@localhost/dbname |
Once everything is set up as per the above steps, we are ready to perform the following steps.
Steps to Generate Migration Versions
- 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.
1 |
$ alembic revision -m “baseline_revision” |
This will generate an empty script with upgrade() and downgrade() functions.
Let’s add some code in the script to create a user table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
"""baseline Revision ID: bc25b5f8939f Revises: Create Date: 2022-11-23 15:00:18.721577 """ # revision identifiers, used by Alembic. revision = 'bc25b5f3949f' down_revision = None branch_labels = None depends_on = None from alembic import op import sqlalchemy as sa def upgrade(): op.create_table( 'user', sa.Column('id', sa.Integer, primary_key=True), sa.Column('first_name, sa.String()), sa.Column('last_name, sa.String(), nullable=False), sa.Column('address', sa.String())) def downgrade(): op.drop_table('user') |
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 –
1 |
$ alembic upgrade head |
This command ensures that the database is updated with the latest available revision.
The output will be like this –
1 2 3 4 5 |
INFO [alembic.runtime.migration] Context impl PostgresqlImpl. INFO [alembic.runtime.migration] Will assume transactional DDL. INFO [alembic.runtime.migration] Running upgrade -> bc25b5f3949f, baseline |
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.
1 |
$ alembic revision -m “add_col_mobile_number” |
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 –
1 |
$ alembic upgrade head |
The output will look like this –
1 2 3 4 5 |
INFO [alembic.runtime.migration] Context impl PostgresqlImpl. INFO [alembic.runtime.migration] Will assume transactional DDL. INFO [alembic.runtime.migration] Running upgrade bc25b5f8939f -> ed34c6h4896k, add_col_mobile_number |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
"""add_col_mobile_number Revision ID: ed34c6h4896k Revises: Create Date: 2022-11-23 15:30:18.721577 """ # revision identifiers, used by Alembic. revision = 'ed34c6h4896k' down_revision = 'bc25b5f8939f' branch_labels = None depends_on = None from alembic import op import sqlalchemy as sa def upgrade(): op.add_column('user', sa.add_column('mobile_number', sa.String()) def downgrade(): op.drop_column('user', 'mobile_number') |
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 –
1 |
$ alembic downgrade -1 |
OR
1 |
$ alembic downgrade ed34c6h4896k |
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 –
1 2 3 4 5 |
INFO [alembic.runtime.migration] Context impl PostgresqlImpl. INFO [alembic.runtime.migration] Will assume transactional DDL. INFO [alembic.runtime.migration] Running downgrade ed34c6h4896k -> bc25b5f8939f |
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
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 Partner, AWS Migration Partner, AWS Data and Analytics Partner, AWS DevOps Competency Partner, AWS GenAI Competency Partner, Amazon QuickSight Service Delivery Partner, Amazon EKS Service Delivery Partner, AWS Microsoft Workload Partners, Amazon EC2 Service Delivery Partner, Amazon ECS Service Delivery Partner, AWS Glue Service Delivery Partner, Amazon Redshift Service Delivery Partner, AWS Control Tower Service Delivery Partner, AWS WAF Service Delivery Partner, Amazon CloudFront Service Delivery Partner, Amazon OpenSearch Service Delivery Partner, AWS DMS Service Delivery Partner, AWS Systems Manager Service Delivery Partner, Amazon RDS Service Delivery Partner, AWS CloudFormation Service Delivery Partner, AWS Config, Amazon 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.
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
1 2 3 4 5 |
Ex – def upgrade(): 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.
Comments