SQLAlchemy / Flask / PostgreSQL pool connection


Solution 1

Flask-SQLAlchemy creates a SQLAlchemy engine using the create_engine method in SQLAlchemy, which you can read about some of the options and defaults in the documentation for the create_engine function. According to the Flask-SQLAlchemy documentation, you can specify some of the configuration options specific to pooling. You can set those values in various ways, which you can read about in the Flask configuration. You already have a settings configuration module, so you can add to your config file something like...


So, yes, there is automatic connection pooling. This is provided by SQLAlchemy by default. As of the posting of this answer, Flask-SQLAlchemy allows you to modify some of these options using the configuration file (although it appears there is an old pull request to allow you to specify ANY create_engine parameter).

If you need more support for configuring the SQLAlchemy engine than Flask-SQLAlchemy provides, you can either use SQLAlchemy without the Flask-SQLAlchemy wrapper, or modify Flask-SQLAlchemy (perhaps merging the pull request) to allow this.

Solution 2

Every worker of your flask instance running a proper wsgi server with multiple workers will have it's own engine - because your script is loaded for every worker separately.

In consequence you have simply no control about the real pooling, except you limit your worker threads of wsgi server and calculate the max allowed connections per engine/pool instance.

Further details can be read at https://docs.sqlalchemy.org/en/13/core/pooling.html#using-connection-pools-with-multiprocessing


Related videos on Youtube

Noé Malzieu
Author by

Noé Malzieu

Updated on September 14, 2022


  • Noé Malzieu
    Noé Malzieu 9 months

    After having played for a long time with Django, I'm trying a bit of Flask with SQLAlchemy, and I must say I quite like it. However there is something that I don't get: I have a small Flask / SQLAlchemy app that uses PostgreSQL. In my __init__.py file I have:

    from flask import Flask
    from flask.ext.sqlalchemy import SQLAlchemy
    app = Flask(__name__)
    db = SQLAlchemy(app)

    I wanted to know:

    1. Is there automatic connection pooling ? Or does every call to Model.query... create a new connection to the database?
    2. If not, how can I configure it?
    3. If yes, what are the defaults, how can I modify the values?

    Thank you very much for you help !

  • Noé Malzieu
    Noé Malzieu about 10 years
    Thank you so much for the detailed answer! I must have missed this part in the Flask-SQLAlchemy documentation!
  • Matthew Moisen
    Matthew Moisen almost 7 years
    Any idea on how to set the connection pool to a NullPool ?
  • SuperShoot
    SuperShoot over 4 years
    This does not answer the OPs question.
  • amj
    amj almost 4 years
    @SuperShoot it was answered to part of one of the comments. They had asked how to set it to a Null Pool
  • Brady Perry
    Brady Perry about 2 years
    This is a key point to make. The default POOL_SIZE is 5, which really confused me when I inspected my database connections directly and saw there were 15 connections (I'm using gunicorn with 4 workers). I had no idea why until reading this. Thank you!