Alembic Branches Example

An Alembic example on how to setup multiple DB modules using branches and dependencies with checked in sample files.

Goals:

• Have a single tool to create the initial database and also apply the revisions to the database objects.
• Demostrate how to use branches and dependencies to implement modularization of the database objects.
• Support environment variable SQLALCHEMY_URL to overwrite default sqlalchemy.url in the alembic.ini file.

My use case:

Instead of a pile of database object (tables), I want a way to organize them into modules that gives me the flexibility to install them either in a single database or in their own seperate database with an eye looking towards micro services. In this example, I am using a single repository to implement the modularization of my database objects. You could modularized your database object in different repositories but that is not what this example is about.

In this example I have a core common module and 3 other modules. It is assumed that the core module is needed by the other modules.

This is my specific uses case and may not fit your use situation. However, if you can gleam ideas off my example ... more power to you.

Enhancements made:

• The alembic.ini have been tweaked.
• The env.py have been enhance to read the environment variable SQLALCHEMY_URL to over write the default set in alembic.ini.
• The script.py.mako have been enhance with extra imports to better integrate SQLAlchemy model and example.

Setup:

1. Clone this repo using: git clone https://github.com/elau1004/Alembic-Branches-Example.git
2. Install pipenv using: pip install pipenv
3. Setup your virtual environment and install the packages using: pipenv install --dev
4. Start your virtual environment using pipenv shell
5. Continue to the "Deployment example" section.

Visual:

The following is to help you see the created branches:

---
title: Alembic branching example
---
%%{init: { 'logLevel': 'debug', 'theme': 'base', 'gitGraph': {'showBranches': true, 'showCommitLabel':true,'mainBranchName': 'INITCORE - Long live branch'}} }%%
gitGraph
    checkout   "INITCORE - Long live branch"
    commit id: "Core new table"
    commit id: "Core func & proc"
    commit id: "Core pop data"
    commit id: "Core ver 0" type: HIGHLIGHT
    branch     "INITALL - Install all branches"
    branch     "CORE - Core Revision branch"
    branch     "INITMOD1 - Module 1 long live branch"
    checkout   "CORE - Core Revision branch"
    commit id: "Core rev1"
    commit id: "Core rev2"
    commit id: "Core rev3"
    checkout   "INITMOD1 - Module 1 long live branch"
    commit id: "Mod1 new table"
    commit id: "Mod1 func & proc"
    commit id: "Mod1 pop data"
    commit id: "Mod1 ver 0" type: REVERSE
    branch     "MOD1 - Module 1 Revision branch"
    commit id: "Mod1 rev1"
    commit id: "Mod1 rev2"
    commit id: "Mod1 rev3"
    checkout   "INITCORE - Long live branch"
    branch     "INITMOD2 - Module 2 Long live branch"
    checkout   "INITMOD2 - Module 2 Long live branch"
    commit id: "Mod2 new table"
    commit id: "Mod2 func & proc"
    commit id: "Mod2 pop data"
    commit id: "Mod2 ver 0" type: REVERSE
    branch     "MOD2 - Module 2 Revision branch"
    commit id: "Mod2 rev1"
    commit id: "Mod2 rev2"
    commit id: "Mod2 rev3"
    checkout   "INITCORE - Long live branch"
    branch     "INITMOD3 - Module 3 Long live branch"
    checkout   "INITMOD3 - Module 3 Long live branch"
    commit id: "Mod3 new table"
    commit id: "Mod3 func & proc"
    commit id: "Mod3 pop data"
    commit id: "Mod3 ver 0" type: REVERSE
    branch     "MOD3 - Module 3 Revision branch"
    commit id: "Mod3 rev1"
    commit id: "Mod3 rev2"
    commit id: "Mod3 rev3"
    checkout "INITALL - Install all branches"
    cherry-pick id: "Core ver 0"
    cherry-pick id: "Mod1 ver 0"
    cherry-pick id: "Mod2 ver 0"
    cherry-pick id: "Mod3 ver 0"

Loading

Create the migration scripts using alembic:

The sample scripts that are checked in was generated and edited from the following execution of "alembic revision" command maximizing its CLI options.

1. Setup initialization of a common core branch. This is meant to be a long living branch.

alembic revision -m "Init core create tables"      --rev-id init_core_newtable --head=base --branch-label INITCORE
alembic revision -m "Init core create replacables" --rev-id init_core_funcproc --head=INITCORE@head
alembic revision -m "Init core seed system data"   --rev-id init_core_popdata  --head=INITCORE@head
alembic revision -m "Init core revision 0"         --rev-id init_core_version0 --head=base --depends-on=init_core_popdata --branch-label CORE

2. Setup initialization of a module 1 branch. This is meant to be a long living branch.

alembic revision -m "Init mod1 create tables"      --rev-id init_mod1_newtable --head=base --depends-on=init_core_newtable --branch-label INITMOD1
alembic revision -m "Init mod1 create replacables" --rev-id init_mod1_funcproc --head=INITMOD1@head
alembic revision -m "Init mod1 seed system data"   --rev-id init_mod1_popdata  --head=INITMOD1@head
alembic revision -m "Init mod1 revision 0"         --rev-id init_mod1_version0 --head=base --depends-on=init_mod1_popdata  --branch-label MOD1

3. Setup initialization of a module 2 branch. This is meant to be a long living branch.

alembic revision -m "Init mod2 create tables"      --rev-id init_mod2_newtable --head=base --depends-on=init_core_newtable --branch-label INITMOD2
alembic revision -m "Init mod2 create replacables" --rev-id init_mod2_funcproc --head=INITMOD2@head
alembic revision -m "Init mod2 seed system data"   --rev-id init_mod2_popdata  --head=INITMOD2@head
alembic revision -m "Init mod2 revision 0"         --rev-id init_mod2_version0 --head=base --depends-on=init_mod2_popdata  --branch-label MOD2

4. Setup initialization of a module 3 branch. This is meant to be a long living branch.

alembic revision -m "Init mod3 create tables"      --rev-id init_mod3_newtable --head=base --depends-on=init_core_newtable --branch-label INITMOD3
alembic revision -m "Init mod3 create replacables" --rev-id init_mod3_funcproc --head=INITMOD3@head
alembic revision -m "Init mod3 seed system data"   --rev-id init_mod3_popdata  --head=INITMOD3@head
alembic revision -m "Init mod3 revision 0"         --rev-id init_mod3_version0 --head=base --depends-on=init_mod3_popdata  --branch-label MOD3

5. Setup a shortcut dependency to upgrade all modules. This is a development environment convenience. I don't recommend to casually migrate all.

alembic revision -m "Init modules" --rev-id init_all_modules --head=base --branch-label INITALL --depends-on=init_mod1_popdata --depends-on=init_mod2_popdata --depends-on=init_mod3_popdata

6. Setup custom development specific.

alembic revision -m "Seed development data" --rev-id init_dev_popdata --head=base --branch-label INITDEV --depends-on=init_all_modules

7. Regular module migrations. You need to know what you are doing by specifying the branch. I don't recommend to casually migrate all.

alembic revision -m "CORE Rev1"  --rev-id core_rev1  --head=CORE@head
alembic revision -m "CORE Rev2"  --rev-id core_rev2  --head=CORE@head
alembic revision -m "CORE Rev3"  --rev-id core_rev3  --head=CORE@head

alembic revision -m "MOD1 Rev1"  --rev-id mod1_rev1  --head=MOD1@head
alembic revision -m "MOD1 Rev2"  --rev-id mod1_rev2  --head=MOD1@head
alembic revision -m "MOD1 Rev3"  --rev-id mod1_rev3  --head=MOD1@head

alembic revision -m "MOD2 Rev1"  --rev-id mod2_rev1  --head=MOD2@head
alembic revision -m "MOD2 Rev2"  --rev-id mod2_rev2  --head=MOD2@head
alembic revision -m "MOD2 Rev3"  --rev-id mod2_rev3  --head=MOD2@head

alembic revision -m "MOD3 Rev1"  --rev-id mod3_rev1  --head=MOD3@head
alembic revision -m "MOD3 Rev2"  --rev-id mod3_rev2  --head=MOD3@head
alembic revision -m "MOD3 Rev3"  --rev-id mod3_rev3  --head=MOD3@head

Workflow:

1. Install the core module.
2. Install the individual modules.
3. Optionally, create a shortcut to install all modules into the current database. Usually, this is in a single development database.
4. Optionally, create a shortcut to populate the development database with test data.
5. Apply all the changes to bring the version of the database up to date.
6. Delete the applied scripts but keep the latest revision script.
7. Edit the latest revision script and set the down_revision value to point back to where we originally started from. In my example it should be the "init_XXXX_version0" where xxx is the module mnemonic.

Deployment example:

alembic heads | sort                # Display all the head branches from all the migration scripts.
alembic history                     # Display all the revision histories reflected from all the migration scripts.

alembic upgrade    INITCORE@head    # Upgrade only the CORE module.
alembic upgrade    INITMOD1@head    # Upgrade only the MOD1 module.
alembic upgrade    INITMOD2@head    # Upgrade only the MOD2 module.
alembic upgrade    INITMOD3@head    # Upgrade only the MOD3 module.
alembic current                     # Display the current versions applied to the database.

alembic upgrade    CORE@head        # Apply all revisions to the CORE module since initialization.
alembic upgrade    MOD1@head        # Apply all revisions to the MOD1 module since initialization.
alembic upgrade    MOD2@head        # Apply all revisions to the MOD2 module since initialization.
alembic upgrade    MOD3@head        # Apply all revisions to the MOD3 module since initialization.
alembic current                     # Display the current versions applied to the database.

alembic downgrade  CORE@base        # Downgrade the CORE module down to the base.
alembic downgrade  MOD1@-1          # Downgrade the MOD1 module by 2 revision from current.
alembic downgrade  MOD2@-2          # Downgrade the MOD2 module by 2 revision from current.
alembic downgrade  MOD3@-3          # Downgrade the MOD3 module by 3 revision from current.
alembic current                     # Display the current versions applied to the database.

alembic downgrade  base             # Downgrade all the above initialized modules.
alembic upgrade    INITALL@head     # Apply all revisions to the all the  modules.
alembic downgrade  base             # Downgrade all the above initialized modules.
alembic upgrade    INITDEV@head     # Create a new development databasse with all the modules with test data.

alembic upgrade    heads            # Becareful! It will upgrade all branches to their heads.  Strictly for illustration.
alembic current                     # Display the current versions applied to the database.

Once the upgrade is done, delete all the patching migration scripts except the latest script. Update the latest branch scripts and change the value of "down_revision" to "init_XXXX_version0".

Miscellanous:

• Once you have initialize the modules, your database objects are the latest according to the declarative model in model.py. However, the version tacked in the alembic_version table is not the latest. Therefore, you need to continue to fast forward all the revisions to arrive at each of the modules latest head revision.
• A sqlite3 database file shall be created in your repository root directory. To start over, just delete this file.
• Try to avoid using "alembic upgrade heads". You should be specific in apply your changes or you could create a big mess by applying the wrong branch into your environment.
• The script template does output commented out code that you could edit to enable the checking of CLI parameter passed via "-x REVISE=" in the alembic upgrade command.
• You do not need to install any package to get sqlite3.
• Do review the sample revision scripts.

Limitations:

• This responsibility falls on the develoepr to understandwhat he/she is doing so that you do not mistakenly mix your production and development branches. The same applies for working with git.
• More verbose and requires the specification of the branch to apply your changes to or passing in a CLI parameter.