The wrong approach, which includes all persistence layers and most ORMs, use object-oriented design to manage data (by generating simple store-and-retrieve SQL code) instead of the relational model; and mash together transient application data with permanent facts that should be made available to other application instances. This approach considerably increases complexity unless the data is highly independent (that is, one application instance doesn’t care what another instance is doing). See my previous post for more details.

Additionally, this wrong approach usually leaves developers wanting when they need richer semantics to handle interdependent data, like constraints that aren’t feasible to check from application code. In response, these projects put complexity back into the system, and the end result is that the complexity is just moved around (but increased overall). Developers usually jump from one bad persistence layer or bad ORM to the next in a series, none of which live up to the promises of simpler database application development.

A much better approach is that used by the SQLAlchemy project (Python) or the Sequel project (Ruby). An excerpt from the SQLAlchemy website: “SQLAlchemy doesn’t view databases as just collections of tables; it sees them as relational algebra engines.” I would have said “centralized relational algebra engines,” but either way, the statement shows a fundamentally different philosophy that I think will be successful in the long run.

The idea is to allow the developers to use arbitrary relational expressions in their application language, and cleanly separates transient application data structures from the permanent facts that should be made available to other application instances. The complex relational expressions in the application language are then translated to equivalent SQL — not simple store-and-retrieve, but real SQL expressions — and then passed to the DBMS which can then optimize and execute the expressions, and the results can be returned and understood by the application language.

This kind of development makes me wonder why we can’t generate a parse tree, serialize it, and send it directly to the DBMS. It would be easier to generate the parse trees, and yet still allow all of the optimization opportunities. Parameterized SQL is a partial implementation of this idea, and has been very successful.

Another big issue that needs to be tackled is to match the type systems between the application language and the DBMS. PostgreSQL is lightyears ahead of other DBMSs when it comes to types. There’s a great set of built in types like text, numeric, bytea, timestamptz — most database management systems don’t even get text right! With these good types come good operators like regular expression matching, date/time math, etc. But the real power comes from user-defined types, which are just as powerful as native types, and there is a coherent infrastructure for defining the type, defining operators, and allowing highly sophisticated index strategies for those operators.

The ideal situation would be to have all of the types in your application language (including the operators on those types) available in your DBMS as well. This means that there should be a character string type for Java programmers (because Java strings are strings of characters) and a byte string type for Ruby programmers (because Ruby strings are strings of bytes). NULLs of course may cause some difficulty because I am not aware of any non-SQL language that supports anything close to a SQL NULL.

I’m optimistic about the direction things are going for database application development. Using real relational expressions in the application language appears to be catching on, and as those projects mature, they will be looking more and more at PostgreSQL for its extensible type system.