SQL Server Route

SQL Server Overview

The SQLSERVER route stores MQTT messages in Microsoft SQL Server databases. It supports Windows and SQL authentication, SSL connections, and enterprise features.

SQL Server is ideal for enterprise environments, especially those integrated with Microsoft ecosystems. It offers advanced features like Always On availability and native JSON support.

Basic Syntax

DEFINE ROUTE SensorDB WITH TYPE SQLSERVER
    ADD SQL_CONFIG
        WITH SERVER "sqlserver.example.com"
        WITH PORT '1433'
        WITH DATABASE "IoTData"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreSensorReading
        WITH SOURCE_TOPIC "sensors/+/reading"
        WITH QUERY "INSERT INTO Readings (RecordedAt, SensorId, Value) VALUES (GETDATE(), '{sensor_id}', '{value.json}')"

Connection Configuration

SQL_CONFIG Parameters

SERVER

string

required

SQL Server hostname or IP address. Can include instance name (e.g., server\instance).

DATABASE

string

required

Target database name.

USERNAME

string

required

SQL Server username.

PASSWORD

string

required

SQL Server password.

Optional Settings

PORT

integer

SQL Server port. Default: 1433.

USE_SSL

boolean

Enable SSL connection. Default: false.

TRUST_SERVER_CERTIFICATE

boolean

Trust server certificate without validation. Default: false.

Never hardcode credentials in production. Use environment variables and encrypted secrets to keep sensitive values out of your route definitions:

WITH SERVER GET ENV "DB_HOST"
WITH USERNAME GET ENV "DB_USER"
WITH PASSWORD GET SECRET "DB_PASSWORD"

See Environment Variables & Secrets for setup and usage.

Writing Data

Store sensor data in SQL Server:

DEFINE ROUTE SensorStorage WITH TYPE SQLSERVER
    ADD SQL_CONFIG
        WITH SERVER "sqlserver.example.com"
        WITH PORT '1433'
        WITH DATABASE "IoTData"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreReading
        WITH SOURCE_TOPIC "sensors/+/reading"
        WITH QUERY "INSERT INTO SensorReadings (Timestamp, SensorId, Value) VALUES (GETDATE(), '{sensor_id}', '{value.json}')"

Secure connection with trusted certificate:

DEFINE ROUTE SecureStorage WITH TYPE SQLSERVER
    ADD SQL_CONFIG
        WITH SERVER "sqlserver.cloud.com"
        WITH PORT '1433'
        WITH DATABASE "Production"
        WITH USERNAME "app_user"
        WITH PASSWORD "secure_password"
        WITH USE_SSL "true"
        WITH TRUST_SERVER_CERTIFICATE "true"
    ADD EVENT StoreEvent
        WITH SOURCE_TOPIC "events/#"
        WITH QUERY "INSERT INTO Events (Timestamp, Topic, Payload) VALUES (GETDATE(), '{source_topic}', '{value.json}')"

Connect to a named SQL Server instance:

DEFINE ROUTE InstanceStorage WITH TYPE SQLSERVER
    ADD SQL_CONFIG
        WITH SERVER "server.example.com\\MSSQLSERVER"
        WITH DATABASE "IoTData"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT StoreData
        WITH SOURCE_TOPIC "data/#"
        WITH QUERY "INSERT INTO DataLog (Timestamp, Topic, Data) VALUES (GETDATE(), '{source_topic}', '{value.json}')"

SQL Server uses the MERGE statement for upsert logic — insert a row if it doesn’t exist, update it if it does:

DEFINE ROUTE DeviceRegistry WITH TYPE SQLSERVER
    ADD SQL_CONFIG
        WITH SERVER "sqlserver.example.com"
        WITH PORT '1433'
        WITH DATABASE "IoTData"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT RegisterDevice
        WITH SOURCE_TOPIC "devices/+/registered"
        WITH QUERY "MERGE Devices AS target USING (SELECT '{value.json.device_id}' AS device_id, '{value.json.name}' AS name, '{value.json.area}' AS area) AS source ON (target.DeviceId = source.device_id) WHEN MATCHED THEN UPDATE SET Name = source.name, Area = source.area, UpdatedAt = GETDATE() WHEN NOT MATCHED THEN INSERT (DeviceId, Name, Area, Active, UpdatedAt) VALUES (source.device_id, source.name, source.area, 1, GETDATE());"

Modify existing records when a status change arrives:

DEFINE ROUTE DeviceRegistry WITH TYPE SQLSERVER
    ADD SQL_CONFIG
        WITH SERVER "sqlserver.example.com"
        WITH PORT '1433'
        WITH DATABASE "IoTData"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT UpdateDeviceStatus
        WITH SOURCE_TOPIC "devices/+/status"
        WITH QUERY "UPDATE Devices SET CurrentStatus = '{value.json.status}', LastSeen = GETDATE() WHERE DeviceId = '{value.json.device_id}'"

Remove records on a decommission or cleanup event:

DEFINE ROUTE DeviceRegistry WITH TYPE SQLSERVER
    ADD SQL_CONFIG
        WITH SERVER "sqlserver.example.com"
        WITH PORT '1433'
        WITH DATABASE "IoTData"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT DecommissionDevice
        WITH SOURCE_TOPIC "devices/+/decommissioned"
        WITH QUERY "DELETE FROM Devices WHERE DeviceId = '{value.json.device_id}' AND Active = 0"

Alternative: STORE IN with Models — Instead of writing EVENT queries, you can bind a model directly to this route. Every PUBLISH MODEL call automatically inserts a row — no query needed:

DEFINE MODEL SensorReading
    ADD STRING "sensor_id"
    ADD DOUBLE "value"
    STORE IN "SensorStorage"
        WITH TABLE "SensorReadings"

See Data Storage Overview for the full STORE IN workflow.

Reading Data

EVENTs also support SELECT queries to read data back from the database. Publish a message to the event’s SOURCE_TOPIC, and the query result is published to DESTINATION_TOPIC:

DEFINE ROUTE SensorDB WITH TYPE SQLSERVER
    ADD SQL_CONFIG
        WITH SERVER "sqlserver.example.com"
        WITH PORT '1433'
        WITH DATABASE "IoTData"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT GetLatestReading
        WITH SOURCE_TOPIC "db/query/latest"
        WITH DESTINATION_TOPIC "db/result/latest"
        WITH QUERY "SELECT TOP 1 SensorId, Value FROM Readings WHERE SensorId = '{payload}' ORDER BY RecordedAt DESC"

To trigger this query, publish the sensor ID to the source topic:

Topic:   db/query/latest
Payload: temp001

The query result is published to db/result/latest, where your actions or external clients can consume it.

Timed Polling

EVENTs can also run SELECT queries on a fixed schedule using WITH EVERY:

DEFINE ROUTE DeviceRegistry WITH TYPE SQLSERVER
    ADD SQL_CONFIG
        WITH SERVER "sqlserver.example.com"
        WITH PORT '1433'
        WITH DATABASE "IoTData"
        WITH USERNAME "iot_user"
        WITH PASSWORD "secure_password"
    ADD EVENT PollActiveDevices
        WITH EVERY 10 SECONDS
        WITH DESTINATION_TOPIC "dashboard/devices/active"
        WITH QUERY "SELECT DeviceId, Name, CurrentStatus, LastSeen FROM Devices WHERE Active = 1 ORDER BY Name"

Table Schema Examples

-- Basic sensor readings
CREATE TABLE SensorReadings (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Timestamp DATETIME2 DEFAULT GETDATE(),
    SensorId NVARCHAR(50),
    Value FLOAT
);

-- JSON storage (SQL Server 2016+)
CREATE TABLE Events (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Timestamp DATETIME2 DEFAULT GETDATE(),
    Topic NVARCHAR(255),
    Payload NVARCHAR(MAX)
);

Troubleshooting

Connection Refused

Verify SERVER and PORT are correct
Check SQL Server is running and accepting TCP connections
Enable TCP/IP in SQL Server Configuration Manager
Verify firewall allows connections on port 1433

Authentication Failed

Verify USERNAME and PASSWORD are correct
Ensure SQL Server authentication is enabled (mixed mode)
Check user has permissions on the DATABASE

SSL/Certificate Errors

Set TRUST_SERVER_CERTIFICATE “true” for self-signed certs
Verify SSL is configured on SQL Server

Next Steps

Data Storage Routes Overview

Compare all storage options.

MongoDB Route

Configure MongoDB document storage.

Quick Start

Coreflux HUB

MQTT Broker

AI

LoT Language

VS Code - LoT Notebooks

Actions

Routes

Models

Rules

SQL Server Overview

Basic Syntax

Connection Configuration

SQL_CONFIG Parameters

Writing Data

Reading Data

Timed Polling

Table Schema Examples

Troubleshooting

Next Steps

Data Storage Routes Overview

MongoDB Route

Quick Start

Coreflux HUB

MQTT Broker

AI

LoT Language

VS Code - LoT Notebooks

Actions

Routes

Models

Rules

​SQL Server Overview

​Basic Syntax

​Connection Configuration

​SQL_CONFIG Parameters

​Writing Data

​Reading Data

​Timed Polling

​Table Schema Examples

​Troubleshooting

​Next Steps

Data Storage Routes Overview

MongoDB Route

SQL Server Overview

Basic Syntax

Connection Configuration

SQL_CONFIG Parameters

Writing Data

Reading Data

Timed Polling

Table Schema Examples

Troubleshooting

Next Steps