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:
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?