There are some databases which are suitable for use with Arduino, ESP32 etc microcontrollers. We can indeed use MySQL (and PHP) to create IoT projects like this one. However that approach requires own API server and such method has limitations. It is a complicated method and presents app and web development moving away from PHP and MySQL/MariaDB. These days it is convenient to use Create React App to build a frontend.
There are NoSQL database which provides API to directly interact hence we can avoid creating a middleware or API for simple projects. For complicated projects, we always need an app with CRUD functions. Also, this database out of the box delivers JSON output and few of them have dashboards with graphing.
MongoDB, InfluxDB, CrateDB, RethinkDB etc are some of the NoSQL databases which are easy to be used in IoT projects. Among them, InfluxDB has more users from Arduino and ESP32 world. InfluxDB can be self-hosted or their pay-as-you-go tier of the cloud-hosted database can be used for developmental purposes.
---
InfluxDB Already Have Libraries and Examples For ESP32/Arduino
InfluxDB has community-driven documentation for the basic steps, such as installing the library:
1 2 | https://www.influxdata.com/blog/getting-started-arduino-influxdb/ https://docs.influxdata.com/influxdb/cloud/api-guide/client-libraries/arduino/ |
Also, the Arduino website has cross-references:
1 2 3 | https://www.influxdata.com/blog/getting-started-arduino-influxdb/ https://docs.influxdata.com/influxdb/cloud/api-guide/client-libraries/arduino/ https://reference.arduino.cc/reference/en/libraries/esp8266-influxdb/ |
This is the GitHub repository:
1 | https://github.com/tobiasschuerg/InfluxDB-Client-for-Arduino/tree/master |
You can create a free account on InfluxDB, install the library, and check the official examples of Arduino codes. This is a basic example of a sketch:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 | #if defined(ESP32) #include <WiFiMulti.h> WiFiMulti wifiMulti; #define DEVICE "ESP32" #elif defined(ESP8266) #include <ESP8266WiFiMulti.h> ESP8266WiFiMulti wifiMulti; #define DEVICE "ESP8266" #endif #include <InfluxDbClient.h> // WiFi AP SSID #define WIFI_SSID "ssid" // WiFi password #define WIFI_PASSWORD "password" // InfluxDB server URL. Don't use localhost, always server name or ip address. // E.g. http://192.168.1.48:8086 (In InfluxDB 2 UI -> Load Data -> Client Libraries), #define INFLUXDB_URL "influxdb-url" // InfluxDB 2 server or cloud API authentication token (Use: InfluxDB UI -> Load Data -> Tokens -> <select token>) #define INFLUXDB_TOKEN "toked-id" // InfluxDB 2 organization id (Use: InfluxDB UI -> Settings -> Profile -> <name under tile> ) #define INFLUXDB_ORG "org" // InfluxDB 2 bucket name (Use: InfluxDB UI -> Load Data -> Buckets) #define INFLUXDB_BUCKET "bucket" // InfluxDB v1 database name //#define INFLUXDB_DB_NAME "database" // InfluxDB client instance InfluxDBClient client(INFLUXDB_URL, INFLUXDB_ORG, INFLUXDB_BUCKET, INFLUXDB_TOKEN); // InfluxDB client instance for InfluxDB 1 //InfluxDBClient client(INFLUXDB_URL, INFLUXDB_DB_NAME); // Data point Point sensor("wifi_status"); void setup() { Serial.begin(115200); // Connect WiFi Serial.println("Connecting to WiFi"); WiFi.mode(WIFI_STA); wifiMulti.addAP(WIFI_SSID, WIFI_PASSWORD); while (wifiMulti.run() != WL_CONNECTED) { Serial.print("."); delay(500); } Serial.println(); // Set InfluxDB 1 authentication params //client.setConnectionParamsV1(INFLUXDB_URL, INFLUXDB_DB_NAME, INFLUXDB_USER, INFLUXDB_PASSWORD); // Add constant tags - only once sensor.addTag("device", DEVICE); sensor.addTag("SSID", WiFi.SSID()); // Check server connection if (client.validateConnection()) { Serial.print("Connected to InfluxDB: "); Serial.println(client.getServerUrl()); } else { Serial.print("InfluxDB connection failed: "); Serial.println(client.getLastErrorMessage()); } } void loop() { // Store measured value into point sensor.clearFields(); // Report RSSI of currently connected network sensor.addField("rssi", WiFi.RSSI()); // Print what are we exactly writing Serial.print("Writing: "); Serial.println(client.pointToLineProtocol(sensor)); // If no Wifi signal, try to reconnect it if (wifiMulti.run() != WL_CONNECTED) { Serial.println("Wifi connection lost"); } // Write point if (!client.writePoint(sensor)) { Serial.print("InfluxDB write failed: "); Serial.println(client.getLastErrorMessage()); } //Wait 10s Serial.println("Wait 10s"); delay(10000); } |
