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?
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
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
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()