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

**URL:** <https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024>\
**Category:** General\
**Created:** [8 July 2025 09:11 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024 "2025-07-08T09:11:10Z")\
**Posts on this page:** 19\
**Page:** 1

<div class="post-metadata">

**Author:** ![fogmajor](https://avatars.discourse-cdn.com/v4/letter/f/bbce88/32.png) [@fogmajor](https://discourse.nodered.org/u/fogmajor)\
**Post date:** [8 July 2025 09:11 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/1 "2025-07-08T09:11:10Z")

</div>

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

Kind Regards  
Andrew

 ![flow](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/4/3/435e6530ce4e49cac6b04df75177b28403ba1023.png)

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 );";

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [8 July 2025 09:18 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/2 "2025-07-08T09:18:39Z")

</div>

> [@fogmajor](#):
>
> msg.topic = "INSERT INTO Sensors (Device\_ID, Event\_Type, Event\_Time\_Date) " + "VALUES (" + Test1 + ", " + Test2 + " ," CURRENT\_TIMESTAMP );";

As a first step try this in a function node.

```auto
// 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;

```

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [8 July 2025 09:20 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/3 "2025-07-08T09:20:53Z")

</div>

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:

```auto
// 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...

```auto
// 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;

```

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [8 July 2025 10:34 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/4 "2025-07-08T10:34:49Z")

</div>

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]

```auto
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;

```

---

<div class="post-metadata">

**Author:** ![fogmajor](https://avatars.discourse-cdn.com/v4/letter/f/bbce88/32.png) [@fogmajor](https://discourse.nodered.org/u/fogmajor)\
**Post date:** [8 July 2025 10:44 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/5 "2025-07-08T10:44:14Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![fogmajor](https://avatars.discourse-cdn.com/v4/letter/f/bbce88/32.png) [@fogmajor](https://discourse.nodered.org/u/fogmajor)\
**Post date:** [8 July 2025 10:46 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/6 "2025-07-08T10:46:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [8 July 2025 11:31 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/7 "2025-07-08T11:31:47Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [8 July 2025 11:42 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/8 "2025-07-08T11:42:41Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 July 2025 11:54 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/9 "2025-07-08T11:54:20Z")

</div>

> [@dynamicdave](#):
>
> using named placeholders in a MySQL node query

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.

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

```

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/5/5/55efcae6117ad8102a64e3bde0545425c74aa984.png)

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [8 July 2025 11:56 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/10 "2025-07-08T11:56:30Z")

</div>

> [@jbudd](#):
>
> ```auto
> 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.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 July 2025 12:03 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/11 "2025-07-08T12:03:40Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/7/f/7f7cb454654720d1257e3ec4caa0ae6b0df91044.png)

```auto
// 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;

```

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [8 July 2025 12:06 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/12 "2025-07-08T12:06:21Z")

</div>

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

For example, you could have...

```auto
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.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 July 2025 12:08 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/13 "2025-07-08T12:08:31Z")

</div>

Got it! Thanks.

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 July 2025 12:13 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/14 "2025-07-08T12:13:00Z")

</div>

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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![Colin](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/colin/32/17040_2.png) [@Colin](https://discourse.nodered.org/u/Colin)\
**Post date:** [8 July 2025 12:19 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/15 "2025-07-08T12:19:41Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [8 July 2025 12:44 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/16 "2025-07-08T12:44:41Z")

</div>

> [@smanjunath211](#):
>
> I couldn't grasp much about what you said about removing ambiguity though.

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)

```auto
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

```auto
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

```auto
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)

```auto
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:

```auto
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
}

```

> [@Colin](#):
>
> Just to note that in javascript you don't need the quotes round property names, provided they do not contain any special characters

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.

---

<div class="post-metadata">

**Author:** ![dynamicdave](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/dynamicdave/32/96_2.png) [@dynamicdave](https://discourse.nodered.org/u/dynamicdave)\
**Post date:** [8 July 2025 14:10 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/17 "2025-07-08T14:10:32Z")

</div>

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

```auto
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.

 ![latest_readings_A](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/9/9/99e3f18bbb0ad180caa1118ae7aa7b6a7894aebf.png)  
This is what the DB contents looks like.  
 ![latest_readings_B](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/1/c/1c2cb299c09f85f6070e6804bb2e462b41508588.png)  
Here's a screenshot of the NR flow.  
 ![latest_readings_C](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/a/5/a5747a4833990c777130de6b2d5e4bf53dcf6163.png)

---

<div class="post-metadata">

**Author:** ![smanjunath211](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/smanjunath211/32/95742_2.png) [@smanjunath211](https://discourse.nodered.org/u/smanjunath211)\
**Post date:** [8 July 2025 14:44 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/18 "2025-07-08T14:44:52Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [6 October 2025 14:45 UTC](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/19 "2025-10-06T14:45:34Z")

</div>

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