Skip to Content

← All functionalities

Automated agricultural irrigationRaspberry Pi (Docker)MySQLDatalogging

An alarm history in MySQL, written by Node-RED

An alarm nobody can review later never happened. This example builds an alarm history in MySQL on a Raspberry Pi: an alarms table storing every event (device, input, message, timestamp) and an alarms_describe table defining which digital input maps to which message and whether it is enabled. Node-RED inserts events with a parameterized query and a Laravel frontend reads the history. It is the audit trail of a real automated irrigation deployment.

Definitions and events are different tables

alarms_describe holds one row per supervised digital input — its message and an enabled flag — while alarms only stores what actually happened. Disabling a noisy alarm is a single UPDATE, with no flow redeploy and no rewriting of history. A unique key on (devices_id, input) keeps definitions clean across the multi-device schema inherited from the Laravel login.

Parameterized inserts from a function node

The Node-RED function node sets msg.topic to an INSERT with named placeholders and passes values in msg.payload, letting node-red-node-mysql do the escaping — no string concatenation, no injection surface. The same message then continues to the Telegram formatter, so database write and phone notification come from one detection point and always agree.

Queries the frontend actually runs

The file ships the two queries behind the Laravel screens: the latest-50 history list and a 30-day aggregation joining events with their definitions for a per-alarm bar chart. Because the schema mounts as a docker-entrypoint-initdb.d script in the MySQL container, a fresh Raspberry Pi boots with tables, seed definitions and credentials already in place.

A snippet from the implementation

Straight from the example as deployed on the Raspberry Pi (Docker) — copy it freely:

-- ============================================================================
-- COMPLETE EXAMPLE — Alarm history in MySQL
--
-- Hardware:  Raspberry Pi (Docker) with MySQL 8 + Node-RED + Laravel frontend
-- Based on:  automated agricultural irrigation project
--
-- Requirements:
--   - MySQL container from the stack (see docker-compose-irrigation.yml).
--   - node-red-node-mysql palette in Node-RED for the inserts.
--   - The password goes in the stack's MYSQL_PASSWORD environment variable.
--
-- Model:
--   alarms_describe  defines each possible alarm: which digital input it is
--                    tied to, the message to display and whether it is enabled.
--   alarms           event history: each trigger inserts a row with the
--                    device, the input, the message and the timestamp.
--   The Laravel frontend queries both tables for the listing and the counters.
-- ============================================================================

CREATE DATABASE IF NOT EXISTS irrigation
  CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE irrigation;

-- ----------------------------------------------------------------------------
-- Alarm definitions: one row per monitored digital input
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS alarms_describe (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  devices_id  CHAR(36)     NOT NULL,            -- device UUID (Raspberry Pi)
  input       VARCHAR(10)  NOT NULL,            -- digital input: I0, I1...
View the full working example Get the full project pack

The full example is a complete program — wiring header, setup and main loop — ready to adapt to your application.

Frequently asked questions

Why is the timestamp stored as VARCHAR instead of DATETIME?

The deployed system writes ISO-8601 strings generated in Node-RED, which sort lexicographically and parse trivially in PHP and JavaScript. With a DATETIME column you gain native date functions; the example keeps the field as deployed for compatibility.

How does Node-RED detect that an alarm input is active?

The I/O tab polls the digital inputs and compares against the enabled definitions from alarms_describe. On a rising edge it emits one message with input and text, which feeds both the MySQL insert and the Telegram notification.

Can several devices share the same database?

Yes, every row carries a devices_id UUID, and the Laravel frontend filters by the device tied to each company login. One MySQL container on one Raspberry Pi can centralize alarms from several field controllers.

Related functionalities