Here some basic examples of working with MySQL database in Node Red using SQL are illustrated. Example includes how to insert new data into the database and how to retrieve data from the database. Also it is shown that we can use either the inject node or function node to perform the database operation.
Node-Red is open source platform to create IoT application. In IoT application we often need to store sensor data and other log information in a database such as humidity and temperature data(see Node Red IoT with Arduino DHT11). So the example provided here can be helpful to build internet of things application with node red utilizing a database.
In order to follow the examples here the node-red-node-mysql library has to be installed either from manage palette in node red as shown below or using npm install.
Also you should have a working database with username and password access to it. The following shows database called
Here two simple methods to inject sql query command in node red are illustrated. One is use the inject node and the other is to use the function node.
The following shows inject node and mysql node. Here the SQL query commands are written in the inject node. When using the inject node, we use the msg.topic and msg.payload fields
because the mysql node uses topic as sql command and payload as values to be inserted.
And the following shows function node with mysql node. Here the inject node is used like a switch only to trigger the function node. The function node contains the sql commands which gets executed when the inject node is pressed.
First we have to setup the database information in the mysql node. Fill up the form in the configuration window in mysql according to your credentials and database name like testDB used here.
1. Inserting data using SQL in Node Red
Suppose we want to insert data into the database testDB.
Here is how we can use the function node to insert values into the database.
The inject node called query has a payload of q.
And the actual SQL query inside the function node called query function is shown below.
The code is below. The table name is testtable. The table has id and comment field but the id is auto-increment so we don't need to specify it. The value inserted into the comment field is my message. Note using "message string" did not work but single quote as used did work.
if(msg.payload == 'q'){
msg.topic = "INSERT INTO testtable(comment) VALUES ('my message')";
return msg;
}
else{
return msg;
}
The updated database is shown below.
In the insert node we can use the msg.payload and msg.topic field to specify the sql command and the values to written into the table as follows.
Using the inject node only we can do the same insert into sql command.
The command is send vis the ms.topic option. The code is below.
INSERT INTO testtable(comment) VALUES ('another message')
2. Retrieving data using SQL in Node Red
Another important task is retrieving data from the database. SQL statement for this is SELECT .....FROM.....We can read all data, we can read specific column(s) or just retrieve selected rows and columns.
To retrieve all columns we have the following SQL query in the function node.
if(msg.payload == 'q'){
msg.topic = "SELECT comment FROM testtable";
return msg;
}else{
return msg;
}
The debug node shows the retrieved values.
To retrieve the data from 2nd row 1st column(the only column here) the function node code is below.
if(msg.payload == 'q'){
msg.topic = "SELECT comment FROM testtable WHERE id=2";
return msg;
}
else{
return msg;
}
The debug node shows this.
To retrieve all the comments we use the wildcard * as follows.
if(msg.payload == 'q'){
msg.topic = "SELECT * FROM testtable ORDER BY comment";
return msg;
}
else{
return msg;
}Again the debug node can be used to see the data retrieved from the database.
All the above codes used in function node can also be used with inject node as illustrated in the first example above.
So in this way we can insert daa and retrieve data from MySQL database usig SQL query in Node-Red.
Next see other Node Red Tutorials:
