Building a dynamic msg.topic to create a mysql insert statement

fogmajor · 8 July 2025 09:11

Good Morning
I wondered if i could get a point in the right direction please.

Kind Regards
Andrew

I created a flow (pic attached) the inject node sends a mysql statement and inserts data as expected.

msg.topic = "INSERT INTO Sensors (Device_ID, Event_Type, Event_Time_Date) VALUES ('151', 'R' , Current_Timestamp())";

return msg;

When I try to create the msg.topic in a function I need to replace the value 151 with a variable called Test1 and replace the value R with Test2

This is my attempt but it throws a syntax error

msg.topic = "INSERT INTO Sensors (Device_ID, Event_Type, Event_Time_Date) " + "VALUES (" + Test1 + ", " + Test2 + " ," CURRENT_TIMESTAMP );";

dynamicdave · 8 July 2025 09:18

As a first step try this in a function node.

// Example values
let Test1 = "Sensor001";  // Device_ID
let Test2 = "ButtonPress";  // Event_Type

// Make sure to quote string values in SQL
msg.topic = "INSERT INTO Sensors (Device_ID, Event_Type, Event_Time_Date) " +
            "VALUES ('" + Test1 + "', '" + Test2 + "', CURRENT_TIMESTAMP);";

return msg;

dynamicdave · 8 July 2025 09:20

A better method is to use a parameterised query:

Some versions of Node-RED’s MySQL node support using msg.topic with placeholders and msg.payload:

// safer approach with placeholders
msg.topic = "INSERT INTO Sensors (Device_ID, Event_Type, Event_Time_Date) VALUES (?, ?, CURRENT_TIMESTAMP)";
msg.payload = ["Sensor001", "ButtonPress"];
return msg;

This prevents SQL injection and handles escaping for you.

If deviceID is numeric (rather than text) you could do something like this...

// Extract values from the incoming payload
let deviceID = msg.payload.device_id;       // numeric
let eventType = msg.payload.event_type;     // string

// Build parameterized SQL query
msg.topic = "INSERT INTO Sensors (Device_ID, Event_Type, Event_Time_Date) VALUES (?, ?, CURRENT_TIMESTAMP)";

// Assign to msg.payload as an array for the query
msg.payload = [deviceID, eventType];

return msg;

jbudd · 8 July 2025 10:34

If you are using node-red-node-mysql, a rather better (IMHO) approach to a parameterised query is for the payload to be an object rather than an array.
Using named object keys removes the possibly ambiguous link between the nth ? and payload[n-1]

msg.topic = "INSERT INTO Sensors (Device_ID, Event_Type, Event_Time_Date) VALUES (:device, :event, CURRENT_TIMESTAMP)";
msg.payload = { 
    "event": "ButtonPress",
    "device": "Sensor001" 
}
return msg;

fogmajor · 8 July 2025 10:44

Good Morning Dave, I guess having dynamic in the name is a clue, Thank you for such concise and plentiful options to allow me to try more than one option.
Kind regards
Andrew

fogmajor · 8 July 2025 10:46

Hi there
Many thanks for the informative and prompt response.
I will give that a try when i get home.

Thank you again

Regards
Andrew

dynamicdave · 8 July 2025 11:31

Have you tried using named placeholders in a MySQL node query? I'm not sure if it's supported.
I'll have to try it out later when I have some spare time.

jbudd · 8 July 2025 11:42

Strictly speaking it's Mariadb, but yes, that's how I do it with node-red-node-mysql.

smanjunath211 · 8 July 2025 11:54

If attached is what you mean, I can confirm it works!
Thanks for making my life much simpler. I have several queries which need simplification.

// safer approach with placeholders
msg.topic = "INSERT INTO cc (rdate, totalcases, totalcl, caseperman) VALUES (?, ?, ?, ? )";
msg.payload = ["2025-07-08", 1234, 25, 49];
return msg;

dynamicdave · 8 July 2025 11:56

jbudd:

msg.topic = "INSERT INTO Sensors (Device_ID, Event_Type, Event_Time_Date) VALUES (:device, :event, CURRENT_TIMESTAMP)";
msg.payload = { 
    "event": "ButtonPress",
    "device": "Sensor001" 
}
return msg;

I couldn't wait or resist - had to try named-bindings - works just fine.
Thanks, I'll be using that method in my future flows.

EDIT: Will also explain it to my IoT students for their projects.

smanjunath211 · 8 July 2025 12:03

This works as well,
I couldn't grasp much about what you said about removing ambiguity though.

// safer approach with placeholders
// msg.topic = "INSERT INTO cc (rdate, totalcases, totalcl, caseperman) VALUES (?, ?, ?, ? )";
// msg.payload = ["2025-07-08", 1234, 25, 49];
// return msg;

msg.topic = "INSERT INTO cc (rdate, totalcases, totalcl, caseperman) VALUES (:rdate, :totalcases, :totalcl, :caseperman)";
msg.payload = {
    "rdate": "2025-07-06",
    "totalcases": 1234,
    "totalcl": 25,
    "caseperman": 49
}
return msg;

dynamicdave · 8 July 2025 12:06

With named-bindings it doesn't matter what the sequence is of the data-items in the payload.

For example, you could have...

msg.payload = {
    "caseperman": 49,
    "totalcl": 25,
    "totalcases": 1234,
    "rdate": "2025-07-06"
}

And it will still work correctly. So it's a more flexible approach.

smanjunath211 · 8 July 2025 12:08

Got it! Thanks.

smanjunath211 · 8 July 2025 12:13

Yes, understood, really useful. I even got it to work without naming the field names before VALUES clause, as i am updating ALL the fields.

msg.topic = "INSERT INTO cc VALUES (:rdate, :totalcases, :totalcl, :caseperman, :costpercase, :cpm, :cpc)";
msg.payload = {
    "totalcases": 1234,
    "rdate": "2025-07-03",
    "caseperman": 49,
    "totalcl": 25,
    "costpercase": 39,
    "cpc": 25,
    "cpm": 52,
}

EDIT: Extremely Sorry @fogmajor for hijacking your thread. I will stop.

Colin · 8 July 2025 12:19

Just to note that in javascript you don't need the quotes round property names, provided they do not contain any special characters. So this will also work

msg.payload = {
    totalcases: 1234,
    rdate: "2025-07-03",
    caseperman: 49,
    totalcl: 25,
    costpercase: 39,
    cpc: 25,
    cpm: 52,
}

jbudd · 8 July 2025 12:44

It hardly matters if you are only inserting a couple of fields, but assuming the table has more fields...
You could do this (which I have not tested)

msg.topic = "INSERT INTO climatesensor"
msg.topic += " (sensorid, timestamp, temperature, pressure, humidity, airquality)"
msg.topic += " VALUES (?, CURRENT_TIMESTAMP(), ?, ?, ?, ?)"
msg.payload = [42, 97.4, 981, 75, 106]
return msg;

And 6 months later you discover that your BME680 can also report sea level pressure so you decide to include that.
Now your code has to change

msg.topic = "INSERT INTO climatesensor"
msg.topic += " (sensorid, timestamp, temperature,  sealevelpressure, pressure, humidity, airquality)"
msg.topic += " VALUES (?, CURRENT_TIMESTAMP(), ?, ?, ?, ?, ?)"
msg.payload = [42, 97.4, 981, 1012.1, 75, 106]

Or is it

msg.topic = "INSERT INTO climatesensor"
msg.topic += " (sensorid, timestamp, temperature, sealevelpressure, pressure, humidity, airquality)"
msg.topic += " VALUES (?, CURRENT_TIMESTAMP(), ?, ?, ?, ?, ?)"
msg.payload = [42, 97.4, 1012.1, 981, 75, 106]

Using named parameters you add in the new fieldname and payload key, no need to ensure they are in the same position in msg.payload (object properties don't really have a position)

msg.topic = "INSERT INTO climatesensor"
msg.topic += " (sensorid, timestamp, temperature, sealevelpressure, pressure, humidity, airquality)"
msg.topic += " VALUES (:sensorid, CURRENT_TIMESTAMP(), :temperature, :sealevelpressure, :pressure, :humidity, :airquality)"
msg.payload = { 
    "sensorid": 42, 
    "temperature": 97.4, 
    "sealevelpressure": 1012.1, 
    "pressure": 981, 
    "humidity": 75, 
    "airquality": 106
}

Note that a BME680 sends it's data already in json form, so you may not actually need to rebuild msg.payload at all.
Here is an example from my flows:

if (msg.payload.Temperature > -20 && msg.payload.Temperature < 80 ) { // sanity check
    msg.topic = "insert into sensordata "
    + "(location, timestamp, sensortype, temperature, humidity, dewpoint, pressure, seapressure, gas) "
// the payload properties are exactly as sent by the sensor
    + "VALUES (:location, :timestamp, :sensortype, :Temperature, :Humidity, :DewPoint, :Pressure, :SeaPressure, :Gas)" 

    return msg
}

Indeed the quotes are not needed here. But if I were assembling msg.payload in a template node they would be needed, so I generally include them.

dynamicdave · 8 July 2025 14:10

Just tried using named-bindings with an On Duplicate Key Update query - works just fine.

msg.topic  = "INSERT INTO energy_latest_readings ";
msg.topic += "(node_ref, voltage, current, power) ";
msg.topic += "VALUES (:node_ref, :voltage, :current, :power) ";
msg.topic += "ON DUPLICATE KEY UPDATE ";
msg.topic += "node_ref = :node_ref, ";
msg.topic += "voltage = :voltage, ";
msg.topic += "current = :current, ";
msg.topic += "power = :power;";

msg.payload = {
    node_ref: 'ev_charging_pod',
    voltage: msg.payload.ENERGY.Voltage,
    current: msg.payload.ENERGY.Current,
    power: msg.payload.ENERGY.ApparentPower
};

return msg;

I have a simple DB table where 'node_ref' is a unique key.

This is what the DB contents looks like.

Here's a screenshot of the NR flow.

smanjunath211 · 8 July 2025 14:44

Wow. there is no ambiguity now. very well explained. Although Dave's explanation also was clear and concise. thank you. learnt something today.

Topic		Replies	Views
Using mysql node but no joy with insert query General	7	1836	29 August 2020
Need to use message TOPIC to do an INSERT into a database General	8	379	14 August 2021
MsgPayload into a query on nodered-sqlite General	2	3305	16 May 2018
Can't Differentiate data from different topics while inserting data into my sql database General	13	454	15 September 2021
[SOLVED]The dreaded "query not defined as a string" mysql General	5	4672	31 October 2018

Building a dynamic msg.topic to create a mysql insert statement

Related topics