Peewee x Dash: DB connections aren't reused when called inside a Dash callback function

Question

I'm noticing that within a Plotly Dash app that uses a DB managed by Peewee, I'm getting playhouse.pool.MaxConnectionsExceeded: Exceeded maximum connections., which is visible here:

ariobot=# SELECT count(*) FROM pg_stat_activity where client_port > 0 and application_name = 'test 1';
 count 
-------
    21
(1 row)

ariobot=# 

This is the code that presents this behaviour:

# test1.py

from peewee import Model, TextField
from playhouse.pool import PooledPostgresqlExtDatabase

db = PooledPostgresqlExtDatabase(
    "postgresql://postgres@127.0.0.1:5432/ariobot", autoconnect=True, application_name='test 1'
)

class BaseModel(Model):
    class Meta:
        database = db

class Test(BaseModel):
    test = TextField()

db.create_tables([Test])



from dash import register_page, dash_table, callback, dcc, Input, Output, html, Dash

app = Dash(__name__)

app.layout = html.Div(
    [
        html.Div(id="value"),
        dcc.Interval(id="db_query_interval", interval=10000, n_intervals=0),
    ]
)

@callback(
    Output("value", "children"),
    Input("db_query_interval", "n_intervals"),
    )
def do_stuff(interval):
    db = Test.select()
    for db in db:
        val = db.test
    return val

if __name__ == "__main__":
    app.run(debug=True)

This test2.py re-uses the same connection on each iteration of the loop, which is how I would have expected Dash to work:

# test2.py

from peewee import Model, TextField
from playhouse.pool import PooledPostgresqlExtDatabase

db = PooledPostgresqlExtDatabase(
    "postgresql://postgres@127.0.0.1:5432/ariobot", autoconnect=True, application_name='test 2'
)

class BaseModel(Model):
    class Meta:
        database = db

class Test(BaseModel):
    test = TextField()

db.create_tables([Test])

from time import sleep

while True:
    db = Test.select()
    for db in db:
        val = db.test
    print(val)
    sleep(1)

ariobot=# SELECT count(*) FROM pg_stat_activity where client_port > 0 and application_name = 'test 2';
 count 
-------
     2
(1 row)

ariobot=# 

Why is the connection not getting re-used in the test1.py case?

Answer 1

This really depends on how "Dash" is configured to run. Does it spawn a new thread to handle each request? If so that would explain the leaking connections (peewee uses a connection-per-thread by default).

My suggestion would be to find the relevant Dash documentation on adding pre- and post-request callbacks to open and close your connections. With flask, for example, you would just add:

# This hook ensures that a connection is opened to handle any queries
# generated by the request.
@app.before_request
def _db_connect():
    database.connect()

# This hook ensures that the connection is closed when we've finished
# processing the request.
@app.teardown_request
def _db_close(exc):
    if not database.is_closed():
        database.close()

Whether or not you use a connection pool, it is very important to call db.close() when you're done working with the connection. With the pool, especially, this is necessary for the conn to be returned to the pool and reused.

From a quick google it looks like you may be able to accomplish this with "Dash" via:

@app.server.before_request
...

@app.server.teardown_request
...

As usual, the peewee docs describe Peewee's connection management: http://docs.peewee-orm.com/en/latest/peewee/database.html#connection-management