Kysely client usage and pooling question
Hello,
We have a long running server that at the moment creates a single Kysely instance. It is setup to use pg pooling. We have a util function called getKysely which returns this instance and is used throught our backend , like on every request handler that calls the DB. So in theory multiple concurrent requests call this function to get the Kysely client and make queries.
We are noticing that even within the same API endpoint handler, executing queries with a fetched client seems to be running on different connections. We think this because we sometimes execute raw sql queries which change the role of the connection, but later on using the same client we execute a query to check the current role and it is not the one set up previously.
I am curious about how Kysely internally uses pooling, does it fetch a connection from the pool on every query? I know we are using a singleton, but how can I make sure that Kysely uses a single connection throughout the scope of a single request?
Solution:Jump to solution
Kysely takes a connection from the pool for every query. It might be a brand new one or an existing connection in the pool. The pool is in charge of that. Kysely just requests a connection from the pool.
There is no way the pool could know about your requests and return the same connection. How do you imagine that could ever work? A request is not a fundamental ecmascript concept. There's no "request local storage" that could be used to store that info.
If you're coming from the java world, there you have a thread per request (at least with ancient frameworks) and the thread-local storage can be used to get the same connection/transaction automatically....
5 Replies
The more I test out different setups, the more I am arriving to the conclusion that the only way to prevent this is using transactions? Is there no way with kysely to ensure the same connection is utilized byt the client for the same request?
Another part to note is that we are using Supabase, which also has its own Pooler called Supavisor, Im not sure if that also plays into this behavior.
Solution
Kysely takes a connection from the pool for every query. It might be a brand new one or an existing connection in the pool. The pool is in charge of that. Kysely just requests a connection from the pool.
There is no way the pool could know about your requests and return the same connection. How do you imagine that could ever work? A request is not a fundamental ecmascript concept. There's no "request local storage" that could be used to store that info.
If you're coming from the java world, there you have a thread per request (at least with ancient frameworks) and the thread-local storage can be used to get the same connection/transaction automatically.
The only way to use the same connection is to start a transaction or use the
connection() method on the Kysely instance.Thanks for clarifying this. How does the "connection" method work?
Kysely | kysely
Documentation for kysely
Or just hover over the method to see that doc