From Discord: Issue with Session Persistence in SQL-Kysely Transactions

[effect-bot]

Summary

Summary:

Wayne is implementing row-level security in a PostgreSQL database, setting a session parameter app.current_tenant_id before executing a query, which works in a SQL IDE/repl. However, when using the @effect/sql-kysely library in TypeScript, the session configuration doesn't persist between queries within a transaction. Wayne provided the initial code attempt and encountered an error indicating the session setting wasn't recognized. After further investigation, Wayne found that db.executeQuery from @effect/sql-kysely does not execute the session parameter setting correctly, unlike when using the kysely library directly, which does work as expected.

Key Takeaways:

1. Transaction Scope: It's crucial to ensure commands like setting session parameters are executed within the same transaction/session scope.

2. Library Specific Behavior: Third-party libraries like @effect/sql-kysely may not behave the same as their underlying libraries (like kysely) and may require custom handling or a different approach to execute raw SQL statements correctly.

3. Debugging and Testing: When encountering issues, cross-verifying with the underlying library (kysely in this case) can help identify if the problem lies within the third-party wrapper or elsewhere.

4. Effectful Programming Understanding: Understanding how asynchronous operations are wrapped in effectful programming libraries is key, as seen with the Effect.tryPromise not influencing the execution of a non-effectified method.

Discord thread

https://discord.com/channels/795981131316985866/1339354717498773555

[wmaurer]

The above AI-bot description doesn't really describe the problem well. Here's the issue:

@effect/sql-kysely does not 'effectify' executeQuery, and running the promise returned by executeQuery returns no results.

The following kysely code works:

import { CompiledQuery, Kysely, PostgresDialect } from 'kysely'
import { Pool } from 'pg'

const dialect = new PostgresDialect({
    pool: new Pool({ database: 'postgres', host: 'localhost', user: 'user', password: 'password' }),
})

const db = new Kysely<unknown>({ dialect })

const program = async () => {
    return await db.executeQuery(CompiledQuery.raw(`select 'abc'`))
}

program().then(console.log).catch(console.error)

The output is:
{ rows: [ { '?column?': 'abc' } ] }

The equivalent code with @effect/sql-kysely does not work:

import { NodeContext, NodeRuntime } from '@effect/platform-node'
import { SqlError } from '@effect/sql'
import * as PgKysely from '@effect/sql-kysely/Pg'
import { PgClient } from '@effect/sql-pg'
import { Console, Context, Effect, Layer, Redacted } from 'effect'
import { CompiledQuery } from 'kysely'

export class PgDB extends Context.Tag('PgDB')<PgDB, PgKysely.EffectKysely<unknown>>() {}

const PgLive = PgClient.layer({ database: 'postgres', username: 'user', password: Redacted.make('password') })

export const KyselyLive = Layer.effect(PgDB, PgKysely.make<unknown>()).pipe(Layer.provide(PgLive))

const toSqlEffect = <A>(a: () => Promise<A>) =>
    Effect.tryPromise({ try: a, catch: (e) => new SqlError.SqlError({ cause: e }) })

const program = Effect.gen(function* () {
    const db = yield* PgDB
    return yield* toSqlEffect(() => db.executeQuery(CompiledQuery.raw(`select 'abc'`)))
})

program.pipe(
    Effect.provide(NodeContext.layer),
    Effect.provide(KyselyLive),
    Effect.tap(Console.log),
    NodeRuntime.runMain,
)

The output is:
{ rows: [] }