> ## Documentation Index
> Fetch the complete documentation index at: https://docs.coreflux.org/llms.txt
> Use this file to discover all available pages before exploring further.

# SQL Server Route

> Store MQTT data in Microsoft SQL Server with automatic query execution and enterprise features

## 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.

> [!NOTE]
>
> 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

```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
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.

> [!WARNING]
>
> **Never hardcode credentials in production.** Use environment variables and encrypted secrets to keep sensitive values out of your route definitions:
>
> ```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
> WITH SERVER GET ENV "DB_HOST"
> WITH USERNAME GET ENV "DB_USER"
> WITH PASSWORD GET SECRET "DB_PASSWORD"
> ```
>
> See [Environment Variables & Secrets](/v2.14/mqtt-broker/secrets-and-env) for setup and usage.

***

## Writing Data

**Basic Storage**

Store sensor data in SQL Server:

```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
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}')"
```

**With SSL**

Secure connection with trusted certificate:

```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
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}')"
```

**Named Instance**

Connect to a named SQL Server instance:

```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
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}')"
```

**UPSERT**

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

```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
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());"
```

**UPDATE**

Modify existing records when a status change arrives:

```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
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}'"
```

**DELETE**

Remove records on a decommission or cleanup event:

```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
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"
```

> [!NOTE]
>
> **Alternative: STORE IN with Models** — Instead of writing EVENT queries, you can bind a [model](/v2.14/lot-language/models/overview) directly to this route. Every `PUBLISH MODEL` call automatically inserts a row — no query needed:
>
> ```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
> DEFINE MODEL SensorReading
>     ADD STRING "sensor_id"
>     ADD DOUBLE "value"
>     STORE IN "SensorStorage"
>         WITH TABLE "SensorReadings"
> ```
>
> See [Data Storage Overview](./overview#with-models-store-in) 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`:

```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
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`:

```lot wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
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

```sql wrap theme={"theme":"css-variables","languages":{"custom":["/languages/lot.json"]}}
-- 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](./overview)**
>
> Compare all storage options.

> **[MongoDB Route](./mongodb)**
>
> Configure MongoDB document storage.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.