Flask Migrate for Multiple Database

Question

To give a bit of the background on our application, we had already configured a database for existing endpoints in the app. Now there is a new requirement to add endpoints to the same application talking to a different database. As stated in the flask-sqlalchemy documentation here, to make Flask-Migrate work with multiple databases, we deleted previous migrations, added SQLALCHEMY_BINDS in configuration, added bind_key in the models and used the following command;

flask db init --multidb

This created a brand new migrations folder successfully with the versions folder. Then we used the following command;

flask db migrate

This created a new migration file with the appropriate migration code (created automatically by Alembic) for creating new tables in the new database and also added the alembic_version table in the new database. No migration code was added to the newly created migration file for the old/existing database (as expected) because no changes were made to Model associated with that existing database. We then went on to do:

flask db upgrade

and everything worked as expected. The tables were created successfully in the new database and we were able to interact with the tables with the newly defined endpoints.

Now, another requirement came in to add new columns to those newly created tables in the second database. So, we added the columns in the model and now we are trying to migrate again, but we keep getting the following error:

INFO  [alembic.env] Migrating database <default>
INFO  [alembic.runtime.migration] Context impl SQLiteImpl.
INFO  [alembic.runtime.migration] Will assume non-transactional DDL.
ERROR [root] Error: Target database is not up to date.

What we have tried till now,

Deleted the migrations folder.
Re-run the init-migrate-upgrade cycle.
Made sure that all the alembic versions are in sync (alembic table in both databases, head in repo).
We also tried to do a single DB migration and it worked. Somehow it seems like there is an issue while migrating multiple DBs.

ORIGINAL CONFIGURATION (single DB):

# DB path definition
SQLALCHEMY_DATABASE_URI='full_path_to_db'

# Model
class Table1(db.Model):
    #column declarations with datatypes

NEW CONFIGURATION (with multidb):

# DB Path definitions (NOT using SQLALCHEMY_DATABASE_URI anymore, using SQLALCHEMY_BINDS instead.)
SQLALCHEMY_BINDS = {
    'db1': 'full_path_to_db',
    'db2': 'full_path_to_db'
    }

# Models
class Table1(db.Model):
    __tablename__ = 'table1'
    __bind_key__ = 'db1'
      #column declarations
    
class Table2(db.Model):
    __tablename__ = 'table2'
    __bind_key__ = 'db2'
      #column declarations

But the error is not getting resolved. I am not sure what we are missing here.

To reiterate, the first/initial migration (and upgrade) works just fine. The alembic_version tables on both DBs are updated properly with the correct migration version number. But thereafter, when we make any change to the Model and are required to do a second migration, no new migration file is created and it throws the error Target database not found.

Appreciate the help!

Faizan AlHassan Faizan AlHassan · Accepted Answer · 2020-11-20T11:29:37

I was also getting same issue. My issue was resolved when I applied one data base using SQLALCHEMY_DATABASE_URI and other using SQLALCHEMY_BINDS and then removed migrations and tables (not confirm for removing table is necessary or not). Then it was pefectly picking all migrations without error Error: Target database is not up to date. My previous code:

SQLALCHEMY_BINDS = {
        'central': POST_CONN_URI % POSTGRES_MAIN_DB_NAME,
        'tenant': POST_CONN_URI % 'demo_tenant'
    }

and new code:

SQLALCHEMY_DATABASE_URI = POST_CONN_URI % POSTGRES_MAIN_DB_NAME
SQLALCHEMY_BINDS = {
   'tenant': POST_CONN_URI % 'demo_tenant'
}

Flask Migrate for Multiple Database

1 Answers