Database handling using Python Alembic and SQL Alchemy

Suppose we made some changes in a Python-based backend application. Alembic and SQLAlchemy are excellent solutions for using migrations in Python. They both provide a modern approach to creating and tracking all changes to your databases. Alembic is a database migrations tool that works together with SQLAlchemy.

We can initialize Alembic in our project through this command:

alembic init migrations

We can see a new folder called migrations under the project folder. This folder will be used by Alembic to manage the migrations, revisions, and configurations.

We need to first understand the Python Alembic and what SQLAlchemy does. What does an alembic upgrade and downgrade do?

SQLAlchemy is like an "Object Relational Mapper." When a Python class is created and defined, SQLAlchemy takes my Python class and "maps" it to a database.

Alembic works together with SQLAlchemy to modify our database without overwriting or deleting any data. Suppose we modified our schema and want to reflect the new changes. Now, Alembic will look at the old database, then compare it to the new one we generate, and will perform the changes on our target database table.

Let's understand the alembic upgrade and downgrade command:

Upgrade the database forward by exactly one migration step from the current version.
Suppose our migrations in version files are the following:
A -> B -> C -> D

Our current head is B in the database. Then type this command:

alembic upgrade +1

will move us from B → C.

Suppose our current version is D and we want to downgrade the database back by one migration step. Then we can type the following command:

alembic downgrade -1

moves us from D → C

Suppose I am working in a branch where I have made some changes, and these changes affected some tables in the database by inserting a new column. Now I want to work on another branch where this feature is not implemented. In that case, if I run the project, I will get an error from the backend since the database migration occurred when I worked on the previous feature. And if we downgrade than it will work.

Every time we make changes to the schema, Alembic will autogenerate a file with a version number that stores all the changes made to the database, as well as a downgrade command to revert changes.

Sometimes, more than one alembic file is generated, which is sometimes not required at all. We can simply merge them into one. To do this, we can follow the process described below.

First, we can type this command:

alembic history

We will see something like:

a1->b2 -> c3 -> d4 -> e5 -> f6 -> g7 -> h8 -> i9 -> j10 (head)
a1 (base).

Suppose we want to merge h8,i9 and j10 into one migration file.

First, we will type this command:

alembic downgrade g7

g7 will be the head now.

Now we will move these three files into another backup folder. And then run this command:

alembic revision --autogenerate -m "squashed_h8_i9_j10_migrations"

Now we can see a new migration file generated.
Then we will give this command to upgrade the head:

alembic upgrade head

There is another approach that we can also follow. If we open any migration file, we will see that every file has a downgrade and an upgrade function. So, we can generate a new file first:

alembic revision --autogenerate -m "combined-features"

Then we can simply take the code of the upgrade and downgrade functions from all the files that we want to remove and will update this new file with this code. Then change the downrevision of that file to the revision of the previous file, which is the HEAD, and then do the upgrade.