Support for multiple databases

[mr-pj]

I was wondering if Flask-Migrate would be able to support the SQLALCHEMY_BINDS option.

I want to have frontend and backend database models be seperated for an application I am developing.

SQLAlchemy provides this option with SQLALCHEMY_BINDS and with defining the context in the table (example see here: http://pythonhosted.org/Flask-SQLAlchemy/binds.html)

Can you support this? If not is there any other recommendation of handling multiple databases in sqlalchemy with migrations?

[miguelgrinberg]

I'll think about this. Alembic does not really care because it works on one database at a time. If you create separate migrations folders for each db then you can work on migrations for each separately. The question is if Flask-Migrate can do something to simplify the workflow taking advantage of the SQLALCHEMY_BINDS configuration.

[mr-pj]

Hey,

what do you mean by different folders for each db?
When running db init , flask migrate/alembic pretty much uses the default database URI defined by SQLALCHEMY_DATABASE_URI

so even if I have two models like

class M1(db.Model):
   __bind_key__ = 'backend'
   [...]

class M2(db.Model):
   __bind_key__ = 'backend'
   [...]

will end up creating all models in the default database.
If I have two directories and change env.py to use a different connection string alembic will end up creating all models in the second database as well.

I mean for now I guess I can work with having all database models in both databases since they both rely on the same backend and just having the tablestructure in there does not do any harm. But when encountering two different types of databases i.e. sqllite and mysql we might run into different issues. (I.e. sqlite and renaming database columns)

[miguelgrinberg]

I haven't tested multiple databases so I don't know if it'll work. But I did not expect you will get all the models in all the databases, since Flask-SQLAlchemy separates the models per database. As long as Alembic is given the correct db instance in each case I think it should do the right thing. Maybe Flask-Migrate can simplify the workflow by providing command line shortcuts to do this, though.

As I said, I'm sure there is something that can be done, but I'm not sure what is it yet, I have to create a sample project to figure it out.

[mr-pj]

Hi,

flask-sqlalchemy indeed supports seperation with the bind command. But neither Flask-Migrate nor alembic use flask-sqlalchemy. They use sqlalchemy which does not provide SQLALCHEMY_BINDS

It is something flask-sqlalchemy provides for easy access of using multiple databases (documentation here: https://pythonhosted.org/Flask-SQLAlchemy/binds.html )

Maybe a possible solution would be to use the flask-sqlalchemy engine instead of the plain sqlalchemy engine? But I am not sure how flask-migrate and alembic are tied together

[espositocode]

I would love support for multiple databases as well, but I don't know how this would be best implemented.

[oztalha]

+1. In my app I am using both a relational db (postgres) and a nosql (mongo). So at this point, have to use the latter off Flask-Migrate.

[miguelgrinberg]

@oztalha Flask-Migrate is based on Alembic, which in turn is based on SQLAlchemy. NoSQL databases are not going to be supported.

[spinningD20]

How would one go about telling flask-migrate to ignore any models that have _ bind_key _ defined, or something to that effect? I'm integrating with a third party mssql db for reading some data, and I don't need it to try and migrate any of that... just the primary db defined in SQLALCHEMY_DATABASE_URI. Thoughts, ideas?

[spinningD20]

I had it in my head that I would just comment out all the stuff regarding the third party db, but the issue is I have foreignkeys and relationships in my primary db referencing that, so there's no clear line there for how far I have to back out the third party db to get the primary db migrations to go through :\ .

[miguelgrinberg]

@trugate: are you familiar with the include_object option? It allows you to provide a callback function that Alembic will invoke before processing an entity in an autogenerated migration. This is how you can make Alembic ignore certain tables or columns.

[spinningD20]

Unfortunately I'm wrestling with how to ignore foreignkeys that reference these from my primary db. I use relationships for convenience in a lot of places already, and I'm looking up now how to make alembic not try to reflect tables referenced in foreignkeys... based on the (lack of) examples out there, I might fall back to some external mssql -> sqlite mirroring service I set up to avoid these issues that haunt me... but I really don't want to do that, it feels so wrong and ugly. I realize my particular situation isn't an issue with flask-migrate, but rather alembic at this level.

Edit: To clarify, include_objects doesn't solve my problem, because it's still trying to reflecttable of the foreignkey reference to those tables.

Edit2: Here's a gist of the two models I'm trying to use with alembic, and the alembic.ini and env.py sections. https://gist.github.com/trugate/b49fcc41cdce7519a358

Edit3: I had a table in my sqlite (primary db) that was existing and had a reference to ProjectInfo. The include_object I have in my gist works correctly, as long as you don't have any foreignkeys referencing these ignored tables in your db already.

[jonelovemira]

@miguelgrinberg was right. I have tried to use include_object in migrations/env.py like this:

// add a function
def include_object(object, name, type_, reflected, compare_to):
if object.info.has_key('bind_key'):
if object.info['bind_key'] == 'bindkey':
return False
return True
// add callbacks in configure of context ( in the function named 'run_migrations_online' )
context.configure(
connection=connection,
target_metadata=target_metadata,
include_object = include_object
)

but this method is only for separate the bind and default databases.

[briglass]

Hello,

I've built my Flask site using Flask-Security and Flask-Migrate, meaning I have a SQLite database. I've deployed it to AWS, and it seems as though MySQL is better for this (especially if I use RDS). Hopefully I am wrong, and there is a decent way to continue to use SQLite on AWS EB. If not, is there a fairly simple way to switch over to MySQL (maintaining my use of Flask-Migrate and Flask-Security)?

Thanks

[reee]

As dict.has_key() has been removed in python3, the python3 version should go as blow:

1. Add the include_object fuction:

def include_object(object, name, type_, reflected, compare_to):                                            
     if 'bind_key' in object.info:                                                                          
         if object.info['bind_key'] == 'bind_key_name':                                                            
             return False                                                                                   
     return True

2. add callbacks in configure of context ( in the function named 'run_migrations_online' )

context.configure(                                                                                 
            connection=connection,                                                                         
            target_metadata=target_metadata,                                                               
            include_object=include_object,                                                                 
            process_revision_directives=process_revision_directives,                                       
            **current_app.extensions['migrate'].configure_args                                             
        )