Feature request: Support transactions using HTTP via batched queries

[pilcrowOnPaper]

I'm not sure if feature requests should be made here, or if this repo even accepts feature requests. Let me know if I need to move this discussion somewhere else.

Most serverless drivers unfortunately do not support transactions, including Neon's. To my knowledge, there are 2 exceptions:

1. Planetscale serverless db: Full transaction support
2. Cloudflare D1: Partial transaction support via batched queries

While I understand Planetscale's approach brings complexity and (likely) performance implications, I'm interested if Cloudlfare's approach is possible in Neon. In Cloudflare D1, you can declare multiple query statements at once which will then be execute in sequence as a transaction once it reaches their servers.

await db.batch([
    db.prepare("UPDATE users SET name = ?1 WHERE id = ?2").bind( "John", 17 ),
    db.prepare("UPDATE users SET age = ?1 WHERE id = ?2").bind( 35, 19 ),
]);

Since this approach just makes a single HTTP request, it provides most of the benefits of using transactions without any downsides of it.

[jawj]

Just to clarify, our driver does support transactions via the Client object, where traffic is carried over WebSockets.

It doesn't currently support transactions via the http transport. But we are continuing to explore the possibilities here.

[pilcrowOnPaper]

@jawj Yes I’m aware, the feature request is specifically for the HTTP transport.

[pilcrowOnPaper]

@jawj Ah, ok, re-reading your comment and docs/source-code, I realized that Neon's Pool is a drop-in replacement for pg but the implementation is different. I wrongly assumed it was just a wrapper around pg. The current web socket implementation fits my need so I'm going to close the issue.

[kleenkanteen]

Just to clarify, our driver does support transactions via the Client object, where traffic is carried over WebSockets.

It doesn't currently support transactions via the http transport. But we are continuing to explore the possibilities here.

Am curious, why did you choose to support WebSockets instead of HTTP?

[jawj]

Well, it was relatively straightforward to switch out TCP for WebSockets and get immediate compatibility with the main existing Postgres driver thrown in for free. :)

That said, we do now offer an HTTP transport option, including a batch mode i.e. non-interactive transactions. See the README: https://github.com/neondatabase/serverless/blob/main/README.md.