uSQLite running on RAM (in memory)

[beyonlo]

Hello, thank you for the great project.

I would like to use the uSQLite on the ESP32-S3 (8MB RAM, 32MB Flash), where the uSQLite database will have ~4MB. So to be faster the idea is to load all database to the RAM, and get (the selects) to the RAM database, and when need to write data (insert/update), write on disk database, syncing with the RAM database.

Is possible to copy entire database to the RAM and use uSQLite on RAM e when needed insert/update data, write that specific data to disc, like SQLite support?

Should be like this (https://stackoverflow.com/a/53253110):

import sqlite3

source = sqlite3.connect('existing_db.db')
dest = sqlite3.connect(':memory:')
source.backup(dest)

and/or this (https://stackoverflow.com/a/53253110):

# first connection
c1 = sqlite3.connect("file::memory:?cache=shared", uri=True)

# second connection, to the *same database*
c2 = sqlite3.connect("file::memory:?cache=shared", uri=True)

# third connection, to a different database altogether
c3 = sqlite3.connect('/tmp/sqlite3.db', uri=True)
# can attach to the shared in-memory database, but only if you used
# uri=True on the original connection
c3.execute("ATTACH DATABASE 'file::memory:?cache=shared' AS inmem")

Thank you in advance!

[spatialdude]

@beyonlo I haven't tried the technique you describe, but it should be possible to ATTACH a :memory: database to one persisted in storage then INSERT ... INTO a table into the in-memory database.

I would also suggest using usqlite.mem_status(True) command so you track how much peak memory you are consuming.

e.g.

import usqlite

def trace(sql):
    print("trace:", sql)

usqlite.mem_status(True)

def mem():
    print("mem current:", usqlite.mem_current(), "peak:", usqlite.mem_peak())

print("Memory Test")

print("sqlite_version:", usqlite.sqlite_version)

db = "test.db"
#db = ":memory:"

mem()

con = usqlite.connect(db)
print("connection:", con)

con.set_trace_callback(trace)

mem()

con.executemany(
    "BEGIN TRANSACTION;"
    "CREATE TABLE IF NOT EXISTS data (name TEXT, year INT);"+
    "INSERT INTO data VALUES ('Larry', 1902);"+
    "INSERT INTO data VALUES ('Cury', 1903);"+
    "INSERT INTO data VALUES ('Moe', 1897);"+
    "COMMIT;"
    "")

print("Total changes:",con.total_changes)

mem()

print("close")
con.close()

mem()

Output

Memory Test
sqlite_version: 3.46.0
mem current: 0 peak: 32
connection: <Connection 'test.db'>
mem current: 11968 peak: 12000
trace: BEGIN TRANSACTION;
trace: CREATE TABLE IF NOT EXISTS data (name TEXT, year INT);
trace: INSERT INTO data VALUES ('Larry', 1902);
trace: INSERT INTO data VALUES ('Cury', 1903);
trace: INSERT INTO data VALUES ('Moe', 1897);
trace: COMMIT;
Total changes: 3
mem current: 26144 peak: 28704
close
mem current: 0 peak: 28704

[beyonlo]

@spatialdude I will try that.

Thank you!