LBasically the same thing but for workerd instead of node?
IKind of? But each DO has it's own SQLite storage.
H- Some extra special sauce for Disaster-Recovery Replication / Point in Time Restores
HAt least AFAIK
node:sqlite doesn't do that out of the boxHYou can probably mod it to do it somehow...
L
LIt creates a sqlite db in memory.
LWhich I think is similar to what DO does since it says it lives in the same thread as the DO v8 isolate.
IThat's only that specific example.
IIt can operate on a disk based DB too.
Lonly thing is that maybe DO would have a specific amount of resources reserved for the SQLite, whereas with
node:sqlite you would need to be careful in not exceeding your memory/disk.HIIRC DO SQLite execution shares the memory of your code, so that is still a concern there
Lbut Cloudflare doesn't have any setting for specifying resources of your Worker/DO
Lyou don't even know how much you are using
IAll Workers/DOs get up to 128mb which may be shared with other invocations of the same Worker
Lah right
IAnd DO SQLite has a size limit of 1GB during beta.
L1GB of disk or memory?
IDisk. Memory is shared with the DO invoking it.
LSo all DOs have 1GB of disk even if you aren't using the SQLite but want to save something else on it?
INo, SQLite storage is seperate. DOs don't have disk, they have SQLite (1GB) or DO KV storage.
IIn general you shouldn't think about it as "disk" but more like "I have a SQLite DB that can have a maximum size of 1GB"
IAll of the underlying hardware resources like disk/memory/cpu are managed by Cloudflare.
Lso it's like Workers and the Browser API which lives in a different machine
INo, it lives on the same machine.
Lyeah, i meant on the fact that you cannot use the resources of the API for doing other stuff
IYeah.
Lwell, i think you could potentially still use the sqlite as an object storage and dumping raw binary files in it
IYes you could. Keep in mind that there is no streaming of SQLite values so it would be held in memory.
I(unlike alternatives such as R2)
Lso is this SQLite better than a Redis
EHow long do we think it'll be until the alarms issue is with the sqlite is resolved?
Built something cool locally and then saw the warning in the docs
Built something cool locally and then saw the warning in the docs
LRedis is key-value oriented so in that sense it's more like Workers KV or the DO KV API. However, it has more functionality than a simple KV store including things like pub/sub and atomic operations like appending to a string or incrementing a value (although you can also do the latter atomicly with the DO KV API. Redis is often used as an in-memory distributed cache (think alternative to memcache) but it also has persistence.
The most obvios difference to SQLite is... well... SQL. SQLite is embedded not distributed (although read-replicas are implemented by some systems which is going to be the case for Cloudflare's D1) which limits scalability. Redis is distributed and scales up well.
The DO SQLite API though has another huge advantage beyond vanilla SQLite. With input gates, output gates, and write coalescing, you can essentially write stored procedures in JavaScript with atomicity, consistency, isolation, and durability (ACID). This means that during a single request where you never await, any combination of reads followed by any combination of writes are executed without worrying about another request changing anything from underneath you and where you can be sure that either all writes will succeed or all writes will fail. Caveat that I haven't confirmed that if you get an error back from an SQL write operation all the writes before that will be rolled back but I suspect that's the case and I intend to verify that experimentally shortly.
Note, these "stored procedures" run not only on the same machine but in the same memory space and thread making them very efficient and fast per request.
The tradeoff is that a single DO instance has a max throughput of about 1000 requests per second. The scaling model is to have many DOs so depending upon your application, there could be some coding complexity on your part and efficiency costs to coordinate between them. That said, many applications have natural boundaries across which little coordination is needed.
The most obvios difference to SQLite is... well... SQL. SQLite is embedded not distributed (although read-replicas are implemented by some systems which is going to be the case for Cloudflare's D1) which limits scalability. Redis is distributed and scales up well.
The DO SQLite API though has another huge advantage beyond vanilla SQLite. With input gates, output gates, and write coalescing, you can essentially write stored procedures in JavaScript with atomicity, consistency, isolation, and durability (ACID). This means that during a single request where you never await, any combination of reads followed by any combination of writes are executed without worrying about another request changing anything from underneath you and where you can be sure that either all writes will succeed or all writes will fail. Caveat that I haven't confirmed that if you get an error back from an SQL write operation all the writes before that will be rolled back but I suspect that's the case and I intend to verify that experimentally shortly.
Note, these "stored procedures" run not only on the same machine but in the same memory space and thread making them very efficient and fast per request.
The tradeoff is that a single DO instance has a max throughput of about 1000 requests per second. The scaling model is to have many DOs so depending upon your application, there could be some coding complexity on your part and efficiency costs to coordinate between them. That said, many applications have natural boundaries across which little coordination is needed.
S@tobias not official driver but i got it working sort of https://x.com/sadsciencee/status/1842714039719432404
successfully duct taped @DrizzleORM onto the new @CloudflareDev durable objects sqlite api
technically DO sqlite is synchronous, i used drizzle's http proxy which requires async. but im gonna let someone smarter than me figure that part out ¯\_(ツ)_/¯
technically DO sqlite is synchronous, i used drizzle's http proxy which requires async. but im gonna let someone smarter than me figure that part out ¯\_(ツ)_/¯
10/5/24, 11:50 PM
TI tried the same thing but got random errors hahah
THave you tried the query syntax?
Sya - tbh havent done any relational or joins yet. but basic findFirst/findMany works
working on streaming in bulk import files to battle test writes rn, ill have enough of a db to try some more complex queries after that
working on streaming in bulk import files to battle test writes rn, ill have enough of a db to try some more complex queries after that
L@kenton , does it also apply when the request exits with an uncaught runtime JavaScript error? Do the ones coalesced so far not get executed?
KNo, not necessarily. If you want to roll back on exceptions, you should wrap your code in
this.ctx.storage.transactionSync(() => {...}).LThanks. I don't see
.transactionSync() in the docsLDoes native replica support in SQLite change anything for SQLite-in-DO? https://sqlite.org/draft/rsync.html
LWill multiple DOs be able to sync with eachother?
HNo, afaik, since DO SQLite doesn't use the default SQLite storage engine anyway
Mjust reading the sqlite blog post that talks about N+1 selects and suggests with sync not to worry with joins - just query the db for every row of data in a seperate sqlite query
if this is the case, why not just stick to kv storage? what type of advantage does using sqlite-backed storage offer here?
if this is the case, why not just stick to kv storage? what type of advantage does using sqlite-backed storage offer here?
LFor most of this answer, I'm going to assume you mean the DO KV API but just in case you meant Workers KV the huge difference are the network round trips.
So, back to SQL vs DO KV...
First, just because N+1 selects can be as efficient as joins, doesn't mean you won't use joins some or even most of the time. For simple joins like inner and natural the syntax is easy to comprehend and it may even be more efficient. For complex joins like outer and cross as well as SQL with subqueries or aggregations it's less about efficiency and more about making sure that the next developer (and even yourself at a later date) can make sense of what's going on. Doing it in JavaScript may be a better alternative from this perspective. Note, neither @kenton nor the SQLite docs say that you should use N+1 selects, just that if you want, you shouldn't worry about efficiency because there is no network round trip or even CPU context switching cost.
Second, SQL isn't just about joins. Indexes in particular are a feature missing from KV that's super helpful but there are also triggers, constraints, aggregations, etc. that you may want to take advantage of.
So, back to SQL vs DO KV...
First, just because N+1 selects can be as efficient as joins, doesn't mean you won't use joins some or even most of the time. For simple joins like inner and natural the syntax is easy to comprehend and it may even be more efficient. For complex joins like outer and cross as well as SQL with subqueries or aggregations it's less about efficiency and more about making sure that the next developer (and even yourself at a later date) can make sense of what's going on. Doing it in JavaScript may be a better alternative from this perspective. Note, neither @kenton nor the SQLite docs say that you should use N+1 selects, just that if you want, you shouldn't worry about efficiency because there is no network round trip or even CPU context switching cost.
Second, SQL isn't just about joins. Indexes in particular are a feature missing from KV that's super helpful but there are also triggers, constraints, aggregations, etc. that you may want to take advantage of.
FI'm wondering the same, hope someone can answer
DPlease avoid pinging people in every message
KYes, embarrassingly we forgot to document it before launch, and worse, if you try to use
BEGIN TRANSACTION, the error message is not helpful, so some people came off thinking we don't support transactions at all. Will get that fixed soon.KFunny you should ask. I have been building a DO-based app of my own (to organize LAN parties...), and I actually decided I was happier with KV rather than SQL because I liked the ergonomics of just shoving whole JS objects into storage. It's a legitimate choice.
That said, as Larry points out, one big thing you'll lose that way is secondary indexes. In my case I had to hand-roll one or two secondary indexes, which was a bit of a pain.
Also note that even if you are using the KV API, you are still getting most of the benefits described in the blog post, since we're actually storing your KV data in SQLite under the hood. It's a bit harder to reason about sync/async though -- the KV interface appears to be async, but it's actually effectively synchronous when backed by SQLite.
That said, as Larry points out, one big thing you'll lose that way is secondary indexes. In my case I had to hand-roll one or two secondary indexes, which was a bit of a pain.
Also note that even if you are using the KV API, you are still getting most of the benefits described in the blog post, since we're actually storing your KV data in SQLite under the hood. It's a bit harder to reason about sync/async though -- the KV interface appears to be async, but it's actually effectively synchronous when backed by SQLite.