Fields with the JSON datatype are returned as strings, not objects

[tharrington1]

I found this issue while using connect-jugglingdb to manage a session store in my express app.

connect-jugglingdb creates a Session schema with a JSON column named session

coll = schema.define('Session', {
    sid: { type: String, index: true}, 
    expires: { type: Date, index: true},
    session: schema.constructor.JSON
});

A row is created with session set to an object.

var s = {
    session: session // <~~~ object
};
// snip
coll.create(s, function(err) {
    callback(err);
});

The problem is that when the row is retrieved, jugglingdb-mysql returns session as a string, not an object, which I believe is in violation of the jugglingdb contract for the JSON datatype.

For example, this behavior causes connect-jugglingdb to crash my express app. In the snippet below (from connect-jugglingdb/index.js), jugglingdb-mysql returns session.session as a string. The crash-causing exception happens in the callback because it expects an object.

this.collection.findOne({where: {sid: sid}}, function(err, session) {
    if (err) return callback(err);
    if (!session) return callback();
    if (!session.expires || new Date() < session.expires) {
        callback(null, session.session); // <~~~ oops... not an object!
    } else {
        self.destroy(sid, callback);
    }
});

[dgsan]

I think for some reason text->JSON and JSON->text used to happen in JDB core, however if that's no longer the case, you could add the appropriate case to fromDatabase and parse into JSON there:

MySQL.prototype.fromDatabase = function (model, data) {
    if (!data) return null;
    var props = this._models[model].properties;
    Object.keys(data).forEach(function (key) {
        var val = data[key];
        if (typeof val === 'undefined' || val === null) {
            return;
        }
        if (props[key]) {
            switch (props[key].type.name) {
              case 'Date':
                val = new Date(val.toString().replace(/GMT.*$/, 'GMT'));
              break;
              case 'Boolean':
                val = Boolean(val);
              break;
              // ?
            }
        }
        data[key] = val;
    });
    return data;
};

I would also look at model.js in JDB core if adding a JSON case to from DB doesn't fully resolve the problem.

If you fix, please add a basic unit test for storing/retrieving JSON.