Alex Rivera | Logout

Database exception handling best practices

Asked 2008-09-02T12:25:35.263
15

How do you handle database exceptions in your application?
Are you trying to validate data prior passing it to DB or just relying on DB schema validation logic?
Do you try to recover from some kind of DB errors (e.g. timeouts)?

Here are some approaches:

  1. Validate data prior passing it to DB
  2. Left validation to DB and handle DB exceptions properly
  3. Validate on both sides
  4. Validate some obvious constraints in business logic and left complex validation to DB

What approach do you use? Why?

Updates:

I'm glad to see growing discussion.
Let’s try to sum up community answers.

Suggestions:

  • Validate on both sides
  • Check business logic constraints on client side, let DB do integrity checks from hamishmcn
  • Check early to avoid bothering DB from ajmastrean
  • Check early to improve user experience from Will
  • Keep DB interacting code in place to simplify development from hamishmcn
  • Object-relational mapping (NHibernate, Linq, etc.) can help you to deal with constrains from ajmastrean
  • Client side validation is necessary for security reasons from Seb Nilsson

Do you have anything else to say? This is converted to Validation specific question. We are missing the core, i.

database exception architecture
Edit
Report

1 Answer

3

You want to reduce unnecessary trips to the DB, so performing validation within the application is a good practice. Also, it allows you to handle data errors where it is most easy to recover from: up near the UI (whether in the controller or within the UI layer for simpler apps) where the data is entered.

There are some data errors that you can't check for programatically, however. For instance, you can't validate data on the existance of related data without roundtripping to the db. Data errors like these should be validated by the database through the use of relationships, triggers, etc.

Where you deal with errors returned by database calls is an interesting one. You could deal with them at the data layer, the business logic layer, or the UI layer. The best practice in this instance is to let those errors bubble up to the last responsible moment before handling them.

For example, if you have an ASP.NET MVC web application, you have three layers (from bottom to top): Database, controller and UI (model, controller, and view). Any errors thrown by your data layer should be allowed to bubble up into your controller. At this level your application "knows" what the user is attempting to do, and can correctly inform the user about the error, suggesting different ways to handle it. Attempting to recover from these errors within the data layer makes it much harder to know what's going on within the controller. And, of course, placing business logic within the UI is not considered a best practice.

TL;DR: Validate everywhere, handle validation errors at the last responsible moment.

answered 2008-09-02T12:54:23.993

Your Answer