MakerSpace

If you need to store long-term historical data, you can cobble together some Arduino modules, sensors, and displays and get them all to talk to an SQL server.

- By Pete Metcalfe

Figure 1: This Arduino project logs data to an SQL database.

In most Arduino projects, devices use messaging protocols like Message Queuing Telemetry Transport (MQTT) [1] or custom dashboard platforms such as Arduino Cloud [2] or ThingsBoard [3] to talk to each other. While those two approaches offer excellent solutions for viewing short-term sensor data, they have limitations when used for long-term historical storage.

A common solution that supports historical storage is to connect MQTT brokers to datalogging packages like Node-RED, InfluxDB, or Grafana. Another approach is to install SQL drivers on the Arduino controllers and then pass sensor data directly to a database server. There are Arduino C/C++ libraries for MariaDB/ MySQL, Microsoft SQL, PostgreSQL and even local SQLite 3 databases. SQL-based solutions have some useful benefits such as:

• No intermediate storage is required

• Access raw historical data or aggregate calculations

• Write custom queries

• Use back-end views and procedures to minimize dashboard code

Depending on your project requirements, using an SQL server could be a good fit for your next Internet of Things (IoT) project.

Figure 2: The garage table stores sensor data and a timestamp. The thetime field will default to the current time when a new record is inserted.

This article presents a sensor project that connects Arduino modules to a MySQL/ MariaDB server (Figure 1). Dashboards showing current values and historical trend charts will be created using some SQL widgets in Node-RED.

SQL Libraries for Arduinos

There are a few C/ C++ SQL libraries to choose from. For this project, I was quite happy with Charles Bell’s MySQL Connector Arduino package [4]: I found it straightforward, and it has some good examples. This and the other SQL packages can be installed directly using the Arduino IDE’s library manager.