Cloudflare Developers•12mo ago

Can you use `transaction` with SQLite?

Can you use

transaction

transaction

with SQLite?

© 2026 Hedgehog Software, LLC

More

Communities Docs About Terms Privacy

Burrito

BBurrito Can you use `transaction` with SQLite?

lambrospetrou

L

lambrospetrou•1/19/25, 5:03 PM

Of course! https://developers.cloudflare.com/durable-objects/api/storage-api/#transaction

When using the SQLite-backed storage engine, the txn object is obsolete. Any storage operations performed directly on the ctx.storage object, including SQL queries using ctx.storage.sql.exec(), will be considered part of the transaction.

You can use both APIs, KV and SQL, inside an SQLite DO.

Burrito

B

BurritoOP•1/19/25, 5:04 PM

Ah, for some reason I was under the impression that I must use transactionSynctransactionSync with SQLite.

lambrospetrou

Llambrospetrou Of course! https://developers.cloudflare.com/durable-objects/api/storage-api/#tr...

森@VerseLink (RW)

森

森@VerseLink (RW)•1/19/25, 5:04 PM

btw does trx.rollback() work with SQL storage backend?

Burrito

B

BurritoOP•1/19/25, 5:05 PM

Cool, that gets rid of one part of the pain points then.

森@VerseLink (RW)

森

森@VerseLink (RW)•1/19/25, 5:05 PM

or do we just throw exceptions

lambrospetrou

Llambrospetrou Of course! https://developers.cloudflare.com/durable-objects/api/storage-api/#tr...

Hello, I’m Allie!

H

Hello, I’m Allie!•1/19/25, 6:23 PM

Can you do transactions across different I/O events?

Hello, I’m Allie!

HHello, I’m Allie!Can you do transactions across different I/O events?

lambrospetrou

L

lambrospetrou•1/19/25, 6:40 PM

What do you refer to as IO event?

森@VerseLink (RW)

森

森@VerseLink (RW)•1/19/25, 6:41 PM

btw it says stream api is only available in request context, does DOs RPC method consider a request context?

森@VerseLink (RW)

森

森@VerseLink (RW)•1/19/25, 7:46 PM

huh does DO have in-memory size limit?

lambrospetrou

Llambrospetrou What do you refer to as IO event?

Hello, I’m Allie!

H

Hello, I’m Allie!•1/19/25, 8:01 PM

Like, can I start a transaction within a fetch handler, then close it out within an RPC?

Hello, I’m Allie!

H

Hello, I’m Allie!•1/19/25, 8:01 PM

Or another combo of RPC/fetch/WebSocketMessage, etc

森@VerseLink (RW)

森森@VerseLink (RW)huh does DO have in-memory size limit?

Hello, I’m Allie!

H

Hello, I’m Allie!•1/19/25, 8:08 PM

Yes, same as a Worker, 128 MB

Hello, I’m Allie!

HHello, I’m Allie!Yes, same as a Worker, 128 MB

森@VerseLink (RW)

森

森@VerseLink (RW)•1/19/25, 8:09 PM

oh i thought they meant DO will always occupy 128MB, didn't know that is also the limit

Skrrrrrrrrr

S

Skrrrrrrrrr•1/19/25, 8:48 PM

is there some issues with the Turkey server?

Hello, I’m Allie!

HHello, I’m Allie!Like, can I start a transaction within a fetch handler, then close it out within...

lambrospetrou

L

lambrospetrou•1/19/25, 8:49 PM

The way you do an async transaction now is through transaction(...)transaction(...), so what you suggest could be done by starting one in one fetch/rpc call, but without awaiting the returned promise and just storing it in-memory. Then another request can await for it when needed.
This should work, but again, consistency is up to you to make sure what you do inside the transaction call is OK.
For example, if you try to iterate a cursor across IO context boundaries, I think it won't work (well-defined).

lambrospetrou

Llambrospetrou The way you do an async transaction now is through `transaction(...)`, so what y...

Hello, I’m Allie!

H

Hello, I’m Allie!•1/19/25, 8:51 PM

But you wouldn't be able to close out the transaction with context from the second event, right?

Hello, I’m Allie!

HHello, I’m Allie!But you wouldn't be able to close out the transaction with context from the seco...

lambrospetrou

L

lambrospetrou•1/19/25, 8:52 PM

What do you mean "close" the transaction? The transaction is a callback that you pass to the transaction()transaction() method.

lambrospetrou

Llambrospetrou What do you mean "close" the transaction? The transaction is a callback that you...

森@VerseLink (RW)

森

森@VerseLink (RW)•1/19/25, 8:59 PM

I guess they meant creating a promise in the transaction and store the resolve function elsewhere (so the transaction keeps waiting) until it is the resolve function is resolved on a different / second event.
(But wouldn't that be a deadlock?)

森@VerseLink (RW)

森

森@VerseLink (RW)•1/19/25, 9:02 PM

@lambrospetrou hey, quick question, is it possible to create a Mock DO using vitest to test for functionality that relies on DO's SQL storage?
Mock DO as in, a dummy DO that only exists to provide it's SQL storage logic

森@VerseLink (RW)

森森@VerseLink (RW)@lambrospetrou hey, quick question, is it possible to create a Mock DO using vit...

lambrospetrou

L

lambrospetrou•1/19/25, 9:04 PM

https://github.com/cloudflare/workers-sdk/blob/main/fixtures/vitest-pool-workers-examples/durable-objects/test/direct-access.test.ts

lambrospetrou

Llambrospetrou What do you mean "close" the transaction? The transaction is a callback that you...

Hello, I’m Allie!

H

Hello, I’m Allie!•1/19/25, 9:06 PM

For example, if within the first fetch/rpc I do

// Using KV for simplicity
const a = txn.get("foo")

// Using KV for simplicity
const a = txn.get("foo")

I wouldn't be able to do this in the second fetch/rpc

txn.put("bar", a + "yo");

txn.put("bar", a + "yo");

Hello, I’m Allie!

H

Hello, I’m Allie!•1/19/25, 9:07 PM

Actually, this example doesn't really display a need for a transaction, but I meant more that you can't start a transaction within one I/O context, then complete it within another with data received in that context

Hello, I’m Allie!

HHello, I’m Allie!For example, if within the first fetch/rpc I do ```ts // Using KV for simplicity...

lambrospetrou

L

lambrospetrou•1/19/25, 9:12 PM

I still don't see what usecase would be good for blocking your transactions across requests. You just increase the likelyhood of having them fail due to conflicts at best. If I remember correctly, DOs have their own context, so in theory you can do what you say.

function doX() {
  this.promiseTxn = this.storage.transaction(async (txn) => {
    const a = txn.get("keyA");
    const nextRequestValue = await this.waitForAValueSetByAFutureRequest();
    txn.put("keyB", a + nextRequestValue);
  });
}

function doX() {
  this.promiseTxn = this.storage.transaction(async (txn) => {
    const a = txn.get("keyA");
    const nextRequestValue = await this.waitForAValueSetByAFutureRequest();
    txn.put("keyB", a + nextRequestValue);
  });
}

I "think" this should be doable, but I didn't confirm/test it. I would never do it though

lambrospetrou

Llambrospetrou https://github.com/cloudflare/workers-sdk/blob/main/fixtures/vitest-pool-workers...

森@VerseLink (RW)

森

森@VerseLink (RW)•1/19/25, 9:16 PM

hmm, my question is more like is it possible to have a dummy / mock DO class in the test folder, instead of in src folder that interferes with the exports of my actual app.

If not, would you suggest instead to create a different isolated package (for example in a monorepo)?

森@VerseLink (RW)

森森@VerseLink (RW)hmm, my question is more like is it possible to have a dummy / mock DO class in ...

lambrospetrou

L

lambrospetrou•1/19/25, 9:17 PM

I don't understand what you are asking. You can create your own worker and its Durable Objects bind to it with miniflare if you want and use that.

lambrospetrou

Llambrospetrou I don't understand what you are asking. You can create your own worker and its D...

森@VerseLink (RW)

森

森@VerseLink (RW)•1/19/25, 9:30 PM

Wait, actually thank you! Your hint of "miniflare" and that github example link really helped out big time!

森@VerseLink (RW)

森

森@VerseLink (RW)•1/19/25, 10:05 PM

hmm now i'm getting

Error: Durable Object test helpers can only be used with stubs pointing to objects defined within the same worker.

森@VerseLink (RW)

森

森@VerseLink (RW)•1/19/25, 10:07 PM

here's my miniflare config

miniflare: {
  workers: [
    {
      name: "dummy-do",
      modules: true,
      scriptPath: "./test/database/mo.ts"
    }
  ],
  durableObjects: {
    "MockSqlBackupDO": { className: "DummyDO", scriptName: "dummy-do", useSQLite: true }
  }
}

miniflare: {
  workers: [
    {
      name: "dummy-do",
      modules: true,
      scriptPath: "./test/database/mo.ts"
    }
  ],
  durableObjects: {
    "MockSqlBackupDO": { className: "DummyDO", scriptName: "dummy-do", useSQLite: true }
  }
}

Eric

E

Eric•1/20/25, 1:54 AM

Question- I've bound some durable object instances to some user id's, but those users no longer exist. The durable object used to store some user data. How can I remove all associated data within the durable object, without removing the entirety of the object itself? There doesn't seem to be a data viewer like KV when i can manually remove the key/value pairs.

Eric

E

Eric•1/20/25, 1:56 AM

Reading the docs, it seems like the data wont delete after becoming stale. Any durable objects only remove on shutdown + once they no longer have data within them.

Eric

EEric Reading the docs, it seems like the data wont delete after becoming stale. Any d...

1984 Ford Laser

1

1984 Ford Laser•1/20/25, 2:33 AM

You will need to do a

state.storage.deleteAll()

state.storage.deleteAll()

to remove an old instance. An old instance will not incur any request or duration charges if they aren't being accessed, but their used storage will be charged until deleted

森@VerseLink (RW)

森

森@VerseLink (RW)•1/20/25, 9:28 AM

oh huh i see ensureReplicas() in the DO storage expirmental API, is this some future planned item?

森@VerseLink (RW)

森森@VerseLink (RW)oh huh i see ensureReplicas() in the DO storage expirmental API, is this some fu...

lambrospetrou

L

lambrospetrou•1/20/25, 9:37 AM

First use-case is for D1, which hopefully will be out soon-ish.

森@VerseLink (RW)

森

森@VerseLink (RW)•1/20/25, 9:38 AM

, does this mean the DO eventually will also have this feature

Darkstar

DDarkstar I've been looking all over for an example of how to do a Hibernatable DO-based W...

1984 Ford Laser

1

1984 Ford Laser•1/20/25, 11:38 AM

I'm pretty sure there's a note in the docs that hibernation only works with ingoing WS requests. I'll try and find the reference in the morning

Darkstar

DDarkstar I adjusted my code to try this: ``` const response = await fetch(gatewayUrl, { ...

1984 Ford Laser

1

1984 Ford Laser•1/20/25, 10:31 PM

I was able to find this note:

Hibernation is only supported when a Durable Object acts as a WebSocket server. Currently, outgoing WebSockets cannot hibernate.

Hibernation is only supported when a Durable Object acts as a WebSocket server. Currently, outgoing WebSockets cannot hibernate.

on this page here: https://developers.cloudflare.com/durable-objects/best-practices/websockets/#websocket-hibernation-api

1984 Ford Laser

1

1984 Ford Laser•1/20/25, 10:47 PM

When you do a

storage.get()

storage.get()

and provide multiple keys, does each key count as a read unit, or do we count units per 4KB for all keys combined? Trying to minimise storage usage without moving to SQL backend

森@VerseLink (RW)

森

森@VerseLink (RW)•1/21/25, 6:53 AM

Is it acceptable to call transaction across multiple DOs
say
DO1 enters a transaction
DO1.work
DO2 enters transactions
DO2.work
DO1 DO2 exits transactions

森@VerseLink (RW)

森森@VerseLink (RW)Is it acceptable to call transaction across multiple DOs say DO1 enters a trans...

Larry

L

Larry•1/21/25, 12:42 PM

Yes, they are separate (mostly) execution environments with separate databases. In other words, there is no global lock. A transaction only "locks" the instance it's invoked in.

I added the "(mostly)" because it is possible for two instances of the same DO Class to run in the same memory space. Any instance members/variables are separate but any global variables you set will be seen by other DO(s) instance running in the same memory space.

HowlOftheSun

H

HowlOftheSun•1/22/25, 4:19 AM

I have a iot project that DO's are a good fit for and im curious how others have handled historical data?
once a new msg comes in where do you store the old state if you wanted to provide a historical view of what happened?

HowlOftheSun

HHowlOftheSun I have a iot project that DO's are a good fit for and im curious how others have...

1984 Ford Laser

1

1984 Ford Laser•1/22/25, 6:28 AM

Depends on how many msgs you expect to be storing. Could just them by timestamp or something, whether that be in the DO's storage directly or in a separate service like D1

HowlOftheSun

H

HowlOftheSun•1/22/25, 7:01 AM

DO's storage seems right for the device state part, I think at some point I'd want to do historical queries across multiple devices. I know I could make it work with DOs but it feels heavy handed and d1 would work better. Is there a clean way to move the data out of the DO and into d1?

HowlOftheSun

HHowlOftheSun DO's storage seems right for the device state part, I think at some point I'd wa...

1984 Ford Laser

1

1984 Ford Laser•1/22/25, 7:19 AM

Nah, you'd need to build the middleware yourself. One of the benefits of DOs is how expandable they are, just means you need to build it all yourself, to your exact specs

1984 Ford Laser

1

1984 Ford Laser•1/22/25, 7:21 AM

I haven't tried it before, but someone in the past has suggested this as a starting point: https://github.com/outerbase/starbasedb/tree/main/src/export

HowlOftheSun

HHowlOftheSun DO's storage seems right for the device state part, I think at some point I'd wa...

lambrospetrou

L

lambrospetrou•1/22/25, 9:36 AM

There are two common patterns I have seen in practice about this and it depends on how much data you have, and how much accuracy you want in your historical/aggregated queries.

If you need 100% accuracy, and actually querying 100% of your data, then there is no alternative than to actually query all your DOs. So, in this case you need to see how much data you have, and maybe reduce the amount of DOs you use (e.g. instead of 1 DO per device, have 1 DO per 100 devices) so that your queries against all of them match your latency requirements.
Each of your DOs publishes aggregated information per N seconds/minutes to a separate group of DOs/D1 which are much less and easy to query all of them, or even better use Workers Analytics Engine (WAE). Each of your DOs sends some events to WAE and then you query WAE across data from all our DOs. Keep in mind, WAE does sampling when you have hundreds/thousands of writes per second for a specific index key you use (https://developers.cloudflare.com/analytics/analytics-engine/sampling/ ).

Rahul gangotri

R

Rahul gangotri•1/22/25, 7:39 PM

If i make a websocket as a service saas like pusher and host on cloudflare worker and durable objects

To sell to millions of users, do i need 1 worker? Or each user require separate worker?

Rahul gangotri

R

Rahul gangotri•1/22/25, 7:40 PM

Just curious

miniflare: {
  workers: [
    {
      name: "dummy-do",
      modules: true,
      scriptPath: "./test/database/mo.ts"
    }
  ],
  durableObjects: {
    "MockSqlBackupDO": { className: "DummyDO", scriptName: "dummy-do", useSQLite: true }
  }
}

miniflare: {
  workers: [
    {
      name: "dummy-do",
      modules: true,
      scriptPath: "./test/database/mo.ts"
    }
  ],
  durableObjects: {
    "MockSqlBackupDO": { className: "DummyDO", scriptName: "dummy-do", useSQLite: true }
  }
}

function doX() {
  this.promiseTxn = this.storage.transaction(async (txn) => {
    const a = txn.get("keyA");
    const nextRequestValue = await this.waitForAValueSetByAFutureRequest();
    txn.put("keyB", a + nextRequestValue);
  });
}

function doX() {
  this.promiseTxn = this.storage.transaction(async (txn) => {
    const a = txn.get("keyA");
    const nextRequestValue = await this.waitForAValueSetByAFutureRequest();
    txn.put("keyB", a + nextRequestValue);
  });
}