Recognize JS Data Type subflow

Introduction

This subflow is part of our Internal flows collection. Internal flows are used for manipulating and storing data inside Node-RED.

SQL Recognize Type Subflow is meant to be used in combination with SQL Create Table and SQL Create Insert Query Subflows, this subflow is used as a helper flow which generates output array which is needed for other two previously mentioned subflows.

Prerequisites

This flow along with other data generating nodes and flows is available for user in the following package:

@senlab/node-red-generate-random-senlab

To learn more about Verdaccio and how to install packages, please refer to this documentation: Verdaccio

Setting I/O nodes and environmental variables

When creating a subflow user needs to define input node and one or more output nodes, so the messages can pass through our custom implementation. In the top bar there are configuration options enabling us to do that.

image::subflows-config.png

Environmental variables can be added to the subflow using the edit properties window, where user can also change the name of the subflow. Environmental variables can be used to make subflow configurable (make subflow reusable in different configurations). For example user can specify environmental variable named addID which is of type boolean and build custom implementation of the code based on this environmental variable where subflow outputs JS object which does or doesnt include ID based on selected option when deploying the subflow.

Inside the subflow environmental variables are accessible with: env.get(’);

This subflow uses 1 environmental variable named addID, working of the subflow strongly depends on this variable. Based on this variable subflow chooses to add ID column to SQL table or not.

Setting Function Node for statistical operations

Function Node is powerful Node-RED node which enables us to add our Custom Javascript code to Node-RED without the need to make custom nodes.

This function is built to recognize datatypes from the incomming object and construct array where it includes datatype, name and value.

For-loop is used to iterate through properties of an incomming object, using isNaN method it checks if object value is number, it can recognize of number is of type float or int using parseFloat method.

if (!isNaN(inputObject[key])){
    if (Number.isInteger(parseFloat(inputObject[key]))) {
        if (key === "id") {
            array.push(key + "_device", parseInt(inputObject[key]), "INT");
        } else {
            array.push(key, parseInt(inputObject[key]), "INT");
        }
        continue;
    } else {
        array.push(key, parseFloat(inputObject[key]), "FLOAT");
        continue;
    }
}

If value of property is not number it checks using Date object if it is timestamp, the last option is that the value is of type string.

if (!isNaN(Date.parse(inputObject[key]))) {
    let date = Date.parse(inputObject[key]);
    let newDate = new Date(date);
    array.push(key, newDate.toISOString(), "TIMESTAMPZ");
    continue;
} else {
    array.push(key, inputObject[key], "VARCHAR(255)");
    continue;
}

Based on the recognized type, SQL datatype is pushed into array next to key and value, these elements of array are later used for dynamic SQL query generation.