Database Exception Handling for Flask-RESTful APIs

Hello all,

I’m building a basic CRUD app with React, Flask-RESTful and SQLite.

I’m looking for a best-practices approach to structure exceptions from SQLite CHECKs. I’ve had a look at the docs for error handling in Flask-RESTful, but it doesn’t provide guidance on best practices. I’ve also had a look through several repos on github which use flask-restful, but unfortunately they’re all using SQLAlchemy. SQLAlchemy has its own set of exceptions built in. I’m using my own models instead of an ORM.

Here’s what I’m dealing with so far
REST endpoint
model class
try: SQL query
except SQLite.exception
model error handling forwards boolean to REST
REST error response

What would be the best-practices way to communicate the exception between the model and the REST api? Create a custom exception to be caught in the model and returned to the API? Declare state constants like STRINGLENGTHFAIL or something? Something else entirely?

So are you rolling this as SQL queries you write yourself or is this through an ORM of some kind?

If you roll your own be sure to never use python string interpolation to do the SQL query. Use the sqlite APIs built in ability to safely inject the variables. So if you’re going this:

sql = f"SELECT * FROM mytable where name={name}"

This is bad because someone can set name to 'alex'; drop mytable; and obliterate your database.

The SQLite api has a mechanism for doing that’s safer.

Then when you get an exception your choice is either raise it again for the function calling you, or to handle it and raise your own exception.

Hey there Zed,

After giving an ORM (SQLAlchemy) a try, I’ve decided I’d rather roll my own injection-safe queries and write my own models as an API to SQLite.

My question is more about what the best practice is for handling database CHECK constraint exceptions in a REST API. I have Python filtering the input before insertion, so it shouldn’t let those exceptions ever even get triggered, but I want to be thorough.