How to SQLAlchemy database session create per Request

user_10500466 0
Viewed 945

I have a multi tenancy python falcon app. Every tenant have their own database. On incoming request, I need to connect to tenant database.

But there is a situation here. Database configs are stored on another service and configs changing regularly.

I tried session create before process resource. But sql queries slowing down after this change. To make this faster, what should I do? P.S. : I use PostgreSQL

from sqlalchemy import create_engine
from sqlalchemy.orm import scoped_session
from sqlalchemy.orm import sessionmaker
import config
import json
import requests
class DatabaseMiddleware:
    def __init__(self):
        pass
    def process_resource(self, req, resp, resource, params):
        engineConfig = requests.get('http://database:10003/v1/databases?loadOnly=config&appId=06535108-111a-11e9-ab14-d663bd873d93').text
        engineConfig = json.loads(engineConfig)
        engine = create_engine(
            '{dms}://{user}:{password}@{host}:{port}/{dbName}'.format(
            dms= engineConfig[0]['config']['dms'],
            user= engineConfig[0]['config']['user'],
            password= engineConfig[0]['config']['password'],
            host= engineConfig[0]['config']['host'],
            port= engineConfig[0]['config']['port'],
            dbName= engineConfig[0]['config']['dbName']
        ))
        session_factory = sessionmaker(bind=engine,autoflush=True)
        databaseSession = scoped_session(session_factory)
        resource.databaseSession = databaseSession
    def process_response(self, req, resp, resource, req_succeeded):
        if hasattr(resource, 'mainDatabase'):
            if not req_succeeded:
                resource.databaseSession.rollback()
            self.databaseSession.remove()
falconframework multi-tenant sqlalchemy python-3.x python
2 Answers

Your approach is probably wrong since it is against the intended usage pattern of engine instances described in engine disposal. The lifetime of engine instance should be the same as for the instance of your middleware.

The Engine refers to a connection pool, which means under normal circumstances, there are open database connections present while the Engine object is still resident in memory. When an Engine is garbage collected, its connection pool is no longer referred to by that Engine, and assuming none of its connections are still checked out, the pool and its connections will also be garbage collected, which has the effect of closing out the actual database connections as well. But otherwise, the Engine will hold onto open database connections assuming it uses the normally default pool implementation of QueuePool.

The Engine is intended to normally be a permanent fixture established up-front and maintained throughout the lifespan of an application. It is not intended to be created and disposed on a per-connection basis; it is instead a registry that maintains both a pool of connections as well as configurational information about the database and DBAPI in use, as well as some degree of internal caching of per-database resources.

user_57812480

In conjunction with SQLAlchemy, I use SQLService as an interface layer to SQLAlchemy's session manager and ORM layer, which nicely centralizes the core functionality of SQLAlchemy.

Here is my middleware component definition:

class DatabaseSessionComponent(object):
    """ Initiates a new Session for incoming request and closes it in the end. """

    def __init__(self, sqlalchemy_database_uri):
        self.sqlalchemy_database_uri = sqlalchemy_database_uri

    def process_resource(self, req, resp, resource, params):
        resource.db = sqlservice.SQLClient(
            self.sqlalchemy_database_uri, 
            model_class=BaseModel
        )

    def process_response(self, req, resp, resource):
        if hasattr(resource, "db"):
            resource.db.disconnect()

With its instantiation within the API's instantiation here:

api = falcon.API(
    middleware=[
        DatabaseSessionComponent(os.environ["SQLALCHEMY_DATABASE_URI"]),
    ]
)
user_45621560
Related