Login
February 17, 2023|
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.
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.
Helping organizations transform their IT infrastructure with top-notch Cloud Computing services
- Cloud Migration
- Devops
- AIML & IoT
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 also the official AWS (Amazon Web Services) Advanced Consulting Partner and Training partner and Microsoft gold partner, helping people develop knowledge of the cloud and help their businesses aim for higher goals using best in industry cloud computing practices and expertise. We are on a mission to build a robust cloud computing ecosystem by disseminating knowledge on technological intricacies within the cloud space. Our blogs, webinars, case studies, and white papers enable all the stakeholders in the cloud computing sphere.
Drop a query if you have any questions regarding Alembic and I will get back to you quickly.
To get started, go through our Consultancy page and Managed Services Package that is CloudThat’s offerings.
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.
PREV
Comments