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?

Answer 1

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

Answer 2

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?

Answer 3

In fact solution for the topic "where to implement data access logic?" is not complex. All you have to remember is that your model code must be separate from your data access code.

Like:

Model layer with some business logic User::name() method

class User 
{
  public $first;
  public $last;
  public $email;
  public function name ()
  {
      return $this->first." ".$this->last;
  }
}

Data access layer:

class Link
{
    $this->connection;
    public function __construct ()
    {
        $this->connection = PDO_Some_Connect_Function();
    }
    public function query ($query)
    {
        PDO_Some_Query ($this->connection, $query);
    }

}

class Database
{
    public $link;
    public function __construct ()
    {
        $this->link = new Link();
    }
    public function query ($query)
    {
        $this->link->query ($query);
    }
}

class Users
{
    public $database;
    public function __construct (&$database)
    {
         $this->database = &$database;
    } 
    public save ($user)
    {
        $this->database->link->query ("INSERT INTO user VALUES( '$user->first', '$user->last', '$user->email' ))";
    }

Usage:

$database = new Database();

$users = new Users();

$users->save (new User());

In this example it is obvious that you can always change your data access class Link which will run queries on whatever (It means you can save your users on any server as you change link).

In the same time you have clean model layer code which lives independently and has no idea who and where is saving its objects.

Also Database class here seems unnecessary but it in fact it can give birth to great ideas like collecting many links one instance for many db connections in one project.

Also there is single file simplest and almighty framework called db.php(http://dbphp.net) which is built on the pattern I described here and even creates tables automatically with ability to fully control its standard sql field/table settings and synch database structure to your model every time you wish.

Answer 4

Axon ORM automatically detects changes in your schema without requiring you to rebuild your code.

Answer 5

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

Answer 6

I always liked using ADOdb. From what I've seen, it looks like it's capable of switching between vastly different platforms.

http://adodb.sf.net

Answer 7

Generally speaking, if you're going to the trouble of using a database then your application will benefit by using features specific to a "brand" of database, and will be a more solid app for it.

It is very rare to move from one database system to another. The only time you might realistically consider that aa feature worth implementing is if you're writing some kind of loosely coupled system or framework intended for mass consumption (like Zend Framework, or Django).

Answer 8

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.

Answer 9

It sounds good in theory but in all likelyhood YAGNI.

You would be better off using an SQL library such as PDO and not worrying about LDAP until you get there.

Answer 10

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.