Simple database using btree and json

[ctimmer]

While testing an sdcard interface I came up with an idea for a database module that allows for table(s) with a primary key and row data.

The btree implementation only allows for a key/value pairs. The key is created by concatenating the table name and primary key. The row data is a dictionary converted to a json string. Lots of limitations but it works.

The module also includes a short demo (test.db) at the end.

Take a look,
Curt

[jonnor]

Cool, thanks for sharing! There really is a need for some convenient and high-level on-device storage options for MicroPython. This is an interesting starting point, and might actually be enough for some usecases. It is kind-of a document store, like MongoDB etc.

Btw, we have a MessagePack implementation at https://github.com/peterhinch/micropython-msgpack
That could maybe be an alternative to JSON in this usecase? MessagePack is conceptually very similar to JSON, but has a more compact binary encoding - should at least be smaller on disk. Theoretically it can also be quicker to encode/decode, though JSON implementation is in C and msgpack in Python, so that might negate the theoretical benefit in execution time.

[ctimmer]

I noticed rp2 does not include btree by default. I've seen a few questions and suggestions on including it in the firmware compile but I haven't seen any reports of success. Is this possible? I did some poking around the btree source and didn't see any malloc references, may not have searched at every line.

Curt

[Josverl]

Is this possible?

If you read the last comment from @Gadgetoid in above thread , yes it's possible, if your willing to blemish your virtue

[ctimmer]

Blemish my virtue? That's not much of a challenge. My Pico2 W test results below.

How much of a project would it be to upgrade btree to micropython's preferred standards. I assume there are MP alternatives to malloc/free.

Again, thanks for all the help,
Curt

MPY: soft reboot
Using umsgpack module
(sysname='rp2', nodename='rp2', release='1.26.0-preview', version='v1.26.0-preview.254.gb725c26b4.dirty on 2025-08-28 (GNU 12.2.1 MinSizeRel)', machine='Raspberry Pi Pico 2 W with RP2350')
Removed: test.db
read: {'dob': 19560606, 'occupation': 'retired', 'key': '000100', 'name': 'Curt'}
read: None
all keys: ["000100'", "000500'", "001000'", "010000'"]
rows: [{'dob': 19200101, 'occupation': 'Three stooges', 'key': '000500', 'name': 'Moe'}, {'dob': 19250303, 'occupation': 'Three stooges', 'key': '001000', 'name': 'Curly'}, {'dob': 19210202, 'occupation': 'Three stooges', 'key': '010000', 'name': 'Larry'}]
next: {'dob': 19250303, 'occupation': 'Three stooges', 'key': '001000', 'name': 'Curly'}
next: {'dob': 19210202, 'occupation': 'Three stooges', 'key': '010000', 'name': 'Larry'}

[Gadgetoid]

You can globally replace "malloc" and "free" by just supplying alternate versions to the linker, but this can have deleterious effects for many, many reasons- one of the more common being that if "malloc" gets called before the gc_heap is initialised you will have a bad time.

examples/natmod/btree does this:

micropython/examples/natmod/btree/btree_c.c

Lines 7 to 32 in 704c70c

	#if !defined(__linux__)
	void *memcpy(void *dst, const void *src, size_t n) {
	return mp_fun_table.memmove_(dst, src, n);
	}
	void *memset(void *s, int c, size_t n) {
	return mp_fun_table.memset_(s, c, n);
	}
	#endif
	void *memmove(void *dest, const void *src, size_t n) {
	return mp_fun_table.memmove_(dest, src, n);
	}
	void *malloc(size_t n) {
	void *ptr = m_malloc(n);
	return ptr;
	}
	void *realloc(void *ptr, size_t n) {
	mp_printf(&mp_plat_print, "UNDEF %d\n", __LINE__);
	return NULL;
	}
	void *calloc(size_t n, size_t m) {
	void *ptr = m_malloc(n * m);
	// memory already cleared by conservative GC
	return ptr;
	}

So for btree it would - iirc - require sifting through the library and replacing any and all calls to malloc/realloc and free with macros such as BT_MALLOC, BT_REALLOC, BT_FREE (for example) which can fall-back to the normal functions or be defined to some alternative for MicroPython compatibility. (AFAIK btree would not try to alloc before MicroPython and heap are up and running, so at least one of the problems above does not apply.)

This is how the CYW43 driver does it on RP2:

micropython/ports/rp2/cyw43_configport.h

Lines 99 to 104 in 704c70c

	#ifndef cyw43_malloc
	#define cyw43_malloc(nmemb) m_tracked_calloc(nmemb, 1)
	#endif
	#ifndef cyw43_free
	#define cyw43_free m_tracked_free
	#endif

Not a complex job, but care has to be taken if using m_malloc over m_tracked_alloc be sure the pointer allocated is actually reachable and not prone to GC. The advantage to the former is speed and less busywork (m_tracked_alloc makes allocated regions reachable by GC by storing them in a linked list), but the latter requires much less care.

[ctimmer]

After looking a some of the code (e.g. bt_split.c) it appears the malloc/free rewrite would be fairly straight forward. Fortunately this is an older version of btree so you don't have to deal with multi-user, record locking, and transaction handling.

Looks like this could be changed incrementally. Leave the heap size set and convert/test source files one at a time.

Curt

[jonnor]

Personally, I am interested specifically in time-series data storage. That might benefit from a strategy maybe more around temporal partitioning, more so than using a key-value-store as the base. Maybe Hive-style partitioning. And maybe with .npy files as the contents of partitions, using
https://github.com/jonnor/micropython-npyfile/

[ctimmer]

I was able to load over 3 million records from example data from here. The total database file size was 1.3GB. Had to load it with unix micropython for speed. After copying the DB file to the SD card I was using for testing with SimpleDB/btree, it could be accessed via an ESP32 processor. Single row access was slow (multi-second) but it shows large databases can be used with Micropython.

Curt

[Josverl]

1.3B bytes

?

[ctimmer]

We're starting to have fun here. i added code to allow using the json or umsgpack module. This is controlled by setting USE_JSON to True or False. Don't mix this option on an existing db file, it definitely doesn't work.

The complete code

Curt

USE_JSON = True

btree = None
dumps = None
loads = None

if USE_JSON :
    ## Use json module for row serialization
    print ("Using json module")
    try :
        import json
        import btree
        dumps = lambda row_dict : bytes (json.dumps (row_dict).encode())
        loads = lambda row : json.loads (row.decode())
    except Exception as e :
        print (e)
else :
    ## Use umsgpack module for row serialization
    print ("Using umsgpack module")
    try :
        import umsgpack
        import btree
        dumps = lambda row_dict : umsgpack.dumps(row_dict)
        loads = lambda row : umsgpack.loads(row)
    except Exception as e :
        print (e)