This no need to compile SQLite into your Cloudflare Worker. We provide it native on our platform as D1. And it gives you replication. https://blog.cloudflare.com/d1-turning-it-up-to-11/ Also, I think the idea of “edge” doesn’t make a ton of sense. What we really need is code and data that move around as needed for the best performance. See: https://blog.cloudflare.com/announcing-workers-smart-placeme... What people c…
Why SQLite is so great for the edge
61–70 of 148 posts
Re: Why SQLite is so great for the edge
#62Earlier quoted context omitted.
No foreign keys by default. Using them means that every connecting session must toggle it on. Forget one and it will be free to violate referential integrity. One writer. Any session that issues BEGIN TRANSACTION and then hangs halts all dml. WAL mode confusion. WAL cannot safely be used on network filesystems, and it breaks ACID on ATTACHed databases, among other problems. Date and time types don't really exist. The…
I believe there is a recent (optional) strict mode that will enforce types. I haven't used it so I don't know how full-featured it is. Your other points are of course correct
(I wrote a little more about this, with examples, at https://news.ycombinator.com/item?id=33282830.)
Re: Why SQLite is so great for the edge
#63Earlier quoted context omitted.
Yea, but does it scale? Yes, for 100 users maybe. But you have 100 000 small sqlite db:s I would be a bit worried about how it scales. Electric is super cool. Can't wait until it is a bit more mature.
In broad strokes having 100k SQLite dbs is a bit like having 100k git repositories. It's doable and just about having tooling to manage it. It actually makes a few things simpler, say progressively rolling out visiting upgrades. You need to write schema migrations anyway, this lets you upgrade one customer at a time.
You can parallelize it, of course, but that's a challenge in of itself.
Re: Why SQLite is so great for the edge
#64I extensively used SQLite in a telemetry system for an electric race car. The car has an onboard computer, first a Raspberry Pi then a dual core Arm processor. Onboard code logs ~4000 messages a second into three SQLite databases. After a drive session a script merges the three databases into a single SQLite session log. The session log is decoded on a different computer to ~400 columns of time series data again stor…
Re: Why SQLite is so great for the edge
#65Earlier quoted context omitted.
> Onboard code logs ~4000 messages a second into three SQLite databases. After a drive session a script merges the three databases into a single SQLite session log. A bit unrelated, but curious as to why you wrote to three separate databases only later to merge them.
One of SQLite's weaknesses is it's not client-server, so it's ill-advised to have multiple applications try to use a single sqlite server. Concurrency is another issue.
It is however possible to have multiple applications access the same sqlite database on the same filesystem (i.e. locally, not via network access, not via remounts or symlinks or other forms of synonyms) and it will sort out the locking for you. Depending on what you need to do, there's I think three possible strategies you can use so it can probably handle your concurrency needs faster than Postgres via the network can. For instance, this particular task is write-heavy, and it would benefit from a different setting than a read-heavy application.
There are certainly occasions when Postgres or MySQL or MS SQL or Oracle is a better choice than SQLite, and sometimes concurrency contributes to that decision. But it's unlikely to be the case that you had an SQLite solution that worked really well with one process reading and writing from the database, and now all of a sudden you need a second or third application to use the database concurrently with the first, and you find you need to switch to a client-server model And even if sometimes you need to take specific steps (like adopting a different locking model), you also have to take specific steps to run Postgres (e.g. using a pool to speed up connections and limit the number of concurrent connections because each connection is a separate process). It's a matter of knowing your tools.
Re: Why SQLite is so great for the edge
#66This no need to compile SQLite into your Cloudflare Worker. We provide it native on our platform as D1. And it gives you replication. https://blog.cloudflare.com/d1-turning-it-up-to-11/ Also, I think the idea of “edge” doesn’t make a ton of sense. What we really need is code and data that move around as needed for the best performance. See: https://blog.cloudflare.com/announcing-workers-smart-placeme... What people c…
How's D1 meant to be used, since it has a very small maximum size (100mb I believe)? Should I create one database per user, for example? Genuine question.
Re: Why SQLite is so great for the edge
#67This no need to compile SQLite into your Cloudflare Worker. We provide it native on our platform as D1. And it gives you replication. https://blog.cloudflare.com/d1-turning-it-up-to-11/ Also, I think the idea of “edge” doesn’t make a ton of sense. What we really need is code and data that move around as needed for the best performance. See: https://blog.cloudflare.com/announcing-workers-smart-placeme... What people c…
How's D1 meant to be used, since it has a very small maximum size (100mb I believe)? Should I create one database per user, for example? Genuine question.
Re: Why SQLite is so great for the edge
#68This no need to compile SQLite into your Cloudflare Worker. We provide it native on our platform as D1. And it gives you replication. https://blog.cloudflare.com/d1-turning-it-up-to-11/ Also, I think the idea of “edge” doesn’t make a ton of sense. What we really need is code and data that move around as needed for the best performance. See: https://blog.cloudflare.com/announcing-workers-smart-placeme... What people c…
How's D1 meant to be used, since it has a very small maximum size (100mb I believe)? Should I create one database per user, for example? Genuine question.
See also: https://archive.is/e7u9x / https://www.the-paper-trail.org/post/2020-04-06-physalia
Re: Why SQLite is so great for the edge
#69This no need to compile SQLite into your Cloudflare Worker. We provide it native on our platform as D1. And it gives you replication. https://blog.cloudflare.com/d1-turning-it-up-to-11/ Also, I think the idea of “edge” doesn’t make a ton of sense. What we really need is code and data that move around as needed for the best performance. See: https://blog.cloudflare.com/announcing-workers-smart-placeme... What people c…
How's D1 meant to be used, since it has a very small maximum size (100mb I believe)? Should I create one database per user, for example? Genuine question.
But yes, I think the next step is then some sort of sharding. Sharding by user would be an obvious approach for many apps. I think we should build a framework to help manage this, so apps would only need to provide some callbacks e.g. to compute shard key for a particular query.
Alternatively, apps that want full control will be able to use Durable Objects directly. We'll soon (in a few months, probably?) enable the new storage engine for all Durable Objects which means every object will have a private SQLite database.
(I'm the tech lead for Workers in general, and currently focused on this project in particular.)
Re: Why SQLite is so great for the edge
#70This no need to compile SQLite into your Cloudflare Worker. We provide it native on our platform as D1. And it gives you replication. https://blog.cloudflare.com/d1-turning-it-up-to-11/ Also, I think the idea of “edge” doesn’t make a ton of sense. What we really need is code and data that move around as needed for the best performance. See: https://blog.cloudflare.com/announcing-workers-smart-placeme... What people c…