Skip to main content

​
Data Storage Integration

Data storage routes enable automatic persistence of MQTT messages to databases and files. Store sensor data for analytics, create historical archives, aggregate logs, and build time-series datasets—all configured through LoT syntax.
Data storage routes use a common EVENT-based configuration pattern. Learn it once, and you can configure any supported database.

​
Supported Databases

PostgreSQL

Open-source relational database for structured data storage.

MySQL

Popular relational database with wide ecosystem support.

MariaDB

MySQL-compatible database with enhanced features.

SQL Server

Microsoft SQL Server for enterprise environments.

MongoDB

Document database for flexible JSON storage.

OpenSearch

Real-time analytics, search, and visualization.

CrateDB

Distributed SQL optimized for time-series data.

Snowflake

Cloud data warehouse for large-scale analytics.

File Storage

CSV and JSON file storage for simple persistence.

​
Database Comparison

DatabaseTypeBest ForQuery Format
PostgreSQLRelationalStructured data, complex queriesSQL
MySQLRelationalWeb applications, general purposeSQL
MariaDBRelationalMySQL compatibility with extrasSQL
SQL ServerRelationalEnterprise, Windows environmentsSQL
MongoDBDocumentFlexible schemas, JSON dataCLEAN
OpenSearchSearch/AnalyticsFull-text search, dashboardsCLEAN
CrateDBTime-SeriesHigh-volume sensor dataSQL
SnowflakeCloud WarehouseLarge-scale analyticsSQL
File StorageFileSimple logging, backupsCLEAN

​
Common Architecture

All data storage routes share a common architecture with two key components:

​
1. Connection Configuration

Each database has specific connection parameters: 
ADD <DATABASE>_CONFIG
    WITH SERVER "database.example.com"
    WITH PORT '5432'
    WITH DATABASE "iot_data"
    WITH USERNAME "user"
    WITH PASSWORD "password"

​
2. EVENT Definitions

EVENTs define what data to store and where: 
ADD EVENT StoreSensorData
    WITH SOURCE_TOPIC "sensors/+/reading"
    WITH DESTINATION_TOPIC "db/responses"
    WITH QUERY "INSERT INTO readings (ts, sensor, value) VALUES ('{timestamp}', '{sensor_id}', '{value.json}')"

​
Common Query Placeholders

These placeholders work across all database routes:
PlaceholderDescription
{value}Full MQTT payload as string
{value.json}Parsed JSON payload
{value.json.field}Specific field from JSON
{timestamp}Message timestamp
{source_topic}Original MQTT topic
{field}Field extracted from topic path

​
Extracting Fields from Topics

For topic sensors/temp001/reading, use these placeholders:
PlaceholderValue
{source_topic}sensors/temp001/reading
{sensor_id}temp001 (position 2 in topic)

​
Query Formats

​
SQL Format

For relational databases (PostgreSQL, MySQL, MariaDB, SQL Server, CrateDB, Snowflake): 
WITH QUERY "INSERT INTO sensor_readings (timestamp, sensor_id, value) VALUES ('{timestamp}', '{sensor_id}', '{value.json}')"

​
CLEAN Format

For document databases and search engines (MongoDB, OpenSearch, File Storage): 
WITH QUERY "CLEAN:{collection: readings, document: {sensor_id: {sensor_id}, value: {value.json}, timestamp: {timestamp}}}"
The CLEAN format provides simplified JSON syntax without requiring proper escaping.

​
Common EVENT Parameters

​
SOURCE_TOPIC
string
required
MQTT topic pattern that triggers the storage operation. Supports wildcards.
​
DESTINATION_TOPIC
string
MQTT topic to publish operation status/results.
​
QUERY
string
required
SQL statement or CLEAN format query with placeholders.

​
Best Practices

Always enable SSL for database connections:
WITH USE_SSL "true"
Create dedicated database users with minimal permissions for the route.
Implement data retention policies:
  • Index lifecycle management
  • Time-based partitioning
  • Automatic cleanup jobs
Use LoT Models to structure data before storage:
DEFINE MODEL SensorReading COLLAPSED
    ADD STRING "sensor_id"
    ADD DOUBLE "value"
    ADD STRING "timestamp"
For high-throughput scenarios, configure connection pools where supported.

​
Next Steps

Choose the database that matches your requirements:

PostgreSQL

Start with the most popular open-source database.

MongoDB

Store flexible JSON documents.