Should I catch exceptions on the database side or client side?

Question

Assume I have a stored procedure in database that inserts a row into a table. The database will obviously throw an exception if I try to insert a row that will violate the primary key constraint in the table. Now, should I handle this exception right in the database level, or is it OK to send the exception to the client and handle it there? Which one is the preferred and better practice?

Answer 1

Whilst I agree that the question linked to by rags does cover the reasoning behind having constraints in the database, I don't think it fully answers your specific question because it doesn't cover what to do with exceptions that are generated from the database.

So, I think the answer is "it depends".

If you are accepting input from the client and trying to insert it using this SP without any additional data being generated inside the SP, then yes, you should throw some kind of exception back to the client since your SP cannot handle this exception and the bad data came from the client.

i.e. don't catch exceptions you can't handle.

On the other hand, if the SP could have been the cause of the problem - let's say that it generates some of the data that forms part of the INSERT and it was this data causing the problem and you can actually recover from it - then catch it, recover, and the client doesn't need to know anything about it; if you fail to recover then by all means you can throw the exception (or some more generalised exception, after having logged the original) back to the client at this point because you can't do anything more about it.

Incidentally, there's nothing wrong with having a "soft" version of the same constraints that exist in the database schema, in your object validation - since any change in the schema necessitates a change to your mapped objects anyway, it makes sense (to me anyway) to treat the relational and structural constraints you have defined in your database as a contract to which mapped object models must adhere.