Generating PHP Database Access Layers

The foundation of any Web application is the database. In a well-factored application, the database is protected by a set of objects contained within the database access layer. On top of this layer is the business object layer, which implements the business rules. During execution, the user interface layer communicates with the business object layer. These three code layers form the structure of a three-tier Web application server. Two-tier servers merge the business logic into either the database layer or the user interface.

In either topology, the database access layer is a focal point for the application because it provides a level of abstraction between the customer view of the data and its implementation within the database. Because of this, the robustness of the application as a whole is partially dependent upon the robustness of the database access layer at the bottom of the technology stack.

What is the best approach to producing a solid database access layer? Code generation. Thankfully, there are a number of tools available to build database layers for PHP automatically. Throughout the remainder of this article, I will concentrate on the use of code generation techniques and tools upon the database access layer solely. However, when applied against the entire code base, these techniques will enhance the reliability and robustness of a Web application tremendously.

Introduction to code generation

Code generation is the technique of using a special program which builds code to match a set of user-defined requirements. This special program is commonly referred to as a code generator. In the case of PHP database access layers, the code generator will read a definition of the database and create PHP files which contain the database access layer code.

There are two basic types of code generators, and it is important to note the difference between them:

Passive generators
generate the code once. Engineers maintain the generated code in the long term. If additional fields are required, they are added manually, including all the necessary code to manipulate the new fields. Should the new code need to be introduced into several different areas of the existing code base, this is done manually as well.
Active generators
build and maintain code in the long term. When additional fields are required, they are added to the input of the generator. The generator is re-executed to produce new database access code, which includes support for the new fields.

I strongly advocate using an active generator to build your code. If you want to use an off-the-shelf generator, make sure it follows the active generation model.

Active generation has strong advantages when compared with writing the same code by hand:

Code generators use text templates to produce code. The quality of the code that is produced relates directly to the quality of the templates used to produce it. As problems are found and addressed in the database access layer, the templates are modified accordingly. The generator is re-executed to propagate the changes throughout the database access layer code.
If the API of the access layer changes, an active generator will make the change consistently across newly-generated code.
Inevitably, during the course of development, tables and fields are added, changed, or removed multiple times. Active generation supports re-building the database layer to match a changing schema almost instantly. This is the kind of development agility that is tough to live without, once experienced.

Obviously, there are compelling reasons to use generators to build code, but why should we apply them to the database access layer in particular?

Why code generation for database access?

The primary reason to generate database access layers is to use the quality and consistency benefits of generation to make a strong foundation for your application. The secondary reason is that database access code is particularly amenable to generation.

Let’s take the example of an INSERT statement. PHP code for an insert statement using PEAR might look like this:

function add_person($first,$last)
  $sql = "insert into names(first,last) values(?,?)";
  $result = $this->db->query($sql,array($last,$first));

This is pretty simple stuff. The function takes the arguments and marshals them into an SQL statement, then checks to see whether an error occurred. Even so, this small function has an error in the ordering of the arguments, which is often where errors occur in this type of code.

Note the repetitive nature of the code. The field names are repeated in both arguments lists, the SQL statement and the query statement. The structure of the function itself is repeated for every SQL statement to be executed against the database.

Repetitive coding is the nature of database access work. It’s easy to mess it up and difficult to excel at it. When you have code that requires clerical work to create and maintain, it is a warning sign that you could, and probably should, be generating the code.

PHP Code Generators

There are a number of generators available for PHP code generation, both commercial and Open Source. Some just build database code; others build entire applications.

This is a Free generator which looks at the table definition of a table on a database server and builds code for inserting, updating, and deleting records from the table.
This is a commercial application builder for PHP. Database specifications are provided to JaneBUILDER using GUI tools, and it builds PHP database access layer code to match those specifications.
This is an Open Source generator which builds code and pages for database tables.

This is just a sampling. There are a number of smaller Open Source projects which can be obtained and tweaked to meet requirements, or you can build a generator from scratch, if this is warranted.

Starting out with code generation can be a daunting experience without help. The field is fairly new and undergoing rapid changes. I’ve included some tips which will help ease your way into making the most of code generation.

Using off-the-shelf generators

Understand the generator.

You should fully understand what the generator requires and what it delivers, particularly the requirements on the form and format of its input and the form its output will take. Determine early in the process whether you can deliver the data in the form the generator requires and whether the templates which generate code can be modified to format the code to meet local coding guidelines. Believe me, time spent vetting the generator is inconsequential in comparison to the time savings that result as the generator builds code for you.

Remember who is boss.

The generator is just a tool. You should be dictating your database design and requirements to the tool, not vice versa. Ideally, you should have a database and API design in mind before you choose the generator, and you should select the generator most suited to implementing that design for you.

Maintain a high level of abstraction in the generator.

A core value of generation is keeping the design separate from the implementation. When a design is codified into SQL and PHP, it is difficult to migrate it to other platforms or newer technologies. The design becomes lost in the implementation minutiae of managing database connections and marshaling data. When a generator is used, the design is maintained in an XML file or some other abstract form which can be reused to generate code for a different platform or technology.


Engineers tout the productivity benefits of generation, but the real advantage comes from how quickly an existing generated code base can be modified to meet changing requirements. Code generation is an invaluable tool whose time has come. Combining code generation with PHP creates a potent mix for agile application development.

Author’s bio:

Jack Herrington is a Software Engineer with over 20 years of experience. He is the editor of the Code Generation Network and Bound Cast. He is the author of the books Code Generation in Action, Podcasting Hacks and the upcoming PHP Hacks. He lives in Union City, California with his wife and daughter.