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?