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

Question

My question is, how can I separate the database connection from the application model level? The main problem is to be able to easily change databases of different types. You might start with a flat file, a comma-delimited database. Then you want to go to the SQL database. Then you decide that the LDAP implementation will be better. How can a person easily plan something like this?

For a simple example, suppose 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 problems with public instance variables):

<?php class User { public $first; public $last; public $email; } ?> 

I often saw where people have a DAO class that has built-in SQL in it:

 <?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 such strategies is that when you want to change your database, you have to change each DAO class, create, update, load and delete functions to work with your new database type. Even if you have a program to automatically generate them for you (which I am not a fan of), you will have to edit this program to work now.

What are your suggestions on how to handle this?

My current idea is to create a superclass for DAO objects with its own create, delete, update, load functions. However, these functions will take arrays of DAO attributes and generate the request itself. So the only SQL is in the SuperDAO class, and not scattered across multiple classes. Then, if you want to change your database level, you will need to change the way you generate SuperDAO queries. Benefits? Disadvantages? Foreseeable problems? Good, bad and ugly?

Answer 1

Using ORM is usually the preferred way to abstract the database. A partial list of PHP implementations is available on Wikipedia .

Answer 2

You can use various frameworks such as PDO, PEAR :: MDB2 or Zend_Db, but, frankly, for 12 years of PHP development, I never had to switch from one type of storage infrastructure to another.

It is extremely rare to even go from something like Sqlite to MySQL. If you did more, however, you would have much greater problems.

Answer 3

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

• easy to use (e.g. ORM)
• db-agnostic: works with SQL, NoSQL, files, etc.
• always optimizes queries if the provider allows (sub-select, map-reduce)

As a result, Agile Data is a database access infrastructure (see video for more details).

Solving a real problem with DB agnostic code and Agile Data h2>

• Start with a description of business models .
• Create a $db save driver (a fancy word for connecting to a database), which can be a CSV file, SQL, or LDAP.
• Associate model with $db and express Action
• Execute Action

At this point, the environment will determine the best strategy based on the capabilities of the database, display your field declaration, prepare and execute queries for you, so you do not have to write them.

Code example

My next code snippet solves the rather difficult task of determining the current total debt of all our VIP clients . Scheme:

Next is a 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 

Received 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, an execution strategy can significantly increase some data, but it will work consistently.

I think my approach is a great way to abstract from the database, and I am working on its implementation under the MIT license:

https://github.com/atk4/data

Answer 4

You should look into the PDO library

PDO provides a level of data access abstraction, which means that regardless of the database you use, you use the same functions to query and receive data.

PDO does not provide database abstraction; it does not overwrite SQL or does not emulate missing functions. You should use a full-blown abstraction layer if you need this object.

Answer 5

The best way is to use the ORM library (object-relational mapping). There are many for PHP. I personally used and can recommend doctrine orm (I used it in combination with Silex, which is a minimalist frame structure).

Here is the StackOverflow thread about PHP ORM, where you can find some alternatives if you want: A good PHP ORM library?

Answer 6

Sounds good in theory, but most likely YAGNI .

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

Answer 7

Generally speaking, if you are faced with the problem of using a database, then your application will be useful, using features specific to the "brand" of the database, and will be a more reliable application for it.

It is very rare to switch from one database system to another. The only time you could really think about a function that is worth implementing is if you are writing some kind of loosely coupled system or structure intended for mass consumption (for example, Zend Framework or Django).

Answer 8

I always liked using ADOdb. From what I saw, it looks like he is able to switch between completely different platforms.

http://adodb.sf.net

Answer 9

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

how

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 Level:

 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' ))"; } 

Using:

 $database = new Database(); $users = new Users(); $users->save (new User()); 

In this example, it is obvious that you can always change your Link data access class, which will run requests for anything (this means that you can save your users on any server when changing the link).

At the same time, you have a clean model-level code that lives independently and does not know who and where stores its objects.

Also, the database class here seems unnecessary, but in fact it can give rise to big ideas, such as collecting many references to one instance for many db connections in one project.

There is also a simple and omnipotent structure of a single file called db.php ( http://dbphp.net ), which is built on the template that I described here, and even automatically creates tables with the ability to fully control their standard sql sql parameters / tables and Synchronize the database structure with your model every time you want.

Answer 10

Axon ORM automatically detects changes in your schema without requiring reflowing your code.