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?
