Traditional Begin and Commit transactions support in websockets #36

Activity

1. G4brym commented on Nov 10, 2024

   

   G4brym

   on Nov 10, 2024

   Author

   More actions

   On a different subject, shouldn't startbase be a npm library?
   This way, users could more easily upgrade their installation to newer version.
   This library could also provide a base durable object, this way developers could implement their own business logic without entangling their code with starbase code, like:

   export class MyPersonalDO extends StartBase {
       async myBusinessLogic(...args) {
           this.query("select 1")
           // ...
       }
   }
2. Brayden commented on Nov 10, 2024

   

   Brayden

   on Nov 10, 2024

   Last edited by Brayden

   Member

   More actions

   @G4brym Great suggestions! First of all I loved seeing your project early on when the SQLite announcement came for Durable Objects!

   Traditional frameworks like Django rely heavily on sql transactions, like BEGIN; COMMIT; and ROLLBACK.

   I'll be honest, I had no idea. Out of curiosity and to edify myself, how does Django use the BEGIN/COMMIT/ROLLBACK paradigm?

   generating a unique id to new connections, and registering the new id with the connection using the tags api

   Question here and perhaps you know better than I do. I am in the middle of refactoring how Starbase positions logical components in the Worker versus the Durable Object and moving some of the web sockets logic away from the DO and into the Worker (see blog post here talking through it a bit: https://starbasedb.com/blog/rethinking-the-starbasedb-architecture/). In doing this, and hopefully I'm wrong, but it seems like the Tags API isn't available for web socket connections from a Worker?

   I had to go about it like this:

   clientConnected() {
       const webSocketPair = new WebSocketPair();
       const [client, server] = Object.values(webSocketPair);
   
       server.accept();
       server.addEventListener('message', event => {
           const { sql, params, action } = JSON.parse(event.data as string);
   
           if (action === 'query') {
               const executeQueryWrapper = async () => {
                   const response = await executeQuery(sql, params, false, this.dataSource);
                   server.send(JSON.stringify(response));
               };
               executeQueryWrapper();
           }
       });
   
       return new Response(null, { status: 101, webSocket: client });
   }

   From this point on, a new transaction is taking place, so when a server receives a message with action=query, we must check if the message is from the socket that sent the transaction.begin.
   If this is true, we execute the query, otherwise, we must block the query, this can be done using timeouts, this way the query execution will just be delayed, until the transaction is cleared.

   Question here. Could a bad client side implementation then that never passes a second message for COMMIT or ROLLBACK stall other web socket requests with type query from executing? Would there be a timeout or period of time where a client must send the follow up message of COMMIT/ROLLBACK so other requests in the queued can eventually be executed in a timely manner?

   On a different subject, shouldn't startbase be a npm library?

   Yes! Matter of fact there is someone contributing a WIP pull request that does this and allows for it to be easier to implement into other projects. See here: #29
3. Brayden commented on Nov 11, 2024

   

   Brayden

   on Nov 11, 2024

   Member

   More actions

   I went ahead and made a work in progress pull request on my current line of thinking on approach. Would be curious if what you're proposing here would still work with that approach or not (especially given that I'm thinking tags API isn't available in Workers but only Durable Objects – maybe I'm wrong though?).

   #37
4. G4brym commented on Nov 11, 2024

   

   G4brym

   on Nov 11, 2024

   Last edited by G4brym

   Author

   More actions

   I'll be honest, I had no idea. Out of curiosity and to edify myself, how does Django use the BEGIN/COMMIT/ROLLBACK paradigm?

   Django always opens a new transaction while applying a new migration, so if anything goes wrong, while moving data between tables, or adding columns, there is always a safe point to recover
   After the migrations, its mostly up to the developer to decide if they want to execute code inside transactions or not

   generating a unique id to new connections, and registering the new id with the connection using the tags api

   Question here and perhaps you know better than I do. I am in the middle of refactoring how Starbase positions logical components in the Worker versus the Durable Object and moving some of the web sockets logic away from the DO and into the Worker (see blog post here talking through it a bit: https://starbasedb.com/blog/rethinking-the-starbasedb-architecture/). In doing this, and hopefully I'm wrong, but it seems like the Tags API isn't available for web socket connections from a Worker?

   looking at the docs, it does seem to be only available inside DO's

   I had to go about it like this:

   clientConnected() {
       const webSocketPair = new WebSocketPair();
       const [client, server] = Object.values(webSocketPair);
   
       server.accept();
       server.addEventListener('message', event => {
           const { sql, params, action } = JSON.parse(event.data as string);
   
           if (action === 'query') {
               const executeQueryWrapper = async () => {
                   const response = await executeQuery(sql, params, false, this.dataSource);
                   server.send(JSON.stringify(response));
               };
               executeQueryWrapper();
           }
       });
   
       return new Response(null, { status: 101, webSocket: client });
   }

   I think websocket workers, can be evicted just like a normal worker. So if a client is connected to it instead of a DO, the connection can be evicted midway through the transaction

   From this point on, a new transaction is taking place, so when a server receives a message with action=query, we must check if the message is from the socket that sent the transaction.begin.
   If this is true, we execute the query, otherwise, we must block the query, this can be done using timeouts, this way the query execution will just be delayed, until the transaction is cleared.

   Question here. Could a bad client side implementation then that never passes a second message for COMMIT or ROLLBACK stall other web socket requests with type query from executing? Would there be a timeout or period of time where a client must send the follow up message of COMMIT/ROLLBACK so other requests in the queued can eventually be executed in a timely manner?

   Totally forgot to mention that, but in my implementation, i added a check on the websocketClose event, that when called it checks if the dropping connection was the one that started the transaction.
   If this is true, then always rollback to before the transaction

   	async webSocketClose(ws, code, reason, wasClean) {
   		ws.close(code, "Durable Object is closing WebSocket");
   
   		// If the socket disconnecting is the one in power, rollback!
   		if (this.isLocked()) {
   			const tags = this.ctx.getTags(ws)
   			if (tags[0] === this.sessionIdInPower) {
   				await this.rollbackTransaction()
   			}
   		}
   	}

   But a max time since last message, sounds great!
5. G4brym commented on Nov 11, 2024

   

   G4brym

   on Nov 11, 2024

   Author

   More actions

   I went ahead and made a work in progress pull request on my current line of thinking on approach. Would be curious if what you're proposing here would still work with that approach or not (especially given that I'm thinking tags API isn't available in Workers but only Durable Objects – maybe I'm wrong though?).

   #37

   I will take a look in a few days 😄

   🚀React with 🚀1Reacted by Brayden Wilmoth
6. wizlor-bfw commented on Aug 21, 2026

   

   wizlor-bfw

   on Aug 21, 2026

   More actions

   I started implementing this (session-scoped transaction.begin/commit/rollback over the websocket, using an app-level lock so other sessions' queries wait while one session's transaction is open — basically what's described above) and hit a platform-level blocker worth writing down here before anyone else spends time on the same approach.

   Raw BEGIN/COMMIT/ROLLBACK/SAVEPOINT sent through this.sql.exec() is rejected outright by the SQLite-backed Durable Object storage:

   To execute a transaction, please use the state.storage.transaction() or state.storage.transactionSync() APIs instead of the SQL BEGIN TRANSACTION or SAVEPOINT statements. The JavaScript API is safer because it will automatically roll back on exceptions, and because it interacts correctly with Durable Objects' automatic atomic write coalescing.
   

   (Tested locally against wrangler dev, wrangler 3.96.0.)

   storage.transaction()/transactionSync() — the APIs that error points to — can't be held open across separate websocket messages either. Their atomicity is implemented via blockConcurrencyWhile() under the hood, which pauses all further event processing on the Durable Object, including the very RPC call that would eventually deliver the commit/rollback signal needed to resolve the closure. Trying to keep the closure pending while awaiting a later, separate message deadlocks it — after roughly 30s the runtime kills it with:

   A call to blockConcurrencyWhile() in a Durable Object waited for too long. The call was canceled and the Durable Object was reset.
   

   So a transaction that stays open across arbitrary client round-trips — BEGIN in one message, queries in later messages, COMMIT/ROLLBACK whenever the client gets to it, which is what this issue is asking for and what Django's ORM needs — doesn't look like it can be safely implemented against the public Durable Object SQLite storage API today. Both building blocks Cloudflare offers are scoped to a single synchronous/async closure call and aren't meant to survive across separate incoming requests/messages.

   Two narrower things that are achievable within these constraints, in case either is useful:

   • A single-message batch transaction: send an array of queries in one message, executed atomically via storage.transactionSync(). This already exists as a REST concept (the transaction array on /query) and could be mirrored on the websocket.
   • A "buffered" begin/commit protocol: transaction.begin queues subsequent queries instead of running them immediately, and transaction.commit replays the whole batch atomically via transactionSync(). This gives real atomicity and a begin/commit-shaped protocol, but query results (including reads) aren't available until commit — no "read your own writes" mid-transaction — so it's likely not enough for a real DB-API-style ORM driver.

   Neither reproduces a genuinely held-open session transaction. Flagging this in case a maintainer knows of a Durable Object mechanism that gets around the blockConcurrencyWhile deadlock, or wants to steer this issue toward one of the reduced-scope options above instead.