Gino: Pattern for Declarative Use of Gino to Connect to Multiple Databases

[mashaalmemon]

• GINO version: 0.7.0
• Python version: 3.5
• Operating System: macOS

Description

We are building a multi-tenant web application using PostGreSQL, Sanic using asyncio semantics. Essentially each tenant has their own distinct database and so the app, depending on who the request is for, must connect to the appropriate database and perform database calls.

Each Sanic request could then potentially access a completely different database. I'd like to use a declarative type interface with Models.

What is the appropriate pattern for this. I may have N different databases to connect to.

What I Did

Assuming I have a separate engine object for each client, I see how I can use db.bind to change the engine currently being used by the model.

Below is what I have so far. Note I define models separately in distinct files for code organization. But I'm not sure if this is the right approach because:

• db object for a declared model is set before await, and we are waiting for a result and processing of other requests will continue.
• While we are waiting for the result we may have passed and control to another Sanic request for a different customer, and in the class, the global db object may have a different object set, (and thus will use a different database).

# m_company.py
import asyncio
from gino import Gino

db = Gino()

class Company(db.Model):
    __tablename__ = 'companys'

    id = db.Column(db.Integer(), primary_key=True)
    company_name = db.Column(db.Unicode(), default='noname')

    @classmethod
    def bind_engine(cls, engine):
        global db
        db.bind

# m_user.py
import asyncio
from gino import Gino

db = Gino()

class User(db.Model):
    __tablename__ = 'users'

    id = db.Column(db.Integer(), primary_key=True)
    username = db.Column(db.Unicode(), default='noname')

    @classmethod
    def bind_engine(cls, engine):
        global db
        db.bind

Something like this must be done to set engine to points to appropriate database, before a query is carried out:

# code which uses declarative model pattern
from m_user import User

class Main:

    # assume different engine per customer
    engine_customer_db1 = #... engine for connection to customer 1 database
    engine_customer_db2 = #... engine for connection to customer 2 database

   # this method is called to get the appropriate engine for a particular customer
    @classmethod
    def get_customer_engine(cls, customer_id):
       # ... assume it returns the appropriate engine depending on customer id

    # this method is called by various Sanic requets
    @classmethod
    async def get_customer_users(cls, customer_id):
        User.bind_engine(Main.get_customer_engine(customer_id))
        users = await User.get(1)
        return users

I've written this code for illustration.

Really what I'm looking for is feedback or a suggestion on if this is appropriate - I'm worried that by binding different engines per request, I may have a database query that is affected by the database engine being modified between requests. Is there a declarative approach that is more appropriate for my use case?

It looks like for SQLAlchemy other's have asked similar questions on stack overflow:
https://stackoverflow.com/questions/13372001/multi-tenancy-with-sqlalchemy

It is important to note that in my application tenancy can change dynamically while the server is running. At one moment we may have n customers, and another we may have n+1 or n-1 customers. So a solution for me wouldn't be statically specifying databases

Thanks in advance.

[mashaalmemon]

Some more thoughts here as I investigate. I see that you can bind an engine to crud functions like so:

    # this method is called by various Sanic requets
    @classmethod
    async def get_customer_users(cls, customer_id):
        users = await User.get(1, bind=Main.get_customer_engine(customer_id))
        return users

However it is not clear how to set the bound engine for a query or for other chainable API calls:

    # this method is called by various Sanic requets
    @classmethod
    async def get_customer_users(cls, customer_id):
        User.bind_engine(Main.get_customer_engine(customer_id)) # can I remove this line
        users = await User.query.gino.all() # if I remove above line, can i bind on the fly in another way?
        return users

[fantix]

Before everything, I would suggest use a single db instance overall so that all tables share the same MetaData(db), instead of several db instances for each table unless you have a reason to do so.

To achieve your goal, the first solution is the same as you investigated - to use engines explicitly without binding them to db. That is to say, implicit execution (chainable .gino.all()) is not an option - instead you should directly use engine.all():

    @classmethod
    async def get_customer_users(cls, customer_id):
        engine = Main.get_customer_engine(customer_id)
        users = await engine.all(User.query)
        return users

This way, different concurrent coroutines won't interfere with each other, because engines are always explicitly specified.

A second solution is a bit more complex and implicit, but cleaner in code. This requires overriding Gino to have a dynamic binding:

try:
    from contextvars import ContextVar
except ImportError:
    from aiocontextvars import ContextVar

current_customer_id = ContextVar('customer_id')

class ContextualGino(Gino):
    @property
    def bind(self):
        return Main.get_customer_engine(current_customer_id.get())

(https://github.com/fantix/aiocontextvars)

Then set and clear the context value in a common place once for all, like Sanic request and response middleware:

@app.middleware('request')
async def set_current_customer_id(request):
    current_customer_id.set(request.get(...))

@app.middleware('response')
async def clear_current_customer_id(request, response):
    current_customer_id.delete()

With such, you would be able to directly use GINO to access database without having to worry about the actual engine to use in any context of Sanic request (be careful about websockets):

    @classmethod
    async def get_customer_users(cls, customer_id):
        # They should both work with correct engine
        users = await User.get(1)
        users = await User.query.gino.all()

[fantix]

Closing for now, please feel free to reopen for further discussion.

[thomas-maschler]

Thanks for posting this pattern @fantix .
I have a similar use case as mashaalmemon, only that I want to use different engines, depending on the Request method. I use AWS Aurora, and for get requests, I would like to point to the read only endpoint (basically a load balancer which forwards requests to the different read replicas). For Put/Post/Patch/Delete requests, I'd like to point to the write endpoint, which forwards requests to the writer node of the cluster.

I use

• Gino 1.0.0rc4
• Gino-Starlette 0.1.1
• FastAPI 0.54.1
• Python 3.7

I tried overriding the Gino class and implement a middleware as suggested below

from gino_starlette import Gino
from fastapi import FastAPI
from contextvars import ContextVar

mode = ContextVar('mode')

class ContextualGino(Gino):
    @property
    def bind(self):
        return get_engine(mode.get())

def get_engine(engine_mode):
    # return correct engine
    ...

app: FastAPI = FastAPI()
db: MetaData = ContextualGino(app)

@app.middleware('http')
async def set_db_mode(request: Request, call_next):
    # set/ delete mode depending on request.method
    ...

However, I get an error

 File "./app/main.py", line 5, in <module>
    from .application import app
  File "./app/application.py", line 36, in <module>
    db: MetaData = ContextualGino(app)
  File "/.../lib/python3.7/site-packages/gino_starlette.py", line 163, in __init__
    super().__init__(*args, **kwargs)
  File "/.../lib/python3.7/site-packages/gino/api.py", line 351, in __init__
    super().__init__(bind=bind, **kwargs)
  File "<string>", line 2, in __init__
  File "/.../lib/python3.7/site-packages/sqlalchemy/util/deprecations.py", line 128, in warned
    return fn(*args, **kwargs)
  File "/.../lib/python3.7/site-packages/sqlalchemy/sql/schema.py", line 3966, in __init__
    self.bind = bind
AttributeError: can't set attribute

Any idea why this doesn't work?

[thomas-maschler]

Sorry, I could fix this issue by adding a property setter

class ContextualGino(Gino):

    @property
    def bind(self):
        return get_engine(mode.get())

    @bind.setter
    def bind(self, _):
        pass

However, this still doesn't work for me.
I now get an error

  File "/.../lib/python3.7/site-packages/starlette/routing.py", line 517, in lifespan
    await self.startup()
  File "/.../lib/python3.7/site-packages/starlette/routing.py", line 494, in startup
    await handler()
  File "/.../lib/python3.7/site-packages/gino_starlette.py", line 198, in startup
    extra={"color_message": msg + self.bind.repr(color=True)},
AttributeError: 'coroutine' object has no attribute 'repr'

I do not use async/ await to create my engine or bind. Why is bind now a coroutine?

[fantix]

https://python-gino.org/docs/en/master/explanation/engine.html#creating-engines

The SQLAlchemy strategy of GINO makes create_engine return a coroutine, you need to await on it to get the engine.

[thomas-maschler]

Ok, thanks.

I think the issue is, that other packages try to access the bind property synchronously, not as a coroutine. To fix the error message above, I would need to update gino_starlette to use
extra={"color_message": msg + (await self.bind).repr(color=True)}
And then also any other function which tries to us bind. Is there a better way to address this issue?

[fantix]

No, self.bind must not be a coroutine - that means you should await on create_engine(). Please see my example below:

from asyncio import Future

from fastapi import FastAPI, Request
from gino_starlette import Gino

from gino import create_engine

e = None


class ContextualGino(Gino):
    @property
    def bind(self):
        if e and e.done():
            return e.result()
        else:
            # not in a request
            return self._bind

    @bind.setter
    def bind(self, val):
        self._bind = val


app = FastAPI()
db = ContextualGino(app)


@app.middleware("http")
async def set_db_mode(request: Request, call_next):
    global e
    if e is None:
        e = Future()
        engine = await create_engine("postgresql://localhost/gino")
        e.set_result(engine)
    await e
    return await call_next(request)


@app.get("/")
async def get():
    return dict(now=await db.scalar("SELECT now()"))

[thomas-maschler]

BOOM!

Thanks, this works.

[yaniaular]

@fantix This issue thread it's very helpfully thanks, however, I have a question about create method with a specific engine.

I am using a single DB instance, but, I have a thread in python (in a different loop) where I need to use a specific engine. (I am using a thread to keep open a RabbitMQ consumer)

When I receive a event from rabbit I need to make queries to DB (including create). The solution that you said above works for me, I made the following:

await User.query.where(User._id == oid).gino.first()

#changed by

await engine.first(User.query.where(User._id == oid))

However, I have not been able to use a specific engine when I want to create a new record in my database. I would like to know if Is it possible to make some like following?:

await engine.all(await User.create(**data))

[yaniaular]

@fantix I made this and It's works for me,

# For create
await engine.scalar(User.insert().values(**data))

# For update
await engine.scalar(User.update.where(User._id == oid).values(**data))

what do you think?, is there a way more cleaner in code?