How to use SQLite with ESP32 PLC

With the help of "esp32_arduino_sqlite3_lib" library
September 12, 2023 by
How to use SQLite with ESP32 PLC
Boot & Work Corp. S.L., Joan Vilardaga Castro


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.

What is SQLite?

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.


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

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 example program

    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_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: ");
    else {
    Serial.println("Opened database successfully");
    return rc;

    void closeDb(sqlite3* 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: ");
    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);

    const char* const sI0_12 = "I0.12";
    const char* const sI0_11 = "I0.11";
    void setup() {

    if (! {


    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.

    ​Search in our Blog

    How to use SQLite with ESP32 PLC
    Boot & Work Corp. S.L., Joan Vilardaga Castro September 12, 2023
    Share this post

    Looking for your ideal Programmable Logic Controller?

    Take a look at this product comparison with other industrial controllers Arduino-based. 

    We are comparing inputs, outputs, communications and other features with the ones of the relevant brands.

    Industrial PLC comparison >>>