Best practices for persistent database connections in Python when using Flask

Question

My question is about the recommended approach for handling database connections when using Flask in a production environment or other environment where performance is a concern. In Flask, the g object is available for storing things, and open database connections can be placed there to allow the application to reuse them in subsequent database queries during the same request. However, the g object doesn't persist across requests, so it seems that each new request would require a new database connection (and the performance hit that entails).

The most related question I found on this matter is this one: How to preserve database connection in a python web server but the answers only raise the abstract idea of connection pooling (without tying it to how one might use it within Flask and how it would survive across requests) or propose a solution that is only relevant to one particular type of database or particular stack.

So my question is about the general approach that should be taken when productionising apps built on Flask that connect to any kind of database. It seems like something involving connection pooling is in the right direction, especially since that would work for a traditional Python application. But I'm wondering what the recommended approach is when using Flask because of the aforementioned issues of persistence across connections, and also the fact that Flask apps in production are run from WSGI servers, which potentially add further complications.

EDIT: Based on the comments recommending flask sqlalchemy. Assuming flask sqlalchemy solves the problem, does it also work for Neo4J or indeed any arbitrary database that the Flask app uses? Many of the existing database connectors already support pooling natively, so why introduce an additional dependency whose primary purpose is to provide ORM capabilities rather than connection management? Also, how does sqlalchemy get around the fundamental problem of persistence across requests?

Answer 1

Turns out there is a straightforward way to achieve what I was after. But as the commenters suggested, if it is at all possible to go the flask sqlalchemy route, then you might want to go that way. My approach to solving the problem is to save the connection object in a module level variable that is then imported as necessary. That way it will be available for use from within Flask and by other modules. Here is a simplified version of what I did:

app.py

from flask import Flask
from extensions import neo4j

app = Flask(__name__)
neo4j.init_app(app)

extensions.py

from neo4j_db import Neo4j

neo4j = Neo4j()

neo4j_db.py

from neo4j import GraphDatabase

class Neo4j:
    def __init__(self):
        self.app = None
        self.driver = None

    def init_app(self, app):
        self.app = app
        self.connect()

    def connect(self):
        self.driver = GraphDatabase.driver('bolt://xxx')
        return self.driver

    def get_db(self):
        if not self.driver:
            return self.connect()
        return self.driver

example.py

from extensions import neo4j

driver = neo4j.get_db()

And from here driver will contain the database driver that will persist across Flask requests.

Hope that helps anyone that has the same issue.