MySQL 5.7.8+ JSON type handling

[maligree]

How does node-mysql stand on the upcoming JSON data type? I'd be interested in helping out with an implementation if it's not in yet.

[dougwilson]

We should get it added to the type casting, but nothing has been done yet. Help is welcome!

[sidorares]

on a wire level there is nothing special - it's just a string with json data

[sidorares]

https://github.com/sidorares/node-mysql2/commit/8c490d9a7f171d511a94dc579604c7b676b3048b

[matteocontrini]

Looking forward to this :)

[RWOverdijk]

:+1:

[crapthings]

+1

[lganet]

+1

[koeniglorenz]

+1

[sidorares]

Note everyone who +1d - it's already possible with typeCast option:

connection.query({
  sql: '...',
  typeCast: function (field, next) {
    if (field.type == 'JSON') {
      return (JSON.parse(field.string())); 
    }
    return next();
  }
});

[koeniglorenz]

@sidorares works perfectly, thank you!

[nicolaspeixoto]

+1

[ChampIsMe]

Note everyone who +1d - it's already possible with typeCast option:

connection.query({
  sql: '...',
  typeCast: function (field, next) {
    if (field.type == 'JSON') {
      return (JSON.parse(field.string())); 
    }
    return next();
  }
});

Where do I add this code specifically? I have same issue here

[dkloke]

The typeCast option can be passed in a connection options object (sometimes referred to as "config" or "options" in the docs).

https://github.com/mysqljs/mysql#connection-options

This object is accepted by:

• mysql.createConnection()
• mysql.createPool()
• mysql.createPoolCluster()
• mysql.query()

Passed to instantiate connection/pool objects, the typeCast operation is applied to every query result received by that connector. The option can also be included on a per-query basis, overriding connector settings and permitting different typeCasts to be applied for specific queries.

[ChampIsMe]

Got it, Thanks.

[hugebdu]

typeCast does the job for retrieval, however is there any trick for inserts, especially SET ? syntax?

assuming I have:

CREATE TABLE `pets` (
    `id` CHAR(36) NOT NULL,
    `json` JSON,
    PRIMARY KEY (`id`)
);

I'd like to execute:

const pet = {
  id: '123',
  json: { color: 'grey' }
};

connection.query('INSERT INTO pets SET ?', pet, ...)

currently I have to manually stringify json property, otherwise fails with invalid syntax.

10x

[dkloke]

Here is a toSqlString() function that renders every key of a query parameter that is an object (except Array, Date, or String types) to MySql-compliant JSON string literal (double-escaped double-quotes in string elements). We ignore the other types to preserve regular features of the SqlString library (.escape() and .escapeId()) which mysql-js exposes from dependency. Lightly tested with nested JSON target objects.

const mysql = require('mysql');  // so we can call .escape() and .escapeId()
// or require the SqlString library for these functions.

const prepMySqlJsonStringLiteral = obj => "'"
  + JSON.stringify(obj)
    .replace(/('|")/g, "\\$1") // handles double quotes in strings for MySql Json literals
  + "'";

const isTargetObject = item => 
  typeof item === 'object' 
  && ! [Array, Date, String].some(typ => item instanceof typ)
;

const toSqlString = function() {
  return Object.keys(this)
    .filter(e => typeof this[e] !== 'function')
    .map(function(e) {
      const val = isTargetObject(this[e])
        ? prepMySqlJsonStringLiteral(this[e])
        : mysql.escape(this[e]);
      return mysql.escapeId(e) + ' = ' + val;
    }.bind(this))
    .join(', ')
};

To make it more specific, change the isTargetObject(self[e]) condition. For example to only work for keys named json, isTargetObject() is not needed, the line would be would be const val = e==='json'. If arrays should be JSON.stringify()ed, remove Array from the isTargetObject() Object type list.

Best to use in a Pet() constructor (or class), and provide .toSqlString() as a method (direct or prototype) of Pet(). The method must be named toSqlString.

const Pet = function(id, json){
  Object.assign(this, {id, json})
  // this.toSqlString=toSqlString  // works, alternative to prototype
}
Pet.prototype.toSqlString=toSqlString
const pet = Pet( '123', { color: 'grey' });

Or monkey-patch: pet.toSqlString=toSqlString;.

connection.query('INSERT INTO pets SET ?', pet, ...) should now work as desired.

Monkey-patch wrapper:

const withToSqlString = (obj) => {
    obj.toSqlString = toSqlString;
    return obj;
  };
connection.query('INSERT INTO pets SET ?', withToSqlString(pet), ...)

Alternatively, supply a custom queryFormat function in the mysql config object, which can be applied per-query or per-connection.