I am going to sound like broken record throwing in shameless plugs. But for people who might want to look for alternate options, Marmot (https://github.com/maxpert/marmot) is another option that can let you both replicate, and backup your database. I have recently embedded NATS into Marmot and based on community feedback introduced example script that gets you up and running with example DB within seconds.
I'm really glad to see this complete solution drop. I've cobbling together sqlite point-in-time restores, automatic snapshots, and S3 backups with litestream and scripts for too long.
I've been bugging cloudflare to do this with D1+tunnels since D1 was announced and they constantly seemed confused what I was even talking about.
Let me know how it works for you if you get a chance to try it out. We learned a lot since making Litestream so hopefully it's a big step up! The underlying format of how we're storing snapshots and incremental backups should make restores A LOT faster. Litestream has to replay all WAL writes since the last snapshot whereas LiteFS is able to perform a lot of deduplication and use multi-level compaction to create a minimal set of restore data.
I wanted this when D1 was first teased, and before I figured out that D1 is a database product that happens to be built on SQLite rather than SQLite being the star of the D1 show. Nonetheless, what I want is to be able to point a CF tunnel at a local SQLite file and instantly be able to leverage all the benefits of D1.
I should be able to...
1. Configure a CF tunnel through the config file or the dash, pointing it to a local path of an existing SQLite file.
1a. On startup, the SQLite file should create and link a new D1 db.
2. Configure a CF tunnel through the config file or the dash, pointing it to an existing D1 db.
2a. On startup, the local sqlite db should be either overwritten or created.
3. Create read replicates through CF tunnels on another server or the local computer/wrangler for development.
4. Have automatic global read replicas on the edge, which is inherent to D1 no?
5. Have automatic/rolling backups and export and import those backups automatically with S3/R2.
6. Do a PITR through the dash or CF tunnel CLI.
6a. PITR should be possible to either the existing live DB or restoring it into a separate new D1 copy.
7. Leverage my SQlite files in workers, another automatic bonus of connecting it to D1.
There are likely plenty of more cool things that could be done if D1 could be exposed as a normal SQLite file. Ultimately, CF tunnels are just in the mix because it seems like the obvious choice for plumbing SQLite files into and out of the CF network/edge.
Is LiteFS+SQLite suitable for something like a SaaS solution? For example - Fresh books, Trello or Craigslist - all three have different needs. So which kind of apps should NOT be built on top of LiteFS+SQlite combination?
Good question. LiteFS is just a replication layer so it's probably better to answer your question just from a SQLite standpoint. One of the biggest limitations of SQLite is that it only allows a single writer at a time so you can't have long-running write transactions in your application. Writes can be incredibly fast though—sometimes a millisecond or less depending on your hardware and settings. Speaking of which, make sure you use the "WAL" journaling mode if you're using SQLite it improves concurrency & write performance.
There are also differences once you get to a large scale. Many databases support compression whereas SQLite does not so it might get expensive if you have terabytes of data. That's an extreme case though.
Ultimately, SQLite is just a database. It's more similar to Postgres & MySQL than it is different. There are some features that those client/server databases have like LATERAL joins but I feel like SQLite includes the 99% of what I typically use for application development.
For compression and encryption, commercial SQLite extensions are available - https://sqlite.org/support.html ... I also recall coming across some free open source projects that had implemented compression and encryption for SQLite databases though I have no idea if they were production ready.
Technical requirements for a SaaS app have a tendency to become something like a Jenga tower over time. You'll be able to sail through launch and initial customers but then slam hard into difficult architectural challenges as you suddenly onboard larger customers or unique use cases.
For SQLite my guess would be areas of high concurrent write throughput - like a seasonal holiday/rush, a viral influx of users, or the onboarding of a large client.
Its not that SQLite can't handle these situations with careful architectural decisions. Its that out-of-the-box solutions, like the kind people depend on to solve business-issues in short time frames, won't support it as readily as more mainstream options.
I agree with everything the OP said above. Typically if you need to scale writes in SQLite, you'll want to look at sharding. The "single writer" restriction is per database so you can split your SaaS customers across multiple databases.
If your SaaS is in the hundreds or thousands of customers then you could split each customer into their own database. That also provides nice tenant isolation. If you have more customers than that you may want to look at something like a consistent hash to distribute customers across multiple databases.
Is there any documentation showing what kind of performance is given up by shoving a userspace server into the SQLite read path? LiteFS looks cool but I'd be worried about all block IO basically becoming a cross-process RPC negating large chunks of SQLite's efficiency. Instinct is screaming this should be a VFS extension for SQLite, but definitely appreciate the idea of doing it as a virtual filesystem.
In terms of pure perversion, I'm wondering if fanotify or kernel tracepoints could be used to gather the information needed asynchronously rather than sticking a userspace server in the way
Author here. I'm planning on writing up a blog post about FUSE performance. I get a lot of questions about it. In practical terms, it mostly affects write performance. If you have a write-heavy application then it's probably not a good fit for LiteFS right now. On the read side, hot pages end up in the OS page cache or SQLite page cache so most page reads don't touch the FUSE layer at all.
We have plans for a VFS implementation soon that'll help with the write performance. We chose FUSE because it's fits most application performance targets and it can work with legacy applications with little to no code changes. It also makes it easy to SSH in to a server and use the sqlite3 CLI without having to load up an extension or do any funny business.
Has anyone built a mobile app on top of SQLite that can work offline, but then sync to a server when it gets connectivity? It feels like this could be built with a similar approach to this distributed SQLite, you'd "just" need more robust conflict handling.
Great that you brought it up. I will fill in the perspective of what I am doing for solving this in Marmot (https://github.com/maxpert/marmot). Today Marmot already records changes via installing triggers to record changes of a table, hence all the offline changes (while Marmot is not running) are never lost. Today when Marmot comes up after a long off-time (depending upon max_log_size configuration), it realizes that and tries to catch up changes via restoring a snapshot and then applying rest of logs from NATS (JetStream) change logs. I am working on change that will be publishing those change logs to NATS before it restores snapshots, and once it reapplies those changes after restoring snapshot everyone will have your changes + your DB will be up to date. Now in this case one of the things that bothers people is the fact that if two nodes coming up with conflicting rows the last writer wins.
For that I am also exploring on SQLite-Y-CRDT (https://github.com/maxpert/sqlite-y-crdt) which can help me treat each row as document, and then try to merge them. I personally think CRDT gets harder to reason sometimes, and might not be explainable to an entry level developers. Usually when something is hard to reason and explain, I prefer sticking to simplicity. People IMO will be much more comfortable knowing they can't use auto incrementing IDs for particular tables (because two independent nodes can increment counter to same values) vs here is a magical way to merge that will mess up your data.
Not 100% sure if it uses SQLite, but there's an app called "Flaming Durtles" on Android. It's a frontend for WaniKani (Japanese learning app). The app downloads all the lessons from WK's servers, and you can do them even when offline. When you go online, it uploads them to the backend and syncs everything else. In my experience, it works flawlessly.
It's actually a 3rd party app, WK provides an API so that people can make apps that interface with the system.
Not sure how it's implemented but seems like a pretty common pattern on Android. For instance, Gmail and Mint apps both allow offline changes that get synced. Not sure if they use SQLite but afaik that's sort of the defacto standard for Android app storage.
we have done it a few times. It is a lot more work and a lot of edge cases come up, especially if the same data can be written by multiple people. But if data or use case is such that only one person can write to same data and you limit only one logged in client at a time, it's much easier to do.
Man, i already intended to try Fly for my upcoming hosting needs. Ya'll keep making the pot sweeter, though.
I'm really curious to see how some of these SQLite toolings will work in the "dumb and simple app" case. Ie i'm writing an app that is focused on being local, single instance. Which i know is blasphemy to Fly, but it's my target audience - self hosting first and foremost.
I had planned on trying Fly through the lens of a DigitalOcean replacement. Notably something to manage the machine for me, but with similar cost and ease. In that realm, i wonder which of the numerous SQLite offerings Fly has will be useful to my single-instance-focused app backed by SQLite and Filesystem.
Some awesome tech from the Fly team regardless. Exciting times :)
Author here. I love single-instance deployments. I think they make a lot of sense when you don't need high-availability. We have quite a number of people that run Litestream on a single instance with a volume attached. We run Litestream internally in several places as well.
LiteFS tries to improve upon Litestream by making it simple to integrate backups and make it easy to scale out your application in different regions without changing your app. I don't think every application needs to be on the edge but we're hoping to make it easy enough that it's more a question of "why not?"
Is it a goal of LiteFS to serve single-instance deployments as well as Litestream does? Would you say LiteFS has already achieved that at this point, or would Litestream still be the better match for single-instance apps?
I've experimented with LiteFS and liked it, but all my apps are single-deployment, so I've stuck with Litestream. But I know LiteFS is receiving much more investment, so I'm wondering if Litestream is long for this world.
The idea is that people with small-to-medium size Rails Turbo apps should be able to deploy them without needing Redis or Postgres.
I’ve gotten as far as deploying this stack _without_ LiteFS and it works great. The only downside is the application queues requests on deploy, but for some smaller apps it’s acceptable to have the client wait for a few seconds while the app restarts.
What are the reasons to continue using SQLite over MySQL/MariaDB when you start to require distributed architectures? Wouldn't it be better to switch at that point? Assuming that being able to read from a database on the same filesystem as the application doesn't provide any tangible benefits for 99.99% of applications that don't have such low latency requirements?
There's an operational argument to make, but fundamentally it's about performance, and secondarily about what having an ultra-fast local database does to your performance budget and thus how you build things. The premise is that in a typical web application, each request incurs multiple round trips to the database, and those round trips add up to eat a chunk of your budget. A database like SQLite can essentially eliminate that round-trip cost.
It's not just about making snappier applications; it's also that even ordinary apps burn engineering time (for most shops, the most expensive resource) on minimizing those database round trips --- it's why so much ink has been spilt about hunting and eliminating N+1 query patterns, for instance, which is work you more or less don't have to think about with SQLite.
This premise doesn't hold for all applications, or maybe even most apps! But there is a big class of read-heavy applications where it's a natural fit.
You appropriately answered the question that was asked. I would add a little extension that this question (about SQLite specifically) was asked in the context of a discussion about "Distributed SQLite". The considerations around round-trip costs for "Distributed SQLite" are very different than for SQLite.
SQLite is not designed for situations like a website backend where you would expect to have multiple actors modifying the db simultaneously. I'm not sure if they've done something with their implementation to improve upon that.
SQLite works just fine for website backends. Writes are serialized, first from replicas to the central write leader (like in Postgres), and then with the WAL and transaction isolation.
This is awesome. Fly.io is now a good replacement for my small team and some indie projects. Currently on render before but their team pricing turned me off.
Fly gives away a lot for free and yet is cheaper than most NewCloud alternatives. Their patient (and painful) investment in building atop CoLos (instead of BigCloud) will pay off in the long run, just like it did for Cloudflare. Not sure if Fly already builds their own hardware, though.
Readit News
I've been bugging cloudflare to do this with D1+tunnels since D1 was announced and they constantly seemed confused what I was even talking about.
We're launching PITR support for the new experimental backend within the next 1-2 weeks. Stay tuned.
(I am the eng director for Cloudflare Workers)
I should be able to...
There are likely plenty of more cool things that could be done if D1 could be exposed as a normal SQLite file. Ultimately, CF tunnels are just in the mix because it seems like the obvious choice for plumbing SQLite files into and out of the CF network/edge.Deleted Comment
There are also differences once you get to a large scale. Many databases support compression whereas SQLite does not so it might get expensive if you have terabytes of data. That's an extreme case though.
Ultimately, SQLite is just a database. It's more similar to Postgres & MySQL than it is different. There are some features that those client/server databases have like LATERAL joins but I feel like SQLite includes the 99% of what I typically use for application development.
Deleted Comment
For SQLite my guess would be areas of high concurrent write throughput - like a seasonal holiday/rush, a viral influx of users, or the onboarding of a large client.
Its not that SQLite can't handle these situations with careful architectural decisions. Its that out-of-the-box solutions, like the kind people depend on to solve business-issues in short time frames, won't support it as readily as more mainstream options.
If your SaaS is in the hundreds or thousands of customers then you could split each customer into their own database. That also provides nice tenant isolation. If you have more customers than that you may want to look at something like a consistent hash to distribute customers across multiple databases.
In terms of pure perversion, I'm wondering if fanotify or kernel tracepoints could be used to gather the information needed asynchronously rather than sticking a userspace server in the way
We have plans for a VFS implementation soon that'll help with the write performance. We chose FUSE because it's fits most application performance targets and it can work with legacy applications with little to no code changes. It also makes it easy to SSH in to a server and use the sqlite3 CLI without having to load up an extension or do any funny business.
[1]: https://github.com/vlcn-io/cr-sqlite
[2]: https://github.com/mycelial/mycelite
For that I am also exploring on SQLite-Y-CRDT (https://github.com/maxpert/sqlite-y-crdt) which can help me treat each row as document, and then try to merge them. I personally think CRDT gets harder to reason sometimes, and might not be explainable to an entry level developers. Usually when something is hard to reason and explain, I prefer sticking to simplicity. People IMO will be much more comfortable knowing they can't use auto incrementing IDs for particular tables (because two independent nodes can increment counter to same values) vs here is a magical way to merge that will mess up your data.
It's actually a 3rd party app, WK provides an API so that people can make apps that interface with the system.
Forum thread: https://community.wanikani.com/t/android-flaming-durtles-and...
Source code (may be outdated): https://github.com/ejplugge/com.the_tinkering.wk
I'm really curious to see how some of these SQLite toolings will work in the "dumb and simple app" case. Ie i'm writing an app that is focused on being local, single instance. Which i know is blasphemy to Fly, but it's my target audience - self hosting first and foremost.
I had planned on trying Fly through the lens of a DigitalOcean replacement. Notably something to manage the machine for me, but with similar cost and ease. In that realm, i wonder which of the numerous SQLite offerings Fly has will be useful to my single-instance-focused app backed by SQLite and Filesystem.
Some awesome tech from the Fly team regardless. Exciting times :)
LiteFS tries to improve upon Litestream by making it simple to integrate backups and make it easy to scale out your application in different regions without changing your app. I don't think every application needs to be on the edge but we're hoping to make it easy enough that it's more a question of "why not?"
Is it a goal of LiteFS to serve single-instance deployments as well as Litestream does? Would you say LiteFS has already achieved that at this point, or would Litestream still be the better match for single-instance apps?
I've experimented with LiteFS and liked it, but all my apps are single-deployment, so I've stuck with Litestream. But I know LiteFS is receiving much more investment, so I'm wondering if Litestream is long for this world.
The idea is that people with small-to-medium size Rails Turbo apps should be able to deploy them without needing Redis or Postgres.
I’ve gotten as far as deploying this stack _without_ LiteFS and it works great. The only downside is the application queues requests on deploy, but for some smaller apps it’s acceptable to have the client wait for a few seconds while the app restarts.
When I get that PR merged I’ll write about how it works on Fly and publish it to https://fly.io/ruby-dispatch/.
It's not just about making snappier applications; it's also that even ordinary apps burn engineering time (for most shops, the most expensive resource) on minimizing those database round trips --- it's why so much ink has been spilt about hunting and eliminating N+1 query patterns, for instance, which is work you more or less don't have to think about with SQLite.
This premise doesn't hold for all applications, or maybe even most apps! But there is a big class of read-heavy applications where it's a natural fit.