Show HN: sqlite-memory-vfs - Open a SQLite db in Python without hitting disk

https://github.com/michalc/sqlite-memory-vfs

by michalc • 3 years ago

17 14 3 years ago

github.com

gxt• 3 years ago

Do you have benchmarks and how does this differ from pointing SQLite to /dev/shm ?

michalc4• 3 years ago

No benchmarks right now. And in fact, I'm not even really sure what would make good ones, so suggestions welcome.

In terms of the difference from pointing SQLite to /dev/shm, my answer is somewhere between "I don't know", and "I suspect the VFS is more portable because not all systems have /dev/shm?"

throwaway815232• 3 years ago

import sqlite3

conn = sqlite3.connect('%MEMORY%')

Is there more to it than that?

jarito• 3 years ago

Second sentence in the linked GitHub:

While SQLite supports the special filename :memory: that allows the creation of databases in memory, there is no built-in way to populate such a database using raw bytes without hitting disk as an intermediate step. This virtual filesystem overcomes that limitation.

throwaway815232• 3 years ago

I'm surprised writing to %MEMORY% hits the disk. Anyway ramdisks like tmpfs are a thing.

up2isomorphism• 3 years ago

It is funny people are doing SQL everything after the NoSQL hype. But why?

stephenr• 3 years ago

Because SQL works really well for a lot of what people need a database for?

ncruces4• 3 years ago

Recent SQLite builds include the memdb [1] VFS (and the associated sqlite3_serialize and sqlite3_deserialize APIs) by default. How does this compare?

I'm also curious what use cases you had in mind. My SQLite wrapper reimplements the memdb [2] VFS mostly as an exercise of the VFS API, but it provides a few additional niceties. The backing memory doesn't need to be contiguous, nor is it copied when the DB grows/shrinks. This makes it possible to have significantly larger in memory DBs. I also make it easy to bootstrap the database from (e.g.) a file on disk, or data embed in the binary. It's not as easy, however, to access the DB bytes after the DB goes “live.”

[1] https://www3.sqlite.org/src/file?name=src/memdb.c

[2] https://pkg.go.dev/github.com/ncruces/go-sqlite3/vfs/memdb#s...

ncruces4• 3 years ago

From a cursory look at your source code…

It looks like memory is also not contiguous in your implementation.

You don't support concurrency at all (xLock/xUnlock/xCheckReservedLock are noops).

I'm not sure how APSW handles journaling (you usually want the journal_mode to be memory in these cases, but to force that, you must return SQLITE_OPEN_MEMORY as an OutFlag from xOpen). Otherwise, you need to deal with files (like journals, etc) for which reads/writes are not page aligned.

There is one caveat, though: if you want to “deserialize” a WAL database with journal_mode memory, you must convert it to rollback before opening (but you can do that by editing bytes 18 and 19).

michalc4• 3 years ago

and the associated sqlite3_serialize and sqlite3_deserialize APIs

So... I have to admit I missed the existence of these. So for many cases, using sqlite3_deserialize is probably a very reasonable choice over sqlite-memory-vfs

Still, as you say, sqlite-memory-vfs doesn't require the memory to be contiguous, unlike sqlite3_deserialize

You don't support concurrency at all (xLock/xUnlock/xCheckReservedLock are noops).

Well, you can read concurrently, just not write

Show HN: sqlite-memory-vfs - Open a SQLite db in Python without hitting disk

Related Stories