MariaDB Route

MariaDB Overview

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.

Basic Syntax

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}')"

Connection Configuration

SQL_CONFIG Parameters

SERVER

string

required

MariaDB server hostname or IP address.

DATABASE

string

required

Target database name.

USERNAME

string

required

Database username.

PASSWORD

string

required

Database password.

Optional Settings

PORT

integer

MariaDB port. Default: 3306.

USE_SSL

boolean

Enable SSL connection. 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

Basic Storage
With SSL
UPSERT
UPDATE
DELETE

Store sensor data in MariaDB:

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"

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 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/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 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"

Troubleshooting

Connection Refused

Verify SERVER and PORT are correct
Check MariaDB is running and accepting connections
Verify firewall allows connections on port 3306

Authentication Failed

Verify USERNAME and PASSWORD are correct
Check user has permissions on the DATABASE
Verify user can connect from your host

Next Steps

Data Storage Routes Overview

Compare all storage options.

SQL Server Route

Configure Microsoft SQL Server storage.

Quick Start

Developing and Managing Coreflux

Coreflux MQTT Broker

LoT Language

Product Updates

MariaDB Overview

Basic Syntax

Connection Configuration

SQL_CONFIG Parameters

Writing Data

Reading Data

Timed Polling

Troubleshooting

Next Steps

Data Storage Routes Overview

SQL Server Route

​MariaDB Overview

​Basic Syntax

​Connection Configuration

​SQL_CONFIG Parameters

​Writing Data

​Reading Data

​Timed Polling

​Troubleshooting

​Next Steps

Data Storage Routes Overview

SQL Server Route

MariaDB Overview

Basic Syntax

Connection Configuration

SQL_CONFIG Parameters

Writing Data

Reading Data

Timed Polling

Troubleshooting

Next Steps