Distributed SQLite: Paradigm shift or hype?

by mooreds • 2 years ago

252 154 2 years ago

west0n• 2 years ago

See some development frameworks for local-first apps support both SQLite and PostgreSQL. The advantage of using PostgreSQL is that when you add a cloud option to your local-first app, the migration becomes much easier.

tptacek7• 2 years ago

This is kind of missing the point. I can only speak for the company that sponsors LiteFS (I don't think any edge computing companies put 'otoolep up to doing rqlite, for instance). We love Postgres. Most of our users use Postgres. And Postgres works fine in edge/backhaul configurations; companies were doing geographically distributed Postgres read replicas long, long before we came around. That's where we got the idea.

The idea of LiteFS is:

* Most apps are read-heavy, and some of them are overwhelmingly read-heavy.

* If you're read-heavy and can thus get away with it, there's a pretty significant performance win in replacing the networked n-tier architecture with SQLite, because Postgres round trips add up over the lifecycle of any given request.

* In fact, that's so much the case that --- as Richard Hipp has been pointing out for over a decade --- you can blow off the N+1 problem and just write natural queries.

I think Ben Johnson would be the first to tell you that LiteFS isn't a perfect fit for every application. We have systems that use both Postgres and LiteFS.

Meanwhile, you have to dig to find it on our web page! It's an open source project that works everywhere Linux does. Dial back the cynicism a bit! :)

A plug here for:

https://kerkour.com/sqlite-for-servers

Same author, and one of the best SQLite articles ever.

codegangsta• 2 years ago

Man! I can’t agree more. It does seem foreign right now, but I truly do believe edge computing will become another “CDN” type abstraction, but it will take time for folks to catch up.

Can’t wait for the day that projects like litefs are just a default that nobody knows about, lol.

Go from “technology nobody knows about” to “technology nobody knows about, but runs the world.”

kragen2• 2 years ago

this page just says 'Forbidden'

tptacek7• 2 years ago

I updated Chrome and reloaded.

evgpbfhnr• 2 years ago

Yeah, says Forbidden in firefox or curl but loads in chromium - welcome to the open web of browser interoperability etc etc where servers now just decide your user agent or tls handshake or whatever is fishy.

kragen2• 2 years ago

thank you!

MobiusHorizons3• 2 years ago

also doesn't work on desktop safari for me

runlaszlorun• 2 years ago

not workinf for me currently either. i’m on safari/ios.

geenat3• 2 years ago

"Something went wrong! Try reloading the page. If the problem persists, please update your web browser to the latest version."

RIP website.

edit: it's back

ec109685• 2 years ago

Should have used SQLite.

zackify2• 2 years ago

I think this skips one mega benefit for apps.

I’ve been using liteFS in production for a couple months.

Your web app is able to resolve db queries instantly.

You don’t need loading states if you’re using complex charts and other frontend JS that waits for data.

All the data is resolved so fast and you can just return all your data like more traditional apps, and the load times are insane.

If you’re multi region you can deploy one app instance there. Instead of 2-3. Postgres read replica, maybe something like redis.

It really replaces both of those, assuming you have a read heavy app it works great.

bgdkbtv2• 2 years ago

How is it for writes? Would a CRM type system benefit from liteFS setup?

thomasfromcdnjs• 2 years ago

In Fly's implementation;

LiteFS’ use of FUSE limits the write throughput to about 100 transactions per second so write-heavy applications may not be a good fit.

https://fly.io/docs/litefs/faq/#what-are-the-tradeoffs-of-us...

linsomniac• 2 years ago

The article says "LiteFS supports roughly 100 writes per second".

danappelxx3• 2 years ago

I think you always need loading states to account for slow network, or am I missing something?

datadrivenangel• 2 years ago

The SQLite database is located on the application server, so there is no network between the DB and the app.

LVB• 2 years ago

to account for slow network

Or a slow anything else, e.g., SQLite queries. This thread has focused on the network aspects, and they do stand out since there can be such a large gap between a network call vs a local SSD read. But we're still talking about a database, which could be huge (presumably it's the main, single DB for the whole app). And there is still all of the actual SQLite work that needs to happen to execute a query, plus opportunities for really bad performance b/c of bad queries, lack of indexes, all the usual suspects. Not to mention the load on the owning process itself. So, I'm agreeing that a loading state is needed.

zackify2• 2 years ago

I “use the platform” so clicking a new tab shows the browser loading state. If you’re on a slow network, that’s the way I go about it.

boxed3• 2 years ago

You can run postgres on the same host as the web server too. Isn't that going to get you most of that same benefit in speed?

yurishimo• 2 years ago

It is. I do this on plenty of hobby Laravel apps.

cztomsik3• 2 years ago

the cool thing with sqlite is that you can compile your whole app into a single binary, so you don't need docker for example (or it's trivial to dockerize it afterwards if you really insist)

giovannibonetti3• 2 years ago

Postgres is great, but managing a fleet of them (one in each web server) and ensuring they are all working fine would bring a lot of operational complexity.

SQLite, on the other hand, skips all of that with its simple in-process model.

kevinak• 2 years ago

It’s better than running it on a separate server but if you’re using Postgres you’re always hitting the network stack on the machine, with SQLite that isn’t the case.

icpmacdo• 2 years ago

Among the various free backend hosting options currently available, does Cloudflare stand out as the best choice with its offerings such as Workers and D1? I would love to hear users experiences with these services or if there any informative resources that discuss the costs associated with scaling applications on the infra?

galaxyLogic• 2 years ago

What portion of writes typically have to go to the primary server? If users are local then their data maybe is often local as well. In other words not all writes need to first go the central server from where they are distributed back to all edge-databases.

I can see that the organization running the application needs a global view of all data. But regional users perhaps don't. Often they just need to know their own data.

Write first to edge then copy to central database rather than write first to central database then trickle down to the site from where the write originated in. Just wondering what portion of applications could use this alternative design.

rmbyrro• 2 years ago

This could work in very small projects. Still very risky.

Say you flag a write as "local". Later, some other place in your app starts relying on this write in another locality. If you don't update your write spec from "local" to "primary", you don't have a consistent database anymore, but you will make decisions thinking that you do.

Now consider a team of 10. Or 20...

Mayhem can spiral very quickly from there.

znkr• 2 years ago

It’s been done and it becomes quite complicated very quickly. Then it becomes necessary to manage that complexity and you end up with something like Spanner.

geenat3• 2 years ago

WAL2 + BEGIN CONCURRENT

...to pretty much eliminate write contention. I know, it sucks these aren't built in yet.

Also if we got some sort of router + map reduce helper (Vitess, Citus -like) it'd make massively distributed SQLite a lot more viable. Setups that don't hammer a single master would make all the difference.

Postgres' main disadvantage at scale is all the additional machinery required (backups, failover, proxy, no DDL replication with built-in logical replication .... UGH!!), even when you're using Citus. Feels like it forces you into k8s with the amount of orchestration you need to run.

raggi2• 2 years ago

this does not eliminate write contention, it just moves a bunch of the problem into the application as now you have to resolve conflicts yourself

alberth• 2 years ago

just moves a bunch of the problem into the application

Would you mind elaborating

xray2• 2 years ago

Most apps never need to scale. Also, worried about scaling? Just use an ORM that allows you to switch from SQLite to Postgres. It’s as simple as that.

mind-blight• 2 years ago

Unless your using database specific features. One of the biggest advantages for Postgres is how incredible the ecosystem is. It doesn't work for everything, but I have an OEM, multiple kinds of text search (vector, inverted indexes, trigrams), recursive and graph-like queries (though that's admittedly less of an issue if n+1 isn't a problem), row-level acls, locks, etc.

It's really nice to have all of that power available in one piece of infrastructure.

victorbjorklund• 2 years ago

Not really a problem if you go from sqlite to postgres. Which sqlite feature is missing from Postgres?

russellbeattie• 2 years ago

I wonder if SQLite will ever be modified to allow concurrent writes. Right now it locks the whole DB on write [1]. But since SQLite usage is rapidly expanding from its embedded db roots, one has to wonder if the devs have considered creating some sort of SQLite daemon, or if the underlying architecture would have to be changed so drastically it wouldn't be worth it.

1. https://www.sqlite.org/faq.html#q5

feverzsj• 2 years ago

In most SQL implementation, modification of row is table level locking anyway, so you can put tables in different sqlite db files and achieve same "concurrent" writes.

simonw12• 2 years ago

There's an official branch that implements BEGIN CONCURRENT: https://www.sqlite.org/cgi/src/doc/begin-concurrent/doc/begi...

throwaway458864• 2 years ago

Something can be both hype and useful. Paradigm shift, no, because there's no fundamental architectural difference from any other replicated sql database.

Sqlite only really works as a throwaway database. For that it's pretty great. Although a lot of the current use cases for sqlite could have just been a .ini file.

emmanueloga_3• 2 years ago

Also I think sqlite would not be a good fit if any sort of slow workers / background jobs are required.

simonw12• 2 years ago

Why not?

(I'm planning a background job system based around SQLite at the moment)

emmanueloga_3• 2 years ago

Because typically all writes need to happen from a single process, so if you want to run multiple processes writing to the same DB, you need to synchronize them somehow.

If you are running a workers loop together with your http serving loop, running on the same process is awkward: you would need to stop serving your webapp each time you want to deploy new workers. Also you would need to wait until all workers are done before you could redeploy the app. If one of the workers does something unexpected, it could take down your webapp together with any other workers running, etc.

If you used multiple processes you would need to perform sync through some IPC or something like redis to perform writes sequentially, but using a DB that already ships as a daemon would fit the problem better.

skybrian2• 2 years ago

I don't believe Cloudflare D1 has read replicas yet? They describe how it will work in detail in a blog post [1], but in the future tense.

We’re actively working on global read replication and realizing the above proposal (share feedback In the #d1 channel on our Developer Discord).

Perhaps it will be out by the time the book is finished.

[1] https://blog.cloudflare.com/building-d1-a-global-database

_heimdall3• 2 years ago

D1 is still pretty limited in my experience. No read replicas really kills any of the meaningful benefit of the architecture, and being built off of a (mostly) SQLite-compliant API makes me nervous as it isn't really SQLite at all.

Last year's major Cloudflare outage really was the final straw for me with regards to D1. I don't mean that as a knock at Cloudflare at all, the situation sounded horrible and I appreciate how the entire team responded to it. I just worry that internal responses to fundamental infrastructure issues will leave newer projects like D1 on ice for a year or two.

rozenmd• 2 years ago

D1 (the ability to query your database at the edge) didn't get knocked offline during that outage though?

benbjohnson• 2 years ago

LiteFS author here. I don't disagree with any points in the article but perhaps a reframing could help. I previously wrote a tool called Litestream that would do disaster recovery for a single-node SQLite server and I still think it's a great default option for people starting new projects. Unless you're doing very database-specific things, most SQL will carry over between SQLite and Postgres and MySQL, especially if you add ORMs in the mix. Pick the one that gets you writing code the fastest and you can switch down the road if you need it.

Rather than a paradigm shift or hype, I see distributed SQLite as an extension of a path that devs can go down. With Litestream, the most common complaint I got was that devs were worried that they couldn't horizontally scale with SQLite and they'd be stuck. While you probably won't hit vertical scaling limits of SQLite on most projects, it still caused concern. So LiteFS became a "next step" that a dev could take if they ever got to that point. It doesn't need to be your starting point.

As for the "hacky" solution of txid, I'm not sure why that's hacky. Your application isn't required to use it or the optional built-in proxy but it's available if it fits your application's needs. It also works for plugging legacy applications into distributed SQLite without retrofitting the code. The proposed solution of caching seems orthogonal to the discussion of distributed application data. I don't think any database provider would suggest to avoid caching when it's appropriate but there's plenty of downsides of caching. Hell, it's one of the two hardest problems in computer science.

TheCapeGreek• 2 years ago

most SQL will carry over between SQLite and Postgres and MySQL, especially if you add ORMs in the mix

I think this goes underappreciated, or rather the opposite is overstated.

Sure there are some edge cases that don't work the same, but most apps won't hit those.

My _biggest_ gripe with SQLite so far is the lack of column reordering like other DBs. And my simplistic understanding is that the others do it exactly the same way as you'd do it manually with SQLite - table gets _replaced_ with an identical table with the data correctly ordered and the data is shoved into the new table.

dkjaudyeqooe2• 2 years ago

Why would you want to recorder columns? SQLite reads in a whole record at a time to access any column.

masklinn7• 2 years ago

Sure there are some edge cases that don't work the same, but most apps won't hit those.

That really depends on your modelling style. If you like things like types, SQL-side processing (eg using functions), or covering indexes, then you’ll hit issues every five minutes in sqlite.

SQLite really wants the logic (including consistency logic) in the application, just compare the list of aggregate functions in postgres versus sqlite, or consider that you have to enable FKs on a per-connection basis.

Which I guess is why ORMs help a lot: they are generally based on application-side logic and LCD database.

cjblomqvist• 2 years ago

I think the bigger issue for many is that tooling, infra(provider), in-house knowledge/skill/experience as well as optimizations may differ quite a lot.

Of course, this will differ a lot between projects.

simonw12• 2 years ago

If you want a more convenient way to do column reordering (and other advanced alter table operations) in SQLite my sqlite-utils CLI tool can do this:

    sqlite-utils transform data.db mytable \
      -o id -o title -o description

That will change the order of the columns in the specified table such that id, title and description come first.

The same command can handle many other operations such as changing column types, renaming columns or assigning a new primary key.

https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...

cztomsik3• 2 years ago

column reordering is simple to fix with this migration script https://david.rothlis.net/declarative-schema-migration-for-s...

if you are using Zig (and like to live on the bleeding edge), you can also just use my library which includes similar script and also a simple query builder https://github.com/cztomsik/fridge?tab=readme-ov-file#migrat...

SJC_Hacker5• 2 years ago

SQLites handling of dates is pretty kludgy.

It stores them as strings, so to do something like extract just the year from a date, you have to do 'CAST(substr(game_date,0,5) AS INTEGER).'

Hackish and error prone.

MobiusHorizons3• 2 years ago

It is a fairly low level abstraction, but one that does not require a verbose api. There is nothing error prone or hackish about what you have written, it will work for all inputs, it is just low level. You are just used to having other people write this code for you and give you a library. With newer versions of SQLite you could also write

CAST(strftime(“%Y”, game_date)) as INTEGER

Which is somewhat higher level and less easily mistyped

_heimdall3• 2 years ago

SQLite is neither shift nor hype, its a misunderstanding of underlying architecture challenges.

SQLite has pros and cons, like anything else. When it comes to web frameworks and app platforms, to fundamental problem is that many committed fully to server less/edge and ignored the los of persistent storage. You just can't use a local database when using serverless, or any type of distributed compute/rendering for that matter.

Databases are centralized by design, you can dodge some of that complexity with clever synchronization protocols but you are still limited to having a single primary DB at the end of the day.

For read-heavy use cases, tools like Turso can be invaluable. If database writes are more common, you'll always be limited by network latency. More importantly for most modern web apps, whether you render HTML in the browser or a server you can't avoid loading states. IMO you might as well lean on the platform and use server rendering whenever persistent state is involved.

Animats• 2 years ago

SQLite is getting a lot heavier. This is becoming more like MySQL or Postgres.

voidfunc• 2 years ago

The fate of all projects if they continue on long enough.

internetter2• 2 years ago

No it's not. SQLite is still the only "serverless" database of those three. Nothing has changed, aside from the companies selling variants of it, but that's not SQLite's fault

simonw12• 2 years ago

The most recent SQLite amalgamation zip file is 2732008 bytes.

Version 3.26.0 from 2018 is 2286469 bytes.

That's a 20% increase in 6 years, but it's still just 2.6MB of compressed C.

internetter2• 2 years ago

Something I've been thinking about is partitioning my SQLite. Instead of storing all user's data in one mega table, what if I made a SQLite database for each user? Provided users never talk to each other, I think this might work?

tptacek7• 2 years ago

You can attach to databases dynamically in queries and join across them. I probably wouldn't (in an ordinary data model) do per-user, but I would consider it for different functional areas.

simonw12• 2 years ago

There's a limit on how many databases you can attach to the same connection (SQLITE_LIMIT_ATTACHED), it defaults to 10.

danr4• 2 years ago

I believe this is what one of the companies mentioned in the article, Turso, can help you do. a per-tenant database.

sorenbs• 2 years ago

This setup can work great and even support elements that are shared between users, if you also give those elements their own DB. I’m working on a prototype to support this natively in Prisma.

config_yml• 2 years ago

You can slice it and dice it any way you want, really. The constraint is often what data needs to be written within a transaction. You'll have to figure our a way to reliably apply a consistent schema to all these database files somehow and keep track of them.

gwd2• 2 years ago

One of the things I appreciate about SQLite is being able to keep all the schema initialization and upgrades in the application itself, which are then checked into git and can be tested like mad with throw-away copies of the data.

Here's a package in golang I wrote to help with that process:

https://pkg.go.dev/gitlab.com/martyros/sqlutil@v0.0.0-202312...

gwd2• 2 years ago

Instead of storing all user's data in one mega table, what if I made a SQLite database for each user? Provided users never talk to each other, I think this might work?

I'm doing this in a project I'm developing for language learning, except that you have both shared databases for content, and individual databases for view logs, preferences, and so on. What I actually do is open a :memory: database, ATTACH all the appropriate databases. Transactions work just fine, but because the shared database is basically read-only, then there's no write contention because each user is just writing to their own database. Overall it makes queries easier, because you don't even need to include the user (or the language). (Of course, the flip side is that getting stats on all the users and languages is more difficult.)

Currently it's just single server, but it should be possible to read-replicate the content, and actually move the write replica of the study database to a local server. It should also make it straightforward to let people download their own information: just hand them the actual SQLite file.

If I ever grow large enough that I need multiple servers in different geos, I'll write up my experience and post it here.

cp9• 2 years ago

at least for awhile this is how bluesky/atproto worked. afaik they only ran into issues when the number of users on each server overwhelmed how many files would fit comfortably in a single directory (which is obviously a large number)

https://news.ycombinator.com/item?id=38171322

pfraze• 2 years ago

that's still how it works, we just shard our users across multiple hosts

mixmastamyk2• 2 years ago

Sqlite handles many tables, per tenant is more reasonable.

SJC_Hacker5• 2 years ago

If you want to query across users, which you probably want for analytics, that is going to be a massive PITA.

giovannibonetti3• 2 years ago

You should probably use a specialized DB for analytics (a.k.a. OLAP DB) anyway. As long as you have an automated way of replicating data from SQLite to your OLAP DB, everything should be fine.

nurettin• 2 years ago

The only way I know for doing concurrent writes in sqlite is to open a transaction, accumulate a bunch of writes and then commit them. Otherwise it is dog slow. And it has to be a single process, or you get data corruption. Has this changed somehow?

lxpz• 2 years ago

This is due to SQLite calling fsync a lot by default, to be on the safe side. You can use pragma journal_mode = "wal" and pragma synchronous = "off" and it should be much faster, without risking corruption if your server powers down unexpectedly (at least in theory, you should still make regular backups, which can be done from within a running sqlite instance using the backup command)

Sytten• 2 years ago

Yes it has been years since the WAL mode fixed this. And WAL2 is even better now.

infogulch• 2 years ago

If you're willing to accept eventual consistency (a big ask, but acceptable in some scenarios) then there are options like marmot [1] that replicate cdc over nats.

[1] https://github.com/maxpert/marmot

geenat3• 2 years ago

The major downside to marmot right now is schema changes don't propagate.

Otherwise, keeping a very close eye on it.

prisenco• 2 years ago

It is a pain, but if your use case allows for scheduled downtime, it's not terrible.

I'm looking at Marmot for high availability. Not necessarily horizontal scaling, but instead having a backup server or two that have a constant up-to-date copy of the live db but also can take over if the main server dies, and the main server can then sync the data back when it comes online.

throwaway20373• 2 years ago

    > While SQLite is a really amazing database, most teams will benefit from avoiding it and going the PostgreSQL way instead.

    > Bazillions of engineering hours have been spent to make Postgres the best backend database and choosing SQLite will inevitably force you to reinvent what Postgres already had for many years, in a fragile and buggy way.

Could the same not also be said for MS SQL Server, Oracle, Sybase, MySQL, or MariaDB? The author offers no supporting evidence for this statement.

Rewrite that: "Bazillions of engineering hours have been spent to make XYZ the best backend database..."

klysm• 2 years ago

Without giving evidence, I do agree that Postgres is a better database that serves the general case better. I use both in production for different purposes, but postgresql makes a lot more sense then SQLite as our primary database

throwaway20373• 2 years ago

Fair comment. First hand experience is important. HN is great for people sharing their technical experiences. I would not say that is "without giving evidence"!

znpy• 2 years ago

Rewrite that: "Bazillions of engineering hours have been spent to make XYZ the best backend database..."

What you're saying (and what the author is saying) however is clashing with the reality of so many developers using sqlite and being happy with it.

I'd suggest to rewrite it another way:

Bazillion of developers think they'll need a full-fledged database for their new project while sqlite will cover most of their needs.

throwaway20373• 2 years ago

This is a good reply. I want to clarify why I excluded SQLite in that list: It is not a database server (all of the others are); it is an embedded database. I say that with zero disrespect. I am 110% a SQLite fanboi. Honestly, I have no experience with PosgreSQL, but I have heard a lot of good things about it. The community looks amazing.

superhx• 2 years ago

Is there a distributed version of SQLite that keeps its embedded library feature? For example, it could use EBS and S3 for shared storage, allowing for distributed read and write access, and possibly even multiple concurrent reads and writes.

Should this be available, numerous lightweight web applications could operate without having to set up a separate PostgreSQL or MySQL database.

tptacek7• 2 years ago

Yes, that's (for instance) what Litefs does.

tutfbhuf• 2 years ago

We have so many distributed X applications nowadays that all try to solve the same problem, either in the same or different ways. I think we first have to come up with a simple, distributed, open-source storage solution. In the cloud, we have things like AWS S3, which is a very reliable distributed storage, but for self-hosting, we have:

Ceph, with which I have much experience, is a very solid and quite bulletproof storage solution that offers S3 protocol and FS. However, maintaining it in the long run is really challenging. You better become a Ceph expert.

SeaweedFS struggles with managing large data groups. It's inspired by an outdated Facebook study (Haystack) and is intended for storing and sharing large images. However, I think it's only average—it has poor documentation, underwhelming performance, and a confusing set of components to install. Its design allows each server process to use one big file for storage, bypassing slow file metadata operations. It offers various access points through gateways.

MinIO has evolved a lot recently, making it hard to evaluate. MinIO relies on many small databases. Currently, it's phasing out some features, like the gateway, and mainly consists of two parts: a command line interface (CLI) and a server. While MinIO's setup is complex, SeaweedFS's setup is much simpler. MinIO also seems to be moving from an open-source model towards a more commercial one, but I have not closely followed this transition.

All of these solutions are not simple enough to be the base for a distributed database application. What we really need would be something like an Ext4 successor, let's call it Ext5, with native distributed storage capabilities in the most dead-simple way. ZFS is another good candidate. ZFS has already solved the problem of how to distribute storage across multiple hard drives within one server very well, but it still lacks a good solution on how to distribute storage across different hard drives on different servers connected via a network.

Yes, I know there is the CAP theorem, so it is really a hard challenge to solve, but I think we can do better in terms of self-hosted solutions.

dikei• 2 years ago

While MinIO's setup is complex, SeaweedFS's setup is much simpler.

Are you sure you are not talking in reverse?

I find Minio single binary deployment very easy, and you also complained about SeaweedFS's complexity in the previous paragraph.

moritzruth• 2 years ago

There is also Garage: https://garagehq.deuxfleurs.fr/

klabb3• 2 years ago

In the cloud, we have things like AWS S3, which is a very reliable distributed storage

Yes, but S3 is basically a standardized protocol at this point. There are many both open and commercial alternatives, like Cloudflare R2 (no egress). So depending on the reason for self-hosting (such as preventing lock-in), S3 might be the least important thing to actually move away from. It’s way more difficult to migrate away from eg a proprietary db, sometimes by design.

giovannibonetti3• 2 years ago

Tigris Data [1] seems like a promising open-source solution [2] in this space.

[1] https://www.tigrisdata.com/ [2] https://github.com/tigrisdata-archive/tigris

samwillis• 2 years ago

The paradigm shift that's going to come with distributed SQLite isn't to the edge, it's going to be to users devices. I believe the DX of building Local-first apps is going to hit the criticality point in the next year or so and its popularity is going to explode.

With dynamic partial replication you can synchronise a subset of your database to a SQLite db on your users device, eliminating the network from the ui interaction loop. Then with the emerging eventually constant syncing systems, many using CRDTs, it's possible to have conflict free eventual consistency. Just read and write to a local database and it will sync with your central server or other clients in the background, both in realtime or after working offline.

I work on one such system at ElectricSQL, but there are many people building variants of this such as Evolu, SQLsync, CR-SQLite, and PowerSync.

That's all not to say SQLite on the edge isn't really damn cool!

azeirah2• 2 years ago

All of this makes me think we'll just be building meteor apps but with sqlite instead of mongo and minimongo whaha

candiddevmike• 2 years ago

You've been able to build these apps for years now, hell PouchDB was released in 2012. I don't think anything significant is going to push for more of these apps--the DX isn't really that great compared to remote write--there are more abstractions, less caching, way more corner cases, and I call BS on your conflict free utopia.

jkestner2• 2 years ago

I'm using Pouch/Couch on a web app, and the sync is really good DX - I got all the functionality I wanted, including easy undo, in a day or so. So yeah, what more DX would actually make people adopt local-first?

I don't think it's a dev problem, but a business one. No consumer is demanding local-first, and no company wants to give up that profitable data. We're doing it because we're purposely not interested in users' data and resiliency is part of our value—but we're still holding some of it for consumer convenience.

uhoh-itsmaciek• 2 years ago

What do you think about non-SQL CRDT frameworks like Automerge and Y.js and how they fit into this local-first future?

winwang• 2 years ago

Do you think SQLite performance on user devices changes anything for UX/DX? i.e. if SQLite were 10x faster than today on user devices in the future.

banish-m4• 2 years ago

Don't get caught up in marriage to a particular tech flavor. Instead, architect and use a generic db adapter pattern that translates a more generic SQL/GraphQL flavor into whatever is necessary and efficient underneath for that particular DBMS or store. Meta does this for many warm storage services. It makes changing underlying DBMSes and scalability architectures much easier to develop and reason about than if every app has to know everything about its backend(s).

datadeft• 2 years ago

I think this is a false dichotomy to try to frame there is a Sqlite vs Postgres situation going on. Sqlite is amazing super fast single writer database without network service so it can be used locally as a library mostly while Postgres is a full fledged RDBMS. Both have use-cases that the given service is a better option and there are many other software products out there to be used for similar purposes.

Notable SQLite use cases: https://www.sqlite.org/famous.html

Postgres does not have a similar page: https://www.postgresql.org/about/press/faq/

elithrar2• 2 years ago

What a nightmare! Now your application code spills into your database and our initial goal of simplifying application development is nothing but a long-forgotten dream. All of that for what? To save a few milliseconds to display a web page.

I think "a few milliseconds" vastly understates this: if you want to run your application closer to users, even just across the US, each query is (at least) 70ms just to get over the network and back again.

"Application code spills into your database" was a bad thing when you wrote one language (say, Java, or PHP) and another language (PSQL/TSQL/etc) for your "stored procedures", but that's not what most modern databases are advocating for.

Instead, and not unlike something like React Server Components (RSC), you can choose whether to run code close to the user or closer to the DB (for transactions) in the same language as your application, because it's still part of your application code. This is the model that Durable Objects[1], our coordinated storage service, uses.

Disclaimer: I work on D1 & Durable Objects at Cloudflare, so I'm likely to be called biased here, but it's not like we haven't a) thought about this deeply and b) actually use D1 and Durable Objects to build distributed systems at Cloudflare.

[1] https://blog.cloudflare.com/durable-objects-easy-fast-correc...

cryptonector• 2 years ago

What if there was a simpler solution?

Then TFA mentions caching without talking at all about how that is a solution, let alone a simpler solution.

williamstein• 2 years ago

Another relevant example is how MicroK8s uses distributed sqlite (dqlite) under the hood to make their Kubernetes distribution highly available, instead of using etcd. https://www.cncf.io/wp-content/uploads/2020/11/MicroK8s-High...

SergeAx• 2 years ago

The problem with Postgres is that out of the box, it is not much different from SQLite in terms of availability and scaling. You need to add a Pgbouncer before it can handle multiple connections, and you need a really complicated dance to make it highly available, with read replicas and hot standbys. This is why I prefer MongoDB for that matter.

Distributed SQLite: Paradigm shift or hype?

Related Stories