Node-RED Tutorial: How to connect PostgreSQL with Raspberry Pi PLC

Save the data of your open-source projects in a object-relational database
November 9, 2021 by
Node-RED Tutorial: How to connect PostgreSQL with Raspberry Pi PLC
Boot & Work Corp. S.L., Fernandez Queralt Martinez

Introduction

More and more, programs are appearing through which you can program without having to strictly code, as is the case with Node-RED.

In this blog post, we will make a small application in which we will receive data from input from the PLC, and we will send it to the database.

Do you want to know how? 

Latest Posts

Your Dynamic Snippet will be displayed here... This message is displayed because you did not provided both a filter and a template to use.

NODE-RED-CONTRIB-POSTGRESQL

Once you have all the tools ready to make Node-RED run in your industrial Raspberry Pi PLC, open your favorite browser with Node-RED, and let's start!

The application will consist in reading an analog input every five seconds, and if the value is higher than 1023, we will send the information to the database. Also, we will be requesting all the data of the table during the same time, five seconds, and we will display the result of the last data stored in a dashboard.

So, there are three nodes that we will need that are not installed by default with Node-RED and that we are going to install. Go to the Menu > Manage Palette > Install, and type and install these three:

  1. node-red-contrib-postgresql
  2. node-red-dashboard
  3. node-red-contrib-rpiplc-node


Once installed, let's develop our flow!

  • On one hand, add an inject node, and repeat an interval every 5 seconds.
  • Add an analog read node from Industrial Shields, select your model and the input where you will add some voltage.
  • Right after the analog read node, wire a switch node and set the property msg.payload to two conditions:

    1. otherwise
    2. > (number) 1023
  • From output number 1 (otherwise), we will connect a debug node, where we will see the values every 5 seconds.
  • From the output number 2 of the switch node, add a function node with the following condition:

    var now = new Date().toLocaleString("es-ES");
    msg.time = now;
    return msg;
  • After the function node, we will add a postgresql node. We will configure a server. As we did in the blog post of the previous reading, we are going to set the following parameters in the postgresql node:

    Host: 127.0.0.1

    Port: (number) 5432
    Database: (String) isdb
    SSL: false

    In the Security tab, set a user and a password.

    In the Query section, we are going to add the following query:
    INSERT INTO myIStable VALUES ('I0.7', '{{msg.payload}}', '{{msg.time}}');
  • On the other hand, add a postgresql after the inject node with the same server, and add this query:
    SELECT * FROM myIStable ORDER BY datetime DESC;
  • Get the information by adding a debug node with the output set to msg.payload[0];
  • Finally, we are going to add the text nodes. 
    In the first one, we are going to set the label to "Input", and the value format to: "{{msg.payload[0].name}}".

    On the second one, we will do the same but setting the label to Value, and the value format to {{msg.payload[0].data}}

    Finally, we will add a third text dashboard node, with the label: Datetime and the value format: {{msg.payload[0].datetime}}

This is how the basic structure of our database looks like.:

Database - Node-RED Tutorial: How to connect PostgreSQL with Raspberry PLC

If you could not manage to make your flow run, compare it with our flows.json and start your application!

flows.json - Node-RED Tutorial: How to connect PostgreSQL with Raspberry PLC
[{"id":"a2278800.0ea508","type":"tab","label":"Industrial Shields","disabled":false,"info":""},{"id":"686ee02c.306a","type":"postgreSQLConfig","name":"@127.0.0.1:5432/postgres","host":"127.0.0.1","hostFieldType":"str","port":"5432","portFieldType":"num","database":"isdb","databaseFieldType":"str","ssl":"false","sslFieldType":"bool","max":"10","maxFieldType":"num","min":"1","minFieldType":"num","idle":"1000","idleFieldType":"num","connectionTimeout":"10000","connectionTimeoutFieldType":"num","user":"pi","userFieldType":"str","password":"raspberry","passwordFieldType":"str"},{"id":"711d368a.8b1ec8","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false},{"id":"b0f41cb4.00be5","type":"ui_base","theme":{"name":"theme-light","lightTheme":{"default":"#0094CE","baseColor":"#0094CE","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":true,"reset":false},"darkTheme":{"default":"#097479","baseColor":"#097479","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif","edited":false},"customTheme":{"name":"Untitled Theme 1","default":"#4B7930","baseColor":"#4B7930","baseFont":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"},"themeState":{"base-color":{"default":"#0094CE","value":"#0094CE","edited":false},"page-titlebar-backgroundColor":{"value":"#0094CE","edited":false},"page-backgroundColor":{"value":"#fafafa","edited":false},"page-sidebar-backgroundColor":{"value":"#ffffff","edited":false},"group-textColor":{"value":"#1bbfff","edited":false},"group-borderColor":{"value":"#ffffff","edited":false},"group-backgroundColor":{"value":"#ffffff","edited":false},"widget-textColor":{"value":"#111111","edited":false},"widget-backgroundColor":{"value":"#0094ce","edited":false},"widget-borderColor":{"value":"#ffffff","edited":false},"base-font":{"value":"-apple-system,BlinkMacSystemFont,Segoe UI,Roboto,Oxygen-Sans,Ubuntu,Cantarell,Helvetica Neue,sans-serif"}},"angularTheme":{"primary":"indigo","accents":"blue","warn":"red","background":"grey","palette":"light"}},"site":{"name":"Node-RED Dashboard","hideToolbar":"false","allowSwipe":"false","lockMenu":"false","allowTempTheme":"true","dateFormat":"DD/MM/YYYY","sizes":{"sx":48,"sy":48,"gx":6,"gy":6,"cx":6,"cy":6,"px":0,"py":0}}},{"id":"5ca14fd8.ffd9b","type":"ui_group","name":"Last","tab":"711d368a.8b1ec8","order":1,"disp":true,"width":"6","collapse":false},{"id":"c907ade9.bac43","type":"rpiplc-config","model":"RPIPLC_21","name":""},{"id":"97597042.0079","type":"postgresql","z":"a2278800.0ea508","name":"","query":"SELECT * FROM myIStable order by datetime DESC;","postgreSQLConfig":"686ee02c.306a","split":false,"rowsPerMsg":1,"outputs":1,"x":330,"y":360,"wires":[["9616bb0.fc2d348","b48447e6.f139c8","81d99e93.465bb","d08407fe.6d5de8"]]},{"id":"b48447e6.f139c8","type":"ui_text","z":"a2278800.0ea508","group":"5ca14fd8.ffd9b","order":0,"width":0,"height":0,"name":"Last name","label":"Input","format":"{{msg.payload[0].name}}","layout":"row-spread","className":"","x":750,"y":400,"wires":[]},{"id":"9616bb0.fc2d348","type":"debug","z":"a2278800.0ea508","name":"Select last from db table","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload[0]","targetType":"msg","statusVal":"","statusType":"auto","x":790,"y":360,"wires":[]},{"id":"81d99e93.465bb","type":"ui_text","z":"a2278800.0ea508","group":"5ca14fd8.ffd9b","order":0,"width":0,"height":0,"name":"Last data","label":"Value","format":"{{msg.payload[0].data}}","layout":"row-spread","className":"","x":740,"y":440,"wires":[]},{"id":"e231470c.234588","type":"rpiplc-analog-read","z":"a2278800.0ea508","rpiplc":"c907ade9.bac43","pin":"I0.7","name":"","x":320,"y":160,"wires":[["73b6875.cf02878"]]},{"id":"411796a1.e76da8","type":"inject","z":"a2278800.0ea508","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"5","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"str","x":130,"y":260,"wires":[["e231470c.234588","97597042.0079"]]},{"id":"73b6875.cf02878","type":"switch","z":"a2278800.0ea508","name":"payload > 1023 ? 2 : 1","property":"payload","propertyType":"msg","rules":[{"t":"else"},{"t":"gt","v":"1023","vt":"num"}],"checkall":"true","repair":false,"outputs":2,"x":520,"y":160,"wires":[["5358c90c.ea5ef8"],["418defc9.3ae52"]]},{"id":"5358c90c.ea5ef8","type":"debug","z":"a2278800.0ea508","name":"Value under 1023","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":770,"y":100,"wires":[]},{"id":"bd13d364.a8193","type":"postgresql","z":"a2278800.0ea508","name":"","query":"INSERT INTO myIStable VALUES ('I0.7', '{{msg.payload}}', '{{msg.time}}');\n","postgreSQLConfig":"686ee02c.306a","split":false,"rowsPerMsg":1,"outputs":1,"x":970,"y":220,"wires":[[]]},{"id":"418defc9.3ae52","type":"function","z":"a2278800.0ea508","name":"Get time","func":"var now = new Date().toLocaleString(\"es-ES\");\nmsg.time = now;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","libs":[],"x":740,"y":220,"wires":[["9ea6befc.650a","bd13d364.a8193"]]},{"id":"9ea6befc.650a","type":"debug","z":"a2278800.0ea508","name":"get msg with value above 1023","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1030,"y":180,"wires":[]},{"id":"d08407fe.6d5de8","type":"ui_text","z":"a2278800.0ea508","group":"5ca14fd8.ffd9b","order":0,"width":0,"height":0,"name":"Last datetime","label":"Datetime","format":"{{msg.payload[0].datetime}}","layout":"row-spread","className":"","x":760,"y":480,"wires":[]}]

Testing

Now, let's test our application with two values.

  1. From the Raspberry Pi industrial PLC, take a cable from the 5V to the I0.7, and get the result. The value should be 1022.
  2. Now, take the cable from the 24V to the I0.7 analog input and get the result. The value should be 2047

 

​Search in our Blog

Node-RED Tutorial: How to connect PostgreSQL with Raspberry Pi PLC
Boot & Work Corp. S.L., Fernandez Queralt Martinez November 9, 2021

Looking for your ideal Programmable Logic Controller?

Take a look at this product comparison with other industrial controllers Arduino-based. 

We are comparing inputs, outputs, communications and other features with the ones of the relevant brands.


Industrial PLC comparison >>>