Connect Node-Red to SQL Database

hello and welcome back to a new video on this YouTube channel in this video we will explore on how to connect our node-red to a database quickly we will be using a mySQL database at the end of this video you will be able to select all the data stored in a specified table insert new data into that table and deleting all of it if required for this tutorial we will use MySQL to install the node go to manage palette install and search for MySQL then click on install on the correct node now we wait until it's installed once it is installed you can go to the left side of your screen there you can collapse all categories and search for storage there you will find the MySQL node the next step is to set up the connection to our database so click on the pencil icon to create a new connection if your database is installed directly on your computer you can leave localhost in my case I have node red and my database running in a separate Docker container so I will need the IP address of my computer in my local network the next setting is the port if you haven't changed anything on the port configuration you can leave it as this is the default port the user and password are your login credentials to the database in my case I use the root user and my specified password last thing to do is to set the database name my database name is tutorial insert here however you call your database and the last three settings we will leave them as they are the first thing we would like to do is to select all the data that is currently stored in our table for that we will need an inject node and a function node by clicking on The Help menu of the SQL note we can see that we have to provide our SQL query in the msg.topic property all right so we give it a name and then let's start defining our query so we use the msg.topic and then we say we would like to select all the things from sensor values sensor values is just a table that I created for this tutorial now we can add a debug to the end of our SQL node so we can see what's the answer of our query will be we can see that we are already connected so that means we can now inject and we will get an answer from the database as you can see the result is an array of objects that will always be the case where an object will represent one row of your answer so my table sensor values has a timestamp and a sensor value so it will just be the value recorded and the time when it was recorded so now we want to insert some new data into our database so we use another function node where we can specify our data as well as our SQL query so we will use our msg.topic again and here we specify insert into then the table name sensor values than the keyword values and now we can specify the values according to each separate column for timestamp we use the now keyword this will just insert the current time and now we would like to insert a random value between 0 and 100. to do that we need to adjust our string so we close it off and add two plus signs so we can add some text to our string inside this plus signs we will use the math.random function this gives us a value between 0 and 1 and by multiplying that by 100 we get a value between 0 and 100. since that is a number now we will need to convert it to a string using the two fixed method which will also round our number to two decimal places and that's it for our insert state important here make sure you close the brackets after the values now we need another inject node and we can copy and paste the SQL connection here we will use another debug node so we can see what our insert statement make sure to select complete MSG object now that we insert it we can see in our payload that we have an affected rows so this means we have inserted one row you could also be more row similar and in the topic you can see exactly what you've inserted into the table if we select all we will now see that we have two data points in our database the last thing to do is deleting all the data in our table so we will again use another function node where we now specify our last SQL command so it's going to be msg.topic is equal to then delete from and now the table name so sensor values quick note here please be careful with this statement as it's going to delete all the things in our table in a future video we will have a look on how to delete one specific row foreign we will use the debug as well so we can see how much got deleted by this statement and as we see we have two affected rows so that means that we deleted two rows from our table and if we select again we will also see that there's nothing left in our database okay so that's it for this short tutorial I hope you liked it and you learned something see you next time

Channel: RuWindustries

Views: 7,445

Keywords: Node-Red, Industry 4.0, IOT, JavaScript, Databases, SQL, MySQL

Id: w1SRebmR_NY

Length: 6min 57sec (417 seconds)

Published: Sat Aug 26 2023