Flask-migrate: Migrate to multiple databases simultaneously

[BobkovS]

Greetings. I am doing a project and I need to migrate to several databases simultaneously. For example, I have bind 2 databases in SQLALCHEMY_BINDS like that :

 **app.config['SQLALCHEMY_BINDS'] = {
'bobkov1': 'postgresql://postgres:zabil2012@localhost:5431/bobkov1',
'bobkov' : 'postgresql://postgres:zabil2012@localhost:5431/bobkov'

}**

And now i want to migrate models to both of this databases. I'm tried to do it like that:

**class User(BaseModel, db.Model):
__tablename__ = 'user'
__bind_key__ = {'bobkov','bobkov1'}

id = db.Column(db.Integer, primary_key=True)
username = db.Column(db.String(64), index=True, unique=True)
email = db.Column(db.String(120), index=True, unique=True)
password_hash = db.Column(db.String(128))
posts = db.relationship('Post', backref='author', lazy='dynamic')

class Post(db.Model):
__tablename__ = 'post'
__bind_key__ = {'bobkov','bobkov1'}
id = db.Column(db.Integer, primary_key=True)
body = db.Column(db.String(140))
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))**

When trying to run this code, the model migrates only to the main database, which is defined in SQLALCHEMY_DATABASE_URI.

Help me please, how to configure that?

[miguelgrinberg]

Did you initialize your migrations repository with the --multidb option?

[BobkovS]

Yep, of course, my repository initialized with db init --multidb

[BobkovS]

The problem is that "db migrate" creates the table "alembic version" in all initialized databases, but the users and post tables are created only in the main database, specified by SQLALCHEMY_DATABASE_URI.

If in the bind_key model field, you specify only one base, then everything works fine, but if you specify more than one, as in my case, the above described problem appears

[miguelgrinberg]

How does specifying multiple binds for a single model work? Didn't know that was an option. Can you refer me to the docs please?

[nchauhan5]

@BobkovS, how did you fix this issue for yourself ? I am facing the same issue myself.

[BobkovS]

@nchauhan5, I am not fix this problem. This lib has no such mechanism

[cipher098]

@BobkovS Hi, I'm facing same problem, did you found any other solution for this problem?

[BobkovS]

@cipher098 Hello, no, i'm didn't find a solution as long as i remember

[pieterbergmans]

@miguelgrinberg - are you implying that it's not possible to create the same model in multiple databases? If I'm understanding @BobkovS correctly, I'm facing the same issue as he. That is, I have two databases in my config:

class DevelopmentConfig(BaseConfig):
    SQLALCHEMY_DATABASE_URI = 'postgres://postgres:postgres@auth-db:5432/tenant1'
    SQLALCHEMY_BINDS = {         
        'tenant2': 'postgres://postgres:postgres@auth-db:5432/tenant2' 
    }

I have a model that I want created in both databases (tenant1 and tenant2):

class Widgets(db.Model):
    __tablename__ = 'widgets'  
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False, unique=True)
    create_date = db.Column(db.DateTime, default=datetime.datetime.utcnow)

I run the db init --multidb command and the _migrations_ folder is created.

Then I run the db migrate command and I get the following out:

INFO  [alembic.env] Migrating database <default>
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.env] Migrating database tenant2
INFO  [alembic.runtime.migration] Context impl PostgresqlImpl.
INFO  [alembic.runtime.migration] Will assume transactional DDL.
INFO  [alembic.env] No changes in schema detected.

Can you explain why the Widgets model is not created in both databases?

[miguelgrinberg]

@pieterbergmans you are asking me questions for things that are outside of my control. This is specifically a SQLAlchemy question, not Alembic, and even less my Flask-Migrate package.

Let me turn it around back to you. Why did you expect the model would be added to both databases? Where did you see that this is how SQLAlchemy works?

[pieterbergmans]

@miguelgrinberg - first, I realize the topic is closed so thanks for answering. In regards to your question, I can't say I know SQLAlchemy that well so I'm not certain that it can or can't handle applying the same model to both databases. But, since you're telling me it can't...then I supposed I need to start looking for another way to make this happen. With that said, I guess I find it odd that it couldn't handle such a use case. After all, a multi-tenant app with one database per tenant is not that uncommon. Any recommendations for achieving this?

[miguelgrinberg]

I'm not telling you it can't be done. I'm genuinely asking what are you basing this on, because you have defined a model that isn't attached to any bind. Didn't know if that was a mistake or done on purpose.

I have always implemented multi-tenancy using a single database. Having an always growing list of databases is unmanageable in my opinion, I would not take that approach myself.

[pieterbergmans]

I don't disagree that multiple databases is more work. If I had my druthers, I'd opt for a single database and propagate a tenant_id throughout. But, a database per tenant is a requirement probably due to security reasons.

Anyway, I was hopeful that it could be done after reading this post: https://smirnov-am.github.io/multitenancy-with-flask/. It does exactly what I'm trying to achieve except that the author creates his databases/tables via a SQL script while simultaneously declaring his models in a models.py file. Not sure why he has the SQL script but, after discussing with you, perhaps it's because SQLAlchemy can't create the same model across multiple databases.

Anyway, I appreciate the input.

[cipher098]

@pieterbergmans I faced same problem and I created a custom solution which I think can help you too, so if you'll see in migrations file there will be an upgrade function and an upgrade_ function, so whenever a upgrade is called for one of bind it looks for instructions in function: upgrade_ and for default db it looks for instruction in upgrade_. But when we create migration they are created for default db. So what I did was, I created a custom script which goes into each migration file and copies content of upgrade_ function to all upgrade_ functions and then I made sure that I run this file everytime before doing upgrade.

[pieterbergmans]

@cipher098 - hey man, thanks so much for the reply! I'm pulling out my hair over this one. That's a neat solution you came up with, can you share any code? Also...I'm curious, did you try other approaches to this problem? I imagine that there are several ways to address this issue.

[cipher098]

@pieterbergmans I'm attaching code file with this message. Also there is one more simple approach that I thought of that could work but I didn't got time to test it, that is in upgrade function for each bind name we can change call from upgrade_ to upgrade_. If you try this other approach and this works please let me know.
mutli_db.zip

[pieterbergmans]

Thanks, @cipher098. I'll take a look and post back.

[pieterbergmans]

@miguelgrinberg - do you see any danger in changing the _script.py.mako_ file from...

def upgrade_${db_name}():
    ${context.get("_upgrades" % db_name, "pass")}

def downgrade_${db_name}():
    ${context.get("%s_downgrades" % db_name, "pass")}

To...

def upgrade_${db_name}():
    ${context.get("_upgrades")}

def downgrade_${db_name}():
    ${context.get("_downgrades")}

In this way, every time a migration is created the changes are applied to all def upgrade_<bind_key> and def upgrade_<bind_key> functions.

cc: @cipher098

[miguelgrinberg]

@pieterbergmans Any Python code that you write in the migration will run just fine, but what I'm missing is why you concentrate so much on getting the migrations to work when you haven't done anything to get SQLAlchemy to work. Or have you done anything on that front that you haven't discussed?

Because you will need SQLAlchemy to work as well, right? And if you get SQLAlchemy to work then Alembic will follow suit without the need to hack anything (probably).

[pieterbergmans]

@miguelgrinberg...I have it working with your Flask-SQLAlchemy extension and your application factory pattern using Blueprints. My __init__.py file is...

db = MultiTenantSQLAlchemy()
cors = CORS()
ma = Marshmallow()
migrate = Migrate()

def create_app():
    app = Flask(__name__)
    app_settings = os.getenv('APP_SETTINGS')
    app.config.from_object(app_settings) 

    db.init_app(app)
    cors.init_app(app)
    ma.init_app(app)  
    migrate.init_app(app, db)

    from project.api.auth import auth_blueprint
    app.register_blueprint(auth_blueprint)

    @app.shell_context_processor
    def ctx():
        return { 'app': app, 'db': db }

    return app 

I borrowed the MultiTenantSQLAlchemy class code from @mikka...https://gist.github.com/miikka/28a7bd77574a00fcec8d

class MultiTenantSQLAlchemy(SQLAlchemy):
    def choose_tenant(self, bind_key):
        if hasattr(g, 'tenant'):
            raise RuntimeError('Switching tenant in the middle of the request.')
        logging.info('setting g.tenant')        
        g.tenant = bind_key
        logging.info(f'{g.tenant}')

    def get_engine(self, app=None, bind=None):
        logging.info('get_engine...')
        if bind is None:
            if not hasattr(g, 'tenant'):
                # raise RuntimeError('No tenant chosen.')
                return super().get_engine(app=app, bind=None)
            bind = g.tenant
        return super().get_engine(app=app, bind=bind)

As mentioned in my previous post, my config file and models are like so:

class DevelopmentConfig(BaseConfig):    
    SQLALCHEMY_DATABASE_URI = 'postgres://postgres:postgres@auth-db:5432/general'
    SQLALCHEMY_BINDS = {         
        'tenant1': 'postgres://postgres:postgres@auth-db:5432/tenant1',
        'tenant2': 'postgres://postgres:postgres@auth-db:5432/tenant2' 
    }

- - - - -

class Widgets(db.Model):
    __tablename__ = 'widgets'  
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    name = db.Column(db.String(100), nullable=False, unique=True)
    create_date = db.Column(db.DateTime, default=datetime.datetime.utcnow)

And, my blueprint...

auth_blueprint = Blueprint('auth', __name__)
api = Api(auth_blueprint)

class authWidgets(Resource):
    def get(self):        
        response_object = {
            'status': 'fail',            
        } 

        # Still need to implement getting the 
        # tenant info from headers
        tenant_from_headers = 'tenant1'

        db.choose_tenant(tenant_from_headers)        
        r = Widgets.query.all()
        if not r:
            response_object, 400

        response_object['status'] = 'success'
        response_object['data'] = f'{r}'
        return response_object, 200

api.add_resource(authWidgets, '/api/auth/widgets')

I just slapped this together so I still have some work to do but it seems to be working well.

Does this address your question? Are there any glaring omissions that you can see? I use about 2% of what SQLAlchemy provides so of course I could be overlooking something major. Given your in-depth knowledge of python, any advice would be appreciated.

[miguelgrinberg]

Okay, looking at this code I can sort of see how that would work. I'm not completely sure this is safe, because you are interfering with the way Flask-SQLAlchemy manages its connections, but if you tested it and it works, then I guess it is okay. I would be very careful about authentication and ensure there is no way a user can somehow gain access to a tenant other that the correct one.

Also, Flask-SQLAlchemy is not my extension. I wrote many, but not that one. :)

[pieterbergmans]

Thanks, @miguelgrinberg. I appreciate your feedback. I'll have to look into how Flask-SQLAlchemy manages its connections, particularly from a security standpoint. And, regarding authentication, I couldn't agree more. I have yet to implement auth but I plan on making it robust, for sure.