POC: Sequential transaction suport

[nicksrandall]

Taking inspiration from prisma, here is a proposed transaction API.

const sql = neon(...);
const results = await sql.transaction([
  sql`SELECT ${1}::int AS int`,
  sql`SELECT ${"hello"} AS str`
], /* options */);

The cool thing about this is that it is a non-breaking change because the sql function works as it always did.

This also paves the way for interactive transactions in the future:

const sql = neon(...);
await sql.transaction(async (trx) => {
  await trx`SELECT ${1}::int AS int`,
  await trx`SELECT ${"hello"} AS str`
}, /* options */);

[nicksrandall]

Sorry, my editor automatically formatted the files with prettier. I can revert those changes if you're open to merging this.

[nicksrandall]

I'll explain a little how this works because it uses a little bit of Promise hackery (that I saw in Prisma codebase).

Basically, the sql function is split into two parts:

1. prepare - this prepares the query and params
2. execute - this actually executes the http request

These two parts are separated by a promise chain, so the execute part isn't actually run until the first promise is awaited (or then is called on it).

This way, the sql function still works as it did before (await/then was always being used to get final result of the query).

However, when used in a transaction (notice how await is not used for the individual queries), the http query is never sent for the individual queries and instead the function is able to collect all the prepared queries together and then run a batch http request instead.

[skyzh]

These two parts are separated by a promise chain, so the execute part isn't actually run until the first promise is awaited (or then is called on it).

That's the tricky part that I didn't think about before, but overall it looks good!

[nicksrandall]

I added support for setting isolation level and read-only mode according to: https://github.com/neondatabase/neon/pull/4830

[janpio]

I think the suggested APIs are really great 😆 👍

[jawj]

Sorry, my editor automatically formatted the files with prettier. I can revert those changes if you're open to merging this.

@nicksrandall, I'm taking a look at this now. Would it be straightforward for you to undo the formatting changes? It will make the substantive changes much easier to follow.

[nicksrandall]

@jawj I did my best to revert all the formatting changes.

FWIW, I highly recommend implementing a formatting standard like Prettier if you plan to accept PRs from the community. It was hard for me to figure out what the code style should be in this repo because it's inconsistent.

[jawj]

@nicksrandall Thanks so much! Yes, you're probably right about the formatting (current code style is whatever I think looks nicest, which is obviously requires telepathy in the general case).