atomic-mssql

MSSQL Module for Brobridge Atomic (Compatible with Node-RED).

Overview

@brobridge/atomic-mssql is a Microsoft SQL Server database module designed specifically for Atomic, fully compatible with the Node-RED development environment. It provides three main nodes:

• MSSQL Connection: Connection configuration and pool manager
• MSSQL Execute: Query execution
• MSSQL Control: Control flow node for streaming data

Installation

npm install @brobridge/atomic-mssql

Features

• Efficient connection pool support with configurable options
• Static or dynamic SQL execution via msg.query
• Delivery options:
  • direct: returns all results at once
  • streaming: sends rows in batches
• SQL Playground UI to test queries interactively
• Supports continue and break controls for streaming sessions

MSSQL Connection Node

MSSQL Connection

Manages database configuration and connection pool options.

• Required fields: server, port, username, password, database
• Configurable: appName, timeouts, reconnect interval
• Pool options: min, max, idleTimeoutMillis

Defines a reusable SQL Server connection. | Setting | Purpose | |---------|---------| | server, port, database | Target SQL Server instance | | username, password | SQL authentication (or leave blank for other auth types) | | appName | Appears in SQL Server logs | | encrypt | Enable TLS; must be true for trustServerCertificate to take effect | | trustServerCertificate | If true, skips certificate validation (dev / self‑signed) | | connectionTimeout, requestTimeout | Time‑outs in ms | | connectionRetryInterval | Re‑connect delay in ms | | Pool – min, max, idleTimeoutMillis | Connection‑pool tuning |

MSSQL Execute

Executes SQL queries with flexible query sources and delivery modes.

• Query Source:
  • auto: Uses embedded SQL command
  • dynamic: Uses msg.query as input
• Delivery Mode:
  • direct: Entire result set is returned
  • streaming: Sends rows in batches with session control
• Output:
  • Output to msg.payload or custom property
• SQL Playground for testing queries interactively in the editor

MSSQL Control

Controls flow when using streaming mode in MSSQL Execute node.

• continue: Resume the session
• break: Cancel the session
• Supports targeting specific Execute nodes

SQL Playground

The MSSQL Execute node includes a built-in SQL Playground, allowing you to write and run SQL queries interactively.

Example Flow: Full Data Type Coverage

The included example.json demonstrates a complete flow that includes:

1. Simulating input using an inject node
2. Executing CREATE, INSERT, SELECT, and DROP operations with the MSSQL Execute node
3. In streaming mode, use the MSSQL Control node to request the next batch.
4. Observing results with a debug node

Refer to the example.json file to load and explore the complete flow.

[{"id":"84d53f75e7029a9e","type":"MSSQL Execute","z":"dda5777ca70ca457","name":"","connection":"9ecc17c92e0eb62c","querySource":"auto","command":"INSERT INTO Users (Name, Email) VALUES ('John', N'[email protected]');\n","deliveryMethod":"direct","maxbatchrecords":"100","outputPropType":"msg","outputProp":"payload","x":360,"y":440,"wires":[["28b5c876aa683789"]]},{"id":"7175de3d245e8f73","type":"MSSQL Execute","z":"dda5777ca70ca457","name":"","connection":"9ecc17c92e0eb62c","querySource":"auto","command":"SELECT * FROM Users;","deliveryMethod":"direct","maxbatchrecords":"100","outputPropType":"msg","outputProp":"payload","x":360,"y":600,"wires":[["235be49af2ffb72b"]]},{"id":"70eba4faf4898e40","type":"inject","z":"dda5777ca70ca457","name":"","props":[],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":170,"y":600,"wires":[["7175de3d245e8f73"]]},{"id":"235be49af2ffb72b","type":"debug","z":"dda5777ca70ca457","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":590,"y":600,"wires":[]},{"id":"2e15ec855de77891","type":"inject","z":"dda5777ca70ca457","name":"","props":[],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":170,"y":440,"wires":[["84d53f75e7029a9e"]]},{"id":"28b5c876aa683789","type":"debug","z":"dda5777ca70ca457","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":590,"y":440,"wires":[]},{"id":"807710a20af36f9c","type":"comment","z":"dda5777ca70ca457","name":"Insert","info":"","x":170,"y":400,"wires":[]},{"id":"e2f369d6d32bedae","type":"comment","z":"dda5777ca70ca457","name":"Query","info":"","x":170,"y":560,"wires":[]},{"id":"ceb757652cc01d17","type":"MSSQL Execute","z":"dda5777ca70ca457","name":"","connection":"9ecc17c92e0eb62c","querySource":"auto","command":"CREATE TABLE Users (\n    ID INT IDENTITY(1,1) PRIMARY KEY,\n    Name NVARCHAR(50),\n    Email NVARCHAR(100),\n    CreatedAt DATETIME DEFAULT GETDATE()\n);","deliveryMethod":"direct","maxbatchrecords":"100","outputPropType":"msg","outputProp":"payload","x":360,"y":280,"wires":[["82b010587df95c29"]]},{"id":"b7362f2eeaf88863","type":"inject","z":"dda5777ca70ca457","name":"","props":[],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":170,"y":280,"wires":[["ceb757652cc01d17"]]},{"id":"82b010587df95c29","type":"debug","z":"dda5777ca70ca457","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":590,"y":280,"wires":[]},{"id":"c292b795b50ebaa5","type":"comment","z":"dda5777ca70ca457","name":"Create table","info":"","x":190,"y":240,"wires":[]},{"id":"ebefbbe0a1300cfc","type":"MSSQL Execute","z":"dda5777ca70ca457","name":"Streaming Out","connection":"9ecc17c92e0eb62c","querySource":"auto","command":"SELECT * FROM Users;","deliveryMethod":"streaming","maxbatchrecords":"2","outputPropType":"msg","outputProp":"payload","x":360,"y":760,"wires":[["68eec76c44a4cf13","08ec6ac1787f2122"]]},{"id":"a8374b6b5b7289ba","type":"inject","z":"dda5777ca70ca457","name":"","props":[],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":170,"y":760,"wires":[["ebefbbe0a1300cfc"]]},{"id":"68eec76c44a4cf13","type":"debug","z":"dda5777ca70ca457","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":590,"y":760,"wires":[]},{"id":"1242a4fd3b92d75b","type":"comment","z":"dda5777ca70ca457","name":"Query with streaming","info":"","x":220,"y":720,"wires":[]},{"id":"08ec6ac1787f2122","type":"MSSQL Control","z":"dda5777ca70ca457","name":"","targetNodeId":"","selectMode":"specific","selectedNodes":["ebefbbe0a1300cfc"],"action":"continue","x":600,"y":800,"wires":[]},{"id":"72f94ba48e088df0","type":"MSSQL Execute","z":"dda5777ca70ca457","name":"","connection":"9ecc17c92e0eb62c","querySource":"auto","command":"DROP TABLE Users;","deliveryMethod":"direct","maxbatchrecords":"100","outputPropType":"msg","outputProp":"payload","x":360,"y":920,"wires":[["c9f381bfa5cc178f"]]},{"id":"248ef8b4fea52e8b","type":"inject","z":"dda5777ca70ca457","name":"","props":[],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","x":170,"y":920,"wires":[["72f94ba48e088df0"]]},{"id":"c9f381bfa5cc178f","type":"debug","z":"dda5777ca70ca457","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":590,"y":920,"wires":[]},{"id":"64ff1fcb66278a32","type":"comment","z":"dda5777ca70ca457","name":"Drop table","info":"","x":180,"y":880,"wires":[]},{"id":"9ecc17c92e0eb62c","type":"MSSQL Connection","name":"","server":"127.0.0.1","port":"1433","authType":"default","domain":"","database":"example","appName":"atomic","encryption":true,"trustServerCertificate":true,"poolMin":1,"poolMax":10,"poolIdleTimeoutMillis":30000,"requestTimeout":15000,"connectionTimeout":15000,"connectionRetryInterval":3000}]

Commercial Support

Brobridge provides the customer service which contains comprehensive technical and commercial support for the module.

License

This module is licensed under the Apache License.

Authors

Copyright(c) 2025 Fred Chien <[email protected]>