LearningToTest

The current project that I am working on has multiple databases, but we wanted to streamline the migrations for while keeping them tracked separately with alembic. Previously we had a migration configuration for each database & it just felt unwieldy & unpythonic having multiple alembic.ini & env.py files that all pretty much contained the same information (other than references to the unique database names in the connection strings.

The multidb template in alembic is interesting, but it tracks all of your migrations in a single alembic_version table. (We found this to be undesirable because if we wanted to downgrade a database, we only wanted those changes to be isolated to that particular database–this doesn’t seem possible with a single alembic_version table for multiple databases.) However, there didn’t seem to be a single source of information to guide you through this scenario.

I figured it out eventually & it felt a little Frankenstein-ish at first since my solution was cobbled together from multiple sources… In short, it uses what is a single database env.py that pulls in some environment variables to aid in local development vs server deployment, and that is mixed with a multi-database alembic.ini. However, the solution is exactly what we were looking to do and perhaps turned out better than we originally envisioned (particularly the directory structure).

alembic.ini

There is some magic going on her as the sqlalchemy.url strings are mostly “{}” for all the elements (user, password, host, & port) that might be different depending on where the migration is being run. Those lines are referenced & populated in the env.py file in the next section. And version_locations is the relative path (from alembic.ini) where alembic will create–and expect to find–the migrations for the respective database.

# **************************************************
# ** All settings for ALL databases should only   **
# ** go in [DEFAULT] section!                     **
# ** Do NOT add settings in [alembic] section!    **
# **************************************************

[alembic]
databases = database1, database2, database3, database4

[DEFAULT]
# sys.path path, will be prepended to sys.path if present.
# defaults to the current working directory.
prepend_sys_path = .

# path to migration scripts
script_location = ./schemas/

# template used to generate migration files
file_template = %%(year)d-%%(month).2d-%%(day).2d_%%(hour).2d-%%(minute).2d_%%(rev)s_%%(slug)s

# max length of characters to apply to the "slug" field
truncate_slug_length = 60

[database1]
sqlalchemy.url = mysql+mysqlconnector://{}:{}@{}:{}/database1
version_locations = ./schemas/database1

[database2]
sqlalchemy.url = mysql+mysqlconnector://{}:{}@{}:{}/database2
version_locations = ./schemas/database2

[database3]
sqlalchemy.url = mysql+mysqlconnector://{}:{}@{}:{}/database3
version_locations = ./schemas/database3

[database4]
sqlalchemy.url = mysql+mysqlconnector://{}:{}@{}:{}/database4
version_locations = ./schemas/database4

[post_write_hooks]  # From here to end of file is default alembic.ini contents
...

env.py

Since the migrations will be deployed in docker containers where the migration container can only run if the database container is active & available, that allowed me to dispose of the offline migrations function, leaving just this:

from logging.config import fileConfig
from os import environ

from sqlalchemy import engine_from_config
from sqlalchemy import pool

from alembic import context

# this is the Alembic Config object, which provides
# access to the values within the .ini file in use.
config = context.config

# Interpret the config file for Python logging.
# This line sets up loggers basically.
fileConfig(config.config_file_name)

# add your model's MetaData object here
# for 'autogenerate' support
# from myapp import mymodel
# target_metadata = mymodel.Base.metadata
target_metadata = None

# other values from the config, defined by the needs of env.py,
# can be acquired:
# my_important_option = config.get_main_option("my_important_option")
# ... etc.
DB_USER = environ.get("FOM_DB_USER")
DB_PASS = environ.get("FOM_DB_PASSWORD")
DB_HOST = environ.get("FOM_DB_HOST")
DB_PORT = environ.get("FOM_DB_PORT")


def run_migrations_online():
    """Run migrations in 'online' mode.

    In this scenario we need to create an Engine
    and associate a connection with the context.
    """
    section = config.get_section(config.config_ini_section)
    # Inject environment variable values into connection string
    url = section["sqlalchemy.url"].format(DB_USER, DB_PASS, DB_HOST, DB_PORT)
    section["sqlalchemy.url"] = url
    connectable = engine_from_config(
        section,
        prefix="sqlalchemy.",
        poolclass=pool.NullPool,
    )

    with connectable.connect() as connection:
        context.configure(connection=connection, target_metadata=target_metadata)

        with context.begin_transaction():
            context.run_migrations()


run_migrations_online()

So when it comes to running any alembic commands, you preface the action with --name DATABASE so that your requests become:

• alembic --name database1 history
• alembic --name database3 current
• alembic --name database2 revision -m "create a new index"
• alembic --name database4 upgrade head

Final Directory Structure

The final directory structure is much easier to navigate than as 4 separate configurations & contains perhaps even less nesting of directories than the default alembic configuration for a single database.

migrations/
|--> schemas/
|   |--> database1/
|   |   |--> first_migration_for_database1.py
|   |--> database2/
|   |   |--> migrations_for_database2.py
|   |--> database3/
|   |--> database4/
|   |--> env.py
|--> alembic.ini

As with many things, the first time you try something, it often doesn’t go well. And recently we’ve adopted using alembic for database migrations with a python-based project. Conceptually, alembic is a lot like git–each migration is a commit-like object that you can apply (upgrade) or remove (downgrade). And an autogenerated hash is assigned to each migration with a pointer back to the has for the previous commit.

Except when it is the first one.

For that first migration, it points back to an empty string. So after trying various pythonic options (<nothing>, an empty string, None, -1), all of which failed, I read a reference in the alembic docs where it called the initial state (or parent) “base”.

Alas that works!

alembic downgrade base

Mess averted, problem solved, & we’re back to the beginning again. Now let’s give that database migration another try…