Database exception handling best practices

Question

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?

Answer 1

In general, I try to validate data as soon as possible after it has been entered. This is so that I can give helpful messages to the user earlier than after they have clicked "submit" or the equivalent.
By the time that it comes to making the db call I am hopefull that the data I am passing should be fairly good.
I try to keep db calls in the one file (or group of files) that share helper methods make it as easy as possible for the programmer (me or whoever else adds calls) to write to a log details about the exception, and what parameters were passed in etc

Answer 2

I try to validate on both sides. 1 rule I always follow is never trust input from the user. Following this to it's conclusion, I will usually have some front end validation on the form/web page which will not even allow submission with improperly formed data. This is a blunt tool - meaning you can check/parse the value to make sure a date field contains a date. From there, I usually let my business logic check as to whether the data entry makes sense in context with how it was submitted. For example, does the date submitted fall into the expected range? Does the currency value submitted fall into the expected range? Finally, on the server side, Foreign Key constraints and Indexes can catch any errors that slip through, which will bubble up a DB exception as a last resort, which can be handled by the app code. I use this method because it filters out as many errors as possible before the DB call is invoked.

Answer 3

@hamishmcn. good point, one reason to have validation in business logic layer is to
create user friendly UI.
But having these validations in multiple places breaks DRY principle.
How do you manage to keep DB and client side validations in sync?

Answer 4

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.

Answer 5

The sorts of apps that I was writing (I've since moved jobs) were in-house fat-client apps.
I would try to keep the business logic in the client, and do more mechanical validation on the db (ie validation that only related to the procedure's ability to run, as opposed to higher level validation).
In short, validate where you can, and try to keep related types of validation together.

Answer 6

@aku: DRY is nice, but its not always possible. Validation is one of those places, as you will have three completely different and unrelated places where validation is not only possible but absolutely needed: Within the UI, within the business logic, and within the database.

Think of a web application. You want to reduce trips to the server, so you include javascript validation of client data entry. But you can't trust what the user enters, so you must perform validation within your business logic before touching the database. And the database must have its own validation in order to prevent data corruption.

There's no clean way to unify these three different types of validation within a single component.

There are some attempts being made to unify cross-cutting responsibilities like validation within policy injectors like the P&P group's Policy Injection Application Block combined with their Validation Application Block, but these are still code based. If you have validation that's not in code, you still have to maintain parallel logic separately...

Answer 7

An object-relational mapping (ORM) tool, like NHibernate (or better yet, ActiveRecord), can help you avoid a lot of validation by allowing the data model to be built right into your code as a proper C# class. You may avoid trips to the database as well, thanks to great caching and validation models built into the framework.

Answer 8

There is one killer-reason to validate on both the client-side and on the database-side, and that is security. Especially when you start using AJAX-stuff, hackable URLs and other things that make your site (in this case) more friendly to users and hackers.

Validate on the client to provide a smooth experience to early tell the user to correct their input. Also validate in database, (or in business logic, if this is considered a totally secure gateway to the database) for security for you database.