Two engines with the same model

[paulin-mipt]

Description

Hi again, fantix and the team!

I'm thinking of the best way of having two engines with the same models (e.g. one engine for a rw- and one for ro- database replica). It seems to me, that if I create two Gino objects, I'll have to fully redeclare models, like this:

db = Gino()
db_ro = Gino()

class MyModel(db.Model):
  attr = db.Column(...)

class MyModelRo(db_ro.Model):
  attr = db_ro.Column(...)

Is there a more elegant way to do this?

[fantix]

Is it necessary for you to have the engine bound to the db (aka MetaData)? If not, you could do something like this:

from gino import Gino, create_engine

db = Gino()

class MyModel(db.Model):
  attr = db.Column(...)

async def main():
    engine = await create_engine(...)
    engine_ro = await create_engine(...)
    
    async with db.with_bind(engine):
        # do normal stuff

    async with db.with_bind(engine_ro):
        # do read-only stuff

And you can always specify the bind on the fly:

await MyModel.get(my_id, bind=engine_ro)

Most query methods have the bind keyword argument.

[renanvieira]

It's possible to have something like SQLAlchemy session bind?

engines = {
    'master':create_engine("sqlite:///master.db"),
    'other':create_engine("sqlite:///other.db"),
    'slave1':create_engine("sqlite:///slave1.db"),
    'slave2':create_engine("sqlite:///slave2.db"),
}

from sqlalchemy.orm import Session, sessionmaker
import random

class RoutingSession(Session):
    def get_bind(self, mapper=None, clause=None):
        if self._flushing:
            return engines['master']
        else:
            return engines[
                random.choice(['slave1','slave2'])
            ]

Doc links: https://docs.sqlalchemy.org/en/13/orm/persistence_techniques.html#custom-vertical-partitioning
https://techspot.zzzeek.org/2012/01/11/django-style-database-routers-in-sqlalchemy/

[fantix]

@renanvieira

It's possible to have something like SQLAlchemy session bind?

Yes and no. For now, if the mapper, clause, and self._flushing is not a must to have, one could always override Gino to have different implicit engines(bind), for example:

from gino import Gino


class RoundRobinGino(Gino):
    num = 0

    async def init_engines(self):
        self.binds = [
            await gino.create_engine(...),
            await gino.create_engine(...),
            await gino.create_engine(...),
        ]

    @property
    def bind(self):
        self.num = (self.num + 1) % len(self.binds)
        return self.binds[self.num]

# remember to call init_engines() manually at the beginning

For a more full-featured custom vertical partitioning function, GINO needs to implement it. Please feel free to open a new feature request issue for that.

[fantix]

Closing due to inactivity. Please feel free to reopen if you have further issues.