Best PHP DAL (data abstraction layer) so far [closed]

Question

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:

• PHPBB DAL http://wiki.phpbb.com/Database_Abstraction_Layer
• Joomla DAL http://api.joomla.org/Joomla-Framework/Database/JDatabase.html
• ADOdb http://phplens.com/adodb/
• Zend_db
• Doctrine (downside only supports PDO_*)
• any other DAL used/developed under any open-source project/branch

Please don't consider:

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

Tinge

@DrColossos, please see my edits. I think ORM is tooo much OOPs for php in my case . I only need good abstraction only for reasons/features listed above , thanks – Tinge

could you please explain us why you think an object oriented DAL cannot be a solution? Don't you think your list of DAL is unmaintained and lacks a lot of things only because they do not use OOP? I don't know any serious programmer that would build a good DAL today and reject OOP. Check Zend_Db_Table (not only Zend_Db). – Feast

@Feast I did not say that I don't like OOP's based DAL. You are not getting my question when I said no ORM. ORM is strictly used when we want business objects coupled with database table ..e.g Active Record is an example . I am not interested in business object+ database coupling . I just want a good DAL without ORM ( Object Relational Mapping) part . If you have other lib to suggest you are welcome – Tinge

I'm just curious here, Why are you excluding PDO ? It's a really good system, and if the roadmap plan are respected, it should become the official default database mecanism in PHP (mysql_ will be deported to PECL, and so will mysqli_) – Pleuropneumonia

I don't understand the PDO hating either. Can you supply reasons? – Tagmemic

Landed here to read what is the status of Pear:DB. It was very famous once. It has no mention here. Any reason? – Zwick

I never used that, on the horizon I think Doctrine and Zend DB are good choices. As you can read the other comments PDO is also nice choice, probably one of the best if you want to use sth standard – Tinge

Answer 1

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

Danziger

please read question properly I explicitly mentioned that PDO is not recommended, and Docttrine is stick to PDO_* rigidly and I am unable to find an adapter for mysql/mysqli for Doctrine. I love to use Doctrine if you could point to mysql/mysqli adapter , thanks – Tinge

Actually, Doctrine abstracts the PDO API so you can use custom drivers that implement those interfaces. (Connection and Statement). – Danziger

From the Doctrine DBAL Docs: The fact that the Doctrine DBAL abstracts the concrete PDO API away through the use of interfaces that closely resemble the existing PDO API makes it possible to implement custom drivers that may use existing native or self-made APIs. For example, the DBAL ships with a driver for Oracle databases that uses the oci8 extension under the hood. – Danziger

mysqli support is planned, but at the moment it seems there is no adapter. I've updated the answer with an alternative DBAL. – Danziger

MDB2 seems to be in-active for an year almost (2.5.0b3 (beta) was released on 2010-08-29) and internet search does not show any good history comparably with other competitors – Tinge

That may be because it just works, MDB2 has every feature you will ever need, including emulation of driver specific features (ie sequence in MySQL). However, i do agree that not-being recently updated is discouraging, that's why i've moved to Doctrine. – Danziger

@sakhunzai, why don't you want something based on PDO? You should really outline such a reason here, and in your question. – Ful

@Ful thanks , As far as I know PDO does not provide special SQL features specific to underlying RDBMS and in a sense Its as wrapper for most common features available around all the RDBMS or say it common denominator – Tinge

@sakhunzai, the SQL passes through. You can use special SQL features all you want. It is true that specific API functions that don't use SQL might not be available, but I've never ran into an issue, at least with MySQL. Everything I've needed was accessible with SQL. If you really think you have a problem, you should research the specific problem. I suspect that there is an SQL way to access whatever you think you need. – Ful

@Ful thanks , what about schema reading functions e.g mysql_field_type() ? What do you suggest to achieve this in PDO ? – Tinge

dev.mysql.com/doc/refman/5.0/en/columns-table.html – Ful

You have a huge misconception about how this all work. PDO is a normalized INTERFACE, read you use the same API no matter what DB backend you choose. The trade of is the lack of vendor specific features, that's why there are more feature-packed DBALs that work on top of PDO, like Doctrine. – Danziger

Regarding mysql_field_type, you have doctrine-project.org/docs/dbal/2.1/en/reference/… in Doctrine. – Danziger

@Danziger ,@Ful thanks But I am not still convinced :) , and I don't have misconceptions about PDO_* . In case of mysql_field_type ( resource $result , int $field_offset ) we just pass the resource and get table schema info without further querying the information schema . In case of DAL e.g Doctrine or PDO I don't see how this will happen except reading the schema info directly as @Danziger suggested. – Tinge

No body is trying to convince you, you can do whatever you want, we are just trying to help you realize that your decision may be misguided. Regarding your example, there is no need to query the INFORMATION_SCHEMA, you can use DESCRIBE table_name to get that information. – Danziger

Reference here dev.mysql.com/doc/refman/5.0/es/describe.html – Danziger

Answer 2

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

Manful

thanks for the details.How can I use the Zend_DB (DAL) in my project( of-course without using whole zend framework components) . Could you point me to a link/example – Tinge

@Tinge added more details and links in the answer.. – Manful

Answer 3

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

Serous

Answer 4

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.

Assortment

Answer 5

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...

Subroutine

thanks your results seems really promising but you miss my point . Is there any adapter non-pdo available for mysqli for Doctrine ? – Tinge

@Prashanth Pratapagiri: If you already do the work of measuring numbers, it would be good to write what those numbers are... Time for an insert ? Time for X inserts ? X Number of Inserts per time Y ? You know, a number means nothing without a measurement unit ... – Burp

Answer 6

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.

Treadwell

seems to be good nice light-weight option, thanks for the link – Tinge

Answer 7

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.

Mattins