Integrating Client-Side and Server-Side Databases

In a web app, we usually fetch some amount of data to be maipulated by the user. The user may wish to search the data, filter the data, sort the data, and carry on the spot update of the data. Carrying this tasks via a run around trip to the server is too slow. Moreover, it misses the required user experience of performing operations locally, committing an update only when a whole task was completed.

What is needed is an approach that integrates some data procesing and storage at the client-side (browser) with the long-time persistence and bulk data processing of data in the server-side.

Across several projects using either plain JavaScript or JavaScript frameworks such as AngularJS, I have distilled an approach that integrates a client-side database and a server-side database.

For the client-side database, the HTML5 storage facilities such as local storage or session storage, were deemed too low level as they do not have SQL capabilities for queries. TaffyDB is a JavaScript datbase that is easy to integrate in the client-side which offers query/update capabilities.

For example,

  // Create DB and fill it with records
  var friends = TAFFY([
        {"id":1,"gender":"M","first":"John","last":"Smith","city":"Seattle,   WA","status":"Active"},
        {"id":2,"gender":"F","first":"Kelly","last":"Ruth","city":"Dallas, TX","status":"Active"},
        {"id":3,"gender":"M","first":"Jeff","last":"Stevenson","city":"Washington, D.C.","status":"Active"},
        {"id":4,"gender":"F","first":"Jennifer","last":"Gill","city":"Seattle, WA","status":"Active"}    
 ]);

// Find all the friends in Seattle
friends({city:"Seattle, WA"});

// Find John Smith, by ID
friends({id:1});

// Find John Smith, by Name
friends({first:"John",last:"Smith"});

TaffyDb can optionally persistently store data in the browser local storage:

db.store("friends"); // starts storing records in local storage

The data to be stored in the client-side database is fetched from the server via AJAX calls. Subsequent filter, search, sort, and edit operation in the web app are carried on the client-side.

Only when a significant update is required to be committed to the server-side, do we perform an AJAX call to the server. The call will invoke a web service at the server that will save the data into the server-side database.

Interaction with the server-side database can be carried in any server-side framework, be it PHP or Node.js or WCF. Because such calls to the server are really a wrapper around operations on the server-side database, I found the Slim Framework PHP micro framework, a very convenient vehicle to build the server-side interaction (API).

Using the NotORM PHP database library we are able to work equally well across MySQL and Microsoft SQL Server. We just switch the DSN connection string.

$dsn_mysql = 'mysql:dbname=friends;host:friends.example.com';
$dsn_sqlserver     ='sqlsrv:Server=friends.example.com;Database=friends';

$dsn = ; // one of $dsn_mysql or $dsn_sqlserver

$username = 'johndoe'; 
$password = 'root';
$pdo = new PDO($dsn, $username, $password);
$db = new NotORM($pdo);