How to Use Node-RED to Store API Data into SQL Server

Introduction

Node-RED is an open-source, low-code programming tool developed by IBM. This is licensed under the Apache License 2.0, a permissive open-source license. If you want to know more about software licenses then visit our earlier post Software Licenses: Open Source Vs Proprietary.
In this tutorial we will use Node-RED for API integration with SQL Server database.

Use cases of Node-Red

·        IoT dashboards (collecting sensor data into a database).
·        API integration (connecting REST APIs to SQL Server/NoSQL).
·        Automation workflows (like ETL pipelines, smart home automation).
·        Data processing pipelines (cleaning & transforming data before storage).

Let's begin

First, we will do the prerequisite setup by installing only required softwires before entering into the problem statement example.
In this example we will use any readily available API endpoint which should return sample sensor data in JSON format. We will create Node-Red flow to read the API response, process the data and save into SQL Server database table. Finally, I will attach the flow JSON file for your reference.
Let’s make a start

Prerequisites

·        Install Node.js if not installed already
·        Install Node-RED
·        Run SQL Server
·        Install node MSSQL
Install Node.js
Go to Node.js official site https://nodejs.org/ and download long term support version of Node.js. We will download and install v22.19.0(TLS) for windows

On successful installation use the following command to check the installed 
version of Node.js and npm -v

Install Node-RED
Run the following command to install node-red. The -g flag means installation is global, Node-RED will be installed in your system-wide npm global folder, not in the current directory. Avoid permission issues with --unsafe-perm 

Now install Node-Red node MSSQL by the command npm install node-red-node-mssql
Start the Node-Red
In command prompt type node-red and hit enter to start the Node-Red server. Once the server is running browse the url to lunch the canvas as shown below

I hope we are all set with prerequisites, let’s move for creating the Node-red flow step by step.
STEP:1 Create Inject Node
Inject node the first node in canvas. The Inject node lets you manually or automatically send a message into a flow. Think of it as a trigger. It’s often used to start a flow for testing or to send periodic data. Drag the inject node and keep in canvas as shown in diagram and double click to edit. Keep a name as per your choice. The name I have given is Start.

STEP:2 Create HTTP Request Node
The HTTP request node is used to make HTTP(S) calls (GET, POST, PUT, DELETE, etc.) from your Node-RED flow.It acts like a built-in API client (similar to Postman or curl).You can use it to consume REST APIs or send data to web services.Drag an HTTP Request node, connect it with inject node. Double click to configure.
Method: Get
Url: https://api.thingspeak.com/channels/9/feeds.json?results=5
Returns: a parsed JSON object

Drag a Debug node and connect with HTTP Request node to text the flow so far that we are receiving the API response. Deploy the code and debug to see the data as shown below. If you see data then so far the configuration is correct.

STEP:2 Create Table and Procedure in Database
Create a database or use if any existing database for this example.
Create the following table to store the sensor data received from API endpoint

  Create table SensorData(
  entityId INT,
  light float,
  outsideTemp float,
  timestamp datetime
  ); 
  --Create a procedure to insert data into this table Sensordata as following
  CREATE PROCEDURE sp_InsertSensorData
  @entityId INT,
  @light float,
  @outsideTemp float,
  @timestamp datetime
  AS
  BEGIN
      INSERT INTO SensorData(entityId,light,outsideTemp,timestamp)
      VALUES(@entityId,@light,@outsideTemp,@timestamp);
  END;
  

STEP:3 Function Node to prepare SQL query

The Function node allow us write custom JavaScript code to process, transform, or generate messages in a Node-RED flow.

It is the logic/brain of our workflow. We will create this node to form the SQL query out of API response data to insert into SQL Server database table.
Delete the debug node and drag the function node to the canvas, connect it to the HTTP Request node. Double click to configure.
Name: Prepare SQL Script
Function Code:

  let feeds = msg.payload.feeds || [];
if (feeds.length === 0) {
    node.error("No feeds found in API response", msg);
    return null;
}
//Extract the first feed from the API response
let feed = msg.payload.feeds[0];

//Construct the SQL query for the store procedure
msg.topic =`
    EXEC dbo.sp_InsertSensorData
        @entryId=${feed.entry_id},
        @light=${parseInt(feed.field1)},
        @outsideTemp=${parseInt(feed.field2)},
        @timestamp='${feed.created_at}'
        `;
        return msg;

  

Attach a debug node to test the flow, if all are working fine then result should appear like this

STEP:4 MSSQL Node to execute the procedure & save data into database table

The MSSQL node in Node-RED lets you connect to a Microsoft SQL Server database and run SQL queries or stored procedures.
Drag an MSSQL node, connect it with function node. Double click to configure.
Enter your SQL Server credentials.

Deploy the flow and click on Start button. If your SQL Server connection is active then new record will be successfully inserted into the table.
You can download and import my ready-made Node-RED flow from the attached flow.json

Conclusion:

In this post, we explored how to build an end-to-end data pipeline in Node-RED to consume sensor data from an external API and store it into a SQL Server database. Along the way, we learned about:
Installing and configuring Node-RED.
Using the Inject, HTTP Request, Function, and MSSQL nodes.
Executing stored procedures from Node-RED and verifying data insertion in SQL Server.
 
With this flow, you now have a low-code integration between IoT-style APIs and enterprise-grade databases. Node-RED’s flexibility means you can extend this further — add dashboards, trigger alerts, or connect additional APIs with minimal effort. 
Whether you are working on IoT projects, automation workflows, or enterprise integrations, Node-RED provides a powerful and user-friendly way to connect data from anywhere to anywhere.