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.

system · 6 October 2025 14:45

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Using mysql node but no joy with insert query General	7	1848	29 August 2020
Read from mysql db Dashboard	54	17492	23 December 2018
Fine tune a "function" node General	17	1466	13 May 2020
Using msg.payload in a SQL query General	7	9138	23 June 2020
MsgPayload into a query on nodered-sqlite General	2	3318	16 May 2018

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

Related topics