Dynamic SQL Queries example flow
Introduction
This flow is a part of internal flows collection. Internal flows are used for processing and storing data inside Node-RED environment and Sandbox product.
This example and all subflows used in this example can be used with various database services running on Sandbox, for more information on how to install and setup databases on Sandbox read this documentation:
TODO: Write documentation for software-center
Important: for more clarity, comments have been added throughout this example flow. It is recommended to read these comments while reviewing the documentation to gain a better understanding of each component’s role and functionality.
Prerequisites
This flow along with other dependencies and flows is available to user in the following package:
@Senlab/node-red-sql-senlab
@Senlab/node-red-postgresql
To learn more about Verdaccio and how to install packages, please refer to this documentation: Verdaccio
The PostgreSQL service must be active and operational on Sandbox for these queries to function. Additionally, other databases can also be integrated with this Node-RED package if the relevant Node-RED package for those databases is installed.
Initializing flow with Inject Node
We initiate our flow with the Inject Node, which is a basic node and already preinstalled inside Node-RED. It can be found under the common tab.
The Inject Node can function as a button or signal generator. Users need to specify which property and value the node will inject into the flow. In this particular setup, the specific configuration of the Inject Node isn’t critical, as its main purpose is to activate other nodes in the flow. In the configuration panel, users can choose whether the Inject Node should inject the value just once or repeatedly at a set interval.
For this example Inject Node is intended to be used only when user chooses, this is why no other configuration needs to be set.
Setting up Function Node for Creating JavaScript Object
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 example will generate dynamic SQL queries based on incomming JavaScript Object, for this reason Function Node is used to set msg.payload property to test object so we can observe how query is generated.
This is the object:
msg.payload = {
"variable_test": 1,
"variabletesting": 2
}SQL Recognize Type Subflow
This subflow is designed to identify the types and properties of incoming JavaScript objects. The output of this subflow is an array that organizes elements in the following order: property key, value, and type. This sequence repeats for each property in the object.
The subflow can be configured via an environmental variable named addID, which appends a SERIAL ID at the beginning of the array. This feature is particularly beneficial in SQL databases, where it facilitates easier searches.
The output array is cruical for the operation of other subflows in this collection.
To learn more about this subflow, please refer to the documentation: Recognize Type Subflow
SQL generate table query
This subflow requires an array from a previous subflow to function correctly. It generates a dynamic SQL `CREATE TABLE' query based on the input array and checks if the table already exists to ensure the subflow can be used without risking errors.
The table name can be customized through an environmental variable, and all other parameters, such as variable names and types, are provided within the input array.
This subflow only generates the query, execution of the query must be handled by another node.
To learn more about this subflow, please refer to the documentation: SQL generate TABLE
PostgreSQL Node
This node is part of the node-red-contrib-postgresql package, which is a dependency included in this project’s package. It should be installed along with the parent package.
Queries can be passed to this node for execution via the msg.query property.
Initial setup is straightforward: double-click the node to open the configuration window, then click the pencil icon next to the Server field to enter additional settings. Here, you need to specify the Host (labeled as the service name, typically `postgres'), the port (container’s port), and the database. The default database is `postgres', but users can create and select their own. It is also necessary to configure security settings, including the username and password for a specific database user.
Once saved, this configuration should be stored globally, meaning any instance of this node will retain the same credentials for subsequent uses.
SQL generate INSERT query
Like the SQL `generate table' query subflow, which requires an array from the `Recognize Type' subflow, this subflow also depends on the same array. An `INSERT' query is generated using the data from this array, and the table name is sourced from the preceding subflow, where it was passed in the msg.tableName property.
This subflow is configurable through an environmental variable named addID. This is a critical setting, if not configured correctly, the subflow will not function.
To learn more about this subflow, please refer to the documentation: SQL generate SELECT query