Re-implement SQL output format

[PotOfCoffee2Go]

It is relatively easy in node-red to query relational databases (such as Oracle, MS, MySql) and update No-Sql databases (such as Mongo, Couch, Raven) . But the reverse is more difficult - inserting/updating data resulting from a No-Sql database to a relational database.

In the current release of node-red alasql, the SQL output format - 'INSERT INTO table_name VALUES (value1, value2, value3, ...);' was depreciated (by my request ;( ) due to requiring some significant mods to the alasql node to operate properly for the SQL output format.

AlaSql is perfect for that task. In the next few weeks I will be changing the alasql node to be able to query the results from No-Sql databases and produce the INSERT INTO statements required to update relational databases.

Any suggestions or comments appreciated! @PotOfCoffee2Go

[mathiasrw]

That sounds very reasonable - thank you again for contributing!

[PotOfCoffee2Go]

I started implementing alasql node to output INSERT INTO commands, but then it dawned on me; after using an alasql node to select the data - wire it to a standard function node with the following code. (Plus the code can be easily modified for special INSERT INTO edge cases).

// Build sql 'insert into' commands from data in payload
msg.db = msg.db || {table: 'node_red_tmp'};
msg.db.fieldnames = []; msg.db.sqlcmds=[];

function isPlainObject(input){
   return input && !Array.isArray(input) && typeof input === 'object';
}

// Create the insert commands for each object in payload
if (Array.isArray(msg.payload) && msg.payload.length && isPlainObject(msg.payload[0])) {
    msg.db.fieldnames = Object.keys(msg.payload[0]);
    msg.payload.forEach((rec) => {
        var values = msg.db.fieldnames.map(name => '"' + rec[name] + '"');
        msg.db.sqlcmds.push('INSERT INTO ' + msg.db.table + ' ' + msg.db.fieldnames.join(',') + 
            ' VALUES (' + values.join(',') + ');');
    });
}
return msg;

The results are in msg.db.sqlcmds, which then are pumped into a SQL database.

Export of node:

[{"id":"d29dc847.4a6038","type":"function","z":"1919d1c3.b05b5e","name":"Create SQL Insert Into","func":"// Build sql insert into commands from data in payload\nmsg.db = msg.db || {table: 'node_red_tmp'};\nmsg.db.fieldnames = []; msg.db.sqlcmds=[];\n\nfunction isPlainObject(input){\n   return input && !Array.isArray(input) && typeof input === 'object';\n}\n// Create the insert commands for each obj in payload\nif (Array.isArray(msg.payload) && msg.payload.length && isPlainObject(msg.payload[0])) {\n    msg.db.fieldnames = Object.keys(msg.payload[0]);\n    msg.payload.forEach((rec) => {\n        var values = msg.db.fieldnames.map(name => '\"' + rec[name] + '\"');\n        msg.db.sqlcmds.push('INSERT INTO ' + msg.db.table + ' ' + msg.db.fieldnames.join(',') + \n            ' VALUES (' + values.join(',') + ');');\n    });\n}\nreturn msg;","outputs":1,"noerr":0,"x":370,"y":420,"wires":[["2040a1d.e19675e"]]}]

[mathiasrw]

Sure, this will solve the problem.

I will reopen the issue and indicate it as a feature request so others might feel inspired to implement it.

[PotOfCoffee2Go]

Thanks for re-open, wasn't sure if to close or not. This would be a great enhancement! Given time I still would like to implement this enhancement, but maybe someone else can help.

The advantage of my solution is flexibility in building the INSERTS. The disadvantage is the field datatypes are unknown thus require creating strings of all data and INSERTing into a tmp table - ultimately SQL needs to update the production tables while converting data from the tmp table to the proper datatypes.

alasql would handle the datatypes properly, so the tmp table would not be required, and could directly INSERT INTO the production tables - which would be awesome!