MicroPython Forum (Archive)

Hi MicroPython developers.

I've been lurking here for a while and finally have something I hope is worthwhile to share with the MicroPython community, namely μSQLite for MicroPython or simply usqlite, a SQLite based database module for MicroPython.

Some may ask why "would you want to a SQL database on a microcontroller device?". In many cases simple text files like CSV and other formats will work perfectly well for reading and storing data. In my case I want to be able to record data in a structured format as well as be able to look up reference data quickly which is stored in in indexed tables. I also want the databases to be "ready to use" in other applications without having to import or convert to other formats.

The implementation of μSQLite tries to take advantage of MicroPython's clever memory management and virtual file system support to achieve effective performance and functionality in constrained device environments.

The module is beta and under development but so far initial tests are promising. I've been primarily targeting the Raspberry Pi Pico and have been pleased so far in how it has been working out. Typical peak memory usage in my test cases has been under 32Kb. In practice, this would of course vary more widely depending on the complexity of the application and the SQL statements being executed.

A description of the module and how to get started and compile it can be found in the GitHub repository - https://github.com/spatialdude/usqlite/ ... /README.md

Please feel free to try it out. Feedback is welcome.

I look forward to hearing from the the first person that jumps in and tries it out!! I hope the instructions are simple and clear enough on what to do.

Wow! This actually works! Thank you.

Was fairly easy to compile, but I submitted a pull request that might help others build the firmware.

From your user name, you weren't considering R-Tree support, were you? I can't imagine that being able to fit at all.

Thanks for being the first to try it out and its great to hear that it worked

Funny you mention R-Tree. I initially included R*tree support in the builds. It can be enabled using the SQLITE_ENABLE_RTREE option in usqlite_config.h I still need to spend a bit more time to better understand and measure the additional memory pressure the r*tree routines create so decided to have it off by default for now. There is of course also an increase in the module's firmware footprint from the extra code. On my "geo-projects" where I also have a GPS module hooked up, I'd like to use the r*tree capabilities to store and lookup data from spatial databases such as geopackages. It might be a bit of a squeeze on the Pico but on platforms with a bit more space to play in it should work nicely.

Also thanks for the PR. I'll merge that in but probably improve the notes further. It may be a bit misleading in what I wrote in cases where developers aren't familiar with additional commands and requirements that need to be executed depending on the operating system and target port.

R-Trees are perhaps a bit heavy for a micro-platform, but even the idea of having spatially-indexed data on a micro-controller blows my mind.

Can you make it available with upip?
like:

pi$>micropython upip import usqlite

I managed to compile Micropython + usqlite on my Raspberry Pi .