Node-RED MySQL Node Cookbook

MySQL Cookbook.md

MySQL is a Node-RED node that wraps the node.js mysql2 module to provide query operations against a configured MySQL database. This enables a Node-RED flow to execute any valid MySQL queries, including SELECT, INSERT, 'UPDATEandDELETE` operations on the database.

On input uses the following msg properties to define the query:

• msg.topic holds the SQL query, and
• msg.payload optionally holds any bind values used in the query

Some query operations (e.g. SELECT) can return a result set, and this is returned in the output msg.payload property.

Bind Values

The recommended mechanism for including runtime values in SQL statements is to use bind values (BVs). These properly convert and safely escape variable content which significantly reduces the risk of enabling a SQL Injection Attack. The module also does Javascript to MySQl data type conversion and data serialisation on BVs, which can simplify your flow coding.

Note that whilst the bind value syntax resembles that of MySQL prepared statements, the module implementation uses its escape() method internally, and so using BVs does not directly realise execution performance advantages.

BV placeholders are embedded in the topic SQL query, with any bound values placed in the payload parameter. The encoding of these placeholders depends on the type of the payload property.

If the payload is an Object, then the placeholders are treated as named parameters, for example:

msg.topic=`INSERT INTO users(userid, username) VALUES (:id, :uname)
           ON DUPLICATE KEY UPDATE username=:urname;`;
msg.payload={ uname: 'example', id: 42 };
return msg;

If the payload is an Array, then the placeholders are treated as positional parameters; that is the i^th element of the payload array is substituted for the i^th parameter. Hence in this case, order is significant and if a parameter is repeated in the SQL then it must be correspondingly repeated in the payload array. There are two types of postitional parameter:

• Identifiers. These are denoted by the ?? escape and are used to substitute data definition identifiers.
• Values. These are denoted by the ? escape and are used to substitute data values.

These two types can be mixed within the same query, for example:

msg.topic="SELECT * FROM ?? WHERE dts> ?;";
msg.payload=[ 'users', new Date(some_date) ];
return msg;

Note that the payload should be an array, an object or null, as other datatypes will generate a MySQL error if any BVs are included in the SQL statement.

Automated Type Conversion

Perhaps the greatest convenience of using BVs is that data types undergo the following conversion, escaping and serialisation:

• Strings are safely escaped.
• Date objects are converted to valid MySQL datetime ('YYYY-mm-dd HH:ii:ss') strings.
• Numbers are passed as-is. Note that Javascript Timestamps are of type Number, and so are not converted correctly; however, using an explicit conversion, new Data(someTS), works fine.
• Buffers are converted to hex strings, e.g. X'0fa5'.
• Booleans are converted to true / false.
• undefined and null are converted to NULL.
• Numeric NaN / Infinity are passed as-is. However MySQL does not support these, and so these will trigger a MySQL error. Your code should avoid generating these values in any payload structures.
• Objects that have a toSqlString method will use this to convert the object to a string value.
• Other Arrays and Objects are stringified in a way that simplifies preparing SQL language statements as discussed below.

The data content of result sets also undergo automatic type conversion to native Javascript types:

• Decimal Types are converted to string representations with the required precision. This avoids decimal fraction rounding errors that can occur when converting to native binary numeric format. Remember to convert these to Number type before doing arithmetic operations such as addition, because using the + operator on two Decimal results will concatenate the strings rather than add their numeric values.
• Most other number types are cast to type Number.
• The Date, DateTime and TimeStamp types are cast to a Javascript Date object, which is usually presented as a string ISO DateTime format because of its toString() method. However all Date methods work fine, incuding getTime() to convert to a native JS Timestamp.

Array Conversion

Arrays are converted into comma-separated lists. For value placeholders, the individial elements are also quoted and escaped. They are left bare in the case of identifier placeholders. For example:

msg.topic="INSERT INTO ??(??) VALUES (?);";
msg.payload=[
    'users',
    ['userid', 'username'],
    ['example', 42] ];
return msg;

Nested arrays are turned into bracket delimited grouped lists, and these can be substituted into a VALUE ? clause. This is particularly useful as this enables bulk row inserts into a table with a message to a MySQL node. For example:

msg.topic="INSERT INTO logtable(??) VALUES ?;";
msg.payload=[
    ['class', 'msg'],
    [
      ['type1', 'Message 1'],
      ['type2', 'Message 2'],
    ]
  ];
return msg;

Coordinated use of identifier and value placeholders can improve the readability of the source code.

Object Conversion

Objects that don't have a toSqlString method are converted into comma separated list of key = 'val' pairs for each enumerable property on the object, with the value. Values are converted using the conversion rules above, except if the value is a function, then the key=value pair is skipped; if the property's value is an object then toString() (not toSQLString()) is called on it and the returned value is used.

This transformation naturally maps into the UPDATE SET and INSERT SET SQL statement syntax, for example:

msg.topic="UPDATE users SET ? WHERE id=?;";
msg.payload=[
    { uname: 'fred', dts: new Date() },
    42,
  ];
return msg;

Multiple statement queries

Multiple statement queries are enabled in the MySQL node. This works pretty much as you would expect: you can concatenate N statements and pass them to the MySQL node, which execute will then ouput an array of result sets (if N > 1). All N statements are processed as a single transaction, so if one fails then they all fail. These queries can include any valid SQL statements that the MySQL connected account has rights to access, including but not limited to: INSERT, SELECT, UPDATE, DELETE, SET and CALL.

This feature can be used to minimise the number of calls to a MySQL node.

MySQL helper node loopback template

TBD. I have developed a standard coding pattern for doing more complex MySQL I/O by using loopback MySQL node helper and function inversion. Is this pattern / template worth documenting?

Connection Parameters

TBD. Document these

Limitations

TBA.

Useful References

• MySQL Node source
• Node.js MySQL2 soure
• MySQL Module: Performing queries