New: the AI firewall watches the whole platform and explains each incident in plain words. See how it works

Store MQTT data in a database: the subscriber that writes your history

An MQTT broker keeps one retained message per topic and a short queue per session. Your history needs a writer service; here is how I build one.

To store MQTT data in a database, run a subscriber that receives every device message from the broker and writes it to the database. The broker is not a history. It keeps one retained message per topic and holds queued QoS 1 and QoS 2 messages only for a session that has not expired, within its storage limits.

A live screen can run straight off the broker, but a chart of last week needs every message written somewhere. That job belongs to a writer service, and I design it on day one.

Does an MQTT broker store messages?

Only in two narrow ways, both defined in the MQTT 5.0 specification, and neither is a history.

  • Retained messages. When a client publishes with the RETAIN flag set, the server replaces any existing retained message for that topic and stores the new one for future subscribers, so it keeps one message per topic.
  • Session state. For a client with a session, the server keeps QoS 1 and QoS 2 messages pending transmission, and optionally QoS 0. A session lasts as long as the network connection plus the Session Expiry Interval; if that interval is 0 or absent, the session ends when the connection closes.

The specification adds that server storage has limits and that session state can be discarded by an administrator or an automated response, so I treat a broker queue as a buffer for a short outage.

How do you save MQTT messages to SQL?

Write a small service that subscribes to your device topics and inserts each message as a row:

  1. Connect with a fixed client identifier, Clean Start set to 0 and a Session Expiry Interval that covers a restart, so the broker queues messages while the writer is away.
  2. Subscribe with QoS 1 to a filter that covers your devices, for example sites/+/devices/+/telemetry. A message arrives at the lower of its publish QoS and the subscription’s, so devices must publish at QoS 1 too.
  3. Take the device id from the topic and the timestamp from the payload when the device clock is reliable, not from the database clock.
  4. Insert in batches inside a transaction, with a unique key on device and timestamp. QoS 1 is “at least once”, so duplicates can arrive; the key makes them harmless.
  5. To run two writers, use a shared subscription such as $share/writers/sites/+/devices/+/telemetry. Each message then goes to only one of the sessions in the group.

Related: MQTT vs HTTP for IoT devices, webhook vs WebSocket vs polling for passing data on, and Sema’s post on downsampling time series data for long charts.

Hermetry connects field devices to live dashboards, rules and alerts: IoT telemetry and SCADA in one platform. Every organisation starts with a Default root flow that receives every device message and saves its telemetry. Messages wait in a queue while a processing worker is down and are still stored when the platform catches up, and a message with the same values at the same time for the same device is stored once. See how telemetry readings are stored.

What happens to MQTT data when the database is down?

It depends on when your writer acknowledges a message. Under QoS 1 the receiver sends PUBACK “having accepted ownership” of the message, it does not need to finish delivery first, and ownership passes to the receiver once the sender gets the PUBACK.

Once the writer’s client has acknowledged a message, the broker has let it go, and if the database is unreachable the writer must keep the message itself. I give the writer a small buffer on local disk, retry from it in order, and alarm when it grows. The other option is to disconnect and let the broker queue for the session, which works only until the session expires or the broker’s storage limit is reached.

Frequently asked questions

Can I use retained messages as a simple history?

No. A new retained message replaces the existing one for the topic, and a retained message with an empty payload removes it. Retained messages give a new subscriber the last known value, for example a device’s status, but never more than one message per topic.

Why does a writer on a shared subscription not get retained messages?

The MQTT 5.0 specification says no retained messages are sent to a session when it subscribes to a shared subscription. A writer that needs the last known values at startup has to read them elsewhere, for example from its own database.

Do I still need a unique key if I use QoS 2?

QoS 2 is the exactly-once level of MQTT and stops duplicate delivery on the link between broker and writer. I keep the unique key anyway, because a writer that retries from its own disk buffer, or a device that resends after a restart, can still produce the same reading twice.

Share

See your field data live this week

Start a trial in your region with your first devices, or book a demo and bring your own use case.