MySQL and NodeRed

Updated 2021-07-07 !

Sometimes the syntax for the Javascript in Node-Red is a little hard to get, despite some Googling. This was evident when i tried the run the "node-red-node-mysql" node inserting data in a MySQL database.

Also timestamps in different environments are a little complicated.

So this post will cover both areas with some code examples done on a Raspberry Pi.

Installing Apache WEB-server

There are many posts regarding installing the Apache WEB-server, needed for phpMyAdmin, but here is mine. 

Installing MySQL and phpMyAdmin

There are many "how to" installing MySQL but I followed this where you also get links to additional software as phpMyAdmin.

phpMyAdmin problems ?

I got 2 problems with phpMyAdmin. One was SQL failures within the GUI, marked with red, and was solved with a manually reinstall.

The other problem was

The $cfg[‘TempDir’] (./tmp/) is not accessible. phpMyAdmin is not able to cache templates and will be slow because of this.

Which was solved creating the missed directory and giving it full access to the default Apache user with 

sudo mkdir /usr/share/phpmyadmin/tmp/
sudo chown -R www-data:www-data /usr/share/phpmyadmin/tmp/

The phpMyAdmin config path is /usr/share/phpmyadmin/libraries/vendor_config.php

Creating the DB

I created, via the phpMyAdmin GUI, a database "Start_Stop" with one table, "Test2", containing 3 fields of different types

Field1 int(10

Field2 text

Field3 timestamp

The timestamp field, differs in different SQL databases, but here with MySQL it is almost a short ISO date "2020-11-20 21:00:35". Check the code below how to create it.

NodeRed

You need a NodeRed installation and also the mentioned "node-red-node-mysql" node. Here you find the example flow.

In the "Database" node, edit your properties for the database. If you followed my naming just add the DB user/password and deploy the flow. If done right you will get a text "Connected" below the database node.

Scenario 1

To test the connection just click on the "Scenario 1, Select" inject node and the response will be shown via the debug node, in the debug window, with a payload "payload: array[0]", confirming that's an empty table. As you se in the inject node we have created a query with the topic, "SELECT * FROM `Test2`" which did the job.

Scenario 2

Go on and click on the "Scenario 2, Inject" inject node and you will add a record to the table via the command "INSERT INTO `Test2` (`Field1`, `Field2`, `Field3`) VALUES ('22', 'text2' , '2020-11-20 21:00:35')"

Check in the phpMyAdmin admin or just click on the "Scenario 1, Select" inject node and get the response in the debug window. 

Scenario 3

Until now we used "harcoded" strings and will continue with dynamic input. Click on the "Scenario 3, Inject" inject node and you will insert new data with a fresh timestamp. Open the connected function node "Scenario 3, function" and check out the code where we build the string with dynamic input fields.

const date = new Date();

let f1 = 23;
let f2 = "text4";
let f3 = date.toISOString().slice(0, 19).replace('T', ' ');

msg.topic = "INSERT INTO `Test2` (`Field1`,`Field2`,`Field3`) VALUES ('"+ f1 +"','"+ f2 +"','"+ f3 +"')";

The constant "date" will get a date with format "2020-11-20T09:47:55.330Z" and the code  at "let f3 ...." will just keep 19 characters and substitute the "T" with a space, which is a MySQL  formated timestamp.

Scenario 4

The last code example is the is the most elegant one. As you seen in the examples "msg.topic" holds the query for the database, and the result is returned in "msg.payload", but the "msg.payload", as input, can contain an array of values to bind to the topic. Check the "Scenario 4", function node.

const date = new Date();
msg.payload={};
msg.payload.f1 = 28;
msg.payload.f2 = "text5";
msg.payload.f3 = date.toISOString().slice(0, 19).replace('T', ' ');

msg.topic = "INSERT INTO `Test2` (`Field1`, `Field2`, `Field3`) VALUES (:f1, :f2, :f3)";

I hope you now are prepared for a deeper dive in SQL sentences.....