Place to initialize DB in Flask

Question

I'm developing app in Flask and it requires DB, so what I have is I do:

app = Flask(__name__)
@app.before_request
def init_db_connection:
  # here I connect to my DB

@app.teardown_request
def destroy_db(exception):
  # here I destroy database connection

On the development server (app.run()) this is not the best place to initialize the database I guess, because also DB will get initialized even it the request comes for the static file. In production I can have a separate web server serving static files, so it shouldn't be a problem.

But still I'm thinking if this is right way to initialize DB or it is better for example to initialize DB in Blueprint which is used in that moment? Just want to know the best practice and how you guys doing this :)

Thanks!

Cofer

What library are you using to connect to the database? If the library is doing connection pooling or using lazy connections it shouldn't be a problem. – Unionist

Hi. I'm using psycopg2 to connect to PostgreSQL. So you suggest using psycopg2.pool and create connections with getconn? – Carsick

Exactly. Or use SQLAlchemy on top of it. SQLAlchemy can also handle connection pooling. – Unionist

Nice. Thanks for the tip. I do not want to use SQLAlchemy for now (I'm working with plain SQL queries and don't need additional complexity yet), but will start to use pooler. – Carsick

Answer 1

Use Connection Pooling:

If you're using a database driver or ORM that supports connection pooling (e.g., SQLAlchemy), configure it to manage connections efficiently. This helps reduce the overhead of creating and destroying connections frequently.

Database Initialization and Teardown:

Initializing the database connection in @app.before_request and tearing it down in @app.teardown_request is suitable. However, you may want to limit the database connection setup to only those routes that actually interact with the database.

Blueprints:

Using Blueprints can be a good practice for organizing your application.

Feticide

You can improve your answer by adding backticks () around code (e.g. @app.teardown`). Also it would be good to add links to the blueprints you're suggesting. Finally (optional) it might help to add a longer snippet to make clear where exactly you'd place the db initialization. – Power

Answer 2

Here are a few options:

1. Use connection pooling or a library which does connection pooling. This will reduce the overhead so that getting a db connection when you don't need it (e.g., for static files) is not a problem.
2. In your init_db_connection method, you can check the route and if it is a static route or route where you don't think the db is necessary then don't do the db init.
3. Use a blueprint and put your init code in the blueprint so routes outside of the blueprint don't do the init.
4. Create the connection to your database inside a context block in the function which uses it (e.g., see below):

@app.route("/example_using_db")
def example_using_db():
    with MyDBCursor() as cur:  # context manager which connects to db and provides cursor
        data = cur.execute("SELECT * from EXAMPLE").fetchall()
    

Depending on your library, you may be able to get a context manager like above. If not you can either write one or put your code which connects to the db in a try/finally block so that you release the connection if there is an exception.

Yacketyyak