You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
To make it even more nice, it would be good to have a current_setting initialisation setup feature.
Let me start from describing a use case.
I have a web application that works with PostgreSQL using this library. Data manipulation logic is implemented in stored functions and data query logic is implemented in views.
When user sends a request, all queries and mutations have to be performed in the user context, in other words all views and stored functions have to have access to the user ID.
The simplest way is to pass ID as one of parameters to functions and as additional condition in WHERE statement of queries from views but it is a lot of boilerplate code in case of functions, and easy to forget in case of views.
The better approach is to set a configuration parameter, e.g. current_user_id, and use it inside functions, views or even Row-Level Security policies.
Example:
SET current_user_id = '153';
# or
SELECT set_config('current_user_id', '153', false);
and access it in functions and views via current_setting('current_user_id', true)
However, there is a challenge since
web application can send multiple queries to database while performing one user request, and often those queries has to be performed in parallel
Postgres library uses a pool of connections, and it is impossible to predict which one will be taken
Workaround would be to start a transaction, set configuration parameter at the beginning and then perform queries, but in this case it is impossible to run queries in parallel. Or we have to run each query in its own transaction and set configuration parameter in each of them - quite a lot of boilerplate code.
Nice solution would be to have a configuration settings parameter that would be automatically applied by library to all connections taken from the pool before processing any SQL command.
Example:
import sql from './db.js'
sql.config({ current_user_id: 153 })
Promise.all([sql`...`, sql`...`]) # current_user_id is set for each sql command
There could be more advanced version that allows to execute any sql command:
import sql from './db.js'
sql.config`SET current_user_id = '153'`
Promise.all([sql`...`, sql`...`]) # current_user_id is set for each sql command
Obviously that in the second version sql can contain any sql command, not only SET.
reacted with thumbs up emoji reacted with thumbs down emoji reacted with laugh emoji reacted with hooray emoji reacted with confused emoji reacted with heart emoji reacted with rocket emoji reacted with eyes emoji
-
First of all, thank you for such a nice library.
To make it even more nice, it would be good to have a
current_setting
initialisation setup feature.Let me start from describing a use case.
I have a web application that works with PostgreSQL using this library. Data manipulation logic is implemented in stored functions and data query logic is implemented in views.
When user sends a request, all queries and mutations have to be performed in the user context, in other words all views and stored functions have to have access to the user ID.
The simplest way is to pass ID as one of parameters to functions and as additional condition in
WHERE
statement of queries from views but it is a lot of boilerplate code in case of functions, and easy to forget in case of views.The better approach is to set a configuration parameter, e.g.
current_user_id
, and use it inside functions, views or even Row-Level Security policies.Example:
and access it in functions and views via
current_setting('current_user_id', true)
However, there is a challenge since
Workaround would be to start a transaction, set configuration parameter at the beginning and then perform queries, but in this case it is impossible to run queries in parallel. Or we have to run each query in its own transaction and set configuration parameter in each of them - quite a lot of boilerplate code.
Nice solution would be to have a configuration settings parameter that would be automatically applied by library to all connections taken from the pool before processing any SQL command.
Example:
There could be more advanced version that allows to execute any sql command:
Obviously that in the second version sql can contain any sql command, not only
SET
.Beta Was this translation helpful? Give feedback.
All reactions