Handle database error for API server

Question

Recently I worked with a Restful API server and encountered a problem with handling database error.

The basic approach is every time a SQL exception occurred, the server will return "500" to client and client will have no idea what is wrong inside the server. It's fine for server errors. But sometimes, the errors are created by user input, especially in case of unique constraint violation and invalid reference, and it is required to return a more user friendly error.

What should I do to correctly handle these cases? As I understand parsing SQL message is a big no. My current approach is add 1 more validation layer to check for these constraints. Which will causes validation to run 2 times, 1 at application level and 1 at database level, which is quite inefficient in theory. So are there any better approaches?

Answer 1

For requests which could be understood but not processed by the server the 422 Unprocessable entity error code might be appropriate.

The 422 (Unprocessable Entity) status code means the server understands the content type of the request entity (hence a 415(Unsupported Media Type) status code is inappropriate), and the syntax of the request entity is correct (thus a 400 (Bad Request) status code is inappropriate) but was unable to process the contained instructions. For example, this error condition may occur if an XML request body contains well-formed (i.e., syntactically correct), but semantically erroneous, XML instructions.

For more specific use-cases, such as unique constraint violation, you could go for the more specific error code, 409 Conflict, basically telling the calee the server once again understood the request but it could not be completed due to conflicting information.