I recently saw the launch post of Electric SQL which syncs to SQlite, I like the pattern on how keeping the data close to the frontend can solve many problems, if synced with the main DB. I hate to run another docker or manage service to manage this layer but if somehow a part of data from the database like Postgres can be synced using something simple like litestream and can be placed either on edge or client can be a solution to many of the problems.
I'm all-in on server-side SQLite (2022)
11–20 of 167 posts
Re: I'm all-in on server-side SQLite (2022)
#12> When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. That’s micro, with a μ. A 50-100x improvement over an intra-region Postgres query.
Why compare the latency of a remote Postgres database with a local SQLite database? If your app is so simple and self-contained that it runs on a single EC2 instance using local files, nothing prevents you from installing Postgres on the same machine, whether inside a container or not.
I have some simple apps on EC2 with MariaDB on localhost, and well-tuned queries rarely take more than 100-200 microseconds. That's total query execution time, not just communication latency. RDS just sucks for this kind of use case. It's not a useful comparison.
> As much as I love tuning SQL queries, it’s becoming a dying art for most application developers. Even poorly tuned queries can execute in under a second for ordinary databases.
Didn't you just say that milliseconds matter?
Re: I'm all-in on server-side SQLite (2022)
#13Use Postgres. Or if you insist on this type of architecture use CouchDB. I shudder thinking about a SQLite schema migration across clients with potentially unknown versions.
Seems like a disaster waiting to happen unless you have a bunch of logic centralized somewhere to keep track of last know schemas per user client database. And if you’re going to do all that, unless you desperately need low latency (in which case you could use a multi region database like cockroach), why not just centralize?
Re: I'm all-in on server-side SQLite (2022)
#14Been feeling a little miffed about this recently. Litestream is excellent but if you have multiple writers your db gets corrupted. Quite easy to do with rolling deploys. LifeFS was announced and is intended to help this. Now seems like ( https://fly.io/docs/litefs/getting-started-fly/ ) it requires an HTTP proxy so that the application can guess about sqlite write/read usage by reading the HTTP request method. This s…
Re: I'm all-in on server-side SQLite (2022)
#15Author here. Cool to see the post make it up on HN again. I'm still as excited as ever about the SQLite space. So much great work going on from rqlite, cr-sqlite, & Turso, and we're still plugging away on LiteFS. I'm happy to answer any questions about the post.
Is there a recommendable way to feign a graph database within SQLite? (because read only replication would be fantastic on fly.io for us.)
Re: I'm all-in on server-side SQLite (2022)
#16[deleted]
Re: I'm all-in on server-side SQLite (2022)
#17I hope fly is able to make it. I’m rooting for them - however - I’m starting to wonder if the SQLite push isn’t more “this is fun and interesting to build” and less “customers want this”.
Don’t get me wrong - this is neat - but I’d never suggest anyone to actually use this outside of a fun experiment. The problem with existing SQL dbs isn’t really the architecture - its the awful queries that do in memory sorting or make temporary tables for no reason or read-after-write, etc, not network latency. SQLite won’t fix your current production problems.
If it turns out they’re building this for customers throwing cash at them, awesome. I just somehow doubt it. I think Planetscale has the better approach: a drop in replacement for MySQL/RDS with a smarter query planner. As a production engineer that’s what I want to pay for!
Re: I'm all-in on server-side SQLite (2022)
#18> When you put your data right next to your application, you can see per-query latency drop to 10-20 microseconds. That’s micro, with a μ. A 50-100x improvement over an intra-region Postgres query. Why compare the latency of a remote Postgres database with a local SQLite database? If your app is so simple and self-contained that it runs on a single EC2 instance using local files, nothing prevents you from installing…
But why take on the operational overhead of a separate DB server (not to mention 200 more microseconds), plus the EC2 surcharge? I would rather run app+SQLite + dumb object storage, than app + MySQL + MySQL incremental backup and restore.
Re: I'm all-in on server-side SQLite (2022)
#19Author here. Cool to see the post make it up on HN again. I'm still as excited as ever about the SQLite space. So much great work going on from rqlite, cr-sqlite, & Turso, and we're still plugging away on LiteFS. I'm happy to answer any questions about the post.
What's the status on litestream? Does that have a future as well or is it LiteFS all the way?
Re: I'm all-in on server-side SQLite (2022)
#20I don't need to be sold on the virtues of applications running on systems like SQLite. The nineties had a lot of servers which were very simple (and performant) compared to LAMP, and I like systems like that. What I would like is a good primer about the layers on top of SQLite. What does Litestream do for me? How does it compare to competitors? Why not just use SQLite directly? A more in-depth technical discussion wo…
> Why not just use SQLite directly?
SQLite does not provide replication, so there is no way to use it directly (other than copy whole file). If you mean it as "Why not use it as a database" than sure, you can use it directly, though the article states reasons for not doing so (resiliency and concurrency). Postgres is a lot better in those areas, and so is the tooling.
>I'd also like to understand wrappers and ORMs for migration to other systems, should SQLite stop scaling
1. It heavily depends on the orms. For example Django provides good abstraction layer and many things works with any database with no change needed, but many other don't bother about that. However just because a query runs, doesn't mean it will return the same results. Any non-trivial app will rely on numerous accidental details and you can't switch db and expect everything will be fine. SQL is not really portable even in the parts it does cover, and there are many it doesn't.