MySQL with Arduino and Node-Red

In this blog I would like to document a project that I did with my kids. The goal of the project was to learn SQL in a couple of different environments.

We did most of our initial testing/learning directly on the MySQL server. Once we got everything worked out we moved to Arduino, then Node-Red.

For our MySQL testing we used alwaysdata.com which has a free low use account. They run MariaDB which looks exactly like MySQL.

MySQL Setup

The plan was to have the Arduino module write 3 values:

randint – a random integer from 1-100
saw – a saw tooth integer from 0-100
sstatus – a text value of : HIGH, OKAY or LOW

We didn’t want to generate a time stamp from Arduino, instead we wanted MySQL to generate it. Our SQL structure was:

The field “thetime” was defined as a timestamp with CURRENT_TIMESTAMP as the default. By doing this we only needed to INSERT the Arduino data and MySQL would add the current timestamp.

After the Arduino Tables was created and tested we were able to start inserting data from Arduino.

Arduino and MySQL

There is an excellent MySQL library for Arduino, that can be added via the Arduino IDE “Manage Libraries” option.

Our Arduino code wrote the 3 values to MySQL every 10 seconds.

/*
  MySQL Connector/Arduino Example : connect by wifi
*/
#include <ESP8266WiFi.h>           // Use this for WiFi instead of Ethernet.h
#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>

IPAddress server_addr(111,222,111,111);  // IP of the MySQL *server* here
char user[] = "yourusername";              // MySQL user login username
char password[] = "yourpassword";        // MySQL user login password

// WiFi card example
char ssid[] = "xxxxx";         // your SSID
char pass[] = "xxxxx";     // your SSID Password

WiFiClient client;                 // Use this for WiFi instead of EthernetClient
MySQL_Connection conn(&client);
MySQL_Cursor* cursor;

int saw = 0;

void setup()
{
  Serial.begin(9600);

  // Begin WiFi section
  Serial.printf("\nConnecting to %s", ssid);
  WiFi.begin(ssid, pass);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }

  // print out info about the connection:
  Serial.println("\nConnected to network");
  Serial.print("My IP address is: ");
  Serial.println(WiFi.localIP());

  Serial.print("Connecting to SQL...  ");
  if (conn.connect(server_addr, 3306, user, password))
    Serial.println("OK.");
  else
    Serial.println("FAILED.");
  
  // create MySQL cursor object
  cursor = new MySQL_Cursor(&conn);
}

void loop()
{
  // Sample insert query
  //
  char INSERT_SQL[] = "INSERT INTO datal (randint, saw, sstatus) VALUES ('%d','%d','%s')";
  char query[128];
  int randint = random(100);


saw++;
if (saw == 100){
  saw = 0;
}

  if (randint <= 10){
     sprintf(query, INSERT_SQL, randint, saw, "LOW");
  } else if (randint >= 90){
     sprintf(query, INSERT_SQL, randint, saw, "HI");
  } else {
     sprintf(query, INSERT_SQL, randint, saw, "OKAY");
  }
  
  Serial.println(query);

  if (conn.connected())
    cursor->execute(query);


  // Sample select query - Get the last 2 values and show them 
  // Table: arduino1 - this is written to from Node-Red 

  int arddata;
  // Initiate the query class instance
  row_values *row = NULL;
  MySQL_Cursor *cur_mem = new MySQL_Cursor(&conn);

  char squery[] = "SELECT thetime,arddata FROM arduino1 ORDER BY thetime DESC limit 2;";
  cur_mem->execute(squery);
  // Fetch the columns (required) but we don't use them.
  column_names *columns = cur_mem->get_columns();

  do {
    row = cur_mem->get_next_row();
    if (row != NULL) {
      //thetime = (row->values[0]);
      arddata = atoi(row->values[1]);
      Serial.print("Time: "); Serial.print(row->values[0]); Serial.print(" data: "); Serial.println(arddata);
      
    }
  } while (row != NULL);
  delete cur_mem;
  
  delay(10000);
  
}

MySQL Views

The table(s) that we created were nice and simple for Arduino data, but they couldn’t be used directly for any types of statistics like daily/hourly/minute averages/maxs or minimums. So we created some MySQL views.

The first things that we needed to do was create some “time” columns like HOUR and MINUTE.

CREATE VIEW v_raw as 
 SELECT HOUR(thetime) as HOUR, 
   MINUTE(thetime) as MINUTE, 
   randint, saw, sstatus from datal 
   order by thetime DESC

This SQL statement creates a view (v_raw) that shows the hour, minute fields with data ordered from newest to oldest. The query of : SELECT * FROM v_raw; gives:

We then created a number of statistic views like:

CREATE VIEW v_min AS 
  SELECT HOUR(thetime) as HOUR, MINUTE(thetime) as MINUTE, 
    AVG(randint), AVG(saw) 
   from datal 
     GROUP BY HOUR,MINUTE ORDER BY thetime LIMIT 720

This gave us some minute averages:

Node-Red with MySQL

The component node-red-node-mysql can be added manually or via the “Manage Palettes” option.

For our project we wanted to show:

Last 100 Arduino values
Latest Average Minute Values
Latest Average Hourly Value

Then we wanted to Insert a new 0/1 value that could be read back in the Arduino. This inserted value could be used to turn on a relay connected to the the Arduino (like a start a fan, turn on a light etc.)

The logic used Dashboard buttons (to select an action). MySQL nodes do the SELECT or INSERT command, and a Dashboard Table to show the results.

The buttons are configured to pass the SQL views statement to the MySQL nodes, with the results showing in a Dashboard table node.

After the logic is complete, use the Deploy button to make the logic live. The web dashboards can be viewed at: http://yourNodeRed_IP:1880/ui . Below is an example of 1) Minute Averages, and 2) Last 100 (raw values).

Final Comments

For this project we kept things simple, and used tables to show the results. As a next step it would be good to show the results in a trend chart. In a previous blog I looked at putting SQLite into a Node-Red chart. I would use this technique with MySQL data.