Effect CommunityEC
Effect Community•11mo ago•
3 replies
wayne

Issue with Session Persistence in SQL-Kysely Transactions

I'm implementing row level security in postgres, so my statements are typically of the form:
set app.current_tenant_id='abc';
select * from messages;

This works for me in a standard sql ide/repl, but I can't seem to get it working with 
@effect/sql-kysely
. This is my attempt:
  db.withTransaction(
      Effect.gen(function* () {
          yield* Effect.tryPromise({
              try: () => db.executeQuery(CompiledQuery.raw('set app.current_tenant_id="abc"')),
              catch: (e) =>
                  new SqlError.SqlError({
                      cause: e,
                      message: 'Error setting current_tenant_id',
                  }),
          })
          return yield* db.selectFrom('messages').selectAll()
      }),
  )

The first statement executes fine, but the configuration parameter (current_tenant_id) is not carried over to the second statement, and so the policies I've implemented cause the error PostgresError: unrecognized configuration parameter \"app.current_tenant_id\". Does the above execute in the same session or should I be doing this another way?
Was this page helpful?