Tagged with abstraction layers

ORM vs DBAL [explained]

I work for a large organisation who have a large legacy code base, so picking and choosing frameworks and technologies isn’t done lightly. We often bat ideas around and do a bit of digging around on Stackoverflow and Google before we even begin to consider downloading a copy and play with it.

Recently on the agenda there has been a lot of talks of database frameworks. There’s a lot of them around now, and they all do the same thing but in slightly different ways. One of the key things to understand however is what all the buzz-words mean. Google will through up words like DBAL and ORM. But what do they mean?

Firstly let’s think about how we generally connect to a database in PHP, and why you might need a framework. In PHP there’s three main, methods of connecting to a standard MySQL database – mysql_query(), mysqli_query(), and the PDO(). What they all have in common is that you give them a full SQL statement to evaluate and pass to the database, this is sometimes known as a Prepared Statement.

DBAL – Abstraction Layers
So for example using PHP’s now ancient mysql_query() a query may look like this, notice how we’ve defined our statement for the query.

1
2
3
4
<?php 
    $sql = "SELECT * FROM `users`"; 
    $query = mysql_query($sql); 
?>

Now lets say we’re writting lots of these types of queries, and we don’t want to worry about writting all the SQL for each one. A framework can help you by providing a nice abstraction layer, on some places on the internet this is known as DBALDataBase Abstraction Layer. What makes these frameworks useful is that they are able build queries on the fly from a given list of parameters. Abstraction Layers are quite simple to build, so for example if we were to roll our own vague Abstraction Layer it might have a method to perform an insert for us like so:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
    /**
     *  Inserts into database...
     *  @params string $table - table name
     *  @params array  $values - key/value pair to insert
     *  @return resource
     */
    function insert($table, array $values)
    {
        global $conn;//import connection
 
        $sql = "INSERT INTO {$table} (";
        //extract column names from the keys
        $sql.= implode(", ", array_keys($values));
        $sql.= ") VALUES('";
        //extract values
        $sql.= implode("', '", $values);
        $sql.= ")";
 
        return mysql_query($sql, $conn);
    }
 
    $result = insert('users',array('username'=>'bond007','password'=>'martini'));
 
?>

You can see already that this is a huge timesaver while coding! But hypothetially say what if one day we decided we’d like to port our application across to using Postgres or Oracle? Well, because we’ve abstracted all those scattered database queries through-out your code they now all funnel through our Abstraction Layer. This means we only have to update the underlying database interaction. So the above might look like this for Oracle:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<?php
    /**
     *  Inserts into database...
     *  @params string $table - table name
     *  @params array  $values - key/value pair to insert
     *  @return resource
     */
    function insert($table, array $values)
    {
        global $conn;//import connection
 
        $sql = "INSERT INTO {$table} (";
        //extract column names from the keys
        $sql.= implode(", ", array_keys($values));
        $sql.= ") VALUES('";
        //extract values
        $sql.= implode("', '", $values);
        $sql.= ")";
 
        $stmt = oci_parse($conn, $sql);
        return oci_execute($stmt);
    }
 
    $result = insert('users',array('username'=>'bond007','password'=>'martini'));
 
?>

What you’ll have noticed in the above is that the query is the same but now we’re running the query through PHP’s Oracle interface rather than MySQL. What we’ve done is begin to make our application Database Agnostic. The example above of course if pretty straight forward but it is possible to think outside the box, and use a NoSQL solution. So perhaps we’re not inserting into the “users” table but instead into the “users” collection in MongoDB or keyspace in Cassandra.As you can see the possiblities here are quite big.

One of the most popular Database Frameworks and big names around is Doctrine. If you take a look at the different projects they have listed you’ll notice they provide ORM/DBAL’s for Relational Databases, like MySQL/Oracle and then also NoSQL Databases like MongoDB and CouchDB.

ORM – Object Relational Mapping
So whats all this ORM business then? Well ORM stands for Object Relational Mapping, which sounds kinda scary and it is a bit at first glance. Object Relational Mapping is the idea of taking Database Abstraction to the next level and binding it to our application code. It’s hard to kind of explain, but it means you’ll need to define a PHP Object in your application that will correspond to your database schema. Let me try and explain via a code example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
<?php
    class User extends MyFirstORM
    {
        /**
         * User ID
         * @var int - defined as INT(11) in schema 
         *            auto-incremental primary key
         */
        public $id;
 
        /**
         * Username
         * @var string - defined as VARCHAR(32) in schema
         */
        public $username;
 
        /**
         * Password
         * @var string - defined as VARCHAR(16) in schema
         */
        public $password;
 
        /**
         * Class Constructor
         */
        public function __construct()
        {
            //some application logic here perhaps
            return ;
        }
    }
 
    //Create a new User Object
    $user = new User();
    //Assign properties of the User Object
    $user->username = 'bond007';
    $user->password = 'martini';
    //Call save method in parent class (MyFirstORM)
    $user->save();
 
?>

I’m hoping the above makes sense, and you can see what we’re trying to achieve here. You’ll notice you don’t have to pass an array or specify a table like our DBAL, instead the ORM framework is intelligent enough to be able to figure it all our for you. What this means is you can create numerous models in your application and using your ORM you can persist them straight to the database without even inferring what the SQL interaction should be. While it might seem clever it comes at a cost. It means your application needs to be structured in a certain way, and it also means you may have to keep your database schema and the models defined in your code base synchronized.

So hopefully you’ve got a vague idea of the benefits of Object Relational Mapping and Database Abstraction Layers for your application. It’s important to remember however my examples above are intended to be simple to understand and straight forward, in reality things are a little more complicated. Whether you use an ORM or a DAL, they’ll require bootstrapping and you’ll be introduced to the method chaining syntax, and more importantly you’ll be encouraged (if not forced) into using PHP 5.3+ which can be a plus or a minus given consideration of legacy code!

Summary
To summarize some of my experiences…

I’ve found ORM’s to be complicated and time-consuming to setup and often come with their own syntax which indicates foreign keys and many-to-many relationships to the ORM. However on the other hand they often provide a very powerful interface for querying data, and given that we’re dealing with objects we can think about solving problems using Object Orientated Programming principles. Remember one of the greatest things about an Object Orientated approach is re-usable code, we can re-create objects over and over again in many places in our application with little over-head. It also means we can manipulate objects many times before we finally commit the object to the database.

In comparision I’ve found DBAL’s to be the quickest solution and often provide a lesser of a learning curve. It also means you get more direct control over your database, and are able to build queries how you want to using more of the features of that database system. For example using Stored Procedures is something which is almost impossible using an ORM but is do-able in most DBALs. We’re also able to begin harnessing the power of transactions in our application along with safely quoting our input and protecting against SQL injection.

That’s my thoughts and experiences using ORMs and DBALs. Let me know if you’ve got anything to contribute in the comments.

Thanks
Adam.

Tagged , , , , , , , , , , ,