Node-RED MySQL: Logging MQTT Data to MySQL or MariaDB with Parameterized Queries

Node-RED and MySQL make a practical pair when you want MQTT sensor data stored in a database your team already knows how to query, back up and report on. In this tutorial you will create a table for readings, subscribe to MQTT topics in Node-RED, insert every message with parameterized queries, switch to batch inserts when the message rate grows, and set up retention so the table does not grow forever. Everything works the same on MariaDB.

Why Log MQTT Data to MySQL?

An MQTT broker moves messages; it is not a historian. Retained messages give you the latest value per topic, but not last week's values. To build reports, compare shifts or feed a BI tool, you need the history somewhere durable.

Time-series databases such as InfluxDB are a natural choice for high-rate telemetry, and we cover that in Node-RED with InfluxDB. MySQL and MariaDB still win in many situations: the company already runs them, reporting tools connect to them natively, you need to join readings with relational data such as work orders or asset lists, or the data rate is modest. A few hundred readings per minute is comfortable territory for a well-indexed table.

Step 1: Create the Database and Table

If you do not have a database server yet, start MariaDB in Docker for testing:

docker run -d --name iot-mariadb -p 3306:3306 \
  -e MARIADB_ROOT_PASSWORD=change-root \
  -e MARIADB_DATABASE=iot \
  -e MARIADB_USER=nodered \
  -e MARIADB_PASSWORD=change-me \
  mariadb:11

Then create the table. One row per reading, with the original topic kept for traceability and the full payload kept for anything you did not anticipate:

CREATE TABLE mqtt_readings (
  id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  received_at DATETIME(3)  NOT NULL,
  topic       VARCHAR(255) NOT NULL,
  device      VARCHAR(64)  NOT NULL,
  metric      VARCHAR(64)  NOT NULL,
  value       DOUBLE       NULL,
  payload     TEXT         NULL,
  INDEX idx_device_metric_time (device, metric, received_at),
  INDEX idx_time (received_at)
);

Store timestamps in UTC and convert in your reports. Mixing local time and daylight saving changes into a time-series table is a reliable source of confusing gaps and duplicate hours.

Grant the Node-RED user only what it needs:

GRANT SELECT, INSERT, DELETE ON iot.mqtt_readings TO 'nodered'@'%';

Step 2: Install the Node-RED MySQL Node

Install node-red-node-mysql from Menu → Manage palette. It adds a mysql node with a database configuration: host, port (3306), user, password, database name and timezone. Set the timezone to Z or +00:00 so dates are interpreted as UTC.

The node follows a simple contract:

• msg.topic holds the SQL statement.
• msg.payload holds the parameters: an array for ? placeholders, or an object for named :name placeholders.
• The result comes back in msg.payload: rows for a SELECT, or a result object with affectedRows and insertId for an INSERT.

Step 3: Subscribe to MQTT

Add an mqtt in node connected to your broker. For testing, use the public MQTT test broker at broker.connectmq.com, port 1883, a unique client ID, and subscribe to connectmq/demo/db91kz/+/+ with QoS 1 and output set to auto-detect. The two wildcard levels capture the device and the metric, for example connectmq/demo/db91kz/boiler1/temperature. Replace db91kz with your own random string; anything on the public broker is visible to others.

Step 4: Insert with Parameterized Queries

Here is the most important rule of this tutorial: never build SQL by concatenating MQTT payloads into a string. Topics and payloads come from devices and, on a shared or misconfigured broker, potentially from anyone. A payload such as 1); DROP TABLE mqtt_readings; -- should end up as harmless text in a column, not executed.

Add a function node between the mqtt in node and the mysql node:

// Topic: connectmq/demo/db91kz/<device>/<metric>
const mqttTopic = msg.topic;
const parts = mqttTopic.split("/");
if (parts.length !== 5) return null;

const device = parts[3].substring(0, 64);
const metric = parts[4].substring(0, 64);

// Accept numbers, numeric strings, or {"value": ...} objects
let raw = msg.payload;
let value = null;
if (typeof raw === "number") {
    value = raw;
} else if (typeof raw === "string" && raw.trim() !== "" && !isNaN(Number(raw))) {
    value = Number(raw);
} else if (typeof raw === "object" && raw !== null && typeof raw.value === "number") {
    value = raw.value;
}

const payloadText = typeof raw === "string" ? raw : JSON.stringify(raw);
// MySQL DATETIME(3) format in UTC: 2026-10-16 08:15:30.123
const receivedAt = new Date().toISOString().slice(0, 23).replace("T", " ");

msg.topic =
    "INSERT INTO mqtt_readings (received_at, topic, device, metric, value, payload) " +
    "VALUES (?, ?, ?, ?, ?, ?)";
msg.payload = [receivedAt, mqttTopic, device, metric, value, payloadText];
return msg;

Note that msg.topic is overwritten with the SQL statement, which is why the code saves the MQTT topic in mqttTopic first for the topic column. Wire the function to the mysql node and add a debug node after it to see the insertId of each row.

Testing the insert

mosquitto_pub -h broker.connectmq.com -p 1883 -i sim-db91kz -t "connectmq/demo/db91kz/boiler1/temperature" -m "71.4"
mosquitto_pub -h broker.connectmq.com -p 1883 -i sim-db91kz -t "connectmq/demo/db91kz/boiler1/pressure" -m '{"value": 2.3, "unit": "bar"}'
mosquitto_pub -h broker.connectmq.com -p 1883 -i sim-db91kz -t "connectmq/demo/db91kz/boiler1/state" -m "running"

Then check the table:

SELECT received_at, device, metric, value, payload
FROM mqtt_readings
ORDER BY id DESC
LIMIT 10;

The third message lands with value set to NULL and running in the payload column, which is exactly what you want for states and other non-numeric data.

Step 5: Batch Inserts for Higher Message Rates

One INSERT per message is fine for a few messages per second. Beyond that, each round trip and commit adds up. Batching cuts the load on both Node-RED and the database.

Change the function from Step 4 to output a row array instead of an SQL statement (set msg.payload = [receivedAt, ...] and leave msg.topic alone), then add a join node in manual mode: combine each msg.payload into an array, send after 100 messages or after a timeout of 5 seconds. Check with a debug node that the result is an array of rows (an array of arrays), not one flat array. Follow it with this function:

const rows = msg.payload;            // array of 6-element arrays
if (!Array.isArray(rows) || rows.length === 0) return null;

const placeholders = rows.map(() => "(?, ?, ?, ?, ?, ?)").join(", ");
msg.topic =
    "INSERT INTO mqtt_readings (received_at, topic, device, metric, value, payload) VALUES " +
    placeholders;
msg.payload = rows.flat();
return msg;

Every value is still passed as a parameter, so the batch is just as safe as the single insert. If Node-RED stops while a batch is pending, up to five seconds of data is lost; for most monitoring that is acceptable, and for anything stricter, keep single inserts.

Step 6: Retention and Useful Queries

Decide how long raw readings are kept. A simple daily cleanup in Node-RED uses an inject node set to repeat at a fixed time, followed by a function node:

msg.topic = "DELETE FROM mqtt_readings WHERE received_at < ? LIMIT 50000";
const cutoff = new Date(Date.now() - 90 * 24 * 3600 * 1000);
msg.payload = [cutoff.toISOString().slice(0, 23).replace("T", " ")];
return msg;

The LIMIT keeps each delete small so it does not lock the table for long; run it more often if a day produces more rows than that. MySQL's event scheduler can do the same job inside the database if you prefer.

Two queries you will use constantly:

-- Hourly average per device for the last 24 hours
SELECT device,
       DATE_FORMAT(received_at, '%Y-%m-%d %H:00') AS hour,
       AVG(value) AS avg_value,
       MAX(value) AS max_value
FROM mqtt_readings
WHERE metric = 'temperature'
  AND received_at >= UTC_TIMESTAMP() - INTERVAL 24 HOUR
GROUP BY device, hour
ORDER BY device, hour;

-- Devices that have not reported for 15 minutes
SELECT device, MAX(received_at) AS last_seen
FROM mqtt_readings
GROUP BY device
HAVING last_seen < UTC_TIMESTAMP() - INTERVAL 15 MINUTE;

The second query is a cheap way to find silent devices. To be notified rather than having to look, combine it with the Node-RED email alert flow. To show the history on a dashboard, the Node-RED chart history guide shows how to query and plot it.

Troubleshooting Node-RED MySQL Flows

Production Setup

For real data, move off the public broker. Create a private broker on ConnectMQ, then configure the Node-RED broker node with port 8883, TLS enabled, and the YOUR_USERNAME, YOUR_PASSWORD and YOUR_CLIENT_ID values from the portal. Use a persistent session (clean session off) with QoS 1 so short Node-RED restarts do not lose messages the broker can still deliver; on the free Serverless plan QoS goes up to 1, which is the right level for logging anyway.

This split of responsibilities is deliberate. ConnectMQ is a fully managed MQTT service that delivers messages; it does not write to databases itself. Node-RED is the database-facing client, which keeps your schema, retention and SQL entirely under your control. The Node-RED MQTT tutorial covers the remaining broker node settings.

Log Your MQTT Data with Node-RED and ConnectMQ

Create a broker on the free Serverless plan at app.connectmq.com, no credit card required, connect Node-RED with TLS, and start building a history you can query with plain SQL. The portal's live topic explorer helps you confirm topics and payload formats before you design your table around them. If you are still weighing hosting options, our cloud MQTT broker guide compares the approaches.

Frequently Asked Questions

How do I pass parameters to the Node-RED MySQL node?

Put the SQL with ? placeholders in msg.topic and an array of values in msg.payload. The node also accepts named :name placeholders when msg.payload is an object.

Does this work with MariaDB?

Yes. node-red-node-mysql connects to both MySQL and MariaDB, and the table, inserts and queries in this tutorial run unchanged on either.

Should I use MySQL or InfluxDB for MQTT data?

Use MySQL or MariaDB when you need joins with relational data, existing reporting tools or moderate data rates. Use a time-series database for high-rate telemetry and long retention with downsampling.

Can ConnectMQ store messages in a database directly?

No. ConnectMQ is an MQTT broker without database integrations. Run Node-RED or another subscriber that connects to the broker and writes to the database, as shown in this tutorial.