# Modbus to mySQL flow suggestions

**URL:** <https://discourse.nodered.org/t/modbus-to-mysql-flow-suggestions/98531>\
**Category:** General\
**Tags:** modbus, mysql\
**Created:** [5 August 2025 19:18 UTC](https://discourse.nodered.org/t/modbus-to-mysql-flow-suggestions/98531 "2025-08-05T19:18:38Z")\
**Posts on this page:** 9\
**Page:** 2

<div class="post-metadata">

**Author:** ![BambosD](https://avatars.discourse-cdn.com/v4/letter/b/54ee81/32.png) [@BambosD](https://discourse.nodered.org/u/BambosD)\
**Post date:** [6 August 2025 20:35 UTC](https://discourse.nodered.org/t/modbus-to-mysql-flow-suggestions/98531/21 "2025-08-06T20:35:58Z")

</div>

Hello again Mr. Steve,

change a little the plan, since each modbus poll could be up to 125 values according to the protocol , and some devices using many thousands in the modbus range, most probably we need 2 or 3 different polls from each device, which giving us 3-4 values from each poll and those values could be in 1 device (in case of a PLC that aggregates the values) or 3 to 4 devices if the field is not organized well. So instead of “device1, device2” before flex getters, we have just polls. poll1 , poll2 etc… now trying to read from the modbus field some real values, finally i have the structure below together with timestamp to make one complete record for the database.

i think now is more clear.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/2/6/264d4f3a125a9e203015624c289b08bbe8b4c6f9.png)

```auto
{"_msgid":"0945f017250cf55a","payload":[0,0,618,39543,0,0,0,0,0,0,0,0,0,0,0,1,0,1,0,0,2,2,3,0,0,0,0,6204,42455,0,0,6186,2219,0,0,0,0,0,0,620,30459,1,31027,0,141,0,4,0,0,0,17,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,65279,65535,65515,63971,50143,40959,0,130,0,0,0,0,0,0,0,0,0,0,0,0,0],"topic":"b65bd4a099fb7463","timestamp":"2025-08-06 23:27:15","settings":{"input":"2025-08-06T20:27:15.937Z","input_format":"","input_tz":"Europe/Athens","output_format":"YYYY-MM-DD HH:mm:ss","output_locale":"en-US","output_tz":"Europe/Athens"},"messageId":"6893baa3d349729cd64ef3fa","modbusRequest":{"unitid":100,"fc":3,"address":40521,"quantity":100,"emptyMsgOnFail":false,"keepMsgProperties":true,"messageId":"6893baa3d349729cd64ef3fa"},"responseBuffer":{"data":[0,0,618,39543,0,0,0,0,0,0,0,0,0,0,0,1,0,1,0,0,2,2,3,0,0,0,0,6204,42455,0,0,6186,2219,0,0,0,0,0,0,620,30459,1,31027,0,141,0,4,0,0,0,17,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,65279,65535,65515,63971,50143,40959,0,130,0,0,0,0,0,0,0,0,0,0,0,0,0],"buffer":[0,0,0,0,2,106,154,119,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,2,0,2,0,3,0,0,0,0,0,0,0,0,24,60,165,215,0,0,0,0,24,42,8,171,0,0,0,0,0,0,0,0,0,0,0,0,2,108,118,251,0,1,121,51,0,0,0,141,0,0,0,4,0,0,0,0,0,0,0,17,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,254,255,255,255,255,235,249,227,195,223,159,255,0,0,0,130,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0]},"originalPayload":[0,0,618,39543,0,0,0,0,0,0,0,0,0,0,0,1,0,1,0,0,2,2,3,0,0,0,0,6204,42455,0,0,6186,2219,0,0,0,0,0,0,620,30459,1,31027,0,141,0,4,0,0,0,17,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,65279,65535,65515,63971,50143,40959,0,130,0,0,0,0,0,0,0,0,0,0,0,0,0],"specification":{"options":{"byteSwap":[],"resultType":"keyvalue","singleResult":true,"msgProperty":"poll3","setTopic":false},"items":[{"type":"uint32le","name":"dcin","offset":0,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":0,"scaler":{"operator":"*","operand":0.1},"value":0},{"type":"int32le","name":"activep","offset":4,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":1,"scaler":{"operator":"*","operand":0.1},"value":200660838.60000002},{"type":"int32le","name":"apparent","offset":8,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":2,"scaler":{"operator":"*","operand":0.1},"value":0},{"type":"int32le","name":"reactiveq","offset":26,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":3,"scaler":{"operator":"*","operand":0.1},"value":0},{"type":"int32le","name":"energy","offset":120,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":4,"scaler":{"operator":"*","operand":0.1},"value":0}]},"values":[0,200660838.60000002,0,0,0],"objectResults":{"dcin":{"type":"uint32le","name":"dcin","offset":0,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":0,"scaler":{"operator":"*","operand":0.1},"value":0},"activep":{"type":"int32le","name":"activep","offset":4,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":1,"scaler":{"operator":"*","operand":0.1},"value":200660838.60000002},"apparent":{"type":"int32le","name":"apparent","offset":8,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":2,"scaler":{"operator":"*","operand":0.1},"value":0},"reactiveq":{"type":"int32le","name":"reactiveq","offset":26,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":3,"scaler":{"operator":"*","operand":0.1},"value":0},"energy":{"type":"int32le","name":"energy","offset":120,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":4,"scaler":{"operator":"*","operand":0.1},"value":0}},"keyvalues":{"dcin":0,"activep":200660838.60000002,"apparent":0,"reactiveq":0,"energy":0},"arrayResults":[{"type":"uint32le","name":"dcin","offset":0,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":0,"scaler":{"operator":"*","operand":0.1},"value":0},{"type":"int32le","name":"activep","offset":4,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":1,"scaler":{"operator":"*","operand":0.1},"value":200660838.60000002},{"type":"int32le","name":"apparent","offset":8,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":2,"scaler":{"operator":"*","operand":0.1},"value":0},{"type":"int32le","name":"reactiveq","offset":26,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":3,"scaler":{"operator":"*","operand":0.1},"value":0},{"type":"int32le","name":"energy","offset":120,"length":1,"offsetbit":0,"scale":"0.1","mask":"","id":4,"scaler":{"operator":"*","operand":0.1},"value":0}],"buffer":[0,0,0,0,2,106,154,119,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,1,0,0,0,0,0,2,0,2,0,3,0,0,0,0,0,0,0,0,24,60,165,215,0,0,0,0,24,42,8,171,0,0,0,0,0,0,0,0,0,0,0,0,2,108,118,251,0,1,121,51,0,0,0,141,0,0,0,4,0,0,0,0,0,0,0,17,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,254,255,255,255,255,235,249,227,195,223,159,255,0,0,0,130,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],"poll1":{"bom":37.800000000000004,"plane":0,"horizontal":692.2,"ambient":21.700000000000003},"poll2":{"setp":-12.9,"setq":-12.9},"poll3":{"dcin":0,"activep":200660838.60000002,"apparent":0,"reactiveq":0,"energy":0}}

```

So now, what is your suggestion to feed those into the database as 1 record ?  
Is further aggregation needed ? or the values is usable for SQL insert as it is ?

Merge with function, template or change node ?

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

---

<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:** [6 August 2025 20:50 UTC](https://discourse.nodered.org/t/modbus-to-mysql-flow-suggestions/98531/22 "2025-08-06T20:50:43Z")

</div>

> [@BambosD](#):
>
> or the values is usable for SQL insert as it is

Are you planning to use a prepared query with the fields in properties in msg.payload as I suggested earlier?

[Edit] In case you missed it I posted:

> [@Colin](#):
>
> use a Prepared query with named parameters as described in [the docs](https://flows.nodered.org/node/node-red-node-mysql), then the query is fixed. You can use a Change node to build the required message.

---

<div class="post-metadata">

**Author:** ![Steve-Mcl](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/steve-mcl/32/4826_2.png) [@Steve-Mcl](https://discourse.nodered.org/u/Steve-Mcl)\
**Post date:** [6 August 2025 20:54 UTC](https://discourse.nodered.org/t/modbus-to-mysql-flow-suggestions/98531/23 "2025-08-06T20:54:46Z")

</div>

Why are you fixated on 1 record? That will be incredibly inflexible. Perhaps it would make more sense for your table to contain the an `id` column for the device id. That keeps it simple. Alternatively consider storing the values as JSON.

The issue with 1 row for 4 devices is if you need to add a 5th, you have to modify the table. Also Column names get difficult to work with bom1, bom2, bomn..., this1, this2, thisn..., that1, that2... Yuck.

I would consider the above. And so long as you are using node-red-contrib-mysql you can use named parameters fed into msg.payload object as per the readme: [node-red-node-mysql (node) - Node-RED](https://flows.nodered.org/node/node-red-node-mysql)

---

<div class="post-metadata">

**Author:** ![BambosD](https://avatars.discourse-cdn.com/v4/letter/b/54ee81/32.png) [@BambosD](https://discourse.nodered.org/u/BambosD)\
**Post date:** [6 August 2025 21:07 UTC](https://discourse.nodered.org/t/modbus-to-mysql-flow-suggestions/98531/24 "2025-08-06T21:07:16Z")

</div>

Hello Mr. Colin,

some working tests i made using hardcoded data and template node using topic / payload (topic for columns , payload for the data coming from another node) like below.

working with Hardcode in template:

```auto
INSERT INTO test (c1 , c2 ,c3) 
VALUES ("4.147" ,4.23,"4.125");

```

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/c/6/c6a56a7b85a724ce4ee8f49071f2adfced07a3c9.png)

this is working with dynamic values coming from msg.payload :

 ![image](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/4/e456d1b81b04af2cce2d5b19ccbe877414a30e66.png)

```auto
INSERT INTO test (c2 , c1 ,c3)
VALUES (? , ? , ?);

```

for now, i’m using a manual template as i mentioned before:

```auto
{
"timestamp" : "{{timestamp}}",
"horizontal" : {{poll1.horizontal}},
"plane" : {{poll1.plane}},
"ambient" : {{poll1.ambient}},
"bom" : {{poll1.bom}},
"energy" : {{poll3.energy}},
"apparent" : {{poll3.apparent}},
"active" : {{poll3.activep}},
"reactive" : {{poll3.reactiveq}},
"dcpower" : {{poll3.dcin}},
"activesetp" : {{poll2.setp}},
"reactivesetp" : {{poll2.setq}}
}

```

the object result is :

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

```auto
{"timestamp":"2025-08-07 00:01:26",
"horizontal":0,
"plane":0,
"ambient":21.8,
"bom":37.800000000000004,
"energy":0,
"apparent":0,
"active":200660838.60000002,
"reactive":0,
"dcpower":0,
"activesetp":-12.9,
"reactivesetp":-12.9}

```

so that’s all the values formatted and aggregated. a single record to make the SQL insert statement. any suggestions appreciated.

---

<div class="post-metadata">

**Author:** ![BambosD](https://avatars.discourse-cdn.com/v4/letter/b/54ee81/32.png) [@BambosD](https://discourse.nodered.org/u/BambosD)\
**Post date:** [6 August 2025 21:15 UTC](https://discourse.nodered.org/t/modbus-to-mysql-flow-suggestions/98531/25 "2025-08-06T21:15:33Z")

</div>

thank you for your comments and your help. now is fixed with names and timestamp on a single object, please see my previous post.

the specific table will be fixed with those values with no additions.

but is interesting how we can store the json object as a whole in the database. how mysql can do that ? do we have to set the type of column as blob or something ?  
And does this mean that we can’t query values by the column name ?

---

<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:** [6 August 2025 21:31 UTC](https://discourse.nodered.org/t/modbus-to-mysql-flow-suggestions/98531/26 "2025-08-06T21:31:45Z")

</div>

> [@BambosD](#):
>
> how we can store the json object as a whole in the database

This seems unwise.

Probably because I don't use Modbus, I'm struggling to understand your descriptions of the setup.

It looks like you need to make 3 requests for various data attributes, and want these joined into a single message.  
The usual way to join messages is to use a Join node.  
In this case you would ensure that each output from Modbus has it's own topic, and join in manual mode, combining 3 messages.

Your picture of the data above has a payload: an array of 100 elements as well as originalPayload, also 100 elements, objectResults and arrayResults, yet all of these properties seem to be ignored. It's most confusing.

---

<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:** [7 August 2025 05:52 UTC](https://discourse.nodered.org/t/modbus-to-mysql-flow-suggestions/98531/27 "2025-08-07T05:52:24Z")

</div>

> [@BambosD](#):
>
> any suggestions appreciated

Don't do it that way, use a prepared query with names properties. That protects against SQL injection and is much easier to implement.

---

<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:** [7 August 2025 06:33 UTC](https://discourse.nodered.org/t/modbus-to-mysql-flow-suggestions/98531/28 "2025-08-07T06:33:40Z")

</div>

this thread may interest you...

> [@Building a dynamic msg.topic to create a mysql insert statement](https://discourse.nodered.org/t/building-a-dynamic-msg-topic-to-create-a-mysql-insert-statement/98024/14):
>
> 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.

---

<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:** [21 August 2025 06:34 UTC](https://discourse.nodered.org/t/modbus-to-mysql-flow-suggestions/98531/29 "2025-08-21T06:34:28Z")

</div>

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

[Previous page](https://discourse.nodered.org/t/modbus-to-mysql-flow-suggestions/98531.md?page=1)