Exercise: Connect a database in Node-Red

SOLUTION

The following file contains the Node-RED flow for this exercise: medicaments.json.

Install MySQL

In the virtual machine, execute the following commands inside the terminal: 
$ sudo apt upgrade
$ sudo apt install mysql-server

Configure MySQL

First think to a good password. Then execute mysql as root. In the following sequence of commands, you have to replace password by your actual password. 
$ sudo mysql

mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
Output
+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

Change the password to your own password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

mysql> FLUSH PRIVILEGES;

mysql> exit;

Create the database

$ mysql -u root -p
mysql> CREATE DATABASE medizininformatik;
mysql> USE medizininformatik;
mysql> exit;

Populate the database

Download the following SQL commands: medinf.sql. (you have to save the file in the directory where you execute the command, i.e. the home directory and not the download directory). 
$ mysql -u root -p medizininformatik < medinf.sql

Install MySQL in Node-RED

You have to type this code inside the terminal (Linux) 
~$ cd .node-red/
~/.node-red$ npm install node-red-node-mysql

Connect the database

Create a flow having the following controls:
  • Inject node
  • Function node to initialize the sql
  • Mysql Node
  • Debug node for the output
The SQL query is select * from medicament.
It should show the list of all the medicaments of the hospital

Connect the database to a http request

Modify the previous solution to answer to the following url /getMedicaments. The output is written in form of a JSON text.

Access to the details of one medicament

Change your script to see only one medicament if /getMedicament?medicament=1 and the list if no medicament number is given.
BACK TO TOP © 2014-2021 Emmanuel Benoist