Comparing PHP Database Abstraction Layers and CRUD Plugins
In this article, we’re going to look at different database abstraction layers for PHP. We’ll also look at a couple of PHP CRUD database plugins that could make your life easier when interacting with a MySQL database.
What Is a Database Abstraction Layer?
As the name suggests, a database abstraction layer is a layer which sits between your application and the underlying database. You’ll use a database abstraction layer to interact with your database. The benefit of the database abstraction layer is that you could switch your underlying database back-end without worrying about code changes in your application. A database abstraction layer also simplifies database code and makes it easier to connect with and update the database.
Let’s look at an example to understand this. Let’s say you’ve chosen the MySQL database as a database back-end in the initial phase of your application development. You’re not using any database abstraction layer in your application and thus you’ll end up writing code specific to the MySQL back-end when you perform database operations.
What if for some reason, you need to change your database back-end from MySQL to Oracle? This switch is not going to be easy. The first thing which you’ll have to do is review every bit of the MySQL-specific code in your application. You’ll have to go through every line of code which interacts with the MySQL database back-end and replace it with the equivalent code for the new database.
On the other hand, if you have used a database abstraction layer to interact with the database back-end, switching to some other ODBC-compliant database back-end is done in the blink of an eye. You just need to change the connection settings to reflect the new database back-end. So as you can see, a database abstraction layer hides complexity by letting you talk to different database back-ends.
Take a look at the following diagram to understand what we’ve discussed so far.
As you can see, the database abstraction layer sits between the application and the actual database, so the application doesn’t need to know the specifics of how the underlying database works.
Datababase Abstraction Layer Options
Next, we’re going to look at a couple of options that you could use in your day-to-day application development. Let’s have a quick look at the options that we’re going to discuss in the rest of article.
PDOModel: Database Abstraction and Helper PHP Class
xCRUD Data Management System
PDO Crud: Advanced PHP CRUD application
The first three options, PDO, Doctrine DBAL and ADOdb, are popular open source options and free to use. The other three are commercial options available from CodeCanyon, so you’ll have to pay for them if you want to use them. On the other hand, these provide a lot of features that allows you to quickly set up basic scaffolding in your applications. Also, they are priced reasonably compare to what they provide as we’ll see later in this article.
A Quick Look at Popular and Free Options
In this section, we’ll look at a couple of popular and free options that you could use as a database abstraction layer in your PHP applications.
The PDO (PHP data objects) library, is a PHP extension which provides an interface to work with different database back-ends. In order to use PDO, you’ll need to install database-specific PDO drivers that you want to use in your applications.
So if you want to work with the MySQL database using the PDO extension, you’ll need to make sure that you’ve enabled the php_pdo extension which is a core PDO extension and the pdo_mysql extension which allows you to talk to the MySQL server using the PDO database abstraction layer.
Of course, if you want to work with some other database than MySQL, you’ll have to install the pdo extension for that database back-end. For more information about the supported database drivers in PDO, you can visit the PDO drivers page in the PDF Manual, which includes everything you need to know.
The Doctrine DBAL (database abstraction layer) library is another popular database abstraction library which provides a wrapper around PDO. The Doctrine DBAL provides easy-to-use API methods and a few additional features compared to using the pure PDO extension on its own.
The list of supported database vendors by Doctrine DBAL is:
Microsoft SQL Server
SAP Sybase SQL Anywhere
Doctrine DBAL is developed under the Doctrine project, which also provides a ORM (object-relational mapper) which sits on the top of the Doctrine DBAL. The Doctrine ORM is a tool which allows you to write database queries in the object-oriented way. In fact, the ORM itself is a wide topic, so we won’t go into the details here, but I’ll write an article on ORM in the future.
Lastly, ADOdb is a very popular class library and a very old player in this territory. It’s an external library which you’ll first have to download and configure with your set up should you wish to use it. ADOdb also supports a wide range of databases to choose from as your database back-end.
In addition to providing components for querying and updating database, it also provides the object-oriented Active Record library which works as an ORM in the same way we discussed earlier in the Doctrine ORM section.
Also, you’ll have to install corresponding drivers for database back-ends for this library, in the same way you would for the PDO extension. For example, if you want to work with a MySQL database, you’ll have to install the mysqli driver in PHP.
So that was a quick look at a couple of useful and free options. In the next section, we’ll look at a few popular commercial options that provides a lot more features than just database abstraction.
Best PHP Database Abstraction Scripts to Purchase
In this section, we’ll discuss a few advanced options that you could choose for a database abstraction library. In fact, the options that we’ve chosen won’t work as just a database abstraction library, they provide a lot more than that.
The PDOModel library is a database abstraction and helper PHP class which is built on the top of the PDO extension. It’s a very excellent wrapper which allows you to perform insert, update, delete and select operations without writing any SQL queries. It thus provides simplicity and readability. In fact, it provides wrapper functions that allows you to do complex operations using simple functions without writing actual SQL queries.
At the moment, it supports MySQL, Postgres, SQlite and SQL Server database back-ends. It supports transactions and batch operations which is an essential feature should you wish to import a database from an external source. The cherry on top is that it allows you to export data to formats like CSV, Excel, PDF, XML and more.
Let’s have a quick look at the following code which demonstrates how easy it is to use of the PDOModel helper.
// initialize the PDOModel class
$pdoModelObj = new PDOModel();
// connect to a database
$pdoModelObj->connect("host", "username", "password", "database_name");
// querying database [select * FROM users]
$result = $pdoModelObj->select("users");
The libraries we’ve talked about so far have been database abstraction layers, but let’s also look at a couple of CRUD (create, read, update, and delete) plugins that allows you to generate scaffolding code easily.
xCRUD is a simple yet powerful CRUD generator plugin written for PHP and MySQL. It allows you to create basic scaffolding code in no time, and it’s even useful for non-programmers as well. At its core, it’s using the mysqli extension to deal with different database operations. But you don’t need to worry about the underlying complexities, you just need to use easy-to-use functions provided by the xCRUD system.
While using the xCRUD plugin, you just need to design your database tables, and the rest will be handled by the xCRUD plugin. With just few lines of code, you’ll have a ready-to-use UI which allows you to create and update records. You’ll also get a list view which lists all records.
Let’s have a quick look at the code which you’ve to write to generate CRUD methods for the users table.
In this article, we discussed a couple of popular database abstraction layers and crud generator plugins in PHP. Initially, we discussed extensions that are free and popular among the PHP community. Later on, we went through a few popular and really useful extensions at CodeCanyon.
I hope this article will help you choose the database abstraction layer which best suits your application requirements.
Feel free to share your experiences and thoughts on the database abstraction layers and CRUD systems below, we’d love to hear from you!