Build an easy JavaScript data access layer

Use PHP, jQuery, and JavaScript to access your database simply and securely

Work through the entire process of building a database access wrapper in PHP for the back end and connecting it to a JavaScript access wrapper at the user interface layer in this article.

Share:

Jack D. Herrington, Senior Software Engineer, Fortify Software, Inc.

Photo of Jack HerringtonJack Herrington is an engineer, author, and presenter who lives and works in the Bay Area. You can keep up with his work and his writing at http://jackherrington.com.



15 March 2011

Also available in Chinese Japanese

Introducing easy JavaScript data access

Frequently used acronyms

  • Ajax: Asynchronous JavaScript + XML
  • HTML: HyperText Markup Language
  • JSON: JavaScript Object Notation
  • PDO: PHP Data Objects
  • REST: Representational State Transfer
  • SQL: Structured Query Language
  • URL: Uniform Resource Locator
  • W3C: World Wide Web Consortium
  • XML: Extensible Markup Language

So you want to rapidly prototype a really cool Web 2.0 JavaScript application, but you don't want to spend all your time writing the wiring code to get to the database? Traditionally, to get data all the way from the database to the front end, you need to write a class for each table in the database with all the create, read, update, and delete (CRUD) methods. Then you need to put some marshalling code atop that to provide an access layer to the front end. Then you put JavaScript libraries on top of that to access the back end. What a pain!

This article presents an alternative method in which you use a single database class to wrap multiple database tables. A single driver script connects the front end to the back end, and another wrapper class on the front end gives you access to all the tables you need. Figure 1 shows the complete stack.

Figure 1. The JavaScript data access layer architecture
Diagram of the JavaScript data access layer architecture

In the sample application, a listing page that has the ability to add new entries is the base of the user interface. This page uses a JavaScript class to access the database through the back end. That JavaScript class, in turn, uses jQuery to make the Ajax requests.

The Ajax requests are fielded by a PHP driver page, which makes sure that the request is valid and then passes it onto a database wrapper class that handles the request. The wrapper returns some results, which the driver then formats into JSON to send back to the browser.

This approach has its limitations. First, it's not good for work across related tables because each request is made on a per-table basis. To fix this method, you can add new custom methods to make relational requests across tables. The second issue is data security because there is no check of roles or users to ensure proper access to the data. You can fix this method, too, with the addition of some code to handle users and roles.

What this code does provide is a fast, easy, and secure way to get data from the database into the user interface for rapid prototyping applications. Plus it's a good learning tool for skills such as database access, PHP, Ajax, jQuery, and JavaScript.


Building the database

It all starts with the database. In this example, you use a database of authors and their related books. Listing 1 shows the MySQL initialization code for the database.

Listing 1. books.sql
DROP TABLE IF EXISTS authors;
CREATE TABLE authors (
  id BIGINT NOT NULL PRIMARY KEY auto_increment,
  name VARCHAR(255) NOT NULL
);

DROP TABLE IF EXISTS books;
CREATE TABLE books (
  id BIGINT NOT NULL PRIMARY KEY auto_increment,
  author_id INT NOT NULL,
  name VARCHAR(255) NOT NULL
);

INSERT INTO authors VALUES ( 0, 'Jack Herrington' );
INSERT INTO authors VALUES ( 0, 'Dave Smith' );
INSERT INTO authors VALUES ( 0, 'Harold Rollins' );

INSERT INTO books VALUES ( 0, 1, 'Code Generation In Action' );
INSERT INTO books VALUES ( 0, 1, 'Podcasting Hacks' );
INSERT INTO books VALUES ( 0, 2, 'Welcome To The Universe' );

To get the code into the database, you first need to create the database like so:

% mysqladmin create books

Then, you add the tables and some example data into the database like so:

% mysql books < books.sql

Of course, your commands might differ slightly if you need to specify a database user name and password.


Creating a database wrapper

To access the database, you first create a DatabaseWrapper class in PHP. This class takes a database connection handle and a table name on construction and provides five methods:

  • get—The get method returns a single row from the table given an integer ID. It assumes that an "id" field in the table has unique integer values.
  • getAll—This method returns all the records from the database. It takes some options. The only one implemented here is limit, which restricts the count of records returned. You can add more if you like—for example, an "order" option to return the records ordered by a specified field.
  • delete—The delete method takes an integer ID and deletes that row from the table.
  • insert—The insert method adds a new row to the table.
  • update—The update method takes an integer ID to specify the row to update, as well as a hash table of fields to update with the specified values.

These five functions present a complete CRUD interface to any client. They are limited, though, in that they work on only a single table and not across multiple tables through relationships.

Listing 2 shows the code for the database wrapper class.

Listing 2. DBWrapper.php
<?php
class DatabaseWrapper {
  private $dbh;
  
  public function __construct( $dbh, $table ) {
    $this->dbh = $dbh;
    $this->table = $table;
  }

  public function get( $id ) {
    $sql = 'SELECT * FROM '.$this->table.' WHERE id = :id';
    $sth = $this->dbh->prepare($sql);
    $sth->execute(array(':id' => $id));
    return $sth->fetchObject();
  }

  public function getAll( $options = array() ) {
    $sql = 'SELECT * FROM '.$this->table;
    if ( isset( $options->{'limit'} ) ) {
      $sql .= ' LIMIT '.$this->dbh->quote( $options->{'limit'}, PDO::PARAM_INT );
    }
    $sth = $this->dbh->prepare($sql);
    $sth->execute();
    return $sth->fetchAll( PDO::FETCH_CLASS );
  }

  public function delete( $id ) {
    $sql = 'DELETE FROM '.$this->table.' WHERE id = :id';
    $sth = $this->dbh->prepare($sql);
    $sth->execute(array(':id' => $id));
  }

  public function update( $id, $values ) {
    $binds = array( ':id' => $id );
    $bindnames = array();

    foreach(array_keys($values) as $k) {
      $k = $this->clean( $k );
      $binds[ ":$k" ] = $values[ $k ];
      $bindnames []= "$k=:$k";
    }

    $bindnames = join( $bindnames, ',' );

    $sql = 'UPDATE '.$this->table." SET $bindnames WHERE id=:id";

    $sth = $this->dbh->prepare($sql);

    $sth->execute( $binds );
  }

  public function insert( $values ) {
    $keys = array();
    $binds = array();
    $bindnames = array();

    foreach(array_keys($values) as $k) {
      $k = $this->clean( $k );
      $keys []= $k;
      $binds[ ":$k" ] = $values[ $k ];
      $bindnames []= ":$k";
    }

    $keys = join( $keys, ',' );
    $bindnames = join( $bindnames, ',' );

    $sql = 'INSERT INTO '.$this->table." ( $keys ) VALUES ( $bindnames )";

    $sth = $this->dbh->prepare($sql);

    $sth->execute( $binds );

    return $this->dbh->lastInsertId();
  }

  private function clean( $k ) {
    return preg_replace( '[^A-Za-z0-9_]', '', $k );
  }
}
?>

The code for get and getAll is the most straightforward. It uses the PDO library for PHP to make the SELECT requests to the database through the connection handle. The delete method is also pretty easy as it just makes a simple DELETE request.

One important thing to note is the use of the colon delimited replacement operators in the SQL statements. These replacement operators are the key to secure database access. Replacement operators ensure that malicious scripts can't append SQL into queries to override the existing code and get access to data that they shouldn't see. This tactic is called "SQL injection" and is a very common form of attack.

The insert and update methods are a bit more complex because they need to format the SQL statements with additional fields for the input data. The field names are cleaned using the clean method that returns any characters that aren't allowed in a field name. This method removes the potential for an SQL injection attack.


Putting a web front end on the database access

To get the data to the browser, you need to make it accessible through a web page. To do this, you create a page called driver.php. Listing 3 is the code for this web page.

Listing 3. driver.php
<?php
include 'dbwrapper.php';

header( 'Content-Type', 'application/json' );

try {
  $table = null;
  
  if ( $_GET['table'] == 'books' || $_GET['table'] == 'authors' )
    $table = $_GET['table'];
  else
    throw new Exception( 'Invalid table name' );

  $dw = new DatabaseWrapper(
    new PDO('mysql:host=localhost;dbname=books', 'root', ''),
    $table
  );

  $additional = array();
  foreach( array_keys( $_GET ) as $k ) {
    if ( $k != 'table' && $k != 'method' && $k != 'id' ) {
      $additional[$k] = $_GET[$k];
    }
  }

  switch( $_GET['method'] ) {
    case 'get':
      print json_encode( $dw->get( $_GET['id'] ) );
      break;
    case 'getAll':
      print json_encode( $dw->getAll( $additional ) );
      break;
    case 'insert':
      print json_encode( $dw->insert( $additional ) );
      break;
    case 'update':
      $dw->update( $newid, $additional );
      print json_encode( true );
      break;
    case 'delete':
      $dw->delete( $_GET['id'] );
      print json_encode( true );
      break;
    default:
      throw new Exception( 'Unknown method' );
      break;
  }

} catch ( Exception $e ) {
  print json_encode( array( 'error' => $e->getMessage() ) );
}
?>

This simple script takes GET parameters from the web request and sends them to a DatabaseWrapper instance. It then formats the result as JSON if it's successful. If there is an error in the processing, an exception is thrown, which is then also formatted as JSON.

To ensure that the script can't be used to access any table in the database, the code first checks the table name requested. In this case, only "authors" or "books" can be accessed. If you use this code for your own schema, you need to alter that fragment of the code.

The code then looks at the method parameter to see which of the five actions (get, getAll, delete, insert, or update) to invoke. For insert, update and getAll also send along an array of the additional keys in the GET array.

To test the code, open a web browser and access the driver.php page using various URL parameters. Figure 2 shows when the URL specifies accessing a single record using the get method.

Figure 2. The JSON request for a single record
Screen capture of request for single author name: Jack Herrington

Figure 2 shows the JSON-encoded record for the author row requested. The JSON object includes both the "id" field and the "name" field for the record encoded as a JavaScript hash table.

The parameters on this request are:

driver.php?table=authors&method=get&id=1

To get all the records, simply change the method and remove the "id" specification:

driver.php?table=authors&method=getAll

Figure 3 shows the JSON result of this.

Figure 3. The JSON request for all records
Screen capture of mutiple author names: Jack Herrington, Dave Smith, Harold Rollins, Geraldo Rivera

In Figure 3 is an array of JavaScript records returned where the array includes an object with a "name" and "id" field for each of the rows in the database.

The foundation of server code is certainly enough to build a web interface around. Let's start to build the front end.


Creating the JavaScript access class

To access the database from the web browser, you need a class that makes use of the driver.php script through Ajax. Ajax requests can be a little tricky without the use of a JavaScript library such as jQuery or Prototype.js. In this case, jQuery is used, but you can use whatever library you like with a few modifications to the code.

In addition to jQuery, you also use the Class extension, which allows you to easily define a new JavaScript class—in this case, the DbWrapper class, as in Listing 4.

Listing 4. dbwrapper.js
var DbWrapper = Class.create({
  table: '',
  get: function( id, callback ) {
    $.getJSON( 'driver.php', { table: this.table, method: 'get', id: id }, callback );
  },
  getAll: function( callback, params ) {
    if ( params == null ) params = {};
    params.table = this.table;
    params.method = 'getAll';
    $.getJSON( 'driver.php', params, callback );
  },
  insertObject: function( params, callback ) {
    params.table = this.table;
    params.method = 'insert';
    $.getJSON( 'driver.php', params, callback );
  },
  updateObject: function( id, params, callback ) {
    params.table = this.table;
    params['id'] = id;
    params.method = 'insert';
    $.getJSON( 'driver.php', params, callback );
  },
  deleteObject: function( id, callback ) {
    $.getJSON( 'driver.php', { table:this.table,
        'id':id, method: 'delete' }, callback );
  }
});

This short class wraps each of the five methods on the back end. Each method calls getJSON with a different set of parameters based on the method selected. In addition, each takes a callback that handles the returned data from the request.

The method names for insert, update, and delete have been changed to insertObject, updateObject, and deleteObject because delete is a reserved word in JavaScript.

Listing 5 shows the page that makes use of this new JavaScript class.

Listing 5. Index.html
<html>
<head><title>Database access test</title>
<script src="jquery-1.5.min.js"></script>
<script src="Class-0.0.2.min.js"></script>
<script src="dbwrapper.js"></script>
<script>
var dbw = null;

function updateAuthorsTable() {
  dbw.getAll( function( data ) {
    $('#authors').html('<table id="authors"><tr><td>ID</td>
                        <td>Author</td></tr></table>');
    $(data).each( function( ind, author ) {
      $('#authors tr:last').after('<tr><td>'+author.id+'</td>
                        <td>'+author.name+'</td></tr>');
    } );
  } );
}

$(document).ready(function() {
  dbw = new DbWrapper();
  dbw.table = 'authors';

  updateAuthorsTable();
  
  $('#addbutton').click( function() {
    dbw.insertObject( { name: $('#authorname').val() },
      function( data ) {
        updateAuthorsTable();
      } );
  } );
} );
</script>
</head>
<body>
  <table id="authors">
  </table>
  <h3>Add A New Author</h3>
  Name: <input type="text" id="authorname">
  <input type="submit" value="Add" id="addbutton">
</body>
</html>

The first thing this page does is include jQuery, the Class extension, and your new DbWrapper.js file. It then creates a DbWrapper as the page loads in the ready function. This DbWrapper instance is then used by the updateAuthorsTable function that requests all the authors from the database using the wrapper. The updateAuthorsTable function then adds rows to a table on the page to display the contents of the database table.

An author name form is located at the bottom of the page, along with an Add button. The script for this add button calls the insertObject method on the DbWrapper class to add a new row to the database. When that operation is successful, it then calls the updateAuthorsTable function to update the display with the new row.

The first time you bring up the page you see something like Figure 4 if everything is set up properly.

Figure 4. The dynamic HTML list and add page
Screen capture of list of authors (Jack Herrington, Dave Smith, Harold Rollins) and Add A New Author field

This figure shows that the page has connected to the driver.php script on the server side, requested the data, and then formatted it for display. This display should show, if everything is working well, the list of authors from the database and an input box and Add button to create a new author record.

The next step is to check whether adding a row works. In this case, enter Geraldo Rivera for the author name field, as in Figure 5, and click Add.

Figure 5. Adding a new record
Screen capture with new author name (Geraldo Rivera) in the Add A New Author field

The code for the Add button calls the insertObject method on the DbWrapper class, which in turn invokes the insert method on the server. When that step is successful, the JavaScript on the page then updates the author table using the function that was first called during the page load. Figure 6 shows the result.

Figure 6. The page updated with the new record
Screen capture with new author (Geraldo Rivera) added to author list (Jack Herrington, Dave Smith, Harold Rollins)

Figure 6 shows that the application has added the "Geraldo Rivera" author to the authors table and it has appeared on the display. This figure demonstrates that you have both read and write access to the database on the back end. To access a different schema with more tables, you only need to add support for the new tables to the driver.php script.


Conclusions

As I mentioned at the beginning of the article, this code is probably best used for rapid prototyping and not in production. There are several reasons for this:

  • The access is very open and account for users, roles, or permissions.
  • It's not particularly efficient if you have relations between tables in your schema, which you most likely do.

I might also be faulted for not being particularly RESTful in this example. In the REST world, applications should not respond to a GET request in a manner that alters the state of the application (which happens on insert, update, and delete requests). In my defense, it's a lot more difficult to set up a PHP service that handles POST, DELE, and all the URL rewriting required by REST. I used a simple page here as a demonstration instead.

All the caveats aside, however, this example is actually a remarkably fun example of how to use PHP, the PDO library, and jQuery together to make a portable data access layer for JavaScript. Try it out; I'm sure that you will have some fun with it even if it doesn't make it into production.

Resources

Learn

  • The jQuery site: Get what you need for rapid web development: Download the jQuery library and find all of the documentation you need.
  • jQuery Class extension: Find this handy extension for class creation and management for jQuery that is hosted on the Google code site.
  • PHP site: Visit the best reference for PHP, a widely used, general-purpose scripting language that is especially suited for web development.
  • The W3C: Visit this great site for standards; in particular, the XML standard is relevant to this article.
  • The PDO library: Find excellent documentation about all of these use cases for accessing databases in PHP and more.
  • More articles by this author (Jack Herrington, developerWorks, March 2005-current): Read articles about Ajax, JSON, PHP, XML, and other technologies.
  • XML area on developerWorks: Get the resources you need to advance your skills in the XML arena.
  • developerWorks Open source zone: Find extensive how-to information, tools, and project updates to help you develop with open source technologies and use them with IBM's products, as well as our most popular articles and tutorials.
  • My developerWorks: Personalize your developerWorks experience.
  • IBM XML certification: Find out how you can become an IBM-Certified Developer in XML and related technologies.
  • XML technical library: See the developerWorks XML Zone for a wide range of technical articles and tips, tutorials, standards, and IBM Redbooks. Also, read more XML tips.
  • developerWorks technical events and webcasts: Stay current with technology in these sessions.
  • developerWorks on Twitter: Join today to follow developerWorks tweets.
  • developerWorks podcasts: Listen to interesting interviews and discussions for software developers.
  • developerWorks on-demand demos: Watch demos ranging from product installation and setup for beginners to advanced functionality for experienced developers.

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into XML on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=XML, Open source
ArticleID=631361
ArticleTitle=Build an easy JavaScript data access layer
publish-date=03152011