Generate Dynamic SQL Select Query subflow

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

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

IMPORTANT: This subflow requires specific input array, which can be generated using the SQL Recognize Type Subflow, it is best practice to use this subflow in combination with *SQL Recognize Type Subflow".

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. Choosing correct value for this environmental variable is cruical for correct implementation, as if there is an id value and environmental variable isnt set to true the subflow wont work correctly.

This subflow requires incomming array of values to construct SQL query for table creation, this array can be generated from JS object using the SQLrecognizeType subflow, to read more about this subflow follow this link: …

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 implements simple for-loop which goes over input array (which has predefined structure, if you are using other structure you need to implement different logic). Inside for loop elements of array are concatenated in a specific way which follows SQL standard. startQuery variable is string defined in a way to create SQL table with the same name if it doesnt already exist, meaning this subflow can be used before any other SELECT statement subflow and it shall not cause any errors.

Query should be defined inside the msg.query property for later use.

This node is a part of postgresql-package. It offers easy integration with the PostgreSQL database, there are few configuration options user needs to define before use like Username and password, along with database name. To pass query inside this node user should pass query inside the msg.query property of an object.