How to use SQLite with ESP32 PLC

SQLite runs directly on any Industrial Shields ESP32 PLC equipped with an SD card, giving you a full relational database without a server. This guide shows how to create the schema on your PC, load it onto the SD card, and log analog pin readings with timestamps using the esp32_arduino_sqlite3_lib library.

SQLite's serverless, file-based architecture makes it ideal for edge applications: lightweight logging, indexed queries across millions of records, and data persistence across reboots — all within the ESP32's constraints.

What Is SQLite and Why Use It on an ESP32 PLC?

SQLite is a popular open-source, serverless, embedded relational database management system that is widely used in various software applications. It is a self-contained, zero-configuration database engine that operates directly from disk files, eliminating the need for a separate database server process.

Despite its small size, SQLite provides many powerful features found in larger database systems. It supports standard SQL queries, transactions, data types, indexes, and various other database operations. Additionally, SQLite offers ACID (Atomicity, Consistency, Isolation, Durability) properties, ensuring data integrity and reliability.

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.

Hardware and Libraries Required to Use SQLite on an ESP32 PLC

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").

How to Log Analog Sensor Data to SQLite on an ESP32 PLC SD Card

This example logs readings from two analog pins into an SQLite database on an SD card, with RTC timestamps on each entry so you can query the full history from your PC.

The schema creates two tables: analog_pins stores the pin identifiers, and analog_log records each reading with its value and timestamp. Two pins are pre-inserted: I0.12 and I0.11.

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 and put the web.db file in the root of the SD card. Insert the SD into 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() {}

The program spawns two FreeRTOS tasks — one per analog pin — each running independently. Every 5 seconds, a task reads the pin voltage, retrieves the current RTC timestamp, and inserts a row into analog_log. The database is opened and closed on each write to ensure data integrity across power cycles.

To verify the logged data, remove the SD card, connect it to your PC, and query the database:

sqlite3 web.db "SELECT * FROM analog_log ORDER BY timestamp_log DESC LIMIT 10;"