porsager
commented
1 year ago Hey @igalklebanov ...
There's a reserve branch now at https://github.com/porsager/postgres/tree/reserve . Would be could if you could give it a go and see how it fits your need.
There's no docs yet, but the api is fairly simple (not set in stone though):
const sql = postgres(..)
const reserved = await sql.reserve()
p(await reserved`select pg_backend_pid()`)
p(await sql`select pg_backend_pid()`)
reserved.release()Looking forward to hear if that helps your implementation out.
igalklebanov
tim-smart
joshxyzhimself
Hey 👋
Thank you @porsager for the impressive work on this library! ❤️
People have been requesting Kysely support for postgres.js for a few months now due to its superior performance. So I have recently published a postgres.js dialect but it comes with caveats.
Kysely was built to wrap client libraries' pools. Ideally, a dialect should get a pool on instantiation, and on-the-fly acquire connections and execute queries/transactions.
This works well with postgres.js' API as far as single query scenario goes, but doesn't work in 2 scenarios:
postgres.js' transaction API is great, but Kysely needs full control over a transaction's lifecycle (execute begin and commit/rollback) for its transaction API to work. So you need to acquire a single connection and use it for multiple queries. Since postgres.js doesn't provide a connection getter, I had to eventually hack a "swap main pool with a N=1 pool for transaction" solution. So the main pool can co-exist with potentially many short-lived pools of 1. This is not ideal if the consumer does not want to exceed the main pool's max size.
Kysely has a single connection API that allows consumers to use a single connection within a callback and perform multiple queries on it. Close to transactions API but without the transaction part. This is impossible to implement right now, as postgres.js doesn't provide a connection getter, and Kysely's internals do not signal this scenario (can't do same swap done in first scenario). Making changes on Kysely's side here would be potentially breaking and quite awkward (signaling single connection mode when we should already be in that mode anyway, for an external dialect).
Was wondering if it's possible and simpler to add a connection getter/reservation in postgres.js, and whether its something you'd consider a good fit for the project?