Controlling the Flask-SQLAlchemy engine

So this week I was fiddling around with some database connection settings. We use Flask-SQLAlchemy as our ORM for our Flask project but have been experiencing some random socket timeouts when we push to our AWS dev environment.

It looked a bit like the database connection was going away, but there was no good reason why so I went for the developer's last resort - read the documentation. As always there's a bit of a difference between pure SQLAlchemy and Flask-SQLAlchemy and how much Flask-SQLAlchemy 'helps' with connections, sessions etc so it's not always super useful.

However, it turns out as of Flask-SQLAlchemy 2.4 some changes have been made to how much/how easily you can poke at the actual underlying SQLAlchemy create_engine method, which is awesome because this is exactly what I wanted to do. And it's the simplest thing ever, it's just a config option. Here's ours:

SQLALCHEMY_ENGINE_OPTIONS = {
    "pool_pre_ping": True,
    "pool_recycle": 300,
}	

Flask-SQLAlchemy is clever and just picks up these options when it creates the db engine and applies them, and what's even nicer is that these are just your standard pure SQLAlchemy engine_options so it's easy to figure out what you need to include.

In our case, we are experimenting with two options at the moment: pool_pre_ping - which enables pessimistic disconnection handling ie, testing the connection is still live before starting your transaction. This has proved to be the killer config feature, and who would be surprised given this very loft claim direct from the SQLAlchemy docs:

The approach adds a small bit of overhead to the connection checkout process, however is otherwise the most simple and reliable approach to completely eliminating database errors due to stale pooled connections.

Can't argue with that, right?

The other option we have implemented is pool_recycle, which simply recycles connections after the given number of seconds. Our legacy app requires a 5 min timeout so we've just added the same thing here for consistency. It may not last as we move more over to the new stack.