Love Your Database project — Thoughts on effectively handling constraints?

I believe a lot of application programmers, particularly but by no means limited to web application developers, have a tragic prejudice against treating their database as anything but a dumb data bucket.

They also often lack awareness of even simple-to-use SQL/Postgres features that would make their lives easier (top of this list would be CTEs and Window Functions).

So I’ve started a project to fix this. I’m initially going to write a series of blog posts demonstrating in principle how a developer can put much/all of their model logic in their database.

I’m starting with constraints. Using Ruby on Rails as my example, a server-side constraint violation shows up in vanilla Rails as an Exception, that looks like this:

PG::CheckViolation: ERROR: new row for relation "users" violates check constraint "family_name_length"
DETAIL: Failing row contains (11, foo).

What I need to do is turn this into something similar to the equivalent Rails-side constraint failure, which is a nicely formatted error message on the model object.

The obvious thing would be to parse the error message.

It occurs to me that I might instead do something on the server side. I’d like to get back a more structured error. Perhaps I could use a rule or trigger or stored procedure to structure returning the results.

Here is my iniitial Statement of Purpose blog post:

https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.l7paeslcs <https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.l7paeslcs>

Even this initial post is starting to get a bit of interest. This feels like something that with a little attention can make a difference. Any thoughts on the project generally, or particularly on effectively handling constraints?