Super simple JSON and MySQL

I invented jsonhib (available for Node.js and PHP at http://github.com/ajaximrpg/jsonhib ) to provide a "good enough" solution to reading and writing JSON data to a MySQL database.

Most developers say this is impossible.

But, to read JSON from a MySQL database, jsonhib has a readRows() method that takes two arguments: a table name and a WHERE clause.  It returns a JSON array of JSON objects where each object represents a MySQL row.  The columns of each MySQL row become JSON property names; the values of each MySQL row become property values.  The WHERE clause only serves to narrow the number of JSON objects returned.

In Node.js, it looks like this:

// assume 'mytable' is a MySQL table with these columns: id, name
jh.readRows('mytable', 'WHERE id > 0', function(s) {
  var str = s;
});
// str='[{"id": 1, "name": "bob"}, {"id": 2, "name": "fred"}]'

Most developers will object that the objects are out of order.  "There's no sorting or ordering," they say, "the objects come out in random order."

They sure do.  Sometimes, the client doesn't care so it doesn't matter.  But, if it does matter and an additional integer column can be added to the table (a sort_column column), jsonhib can be directed to use this hidden column to maintain the order of rows.  And, if the table can't be modified, jsonhib relies on the caller to reorder the JSON array if he wishes.

Another objection is that MySQL databases require a schema and JSON objects can have arbitrary properties.  "The only choice is to put all your JSON objects in one table and serialize the JSON to a single MySQL text column," they say.

Uh, is that how you do it in client?  Just have all your JSON objects crammed into a single humongous array?  Mix your customer JSON objects with your sales order JSON objects and your permissions JSON objects?  Of course not!  You assign arrays of similar JSON objects to different variables.  In this case, similar JSON objects are assigned to specific MySQL tables.  Your client doesn't add customer JSON objects to the permissions variable; don't add customer data to the MySQL permissions table.

Also, don't all customers have a name?  Isn't their name always a string, not a floating point value?  Yes, JSON objects can have arbitrary properties but they always have a lot of properties that are expected and required and are of a specific type.  jsonhib relies on the caller to know that, by default, JSON properties that do not fit into the MySQL schema will be discarded.  Avoid extra properties or store them somewhere else.  If that is not desirable and an additional string column can be added to the table (a json_column column), jsonhib can be directed to use this hidden column to keep track of JSON data that doesn't fit into the MySQL schema.  This column doesn't keep all the JSON data; it just keeps the JSON properties that don't have a corresponding dedicated MySQL column or have a MySQL column of the wrong type.

jsonhib really is just an object-relational mapping (ORM) layer for JSON and MySQL.  When reading, jsonhib queries MySQL rows and sensibly maps MySQL columns to JSON properties and, if available, uses the sort_column and json_column columns to fix up the mismatches between how MySQL works and how JSON works.

Besides reading, jsonhib can also insert, delete and update JSON objects.  JSON arrays (i.e. MySQL tables) can also be reordered using the moveRow() method.

// insert a row

jh.insertRow('mytable', '', -1, '{"id": 1, "name": "bob"}',

  function() {});

// delete a row

jh.deleteRow('mytable', '', 0,

  function() {});

// update a row

jh.updateRow('mytable', 'WHERE id=1', 0, '{"id": 1, "name": "eric"}',

  function() {});

// move a row (huh?)

jh.moveRow('mytable', 'WHERE id=1', 0, 1,

  function() {});

jsonhib has a lot of nice attributes.  Other solutions require new SQL syntax provided by specially modified MySQL database software, plugins or new versions but jsonhib works with any MySQL version and any MySQL data.  Existing applications and processes that use the MySQL database work without modification and can work with data that is inserted, updated and deleted using jsonhib.

Impossible?  No, not impossible.  JSON can be stored in MySQL.