> What we’re doing here is instantaneous point-in-time recovery (PITR), expressed simply in SQL and SQLite pragmas.
> Ever wanted to do a quick query against a prod dataset, but didn’t want to shell into a prod server and fumble with the sqlite3 terminal command like a hacker in an 80s movie? Or needed to do a quick sanity check against yesterday’s data, but without doing a full database restore? Litestream VFS makes that easy. I’m so psyched about how it turned out.
Man this is cool. I love the unix ethos of Litestream's design. SQLite works as normal and Litestream operates transparently on that process.
This is great... just got it working using bun:sqlite! Just need to have "LITESTREAM_REPLICA_URL" and the key id and secret env vars set when running the script.
import { Database } from "bun:sqlite";
Database.setCustomSQLite("/opt/homebrew/opt/sqlite/lib/libsqlite3.dylib");
// Load extension first with a temp db
const temp = new Database(":memory:");
temp.loadExtension("/path/to/litestream.dylib", "sqlite3_litestreamvfs_init");
// Now open with litestream VFS
const db = new Database("file:my.db?vfs=litestream");
const fruits = db.query("SELECT * FROM fruits;").all();
console.log(fruits);
This is awesome. Especially for sqlite db’s that are read only from a website user perspective. My use case would be an sqlite DB that would live on S3 and get updated by cron or some other task runner/automation means (eg some other facility independent of the website that is using the db), and the website would use litestream vfs and just make use of that “read only” (the website will never change or modify the db) db straightup. Can it be used in this described fashion? Also/if so, how will litestream vfs react to the remote db updating itself within this scenario? Will it be cool with that? Also I’m assuming there is or will be Python modules/integration for doing the needful around Litestream VFS?
Currently on this app, I have the Python/flask app just refreshing the sqlite db from a Google spreadsheet as the auth source (via dataframe then convert to sqlite) for the sqlite db on a daily scheduled basis done within the app.
Author here. Litestream VFS will automatically poll for new back up data every second so it keeps itself up to date with any changes made by the original database.
You don't need any additional code (Python or otherwise) to use the VFS. It will work on the SQLite CLI as is.
Forgot to say, thanks for posting this, looks quite useful for various projects that have been on my mind. At one point I was looking for a git vfs for Python (I did find one for caddy static serving specifically, but I needed it for Python) but couldn’t find much that wasn’t abandoned—- an s3 vfs might do the trick for a lot of use cases though.
Does this work with sqlite extensions? If I were using e.g. sqlite-vec or -vss or some other vector search extension would I be able to use litestream to back it up to S3 live, and then litestream-vfs to query it remotely without downloading the whole thing?
Yeah I wonder how it would work with things like custome application functions etc. But I guess the query is run locally and it's the pages that are fetched from S3? So it just works? That would be awesome.
I'm glad they did this! I've always thought VFS was a better fit for the objectives of Litestream than the original design.
SQLite VFS is really cool tech, and pretty easy to work with (IMO easier than FUSE).
I had made a _somewhat similar_ VFS [1] (with a totally different set of guarantees), and it felt pretty magical how it "just worked" with normal SQLite
so how would i connect from a separate machine, i can't figure out from the post or release notes or current litestream website docs, how would i use the extension to do that?
Edit:
need to set LITESTREAM_ACCESS_KEY_ID, LITESTREAM_SECRET_ACCESS_KEY, LITESTREAM_REPLICA_URL
Been tinkering with litestream... the read-only VFS is neat but I'm curious about eventual write capabilities... using VFS for distributed DBs could unlock some interesting patterns.
ALSO I'm thinking about mixing this with object store caching... maybe combining memfs with remote metadata; would love to see more details on performance.
BUT I might be overthinking it... just excited to see SQLite exploring beyond local files...
Opens up a whole new can of worms. Transactions come to mind. Who would be responsible for coordinating? If two nodes wrote to the table conflicting information at the same time, who wins?
Author here. We've done some proof-of-concept work on creating distributed leases using S3. We have some use cases internally where we've considered adding write capabilities to the VFS but we haven't started any work on it yet.
It’s a tricky problem that goes beyond the fs as you know. Since it’s cloud and since it’s distributed, a manager wouldn’t be that far fetched that could issue CRDT like messages across the cluster of nodes to issue a “write” or utilize the sync mechanism you have to propagate a “master” db that you write to (aggregate or designate, either way). I did some work on this on a go based database graph and ended up doing a gossip sync crdt message bus.
Is this VFS for read-only databases? Or can I query a database that has a single litestream writer somewhere continously making updates and backing them up to S3?
The VFS is read only but it will continuously poll for new updates so if you have a writer somewhere else using regular Litestream then it will pick up those updates automatically.
Author here. You can query the state of a table from an hour ago with Litestream VFS. It won't give you versioned tables in the sense that every time you update a row that it writes a new version in a table somewhere though.
I was doing something similar just the other day and came across sqlite-s3vfs[0]. It is likewise a SQLite VFS that translates IO to S3 api calls. However, that project is only for python and seemingly abandoned. Additionally, if you want more than one writer, you'd have to coordinate that yourself, afaik.
I am going to integrate Litestream into the thing I am going to building[1]. I experimented with a lot of ways, but it turns out there is WebDAV support recently merged, not in the docs.
So much fun streaming/sync/cdc stuff happening, all so cool. Having an underlying FUSE driver doing the Change Data Capture is really neat. This looks like such an incredibly lightweight way to remote-connect to sqlite. And to add a sort of exterior transaction management.
Different use case, but makes me think of sqlite Rewrite-it-it-Rust Turso announcing AgentFS. Here the roles are flipped, sqlite is acting as a file store to back FUSE, to allow watching/transaction-managing the filesystem/what agents are doing. Turso also has a sick CDC system built in, that just writes all changes to a cdc table. Which is related to this whole meta question, of what is happening to my sqlite DB.
https://turso.tech/blog/agentfs
Just to be clear, the underlying FUSE thing is LiteFS, not Litestream; nothing described in this post needs a FUSE filesystem, just a small SQLite VFS plugin library.
To just drop the relevant paragraph that addresses my un-clarity/in-correctness (and which is super fun to read):
> Litestream v0.5 integrates LTX, our SQLite data-shipping file format. Where earlier Litestream blindly shipped whole raw SQLite pages to and from object storage, LTX ships ordered sets of pages. We built LTX for LiteFS, which uses a FUSE filesystem to do transaction-aware replication for unmodified applications, but we’ve spent this year figuring out ways to use LTX in Litestream, without all that FUSE drama.
You got it. Yeah, Ben built LiteFS a year or two ago, which is why he designed LTX. But using LiteFS required people to set up FUSE filesystems, which was too complicated for a lot of people, so Litestream got way more uptake. This past year he's been harvesting all the good stuff from LiteFS that doesn't require FUSE and building it into Litestream.
The easiest way so far to understand the split between Litestream and LiteFS: Litestream is an operational tool, for backup and restore. LiteFS is a method for doing online leader/follower replica clusters.
People are building DBMSes and, instead of writing the engine from scratch, are choosing an off-the-shelf solution that integrates into a DBMS with ease.
A better question to ask is why the world needs yet another DBMS, but the reasons are no doubt valid.
I am a heavy skeptic of this thing, but I can see a good use case for it: S3 I/O, ephemeral compute (1 instance), versioned blobs. The first two allow you to abstract the data away from the compute (flexibility), and the third lets you recover from mistakes or bugs quicker (or do immutable migrations easier).
I think the devil's in the details though. I expect a high number of unusual bugs due to the novel code, networking, and multiple abstractions. I'd need to trial it for a year before I called it reliable.
I am. Super simple. Super cheap. Great dev experience. Want to know whether the migration is going to work? Just download the prod db locally and test it. I'm happy.
For things like config management I feel like it makes all the sense in the world. Whomever the primary is can soak some infrequent-ish write-load. Then the whole DB can quickly copy to where it's needed, or, in lite stream VFS 's case, even less needs to be shipped.
If you are not familiar with data systems, havea read DDIA(Designing Data Intensive Applications) Chapter 3. Especially the part on building a database from the ground up — It almost starts with sthing like "Whats the simplest key value store?": `echo`(O(1) write to end of file, super fast) and `grep`(O(n) read, slow) — and then build up all the way to LSMTrees and BTrees. It will all make a lot more sense why this preserves so many of those ideas.
[0]: https://github.com/psanford/sqlite3vfs
Slightly different API (programmatic, no env variables, works with as many databases as you may want), but otherwise, everything should work.
Note that PRAGMA litestream_time is per connection, so some care is necessary when using a connection pool.
> Ever wanted to do a quick query against a prod dataset, but didn’t want to shell into a prod server and fumble with the sqlite3 terminal command like a hacker in an 80s movie? Or needed to do a quick sanity check against yesterday’s data, but without doing a full database restore? Litestream VFS makes that easy. I’m so psyched about how it turned out.
Man this is cool. I love the unix ethos of Litestream's design. SQLite works as normal and Litestream operates transparently on that process.
brew install sqlite3, then change the bottom part:
you have to manually pass in the init function nameCurrently on this app, I have the Python/flask app just refreshing the sqlite db from a Google spreadsheet as the auth source (via dataframe then convert to sqlite) for the sqlite db on a daily scheduled basis done within the app.
For reference this is the current app: (yes the app is kinda shite but I’m just a sysadmin trying to learn Python!) https://github.com/jgbrwn/my-upc/blob/main/app.py
You don't need any additional code (Python or otherwise) to use the VFS. It will work on the SQLite CLI as is.
[0] https://github.com/Barre/ZeroFS
[1] https://github.com/Barre/ZeroFS?tab=readme-ov-file#sqlite-pe...
I guess there's only one way to find out.
SQLite VFS is really cool tech, and pretty easy to work with (IMO easier than FUSE).
I had made a _somewhat similar_ VFS [1] (with a totally different set of guarantees), and it felt pretty magical how it "just worked" with normal SQLite
[1] https://github.com/danthegoodman1/gRPSQLite
Edit:
need to set LITESTREAM_ACCESS_KEY_ID, LITESTREAM_SECRET_ACCESS_KEY, LITESTREAM_REPLICA_URL
then the module works
ALSO I'm thinking about mixing this with object store caching... maybe combining memfs with remote metadata; would love to see more details on performance.
BUT I might be overthinking it... just excited to see SQLite exploring beyond local files...
[0]: https://pypi.org/project/sqlite-s3vfs/
I am going to integrate Litestream into the thing I am going to building[1]. I experimented with a lot of ways, but it turns out there is WebDAV support recently merged, not in the docs.
[1]: https://github.com/blue-monads/potatoverse
Different use case, but makes me think of sqlite Rewrite-it-it-Rust Turso announcing AgentFS. Here the roles are flipped, sqlite is acting as a file store to back FUSE, to allow watching/transaction-managing the filesystem/what agents are doing. Turso also has a sick CDC system built in, that just writes all changes to a cdc table. Which is related to this whole meta question, of what is happening to my sqlite DB. https://turso.tech/blog/agentfs
To just drop the relevant paragraph that addresses my un-clarity/in-correctness (and which is super fun to read):
> Litestream v0.5 integrates LTX, our SQLite data-shipping file format. Where earlier Litestream blindly shipped whole raw SQLite pages to and from object storage, LTX ships ordered sets of pages. We built LTX for LiteFS, which uses a FUSE filesystem to do transaction-aware replication for unmodified applications, but we’ve spent this year figuring out ways to use LTX in Litestream, without all that FUSE drama.
The easiest way so far to understand the split between Litestream and LiteFS: Litestream is an operational tool, for backup and restore. LiteFS is a method for doing online leader/follower replica clusters.
A better question to ask is why the world needs yet another DBMS, but the reasons are no doubt valid.
I think the devil's in the details though. I expect a high number of unusual bugs due to the novel code, networking, and multiple abstractions. I'd need to trial it for a year before I called it reliable.
https://news.ycombinator.com/item?id=46124205
If you are not familiar with data systems, havea read DDIA(Designing Data Intensive Applications) Chapter 3. Especially the part on building a database from the ground up — It almost starts with sthing like "Whats the simplest key value store?": `echo`(O(1) write to end of file, super fast) and `grep`(O(n) read, slow) — and then build up all the way to LSMTrees and BTrees. It will all make a lot more sense why this preserves so many of those ideas.