As the Teensy doesn't have an OS, and associated OS services, you need to provide all the interfaces for things the OS would normally provide-- including file access. In the case of SQLite, this is done via the "VFS" interfaces (https://www.sqlite.org/vfs.html). Compiling the core SQLite library will require turning off the default VFS modules (I assume this can be done with #defs), and integrating one that works with the SPIFFS libs. I'd guess you can modify the "unix-none" VFS module to suite your needs, replacing the standard file/OS calls with calls to the SPIFFS and other libraries.

Given that the Teensy mostly uses the Arduino environment, you might also be able to find an existing VFS module that can be used, or used with minimal modification. This project looks like it might be a good starting point, although it includes the whole SQLite source code (and is about a year behind, at 3.31.1), not just the required VFS module: https://github.com/siara-cc/esp32_arduino_sqlite3_lib/tree/master/src

Overall, a project like this is going to take some non-trivial understanding of how SQLite is ported to different environments, and how to integrate it into the Teensy environment. I'd start by spending some time understanding the VFS interfaces, and looking at the source code for the existing VFS modules used by higher level OSes. Assuming you're comfortable with the SPIFFS libraries, it should be more apparent how to hook everything together. It's not an out-of-the-box kind of thing, however.

Thank you for showing me the right direction.

When documenting myself a little, I found the "test_onefile" VFS. Would it be more suitable or easier than the "unix-none" one you previously suggested considering my use-case?

Thank you 1000 times!

The test_onefile.c VFS provides the barest VFS, without locking support, just like unix-none. I cannot tell from your initial post (or any other) whether your use cases include multi-threaded or possibly reentrant access. Neither starting point does anything with locking calls, which dooms multi-threaded use cases unless you arrange your own locking outside of the library. For mere file I/O, both are trivially easy to adapt for a different set of underlying APIs, so I think your concern with "easier" may be misplaced.

I would be concerned also with write and rewrite performance. SD storage devices can fail quickly if the system using them keeps rewriting the same block a lot. (I speak as a student of the hard knocks school on this.) You might, depending on the application, do well keep the DB in fast, durable memory and use a cache flushing strategy to reduce wear on the SD memory. That would also largely bypass the speed issues with SD memory devices.

Thanks Larry!

I'm currently studying this ESP32 VFS that jkreibich found. I'm halfway through it and it seems it can be pretty much compiled with arm-none-eabi to target the Teensy board without issues. A cosmetic renaming of ESP32* functions into Teensy* is the only modification I've done so far.

Apparently the author of the ESP32 VFS decided to make the locking and unlocking functions (lock(2), unlock(2), checkreservedlock(2)) no-ops even though the ESP32 has 2 cores and thus true parallellism is achievable. It makes sense considering the embedded applications that SQLite should power: a global pointer to the sqlite file and an I/O attempt every now and then for saving or aggregating data coming from a sensor. A crash or weird behavior in case of parallel access to the sqlite instance is somehow expected.

SPIFFS is a very lightweight filesystem for low-power devices and has wear-levelling. I'd say a sqlite3 VFS module on top of it is then sufficiently abstracted from the raw SD cells given the performance and durability expectations of such systems.