Jumping into SQLite in DOs

So I'm finally making the jump to SQLite in DOs and don't have any prior experience with SQL, are there any gotchas I need to be aware of when using things like VIEWs or TRIGGERs? The pipelines I'd be looking at would be at most hundreds of rows total in a table, using index columns where possible. Generated columns from JSON data also sounds interesting for some uses. My use cases would usually be write once-read a few times at most, wanna simplify how I'm storing and managing my relational data

Larry•10/23/25, 10:50 AM

I'm not a fan of ORMs in general because they can hide what's going on under the covers and do things inefficiently. Also, in the case of Drizzle, I think it complicates the already tricky schema migration work. I would recommend Lambros's durable-utilsdurable-utils npm package or the migrations capability in Brayden's Actors package.

Larry•10/23/25, 10:51 AM

VIEWs and TRIGGERs work fine. However, I have found I don't use TRIGGERs in this situation. Your code will always know when something is changing. Only your DO has access to the data so there is no risk of someone else bypassing that code.

Larry•10/23/25, 11:01 AM

I do exactly what you are suggesting with JSON data and indexes on fields. It's a great model when you want document database behavior and SQL performance.

Larry•10/23/25, 11:01 AM

In fact, my model takes this idea one step further. I don't have separate tables for each entity type. The metadata for each entity has fields like isIssue, isBugReport, isSchedulableItem. Then I have filtered indexes for those types. The indexes are the same size as they would be if those things were all in separate tables which mostly drives query performance. The big advantage of this approach is that it's an upgrade to the single table inheritence concept. It's more flexible though because the same row can be multiple things and show up in multiple so it's more like a mixin pattern than a linear inheritance pattern.

LLarry I'm not a fan of ORMs in general because they can hide what's going on under the...

1984 Ford LaserOP•10/23/25, 11:38 AM

Good call on durable-utilsdurable-utils here, that's a must have. Appreciate you confirming that I'm thinking down the right track. I think I wouldn't be using triggers often either but at least I know I don't have to be concerned about performance or anything

Larry•10/23/25, 11:57 AM

The biggest mistake I see when people make the jump to doing SQL inside of DOs is that they come with prior "learning" that they have to unlearn. Much of what an SQL user learns over their career is to optimize for minimum round trips. For instance, they want you to use a database transaction. That's just not important. Perhaps the hardest to unlearn/relearn is that with embedded databases N+1 queries are just as likely to be more performant and they are always more comprehensible than a query that has to use all the SQL tricks to save round trips. That sounds like nails on a chalkboard to "experienced" SQL users, but it's true.

Larry•10/23/25, 11:58 AM

My recommendation is to think of your DO code as stored procedures written in TypeScript/JavaScript. Follow a few simple rules and the DOs input/output gates give you the same performance and ACID benefits that transactions and traditional stored procedures do in an over-the-wire SQL database.

Larry•10/23/25, 11:59 AM

Also, remember that D1 is still an over-the-wire database from the perspective of your application code. Your prior learning about SQL is much more applicable if you use D1 than SQLite in DOs.

Ggnomesgames > I'm not a fan of ORMs in general because they can hide what's going on under t...

Larry•10/23/25, 12:18 PM

Is "../drizzle/migrations""../drizzle/migrations" auto-generated in your case? It's where that generation step goes in the deploy process that I find adds complexity.

Larry•10/23/25, 12:21 PM

Another complication is that migrations sometimes need per-row transformation (e.g. create a new field from the values in two other fields). When it's in code, I can see all that in one spot. Does Drizzle support that?

1984 Ford LaserOP•10/23/25, 11:19 PM

Wonder how far off we are from (or if it's even possible) being able to do like cross-DO SQL queries. I'm sure there's plenty of people who would be able to make use of aggregating together many DO SQL tables/rows at once

1984 Ford LaserOP•10/23/25, 11:20 PM

Like I'm definitely gonna need to store data in two places for some use cases as reaching out to many DOs first to pull necessary data will be more effort instead of simply writing the values again in a second place to start with

11984 Ford Laser Like I'm definitely gonna need to store data in two places for some use cases as...

Larry•10/23/25, 11:50 PM

Someone on this channel (maybe @Marak ?) has talked about having a source of truth in another place and one in the DO.

Larry•10/23/25, 11:52 PM

However, I would try the reach out every time approach first if it were me. Only if that was a performance problem would I consider making copies and even then I'd try to find another way first.

1984 Ford LaserOP•10/23/25, 11:57 PM

Like for example I have some data that is spread across ~100 DOs, the data needing to be accessed once a week or so. It seems more performant to write the required data to a second place once, as opposed to needing to reach out to those 100 every time I need to read. Its not necessarily a performance issue, more about saving the extra requests where necessary

1984 Ford LaserOP•10/23/25, 11:57 PM

Again probably one of those scenarios where Im trying to optimise way earlier than I should be

Larry•10/24/25, 12:22 AM

That's my gut instinct. A fanout of 100 is a lot but not too much to deal with once a week. Even a 1000 or 10,000 fanout might be OK. Verify experimentally first before optimizing.

LLarry That's my gut instinct. A fanout of 100 is a lot but not too much to deal with o...

1984 Ford LaserOP•10/24/25, 2:04 AM

For sure, I'll see what works best for my workload. The way I would be storing it in the secondary location, the data in those 100 could be a single row in a parent DO, meaning I'd be doing 1 DO request and 100 rows read, whereas with fanout it would be 100 DO requests and many more times the rows read cos the data is stored across many rows in the fanout DOs

1984 Ford LaserOP•10/24/25, 2:04 AM

Bit for me to think about architecturally

1984 Ford LaserOP•10/24/25, 2:07 AM

For a bit of extra context: I'm storing sports match statistics, with a DO representing each match, and also a different DO (namespace) for a league. Each match DO has stats only for that match, but the league DO will want to store individual match stats to be able to do things like calculate sums/averages. We use the data in a few different ways so I wanna make sure I'm building this correctly from the get-go

James•10/24/25, 4:07 AM

i dont think there is any good way to get around needing some type of analytics store that isn't a DO, its not feasible to even query DOs fan-out or not for mass aggregate queries, its costly and inefficient

JJames i dont think there is any good way to get around needing some type of analytics ...

Larry•10/24/25, 11:43 AM

Agreed if it's a normal feature but if it's a once a week thing...

James•10/24/25, 1:59 PM

My shortcut for the time being is workers analytics engine, even sampled data in aggregate is good enough for regular large scale reporting and dashboards.

11984 Ford Laser Like for example I have some data that is spread across ~100 DOs, the data needi...

Marak•10/26/25, 5:33 AM

If we require a unique DO instance per entity and we also require the ability to query all entities across the DO set, we've gotten into the habit of creating a D1 table to track all the entities by name/id and their last reported state.

MMarak If we require a unique DO instance per entity and we also require the ability to...

1984 Ford LaserOP•10/26/25, 5:43 AM

In theory I don't even need to keep this data in a DO to be able to effectively use it, as Im really small scale overall sompared to others. It just makes sense to store this stuff by DO as that's how I've architected everything else this far and like the experience overall

11984 Ford Laser In theory I don't even need to keep this data in a DO to be able to effectively ...

Larry•10/26/25, 11:35 AM

That's now our default. Try to do it in DOs. If that doesn't scale or perform well, or even if smells wrong - only then, try something else.

LLarry The biggest mistake I see when people make the jump to doing SQL inside of DOs i...

kieran5101•11/6/25, 8:49 PM

Hey, would you mind explaining why transactions are not important with SQLite inside DO? I currently have 2 tables, an event store and an outbox, I want to atomically store the domain event and the outbox event in the database. Typically, as you said, I would use a transaction, can I handle this differently with DOs?

Kkieran5101 Hey, would you mind explaining why transactions are not important with SQLite in...

Larry•11/6/25, 9:07 PM

I had to read this several times and have several conversations with Kenton about this before I really understood, but this explains it all in great detail: https://blog.cloudflare.com/durable-objects-easy-fast-correct-choose-three/

The jist of the correctness part is that DOs are carefully designed so that as long as you don't break certain rules while handling a request/message/alarm, you get pure single-threadedness. SQLite is running in the same process and super-fast so don't need asyncasync/awaitawait. I enforce this in my own code by making sure that my handlers don't have the asyncasync keyword in front of them. I constantly have to correct the AI coding agent who assumes that if you are doing storage inside, then it must be asyncasync. Then there is no risk that I call something with awaitawait inside the handler. Your handler can call out to whatever helpers it needs but the'll have to be sync also. So no new developer actidentally making an outside fetch in your handler. You should also avoid setTimeout/setInterval in the handler.

Does that help?

The Cloudflare Blog

Durable Objects: Easy, Fast, Correct — Choose three

When multiple clients access the same storage concurrently, race conditions abound. Durable Objects can make it easier. We recently rolled out improvements to Durable Objects that automatically correct many common race conditions while actually making your code faster.

kieran5101•11/6/25, 9:24 PM

Thanks for the response. I have read that blog post a couple of times, and I get it conceptually. I think the confusion for me is caused by using the rust library where I don't really have the option to not await a future.

Larry•11/6/25, 9:58 PM

Ohhh, I know nothing about the Rust library or Rust for that matter. I can't imagine that every function must be a future. It's OK to have some async outside of the sync part. Even in TypeScript, the standard handlers for requests/messages/alarms = fetch/webSocketMessage/alarm are all async. In fetch, I await body.json()await body.json(), but then I call a handler that's sync. There has to be something similar in Rust?

LLarry Ohhh, I know nothing about the Rust library or Rust for that matter. I can't ima...

kieran5101•11/7/25, 10:47 PM

Oh my god I can't believe I didn't notice that the SQL storage in durable objects is synchronous. Thank you, you've cleared that up for me (I have lots of code to edit now)

Larry•11/7/25, 10:50 PM

KV storage is also sync as of 2027-09-12 or so. You just need to use this.ctx.storage.kv.get()this.ctx.storage.kv.get(), .put().put(), etc.

Jumping into SQLite in DOs

Similar Threads

Similar Threads

Similar Threads