μSQLite module

Discussion about programs, libraries and tools that work with MicroPython. Mostly these are provided by a third party.
Target audience: All users and developers of MicroPython.
Post Reply
SpatialDude
Posts: 2
Joined: Fri Oct 15, 2021 11:37 am

μSQLite module

Post by SpatialDude » Sat Oct 16, 2021 12:12 pm

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.

User avatar
scruss
Posts: 360
Joined: Sat Aug 12, 2017 2:27 pm
Location: Toronto, Canada
Contact:

Re: μSQLite module

Post by scruss » Sat Oct 16, 2021 7:24 pm

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.

SpatialDude
Posts: 2
Joined: Fri Oct 15, 2021 11:37 am

Re: μSQLite module

Post by SpatialDude » Sat Oct 16, 2021 10:49 pm

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.

User avatar
scruss
Posts: 360
Joined: Sat Aug 12, 2017 2:27 pm
Location: Toronto, Canada
Contact:

Re: μSQLite module

Post by scruss » Sun Oct 17, 2021 10:15 pm

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.

easylab4kids
Posts: 20
Joined: Mon Aug 12, 2019 10:58 am
Location: Ellisras, Suid-Afrika ("South Africa")

Re: μSQLite module

Post by easylab4kids » Tue Feb 08, 2022 9:31 pm

Can you make it available with upip?
like:

pi$>micropython upip import usqlite

I managed to compile Micropython + usqlite on my Raspberry Pi 8-) .
met vriendelijke groet,
Easylab4kids.nl

Post Reply