The MARIADB route stores MQTT messages in MariaDB databases. MariaDB is a MySQL-compatible database with enhanced features and performance optimizations.
MariaDB offers drop-in MySQL compatibility with additional features like improved replication and storage engines. Use the same configuration as MySQL with the MARIADB route type.
DEFINE ROUTE SensorDB WITH TYPE MARIADB ADD SQL_CONFIG WITH SERVER "mariadb.example.com" WITH PORT '3306' WITH DATABASE "iot_data" WITH USERNAME "iot_user" WITH PASSWORD "secure_password" ADD EVENT StoreSensorReading WITH SOURCE_TOPIC "sensors/+/reading" WITH QUERY "INSERT INTO readings (ts, sensor_id, value) VALUES (NOW(), '{sensor_id}', '{value.json}')"
DEFINE ROUTE SensorStorage WITH TYPE MARIADB ADD SQL_CONFIG WITH SERVER "mariadb.example.com" WITH PORT '3306' WITH DATABASE "iot_data" WITH USERNAME "iot_user" WITH PASSWORD "secure_password" ADD EVENT StoreReading WITH SOURCE_TOPIC "sensors/#" WITH QUERY "INSERT INTO sensor_data (timestamp, topic, payload) VALUES (NOW(), '{source_topic}', '{value.json}')"
Secure connection:
DEFINE ROUTE SecureStorage WITH TYPE MARIADB ADD SQL_CONFIG WITH SERVER "mariadb.cloud.com" WITH PORT '3306' WITH DATABASE "production" WITH USERNAME "app_user" WITH PASSWORD "secure_password" WITH USE_SSL "true" ADD EVENT StoreEvent WITH SOURCE_TOPIC "events/#" WITH QUERY "INSERT INTO events (ts, topic, data) VALUES (NOW(), '{source_topic}', '{value.json}')"
Insert a record or update it if a duplicate key exists. MariaDB supports both ON DUPLICATE KEY UPDATE (MySQL-compatible) and ON CONFLICT (PostgreSQL-compatible in newer versions):
DEFINE ROUTE DeviceRegistry WITH TYPE MARIADB ADD SQL_CONFIG WITH SERVER "mariadb.example.com" WITH PORT '3306' WITH DATABASE "iot_data" WITH USERNAME "iot_user" WITH PASSWORD "secure_password" ADD EVENT RegisterDevice WITH SOURCE_TOPIC "devices/+/registered" WITH QUERY "INSERT INTO devices (device_id, name, area, active, updated_at) VALUES ('{value.json.device_id}', '{value.json.name}', '{value.json.area}', 1, NOW()) ON DUPLICATE KEY UPDATE name = VALUES(name), area = VALUES(area), updated_at = NOW()"
Modify existing records when a status change arrives:
DEFINE ROUTE DeviceRegistry WITH TYPE MARIADB ADD SQL_CONFIG WITH SERVER "mariadb.example.com" WITH PORT '3306' WITH DATABASE "iot_data" WITH USERNAME "iot_user" WITH PASSWORD "secure_password" ADD EVENT UpdateDeviceStatus WITH SOURCE_TOPIC "devices/+/status" WITH QUERY "UPDATE devices SET current_status = '{value.json.status}', last_seen = NOW() WHERE device_id = '{value.json.device_id}'"
Remove records on a decommission or cleanup event:
DEFINE ROUTE DeviceRegistry WITH TYPE MARIADB ADD SQL_CONFIG WITH SERVER "mariadb.example.com" WITH PORT '3306' WITH DATABASE "iot_data" WITH USERNAME "iot_user" WITH PASSWORD "secure_password" ADD EVENT DecommissionDevice WITH SOURCE_TOPIC "devices/+/decommissioned" WITH QUERY "DELETE FROM devices WHERE device_id = '{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 "sensor_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 MARIADB ADD SQL_CONFIG WITH SERVER "mariadb.example.com" WITH PORT '3306' WITH DATABASE "iot_data" 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 sensor_id, value FROM sensor_data WHERE sensor_id = '{payload}' ORDER BY timestamp DESC LIMIT 1"
To trigger this query, publish the sensor ID to the source topic:
Topic: db/query/latestPayload: temp001
The query result is published to db/result/latest, where your actions or external clients can consume it.
EVENTs can also run SELECT queries on a fixed schedule using WITH EVERY:
DEFINE ROUTE DeviceRegistry WITH TYPE MARIADB ADD SQL_CONFIG WITH SERVER "mariadb.example.com" WITH PORT '3306' WITH DATABASE "iot_data" WITH USERNAME "iot_user" WITH PASSWORD "secure_password" ADD EVENT PollActiveDevices WITH EVERY 10 SECONDS WITH DESTINATION_TOPIC "dashboard/devices/active" WITH QUERY "SELECT device_id, name, current_status, last_seen FROM devices WHERE active = 1 ORDER BY name"
