Database Migration with Alembic

Hello Everyone, I used the Alembic tool in one of my project to manage the versioning of Database in Multi Tenant architecture. My problem was to make the version of each database whatsoever we are making changes in database tables. There should be a provision of revert back also. in other words, we must have a feature of upgrade and downgrade a database with a single command line utility.

Alembic solve my problem in a very efficient way, the same i would like to share to everyone. Please follow the step wise notes to install and use of alembic in your project.

Step 1: Install the Python in windows machine.

Step 2: Install the virtual environment

python -m venv venv

Step 3: Activate the virtual environment

.\venv\Script\activate.bat

Step 4: Install the required dependency module, if you have requirements.txt file

pip install -r requirements.txt

Step 5: Install the alembic tool

pip install alembic

Step 6: Check the alembic cli via below command

alembic --version //should be alembic 1.8.0

Step 7: Go to your empty project directory and run the alembic to generate the initial files for the alembic operations

alembic init .  // It will take the generic template 
alembic init --template multidb . //it will be used for the multi tenant architecture

Step 8. With alembic init, you will get the directory and files created like as below:

Step 9. Configure the alembic.ini file for the database connectivity.

Step 10. define your .env file to keep the database credentials there

Step 11. Create the models.py file

from sqlalchemy import Table, Column, Integer
from sqlalchemy.ext.declarative import declarative_base


BaseA = declarative_base()

Step 12. update in the env.py file for the target database

Step 13: Create the revision file to maintain the database table changes

alembic revision -m "file-name"

Step 14: Update your database with the upgrade command

alembic upgrade head

When we run first time with upgrade, then alembic tool will create the alembic_version table where you can see the version. version number would be alphanumeric and it denotes the current version of your updated database. Please see the screenshot of the same.

Step 15: If you want to downgrade the database based that you did

alembic downgrade -1
alembic downgrade -n // here n specifies how many level down that you want to go, like 2, 3, 4 and so on... 

Step 16: To know the alembic history

alembic history