Trouble using Supabase's Postgres transaction pooler

[frederikheld]

I'm trying to use Sidequest with Supabase's postgres transaction pooler. It works until it fails with the error relation "sidequest_jobs" does not exist.

I had the same error in my own code, so I know what is going on here: the relation does exist but not in the current schema. This is an issue with the transaction pooler, which might hand out a connection that was previously used on a different schema. Therefore you have to explicitly set the schema before you run a query.

I tried to figure out how to configure Knex to work with the transaction pooler. The safe approach would be to execute the query SET search_path to sidequest (replace sidequest with the respective config value). So far I couldn't figure out how to do that.

What is the right way to set up sidequest to work with a pooled connection?

Thanks in advance!

EDIT: another relevant thing to mention is that the transaction pooler does not support prepared statements

Here's the full error log:

[info] [2025-12-16 15:53:20] [Sidequest] [Worker] : Starting worker with provided configuration...
[error] [2025-12-16 16:00:00] [Sidequest] [Worker] : Error on running CleanupJob! delete from "sidequest_jobs" where ("completed_at" < $1 or "failed_at" < $2 or "canceled_at" < $3) - relation "sidequest_jobs" does not exist
{
  length: 113,
  name: 'error',
  severity: 'ERROR',
  code: '42P01',
  detail: undefined,
  hint: undefined,
  position: '13',
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'parse_relation.c',
  line: '1449',
  routine: 'parserOpenTable',
  [Symbol(level)]: 'error',
  [Symbol(splat)]: [
    error: delete from "sidequest_jobs" where ("completed_at" < $1 or "failed_at" < $2 or "canceled_at" < $3) - relation "sidequest_jobs" does not exist
        at Parser.parseErrorMessage (/path/to/project/task-runner/node_modules/pg-protocol/dist/parser.js:285:98)
        at Parser.handlePacket (/path/to/project/task-runner/node_modules/pg-protocol/dist/parser.js:122:29)
        at Parser.parse (/path/to/project/task-runner/node_modules/pg-protocol/dist/parser.js:35:38)
        at Socket.<anonymous> (/path/to/project/task-runner/node_modules/pg-protocol/dist/index.js:11:42)
        at Socket.emit (node:events:524:28)
        at addChunk (node:internal/streams/readable:561:12)
        at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
        at Readable.push (node:internal/streams/readable:392:5)
        at TCP.onStreamRead (node:internal/stream_base_commons:189:23) {
      length: 113,
      severity: 'ERROR',
      code: '42P01',
      detail: undefined,
      hint: undefined,
      position: '13',
      internalPosition: undefined,
      internalQuery: undefined,
      where: undefined,
      schema: undefined,
      table: undefined,
      column: undefined,
      dataType: undefined,
      constraint: undefined,
      file: 'parse_relation.c',
      line: '1449',
      routine: 'parserOpenTable'
    }
  ]
}
error: delete from "sidequest_jobs" where ("completed_at" < $1 or "failed_at" < $2 or "canceled_at" < $3) - relation "sidequest_jobs" does not exist
    at Parser.parseErrorMessage (/path/to/project/task-runner/node_modules/pg-protocol/dist/parser.js:285:98)
    at Parser.handlePacket (/path/to/project/task-runner/node_modules/pg-protocol/dist/parser.js:122:29)
    at Parser.parse (/path/to/project/task-runner/node_modules/pg-protocol/dist/parser.js:35:38)
    at Socket.<anonymous> (/path/to/project/task-runner/node_modules/pg-protocol/dist/index.js:11:42)
    at Socket.emit (node:events:524:28)
    at addChunk (node:internal/streams/readable:561:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:512:3)
    at Readable.push (node:internal/streams/readable:392:5)
    at TCP.onStreamRead (node:internal/stream_base_commons:189:23)

[GiovaniGuizzo]

How are you configuring the backend? You need to provide the schema if you applied migrations before on a different schema.

[frederikheld]

I don't change anything in the Sidequest schemas. I'm just using Sidequest with the same Postgres database I'm deploying my own schemas to. Those schemas are in different search paths, so there should be no interference, as Sidequest allows me to configure a custom search path.

The issue here is not the schema but the reused connections from the transaction pooler. The connection will run the query on the search path that is currently set. If the previous request set a different search path than the one that I have configured for Sidequest, the query will fail. Connections are re-used across schemas!

Sidequest needs to set the search path before it runs the query. Otherwise it can't be used with pooled connections.

[frederikheld]

Maybe it shouldn't be used with pooled connections? As it is polling the database every 100 ms, the direct connection might be the better choice?

The issue with the direct connection was that it breaks after some time. Idk if it breaks because Supabase terminates idle connections (it shouldn't be idle if it polls every 100 ms), if Supabase terminates connections after a certain time or if it was terminated due to network issues.

However: the direct connection was unreliable. But the pooler doesn't work well either.

So what's the reliable way to connect to a postgres database that is neither on the same server nor in the same data center (I think it isn't even on the same continent) than my Sidequest task runner?