Love Your Database: Simple Validations

Aside: Postgres and Rails

Most of what I’ll be discussing in this series will be applicable to all the decent SQL databases. (And quite a bit will even apply to MySQL). I will initially be focusing on PostgreSQL, for a few reasons.

First, it’s the SQL database I’m most familiar with. But more than that, Postgres is arguably the best model server of all the SQL databases:

• Its SQL features compare favorably to any other database out there, even the 20lb gorilla of the space, Oracle;
• It supports all of its programming features in any of about 20 different programming languages, including Java, C, Perl, Python, Ruby, R, Scheme, BASH and Common Lisp. All of the languages and more are first-class citizens, able to implement stored procedures, triggers, user defined functions, the works;
• It has robust support for efficiently storing, indexing and retrieving non-relational data, in the form of arrays, namespaces (hstore), XML and JSON;
• You can define your own data types, and then define the functions that make the data type work using your favorite programming language; and
• Its Foreign Data Wrappers feature let you treat other sources of structured data, from mail servers to LDAP servers to most other databases and text search services as just tables in Postgres. So you can use Postgres as a data integration hub, through which you access all of your Model, not just the relational part.

I’ll also be using Ruby on Rails initially for the client side of my examples. This is for expedience, as it’s what I work with the most, but also because I can write examples in simple Rails and Ruby that will be obvious to any web developer.

Simple Validations

Job 1 in storing and retrieving data is ensuring the data you want to store is valid.

But we need to do more than just reject invalid data: we need to get meaningful feedback about what was wrong so we can display an error message or whatever.

So job 1 is: implement a basic validation in Postgres with a custom error message. We should be able to get the general class of error in Rails and also the error message.

With the help of the folks on the utterly amazing pgsql_general mailing list (thanks, Tom!), I’ve sorted this out in principle. Postgres (as of 9.3, apparently) offers nicely-structured standardized error reporting. In Rails, you can get at this through the PG::Result object. How you get at that depends on where you are. If you’ve just received an Exception e from your invalid operation, you can get at it as e.original_exception.result, for example. In any event, PG::Result has on it a function error_field, which takes any of a variety of standard constants and returns all of the information we need: a standard error code (23514, for example, is check_violation), the table name, the constraint name, and other details. It seems clear that all we would need server side is a mapping from the constraint name to the user-friendly error message we want, and the field it applies to, and we can replicate the very nice standard Rails error handling.

For the moment, this is enough. I’m going to consider the major issues in moving Model logic to the server, and research enough to demonstrate that we should be able to replace the web server behavior effectively. Once I’ve covered in-principle all the major issues, I’ll circle back and write code and a book and otherwise provide details.