Using alembic with multiple databases

Question

I have a pretty standard flask app. It uses flask_sqlalchemy to manage connections to a postgres server and alembic to manage migrations.

Now the issue is that I'm in the process of integrating it with another project and that means that I'm trying to allow it to pull a single model from another database. Luckily, flask_sqlalchemy has great support for this with the SQLALCHEMY_BINDS flag. So I have my app with a new model set up like so:

class CoreUser(UserMixin, db.Model):
    __bind_key__ = 'core'

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(255), unique=True)

So far so good. My app now checks this other database when it should. Here's the minus though. Since I'm using alembic to actually manage all of the schemas in the database, when I run my typical alembic revision --autogenerate script, it doesn't actually do anything to this second database at all, and seems to just ignore the __bind_key__ piece of the puzzle.

Instead, it sets up the proper core_user relation in the first database. How can I indicate to alembic that when it encounters this schema definition it should be creating the relation in a different database?

I've tried updating my alembic.ini file as shown below on the advice of this (ancient) thread:

[core_db]
sqlalchemy.url = <DATABASE_URI>
script_location = alembic

And then running the following commands:

alembic -n core_db revision --autogenerate
alembic -n core_db upgrade head

And while the revision is generated and this doesn't fail, there are no relations actually created in the database indicated by the core_db sqlalchemy.url parameter.

I haven't been able to find any better documentation than that 4 year old Google group thread, but my current approach doesn't seem to be working and I don't have any obvious next steps. I'm unable to find the appropriate alembic documentation, but this seems like a very simple and common use case, so I can't imagine that this isn't supported.

Any advice?

Answer 1

It's hard to handle migrations for many databases with different models.

In our case, we have N databases with different models. Following structure helps to keep databases isolated:

.
├── app
│   ├── __init__.py
│   ├── alembic.ini
│   ├── employee
│   │   ├── __init__.py
│   │   ├── models.py
│   │   └── views.py
│   ├── migrations
│   └── user
│       ├── __init__.py
│       ├── models.py
│       └── views.py
├── daemon
│   ├── __init__.py
│   ├── alembic.ini
│   ├── daemon_engine.py
│   ├── migrations
│   └── models.py
├── run.py
└── tests

Answer 2

The steps you've taken so far are correct.

But, did you assign the correct target_metadata in env.py for each database?

If the metadata that is passed in is the same for both your databases, no different relations will be found and therefore the autogenerated script will not reflect what you're looking for.

Check which tables are associated with the metadata by looking at the Base.metadata.tables variable.

Pass in only the tables you want for each database env.py. You should have one for each database in their respective script_location.

Answer 3

I got this to work with flask-sqlalchemy and alembic by using flask-migrate and its --multidb option here:

https://flask-migrate.readthedocs.io/en/latest/#multiple-database-support

I then had to modify all of my old database migrations (when i only had one db) as mentioned here:

https://github.com/miguelgrinberg/Flask-Migrate/issues/181

Still running into a bit of trouble with the upgrade script in that it adds and removes all of my old tables to this new database. Not sure what that's about but will know soon...

Answer 4

It is difficult. When creating a new revision with auto-generate feature, Alembic (as of v1.11.3) checks the history by connecting ONLY to one single database for the version table, and extract all the heads by accessing all script files in the folder migrations/versions (assume only one version path).

In other words Alembic always assumes there is only one database for each migration folder. You will get error when upgrading the second (or subsequent) migrations to one of the databases

For those interested with implementation detail, check out the following functions :

• MigrationContext.get_current_heads() at here, where Alembic verifies last commit history from database.
• RevisionContext._run_environment() at here, where Alembic loads the scripts of all the heads / branches, and compare with the result of the operation above.