alembic + sqlalchemy multiple databases

Question

Within Flask i have setup SQLAlchemy to use multiple databases which are selected upon request as described in https://quanttype.net/posts/2016-03-15-flask-sqlalchemy-and-multitenancy.html

So SQLAlchemy is configured with SQLALCHEMY_BINDS to redirect an incomming request to the correct database.

I'm trying to get Alembic working with multiple databases. Eg. the models in models.py should be identical in all databases.

SO far as i can tell alembic multidb only works with the bind_key in the models to specify which table should be created in which database, but i want to create all tables in all databases in one go. How do i do that?

Answer 1

You need to ensure that you perform each version of migration with all database credentials, you can write your own migration script in order to do it in one go.

Alembic provides a set of command functions to let you synchronize your ORM models with the database(s) programmatically. You can invoke command.upgrade(...) and command.downgrade(...) in your script.

For example, you have 2 databases DB1 and DB2 mapped to the same set of ORM models in models.py, the migration script looks like :

from alembic import command
from alembic.config import Config

cfg = Config(YOUR_ALEMBIC_CFG_FILE_PATH)
# for autogenerate is True, make sure you set correct `target_metadata` in env.py
result = command.revision(config=cfg, message='some msg',
            autogenerate=True, rev_id=your_last_rev_id )

# update url with DB1 credential
cfg.set_main_option(name='sqlalchemy.url', value=db1_credential)
command.upgrade(config=cfg, revision=result.revision)

# update url with DB2 credential
cfg.set_main_option(name='sqlalchemy.url', value=db2_credential)
command.upgrade(config=cfg, revision=result.revision)

Answer 2

Alembic provides a template to work with multiple databases:

alembic init --template multidb ./multidb

For your particular use case:

1 - Modify alembic.ini with your database names and SQLAlchemy URLs

#alembic.ini

databases = engine1, engine2

[engine1]
sqlalchemy.url = driver://user:pass@localhost/dbname

[engine2]
sqlalchemy.url = driver://user:pass@localhost/dbname2

Note: if you don't want to expose the passwords, generate the SQLAlchemy URLs at runtime in env.py instead.

2 - Point all target_metadata in multidb/env.py to the same model

# env.py

from models import mymodel

target_metadata = {
    "engine1": mymodel.Base.metadata,
    "engine2": mymodel.Base.metadata,
}

Note: If you have many databases, probably it's better to do this programmatically.

3 - Test the Alembic setup

alembic revision --autogenerate -m "test"