DOCSEARCHbyAlgolia
Submit Docs

esp32_arduino_sqlite3_lib

repository·master·Indexed 19 days ago

https://github.com/siara-cc/esp32_arduino_sqlite3_lib

A general-purpose SQLite3 library for ESP32 that enables full SQL database capabilities using SPIFFS or SD cards. It supports the standard SQLite C API, B+Tree indexing for large datasets, and includes text compression utilities via Shox96 and Unishox. The library provides a VFS layer with specific mount points for SD_MMC (/sdcard), SD (/sd), and SPIFFS (/spiffs).

Tokens
1.2K
Snippets
3
Records
7
Agent score
16%

What's inside esp32_arduino_sqlite3_lib

  1. Important limitations and best practices

    master

    Multi-threading / Multi-core

    Locking is not implemented. This library cannot be reliably used in multi-threaded or multi-core environments except for read-only operations.

    Flash Memory Wear

    Flash memory (SPIFFS/SD) has a limited number of write/erase cycles per sector (typically 10,000 to 100,000). While ESP32 supports wear-levelling, be cautious with write-intensive database projects to avoid premature hardware failure.

    Performance

    While SQLite can handle massive datasets, it may be slow on large datasets on ESP32. For example, retrieving a row from a 10-million-row dataset using an index can take approximately 700 ms.

  2. Install the Sqlite3 Arduino library for ESP32

    master

    To install the library, download it and unzip it into your ESP32 SDK's libraries folder. The exact path depends on your operating system:

    • Windows: C:\Users\(username)\AppData\Roaming\Arduino15
    • Linux: /home/<username>/.arduino15
    • MacOS: /home/<username>/Library/Arduino15

    Navigate to packages/esp32/hardware/esp32/<version>/libraries within the Arduino15 folder to place the unzipped library.

    Note: You must have the ESP32 core for Arduino installed. If not, install it from https://github.com/espressif/arduino-esp32.

    Windows: C:\Users\(username)\AppData\Roaming\Arduino15
Linux: /home/<username>/.arduino15
MacOS: /home/<username>/Library/Arduino15

  3. Access SQLite databases on SPIFFS, SD, or SD_MMC

    master

    This library allows you to use the standard SQLite C API (e.g., sqlite3_open) to interact with database files stored on different file systems.

    1. Initialize the File System

    Before calling SQLite APIs, you must initialize the underlying storage medium:

    • For SD_MMC (High-speed 4-bit mode): SD_MMC.begin();
    • For SD (SPI mode): SPI.begin(); SD.begin();
    • For SPIFFS: SPIFFS.begin();

    2. Use Correct Mount Points

    The library uses a VFS layer with specific default mount points. You must prefix your database filenames with these paths in sqlite3_open():

    • SD_MMC: /sdcard (e.g., sqlite3_open("/sdcard/my.db"))
    • SD (SPI): /sd (e.g., sqlite3_open("/sd/my.db"))
    • SPIFFS: /spiffs (e.g., sqlite3_open("/spiffs/my.db"))
    // Example for SD_MMC
SD_MMC.begin();
sqlite3_open("/sdcard/my.db", &db);

// Example for SPI
SPI.begin();
SD.begin();
sqlite3_open("/sd/my.db", &db);

// Example for SPIFFS
SPIFFS.begin();
sqlite3_open("/spiffs/my.db", &db);

  4. Compress text data with Shox96

    master

    Shox96 is a compression technique for short strings (ASCII 32-126, CR, LF, TAB, and space). It can achieve up to 40% size reduction.

    Warning: Attempting to decompress a blob that was not compressed with shox96_0_2c() will crash the program. It does not support binary characters.

    Functions:

    • shox96_0_2c(): Compresses text into a blob.
    • shox96_0_2d(): Decompresses a blob back to text.
    -- Create table
create table test (b1 blob);

-- Compress and insert
insert into test values (shox96_0_2c('Hello World'));

-- Decompress and select
select txt, length(txt) txt_len from (select shox96_0_2d(b1) txt from test);

-- Check compressed size
select length(b1) compressed_len from test;

  5. Compress Unicode text with Unishox

    master

    Unishox is a compression technique for short Unicode strings (UTF-8). It can achieve up to 40% size reduction.

    Warning: Attempting to decompress a blob that was not compressed with unishox1c() will crash the program.

    Functions:

    • unishox1c(): Compresses UTF-8 text into a blob.
    • unishox1d(): Decompresses a blob back to UTF-8 text.