How do I make Flask SQLAlchemy reuse db connections?
After reading the SQLAlchemy docs and some fiddling with the db instance, I finally got the solution. Add db.get_engine(self.app).dispose() in tearDown() so that it looks like:
def tearDown(self): db.session.remove() db.drop_all() db.get_engine(self.app).dispose() self._ctx.pop()
Since the questions was asked about an year ago, I figure the OP must've resolved his issues. But for whoever wandered to here (like me) trying to figure out what's going, here's my best explanation:
As van said, the problem is indeed with the test case calling setUp and tearDown for each test. Though the connection is not exactly leaking from SQLAlchemy, but instead it is because of that each test has its own setUp, multiple instances of the app is created: each app will have its own database connection pool, which presumably isn't reused or recycled when the test finishes.
In other words, the connection is being checked out and returned to the pool correctly, but the connection then live on as an idle connection for future transactions within the same app (the point of connection pooling).
In the test case above, around 20 connection pools (each with an idle connection because of the create/drop_all) is created and occupying postgres connection limit.
EDIT: SQLALCHEMY_COMMIT_ON_TEARDOWN was deprecated in Flask-SQLAlchemy in version 2.4.3. You can see the change note here where they recommend calling .commit() on your own:
What I do is register my own app.after_request which calls .commit() if the response status code is < 400. That requires that you properly structure your application to make sure HTTP transactions with response status codes < 400 should be committed to the database but I think it is a good design principle.
---- OLD OUTDATED ANSWER BELOW ----
In the most recent versions of Flask-SQLAlchemy, session.remove() is automatically called in app.after_request.
Also, see the SQLALCHEMY_COMMIT_ON_TEARDOWN setting here:
https://pythonhosted.org/Flask-SQLAlchemy/config.html?highlight=sqlalchemy_commit_on_teardown
That will automatically commit the transaction also.