Problem

In Postgres you can set a user for a connection on the database:

await prisma.$executeRaw(`SET current_user_id = ${currentUser.id}`)

This SET can then be used in combination with row-level security (RLS) to issue queries like this:

select * from messages

that only give you back messages from that user. This technique is used by Postgraphile and Postgrest and really takes advantage of what Postgres offers.

Without access to the connection pool, there's no way to guarantee you'll get the same connection each query. Since SET values are bound to the query, subsequent queries may be missing the SET or may even override another request's SET.

I'm not sure what the best approach is. Tying a connection to the lifecycle of a request has its own performance implications.

A point of reference potentially worth investigating. Go's standard SQL library uses a connection pool under the hood that's transparent to developers. Do they run into this problem too? If so, do they or how do they deal with it?

Originally from: #4303 (comment)