Best PHP DAL (data abstraction layer) so far

34.6k views Asked by At

What is the best PHP DAL (data abstraction layer) so far developed under any open source project which we could re-use with good faith?

I am finding it hard to choose a DAL for my application that sufficiently supports abstraction to most common databases systems (MySQL, PostgreSQL, MSSQL, Oracle, etc) and is:

  1. widely tested,
  2. has good interface (readable method names, good parameter passing strategy),
  3. fast,
  4. lightweight,
  5. providing cache (e.g integrates with Memcache or supports a good caching mechanism),
  6. open-source license,
  7. should have adapters for at least MySQL/MySQLi (non-PDO based)

Some of the libararies to consider:

Please don't consider:

  • PDO
  • All ORMs (however, Doctrine seems to have a separate DAL besides ORM)
7

There are 7 answers

1
nbari On

If you only need to work with MySQL, DALMP Database Abstraction Layer for MySQL using PHP. can be an option

works with cache/memcache/redis and also does session handling very simple and light.

0
Jorrit Schippers On

I have good experience with Propel. Doctrine is similar, I heard good things about it but I don't have experience.

16
xmarcos On

If you can do with PHP 5.3, I would highly recommend Doctrine DAL, it's built on top of PDO, so you get the same performance plus a great API.

Update: If Doctrine is not good, you can try MDB2. It has drivers for most of the popular RDBMS, a robust API, great docs and a huge user base:

  • MySQL
  • MySQLi (PHP5 only)
  • PostgreSQL
  • Oracle
  • Frontbase
  • Interbase/Firebird (PHP5 only)
  • MSSQL
  • SQLite
0
Dmytro Zavalkin On

What about Zend_Db? The only thing that for caching you need Zend_Cache, and lightweight is vague thing. All other requirements are matched I guess.

0
nimmen On

I had some trouble with doctrine DBAL, mostly with the schema/database/table creation, it was buggy and some of documentation was different from actual interfaces and class methods(I did read right version documentation), I had to use raw sql statements for some of those things.
Everything else seemed to be fine, it was small project so I did not use all the features doctrine DBAL provides.

Note: I did it around a year ago with latest stable version of doctrine DBAL and php, maybe all those problems are fixed by now.

2
Raj On

I have been using Zend_Db for my web application for the past 1 year. I think, Zend Framework is the best by far.

Zend was started by folks who were the core contributors of PHP.(1)

widely tested

Yes. It is used by thousands of projects and has a active community of developers.

has good interface (readable method names ,good parameter passing strategy)

Yes. All Components can be easily customized to your needs. Every component in Zend is loosely coupled, meaning you can use any component without any dependency on any other component in the framework.

speed

Yes. Zend_Db using PDO, by default.

lightweight

Yes

providing cache (e.g integrates with memcache or supports a good caching mechanism)

Zend has an extensive caching system.

open-source license

Yes

To access a DB table, all you have to do, is create a class for it by setting the table name and its primary key as its fields.

class User extends Zend_Db_Table {

    protected $_name = "users";  //tablename
    protected $_primary = "user_key"; //primary key column

    function addNewUser($name,$age,$email) {
          //Validate input and add Logic to add a new user
          //Methods are available to insert data like $this->insert($data)
          // where $data is an array of column names and values
          // Check links below for documentation
    }
}

This is called a model. In this class, you can create all the methods related to 'User' entity like adding a new user, editing user etc.

In your application code, you can use this as,

$u = new User();
$u->addNewUser('Name','Age','email');

Must Read this - http://framework.zend.com/manual/en/zend.db.table.html

More reference here. Check this relation question for more information

2
Prashanth Pratapagiri On

Doctrine 2.0 is the best one in the market because it is supported by topmost frameworks like Zend framework, Symfony.

It also supports nosql db like mangodb etc...

It has inbuilt cache system which can boost the application.

It is also having Extension support like pagination, query builder etc

Here are some of the bechmarks results of propel and doctrine

                               | Insert | findPk | complex| hydrate|  with  |
                               |--------|--------|--------|--------|--------|
                  PDOTestSuite |    132 |    149 |    112 |    107 |    109 |
             Propel14TestSuite |    953 |    436 |    133 |    270 |    280 |
        Propel15aLa14TestSuite |    926 |    428 |    143 |    264 |    282 |
             Propel15TestSuite |    923 |    558 |    171 |    356 |    385 |
    Propel15WithCacheTestSuite |    932 |    463 |    189 |    342 |    327 |
           Doctrine12TestSuite |   1673 |   2661 |    449 |   1710 |   1832 |
  Doctrine12WithCacheTestSuite |   1903 |   1179 |    550 |    957 |    722 |
            Doctrine2TestSuite |    165 |    426 |    412 |   1048 |   1042 |
   Doctrine2WithCacheTestSuite |    176 |    423 |    148 |    606 |    383 |

These are the key observations for the Doctrine 2 results.

see the last two records how the performance has increased with doctrine 2.0...