Handling JSON data type.

[harveyramer]

It looks like the JSON data type is simply handled as a string. Long-term, it should be handled as the the mysql npm project does. For reference, see this PR: mysqljs/mysql#1299

[harveyramer]

For the time being, a typeCast function seems to handle the conversion for both reads and writes.

config[env].config({
    host: dbSecret[env].host,
    user: dbSecret[env].username,
    password: dbSecret[env].password,
    database: dbSecret[env].dbname,
    port: dbSecret[env].port,
    typeCast: function castField(field, useDefaultTypeCasting) {
      if (field.type === "BIT" && field.length === 1) {
        var bytes = field.buffer();
        return bytes? bytes[0] === 1 : false;
      }
      if (field.type === "JSON") {
        return JSON.parse(field.string());
      }
      return useDefaultTypeCasting();
    }
  });

[akleiber]

AWESOME - this saved my day, thank you!

[Jay-flow]

@harveyramer Thx :)

[sl45sms]

@harveyramer thanks!

[adlabac]

There is one catch - if the BIT value is NULL then this will cause the exception. Therefore you should check if the variable bytes is NULL before using it.

Also, sometimes TINY is used for BOOLEANs. What's interesting is that in this case buffers are different- they contain ASCIII values of '0' and '1', instead of 0 and 1.

Here's my updated code:

if ((field.type === 'BIT' || field.type === 'TINY') && field.length === 1) {
    const bytes = field.buffer();
    if (bytes === null || bytes === undefined || bytes.length === 0) { return null; }
    return !((field.type === 'BIT' && bytes[0] === 0) || (field.type === 'TINY' && bytes[0] === 48));
}

[naorpeled]

Hey @harveyramer,
thanks so much for this insightful thread, much appreciated ❤️

I'll be closing this issue for now if that's okay with you,
let me know if you want me to re-open it or if you have any questions 🙏