Not A Record: Introducing the DDDBL

written by André Gawron, 28. February 2011

The DDDBL was originally created by Torsten Zühlsdorff written in PHP. This is a Ruby port using RDBI as communication layer between different RDMS and the application.

Most frameworks make use of the Active Record pattern nowadays - or to be more general: ORM. In Ruby, that pattern got popular through Rails and is now a kind of defacto-standard in application development when using a relational database under the hood.

Trying to force one paradigm, Object-Orientation, upon a totally different paradigm like relational databases feels, at least for me, wrong. Using the database's structure to define the application's behaviour (or vice versa) is a break of an important principle: separation of concerns. With an Active Record approach you're tightly coupling your application to the underlying database.

Why does everybody use it? One of the greatest advantage of Active Record is not being forced to write SQL. Depending on the implementation, Active Record generates a model based upon the table's schema, creating simple methods like insert to abstract the need of writing queries in SQL. Just set the model's attributes, call insert and the record will be saved into the database. Easy, ain't it?

This methodology goes on for other, simple queries. And most of the time, applications don't require more complicated interactions with their database. More sophisticated implementations like Rail's are even capable of managing joins, foreign keys and the like. But how much time do you actually save writing an easy INSERT query?

Another drawback, which AR tries to solve, is the ugly code which results from writing SQL inside the application's source. The function's size increases and the readability decreases. Flavoured with another programming language, understanding the written SQL gets hard and difficulties in maintainability increase. Specially hired DBAs have to know the programming language used in the software to fix potential bugs while not breaking anything else. As mixing different languages in the same file is frowned upon, mixing in SQL is tolerated.

Introducing the DDDBL

The concept behind DDDBL (Definition Driven Database Layer) is, in short, to split up SQL and application code by taking it out of the application's source and putting it into an external file with an easy to understand syntax. How do you perform a SQL query then? It's as simple as calling a method. The SQL will be stored under an alias chosen by yourself. This way, you can make sure that the alias is understandable and reasonable. Here's an example:

DDDBL::get('INSERT-ARTICLE', author, title, content)

The corresponding external sql-file:

[INSERT-ARTICLE]
QUERY = "INSERT INTO articles VALUES (?,?,?)"

This is just a tiny query, but it hopefully illustrates my point. Imagine a query lasting over couple of lines, using multiple conditions and having dozens of parameters - that'll get messy quite fast. Another advantage of relocating your SQL is that it's now possible to generate it. Need simple INSERT, UPDATE and DELETE queries? Just write a program which generates those from a table schema. Yes, that's to some extent the same an ActiveRecord implementation does, but it's without coupling your application to the database. Application's code is still untouched.

Parameter-Binding

As you see in the above example, I used placeholders in the SQL. The DDDBL supports Prepared Statements with numeric binding as shown above. What are the advantages? Ever heard of SQL Injections? As long as you're using a database which supports Prepared Statements, you can rest easy. No databases will get hurt by SQL Injections although there's a downside: it can be slower1. If you have a need for speed, it will be possible to fallback to an unprepared query with parameter replacement (or directly query the database) without any speed drawbacks in a future version of the DDDBL.

Result-Formats

The PHP-version of DDDBL is capable of transforming the bound parameters and the result. The ruby version does not implement any of this, but the gem used to query the database, RDBI, does.

It's straight forward: just extend RDBI's RDBI::Result::Driver class and implement at least #fetch. The name of the new result driver is then the name which has to be used in the query's HANDLER configuration field. It's also possible to pass a configuration to the result driver defined in the query definition:

[QUERY-ALIAS]
QUERY   = "SELECT bar FROM foo;"
HANDLER = MULTI INT::bar

MULTI is the name of the result driver and comes with the DDDBL2. If there's a whitespace following with more text, this additional text will be passed to the result driver's constructor. The example definition will explicitly cast every row's bar-column value to an Integer.

For more information on creating a result driver, please read the documentation of RDBI::Result::Driver. If you want to overwrite a default result driver, just override the class.

Roadmap

There's a lot of work to do. For example:

Caching is especially interesting since many queries will be sent multiple times to the database with either different parameters bound (and therefore different result sets) or complete identical result set. Reducing the amount of queries send to the database is one point to optimize an application3.

I'll occasionally blog about the development and (my) adoption of the DDDBL with more examples and cookies to give away. Got interested? Just follow my DDDBL repository and for a deeper look into the DDDBL's capabilities, check out the README which will be updated frequently.

  1. bust most of the time it's actually faster though it depends on the database
  2. other pre-defined result handler will follow in the near future
  3. of course, there are plenty of other caching possibilities - why even bother querying the database at all