Flask Connection Pool for PostgreSQL

There are a lot of technology words in that title so I’m going to explain exactly what this is. This is a connection pool for Python applications that make use of the Flask web framework and connect to PostgreSQL databases. Why am I sharing this? In my experience there are a dearth of connection pool libraries for Flask outside of SQLAlchemy.

I wanted something that would reuse connections rather than connecting and disconnecting with every request. Yes, the Psycopg pool library does this but it doesn’t let you make changes to the connection when initially created and on each reuse. For example, in our set up we want to enable auto-commit which is not the default configuration for Python database connectors. We also wanted to set a session configuration value that would indicate to our stored procedures the name of the user, if known, every time a new request takes over the connection. So the Psycopg pool library didn’t cut it.

I also want call out the great Python library Tenacity which I’ve begun using in lots of places in my code for things that need to be retried with different retry strategies. It has greatly minimized the amount of code I’ve had to write and the number of times I’ve had to write time.sleep(1).

All that said, I’ve posted my database connector on GitHub as a “gist”. I’m using it at the University of Washington to pool connections on all of my team’s web applications.