SQLAlchemy Session.remove() doesn't seem to terminate sessions-CodePudding

I'm creating a Flask app, and initially was using Flask-SQLAlchemy for my db connection for initial development. To start, I am using a single database for all development. However, once the app goes to production, I'll be using multiple databases - clients can sign up for the service, launch their DB, and the Flask app with then access their specific DB keying off of the cname.

I know Flask-SQLAlchemy handles the opening and closing of sessions on it's own, but due to the inability to use Session.configure(bind=engine) to reconfigure the db connection, this hasn't worked for my needs. (This paragraph is here to give insight into why I'm steering away from Flask-SQLAlchemy in spite of the built in session management).

I have the POC working - I set up a dbConn blueprint where the connections are handled. I set up a @dbConn.before_app_request handler to grab the cname from the request, create an engine, and start a scoped_session using the new engine. This successfully connects to the new database and gets the user's specific information:

Base = declarative_base()
Session = scoped_session(sessionmaker())

def init_db(url):
    engine = create_engine(url)
    Session.configure(bind=engine)
    Base.metadata.create_all(bind=engine)

@dbConn.before_app_request
def set_database():
    cname = request.headers['X-Subdomain']
    db_url = 'postgresql://tracer_connect:postgrespw@db:5432/' cname
    init_db(db_url)

@dbConn.after_app_request
def close_db_connect(resp):
    Session.remove()
    return resp

My big issues comes in closing/removing the session. Per SQLAlchemy docs:

Ensure that scoped_session.remove() is called when the web request ends, usually by integrating with the web framework’s event system to establish an “on request end” event.

In theory, in my block, the sessions should be removed, But when I pull up PGAdmin and take a look at the active server connections, I see all of the new sever sessions that were initiated by the request still active.

I've also tried variations on the end event, by using flask events like .teardown_request and .teardown_appcontext to no avail.

The requests in question are all currently only read requests, if that makes a difference. I'm at a loss. From everything I have read on the issue, this should be the solution, but it doesn't seem to be. I'm fairly new to Flask and SQLAlchemy, so I am happy to take any direction anyone can give. I'm also willing to accept that maybe this is a postgres or PGAdmin issue, too. In theory, maybe the sqlalchemy session is being removed but a server progres session persists, but I'm running out of techniques to try to test these theories.

CodePudding user response：

SQLAlchemy uses a connection pool so that connections can be re-used between requests. Creating a new connection is expensive from a performance perspective. Maybe you are seeing active connections that are just sitting in the pool. I would first setup the NullPool and see if connections are closing, then remove that and set the echo_pool flag and see if connections are returned to pool both explained below.

I think there is a set size with an allowed overflow explained here: connection-pool-configuration

The next section also explains how to use a NullPool, which I think just opens and closes a connection (not good for performance but maybe easy for debugging): switching-pool-implementations

The pool documentation also explains how to turn on debugging for the pool. Then you can see if the connections are being returned to the pool or not.

https://docs.sqlalchemy.org/en/20/core/pooling.html#logging-reset-on-return-events