What's the best way to abstract the database from a PHP application?

Question

My question is how does one abstract a database connection from the model layer of an application? The primary concern is to be able to easily change from different types of databases. Maybe you start with a flat file, comma-delimited database. Then you want to move to a SQL database. Then later you decide an LDAP implementation would be better. How can a person easily plan for something like this?

For a simple example, let's say you have a user with a first name, last name, and email. A very simple PHP class representing it might look like this (please ignore the problems with public instance variables):

<?php

class User {
  public $first;
  public $last;
  public $email;
}

?>

I have frequently seen where people have a DAO class which has the SQL embedded in it as such:

<?php

class UserDAO {
  public $id;
  public $fist;
  public $last;
  public $email;

  public function create( &$db ) {
    $sql = "INSERT INTO user VALUES( '$first', '$last', '$email' )";
    $db->query( $sql );
  }
}

?>

My problem with strategies like this is when you want to change your database, you have to change every DAO class' create, update, load, delete functions to deal with your new type of database. Even if you have a program to auto-generate them for you (which I am not particularly a fan of), you would have to edit this program to make it work now.

What are your suggestions for how to handle this?

My current idea is to create a super class for DAO objects with its own create, delete, update, load functions. However, these functions would take arrays of the attributes of the DAO and generate the query itself. In this manner, the only SQL is in the SuperDAO class rather than being scattered about several classes. Then if you wanted to change your database layer, you would only have to change how the SuperDAO class generates the queries. Advantages? Disadvantages? Foreseeable problems? The good, the bad, and the ugly?

Solution 1

Using an ORM is usually the preferred way of abstracting the database. An incomplete list of PHP implementations is available on Wikipedia.

Solution 2

You can use various frameworks such as PDO, PEAR::MDB2 or Zend_Db, but to be honest in 12 years of PHP development, I've never had to transition from one type of data storage infrastructure to another.

Its exceedingly rare to even go from something quite similar like Sqlite, to MySQL. If you did do more than that, you'd have far larger problems anyway.

Solution 3

I came up with an interesting concept that would allow developer to create database-agnostic code, but unlike ORM will not sacrifice performance:

• simple to use (like ORM)
• db-agnostic: works with SQL, NoSQL, files etc
• always optimize queries if vendor allows (sub-select, map-reduce)

The result is Agile Data - database access framework (see video for detailed explanation).

Solving real-life task with DB-agnostic code and Agile Data

1. Start by describing business models.
2. Create persistence driver $db (a fancy word for DB connection), which could be CSV file, SQL or LDAP.
3. Associate model with $db and express your Action
4. Execute Action

At this point framework will determine best strategy given the capabilities of the database, will map your fields declaration, prepare and execute queries for you, so that you don't have to write them.

Code Example

My next code snippet solves a rather complex problem of determining what is the current total debt of all our VIP clients. Schema:

Next is the vendor-independent code:

$clients = new Model_Client($db);
// Object representing all clients - DataSet

$clients -> addCondition('is_vip', true);
// Now DataSet is limited to VIP clients only

$vip_client_orders = $clients->refSet('Order');
// This DataSet will contain only orders placed by VIP clients

$vip_client_orders->addExpression('item_price')->set(function($model, $query){
    return $model->ref('item_id')->fieldQuery('price');
});
// Defines a new field for a model expressed through relation with Item

$vip_client_orders->addExpression('paid')
  ->set(function($model, $query){
    return $model->ref('Payment')->sum('amount');
});
// Defines another field as sum of related payments

$vip_client_orders->addExpression('due')->set(function($model, $query){
    return $query->expr('{item_price} * {qty} - {paid}');
});
// Defines third field for calculating due

$total_due_payment = $vip_client_orders->sum('due')->getOne();
// Defines and executes "sum" action on our expression across specified data-set

The resulting query if $db is SQL:

select sum(
  (select `price` from `item` where `item`.`id` = `order`.`item_id` )
  * `order`.`qty`
  - (select sum(`payment`.`amount`) `amount` 
     from `payment` where `payment`.`order_id` = `order`.`id` )
) `due` from `order` 
where `order`.`user_id` in (
  select `id` from `user` where `user`.`is_client` = 1 and `user`.`is_vip` = 1 
)

For other data sources, the execution strategy might heavy-lift some more data, but would work consistently.

I think my approach is a great way to abstract database and I'm working to implement it under MIT license:

https://github.com/atk4/data

Solution 4

The best way is to use an ORM (Object-relational mapping) library. There are plenty of them for PHP. I've personally used and can recommend doctrine orm (I've used it in combination with silex, which is a minimalistic php framework).

Here is an StackOverflow thread about PHP ORMs where you can find some alternatives if you like: Good PHP ORM Library?

Solution 5

You should look into the PDO library.

PDO provides a data-access abstraction layer, which means that, regardless of which database you're using, you use the same functions to issue queries and fetch data.

PDO does not provide a database abstraction; it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

View more solutions

Comments

[Ben S]

This is a common maintenance problem and will most likely be needed eventually. Database schemas are rarely set in stone.

[Ólafur Waage]

He isn't abstracting the DB away in his example. Just accessing it through precreated functions.

[Marshmellow1328]

I use the PDO. Unfortunately, it only handles the SQL databases which I can already handle.

[Greg]

He's not talking about the schema, he's talking about seamlessly switching between CSV and relational databases. How often do you do that?

[Marshmellow1328]

Or LDAP or any new type of database that gets invented. I wouldn't be asking the question if I didn't have a need.

[Greg]

Even changing database engine is rare (say, from MySQL to Oracle) but that could be taken care of with a database abstraction layer such as PEAR::MDB2 (though maybe there is a CSV driver for that, or one could be written...)

[analytik]

I use SQLite for development and MySQL for production (using Propel ORM), but my personal projects are tiny at the moment; I agree with what you said.

[codercake]

We use ADOdb as well where I work for abstraction and ORM (in a custom MVC framework), then wrap it in our application specific API - it's been great. Can't imagine doing it any other way now.

[jairhumberto]

In my case PDO didn't help to abstract database layer. I needed to develop an application using mysql and the client changed his database to oracle. PDO didn't help because the SQL syntatic of the two databases are diferent. I had a lot of trouble because of this. If I had developed the system using two types of daos, mysqldaos and oracledaos, for example, I could manage to aply which one that I would want through a factory (the pattern). The main problem is that now I run the software in develop mode in mysql, while in production it runs in oracle.