How to use SQLite with ESP32 PLC

The integration of SQLite within ESP32, a powerful microcontroller widely used in industrial IoT applications, revolutionizes the storage capabilities of embedded systems. By incorporating SQLite, a lightweight and feature-rich relational database management system, developers can unlock enhanced data persistence, query functionality, and security at the edge.

This blog explores the rationale behind leveraging SQLite in ESP32, delves into its practical implementation techniques, and highlights its suitability for resource-constrained environments. Explore with us how the fusion of SQLite and ESP32 empowers industrial IoT solutions with efficient data management and real-time decision-making.

Because SQLite has a serverless approach, you can integrate SQLite into ESP32-based projects. This enables you to leverage the power of a relational database management system directly on the ESP32 device, for things like lightweight logging, advanced data queries and variable saving across reboots.

For this demo, we are using the ESP32 21+ Industrial PLC, but any ESP32 PLC with an SD card will be compatible.

We will need to install the sqlite3 command-line tool on your PC, by using a package manager (like "apt") or by downloading it from the official SQLite website at https://sqlite.org/cli.html.

Finally, to install the esp32_arduino_sqlite3_lib library you will have to use the Library Manager in the Arduino IDE (search for "esp32_sqlite").

The objective of the ESP32 program we'll develop is to log the values of two specific pins on the ESP32 microcontroller and store them in a table. This functionality serves a wide range of applications where continuous monitoring and recording of pin state changes is necessary.

While the specific use case of logging pin values may be of interest to certain readers, the broader takeaway from this blog is the integration of SQLite with the ESP32 and the potential it holds for various data logging applications. Whether you're monitoring environmental conditions, tracking sensor readings, or collecting any other type of data, understanding how to utilize SQLite with an ESP32 and an SD card opens up a world of possibilities.

First of all, we have to create the database in the PC with a SQL schema. Open a command prompt or terminal and navigate to the directory where you want to create the database file. Create a schema.sql file and paste this code:

PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS analog_pins (
       id INTEGER PRIMARY KEY,
       pin VARCHAR(5) UNIQUE
);

CREATE TABLE IF NOT EXISTS analog_log (
       id_pin INTEGER,
       value INTEGER,
       timestamp_log DATETIME,
       FOREIGN KEY (id_pin) REFERENCES analog_pins(id)
);

INSERT INTO analog_pins (pin) VALUES("I0.12");
INSERT INTO analog_pins (pin) VALUES("I0.11");

With the schema defined, you can create the database with the command sqlite3 web.db < schema.sql. Now, format an SD card using the FAT32 file system (if it isn't already in FAT32), and put the web.db file in the root of the SD. Extract the SD and put it inside the ESP32 PLC, and upload the following program:

#include "SD.h"

#include <sqlite3.h>
#define DB_NAME "web.db"

#include <RTC2.h>

int openDb(sqlite3** db) {
  int rc = sqlite3_open("/sd/" DB_NAME, db);
  if (rc) {
     Serial.print("Can't open database: ");
     Serial.println(sqlite3_errmsg(*db));
  }
  else {
    Serial.println("Opened database successfully");
  }
  return rc;
}

void closeDb(sqlite3* db) {
  sqlite3_close(db);
}

int execDb(sqlite3* db, const char* query) {
  char *zErrMsg = NULL;
  int rc = sqlite3_exec(db, query, NULL, NULL, &zErrMsg);
  if (rc != SQLITE_OK) {
     Serial.print("SQL error: ");
     Serial.println(zErrMsg);
     sqlite3_free(zErrMsg);
   }
   else {
     Serial.printf("Operation done successfully\n");
   }
   return rc;
}

#define START_QUERY "INSERT INTO analog_log SELECT (SELECT id FROM analog_pins WHERE pin='"
#define MAX_LENGTH_QUERY sizeof(START_QUERY)+32
template <const int PIN>
void read_analog(void* pvParameter) {
  pinMode(PIN, INPUT);
  sqlite3 *db;
  char query[MAX_LENGTH_QUERY];
  const int occupied_query_bytes = snprintf(query, sizeof(query), START_QUERY "%s'),", (const char* const)pvParameter); 
  char* parameters = query + occupied_query_bytes;
  while (1) {
    int r = analogRead(PIN);
    auto timestamp = RTC.getTime();
    snprintf(parameters, MAX_LENGTH_QUERY-occupied_query_bytes, "%d,%ld;", r, timestamp);
    if (openDb(&db) != SQLITE_OK) break;
    execDb(db, query);
    closeDb(db);
    vTaskDelay(5000);
  }
}

const char* const sI0_12 = "I0.12";
const char* const sI0_11 = "I0.11";
void setup() {
  Serial.begin(115200);
  SD.begin(13);

  if (!RTC.read()) {
    RTC.setTime(0);
  }
  
  sqlite3_initialize();

  xTaskCreate(read_analog<I0_12>, "TaskI0.12", 8192, (void*)sI0_12, 0, NULL);
  xTaskCreate(read_analog<I0_11>, "TaskI0.11", 8192, (void*)sI0_11, 0, NULL);
}

void loop() {}

This program creates two tasks. Each task is responsible for reading the value of an analog pin every 5 seconds and storing the readings inside an SQLite database. It also uses the RTC library to get the timestamps for every read, so you can know for certain when the value has been read.