Easy Example Guide to Using Node-Red with SQL Server|MSSQL

Node-Red allows you to point to different data points. If you are using SQL databases, Microsoft SQL Server (MSSQL) is a great idea. MSSQL works perfectly with Node-Red. This way, you will have a running MSSQL server, then allow Node-Red to connect to it. Node-Red will then create flows to connect and interact with MSSQL TO READ, VIEW, or INSERT data,

In this comprehensive example tutorial, you will learn all you need to get Node-Red running with Microsoft SQL Server. You will learn:

• How to install an MSSQL server to a running Node-Red server
• What you need to connect SQL Server with Node-Red.
• Creating Node-Red flow to create database tables on SQL Server
• How to execute MSSQL queries to INSERT and SELECT data with Node-Red
• View data from MSSQL within Node-Red

Ready? Dive in and use Node-Red with SQL Server|MSSQL like a pro.

Related: How to Connect Node-Red, Save|Select|Insert Data to MySQL Example Tutorial

Getting SQL Server|MSSQL Ready

This guide requires you to have a running Node-Red server. Based on your machine ensure Node-Red is installed and up and running.

Related: Install Node-RED on Ubuntu

On top of that you are using MSSQL as your database. Therefore, Microsoft SQL Server Must be running. If you don’t like manually running your MSSQL database, check the following guide and set up one using Docker:

Related:

• Run Microsoft SQL Server|MSSQL Express on Docker
• Connect SQL Server Management Studio|SSMS to Docker Container

Integrating MSSQL with Node-Red

Once you have these two servers running, you can now proceed and get Node-Red ready to ass the Microsoft SQL server as follows:

• On Node-Red, navigate to Manage Palette as follows:

• Here, Node-Red will need the correct plugin to access MSSQL. Go to install and search MSSQL and install the following specific MSSQL Node-Red plugin:

• This will create the Node for Node-Red to connect to a Microsoft MSSQL Database:

Either of the above nodes will be a file.

• At the same time, navigate to the Storage section and you should now have the Node-Red MSSQL node ready:

Connecting Microsoft MS SQL with Node-Red

At this point, you need to add MSSQL Node to Node-Red flow:

Click this Node and open to Edit the connection details. Use the Edit Icon:

You should add your Microsoft SQL Server Connection details to Node-Red as follows:

Note that the Serve name might change based on your SQL Server Authentication:

At the same time, the database you added in the above connection details should be available in your MS SQL. Otherwise, Node-Red will fail to create a connection:

How to Create a MSSQL Database Table using Node-Red

In your MSSQL, you can use an SQL statement to create a table and its attributes. This process will be manual.

But because we are on Node-RED, let’s use the above-created connection and have a working MSSQL table created through Node-RED.

• First, add an Inject Node to the flow.
• Add an Inject Node
• Add a Function Node. This function Node will contain the SQL query that Node-RED will send to the SQL Server.

With Node-RED, The MSSQL node uses the msg.payload value as the query. This means you will edit the function Node with msg.payload containing the CREATE TABLE query as follows:

msg.payload = 'CREATE TABLE iot_info (id INT PRIMARY KEY IDENTITY(1,1), device_name VARCHAR(50), sensor_type VARCHAR(50), sensor_value FLOAT, timestamp DATETIME);'

Now, you have Nodes Read. Connect them as follows:

To apply this query, first Deploy your flow. Click the Inject Node as follows:

You should now have your MSSQL table ready:

How to Insert Data to MS SQL with Node-RED

The same process as the above applies here. Only that, in this step you will use a query to Insert Data as such.

Follow these steps:

• Create an Inject Node
• Add a Function Node with the following query:

msg.payload = "INSERT INTO iot_info (device_name, sensor_type, sensor_value, timestamp) VALUES ('Device001','Temperature', RAND() * 100, GETDATE());"

The best thing about using the Function Node is that it gives you the ability to use functions such as RAND() to generate random values.

Connect these nodes as follows:

Deploy and inject timestamp:

This should add your data to Microsoft SQL Server. Click inject timestamp to generate several random data and check them on the database as follows:

Select Data from MSSQL to Node-RED

Based on the above steps, let’s create a query to select the added data and show them in Node-RED.

• You need an Inject Node.
• Add your function Node with a SELECT query as follows:

The Complete Flow should look as follows:

Deploy and inject timestamp. Check the Debug section and you should have the selected data in array objects as such:

Conclusion

This guide taught you how to use MSSQL with Node-RED. You have learned:

• How to install an MSSQL server to a running Node-Red server
• What you need to connect SQL Server with Node-Red.
• Creating Node-Red flow to create database tables on SQL Server
• How to execute MSSQL queries to INSERT and SELECT data with Node-Red
• View data from MSSQL within Node-Red