flask-sqlalchemy rollback best practices

Question

I am trying to find some clarity on the best practices for where in a flask application database errors should best be handled and rollback should be performed. I have looked through the SQLAlchemy docs and I have not found any recommendations on the best practice. However, the way I am doing it does not strike me as a well engineered solution. Here are the two ways I have implemented error handling of database errors.

1) Use one error handler on the api level. This is simple and easy. However, perhaps it catches the error to late and returns a potentially unnecessary error to the user when simply rolling back and trying again may have worked.

@ERROR.app_errorhandler(sqlalchemy.exc.InvalidRequestError)
@ERROR.app_errorhandler(pymysql.err.OperationalError)
@ERROR.app_errorhandler(sqlalchemy.exc.OperationalError)
def operational_error_handler(error):
    DB.session.rollback()
    current_app.logger.error(f'Rolling back session due to an '
                             f'unhandled error {error}')
    return {'message': 'SERVICE NOT AVAILABLE: The service is unavailable. '
                       'Please try again later.'}, 503

2) Put error handlers in the business logic. This is super specific, allows use case specific logic and catches the error where it occurs. However, it adds a lot of extra code to wrap every single function. There must be a better way.

def get_thing(id):
    """Outer function to handle rollback"""
    def _get_thing(id):
        """Inner function"""
        thing = ThingModel.query.get(id)
        DB.session.commit()
        return thing

    try:
        return _get_thing(id)
    except sqlalchemy.exc.OperationalError:
        DB.session.rollback()
        return _get_thing(id)

flask-sqlalchemy rollback best practices

0 Answers0