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 the 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 MySQL as a database back-end in the initial phase of your application development. You're not using any database abstraction layer in your application, so 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 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.
Database 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 the article.
- PDO
- Doctrine DBAL
- ADOdb
- 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 allow you to quickly set up basic scaffolding in your applications. Also, they are priced reasonably compared 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.
PDO
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 a database other 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.
Doctrine DBAL
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:
- MySQL
- Oracle
- Microsoft SQL Server
- PostgreSQL
- SAP Sybase SQL Anywhere
- SQLite
- Drizzle
Doctrine DBAL is developed under the Doctrine project, which also provides an 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 an 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.
ADOdb
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 setup 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 the 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 as 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 free options. In the next section, we'll look at a few popular commercial options that provide 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 just work as a database abstraction library—they provide a lot more than that.
PDOModel: Database Abstraction and Helper PHP Class
The PDOModel library is a database abstraction and helper PHP class which is built on top of the PDO extension. It’s an 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 allow 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 the PDOModel helper.
1 |
<?php
|
2 |
// initialize the PDOModel class
|
3 |
$pdoModelObj = new PDOModel(); |
4 |
|
5 |
// connect to a database
|
6 |
$pdoModelObj->connect("host", "username", "password", "database_name"); |
7 |
|
8 |
// querying database [select * FROM users]
|
9 |
$result = $pdoModelObj->select("users"); |
So as you can see, it's a breeze to perform database operations using the PDOModel helper class. For more information, you can visit the official PDOModel page on CodeCanyon.
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 allow you to generate scaffolding code easily.
xCRUD: Data Management System
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 the easy-to-use functions provided by the xCRUD system.
While using the xCRUD plugin, you just need to design your database tables, and the xCRUD plugin will handle the rest. With just a 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 you have to write to generate CRUD methods for the users table.
1 |
<?php
|
2 |
$xCRUD = Xcrud::get_instance(); |
3 |
$xCRUD->table('users'); |
4 |
echo $xCRUD->render(); |
Isn't that neat? With just a few lines of code, you've generated the scaffolding code for the users table. And you can do much more with this plugin as it comes with many features. You can visit the official plugin page on CodeCanyon for further information.
PDO CRUD: Advanced PHP CRUD Application
Lastly, let's look at the PDO CRUD plugin, which is also a CRUD generator plugin which supports MySQL, Postgres and SQLite database back-ends. With just a few lines of code, this plugin is able to generate a beautiful and appealing UI, which allows you to perform day-to-day operations with your database effortlessly.
As the name suggests, it's not just a basic CRUD generator, but rather it's a complete template for a database application. Let's have a quick look at a couple of the powerful features of this plugin:
- inline edits
- file uploads
- support for JavaScript plugins
- join operations
- searching, pagination, and export
- multi-language support
- and many more
And here's a quick look at the code which you would use to generate a basic CRUD interface:
1 |
<?php
|
2 |
$pdoCrudObject = new PDOcrud(); |
3 |
echo $pdoCrudObject->dbTable('users')->render(); |
That’s it! Visit the official page of this CRUD plugin on CodeCanyon for an insight into how powerful this plugin is.
Conclusion
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 database abstraction layers and CRUD systems below. We'd love to hear from you!
