raw sql with character ' | question

[seeden]

I will get next error: bind message supplies 4 parameters, but prepared statement "" requires 3 When I try to use:

db.selectFrom('verifyCodes')
      .where('userId', '=', userId)
      .where('name', '=', name)
      .where('used', '=', false)
      .where('expireAt', '>', sql`NOW() + INTERVAL '${maxExpireIn} HOURS'`)
      .selectAll('verifyCodes')
      .executeTakeFirst();

  select "verify_codes".* from "verify_codes" where "user_id" = $1 and "name" = $2 and "used" = $3 and "expire_at" > NOW() + INTERVAL '$4 HOURS'

    [ 971807, 'test', false, 1 ]

Stacktrace

error: bind message supplies 4 parameters, but prepared statement "" requires 3
        at Parser.parseErrorMessage (/Users/seeden/Documents/git/quizana-api/node_modules/pg-protocol/src/parser.ts:369:69)
        at Parser.handlePacket (/Users/seeden/Documents/git/quizana-api/node_modules/pg-protocol/src/parser.ts:188:21)
        at Parser.parse (/Users/seeden/Documents/git/quizana-api/node_modules/pg-protocol/src/parser.ts:103:30)
        at Socket.<anonymous> (/Users/seeden/Documents/git/quizana-api/node_modules/pg-protocol/src/index.ts:7:48)
        at Socket.emit (node:events:513:28)
        at addChunk (node:internal/streams/readable:324:12)
        at readableAddChunk (node:internal/streams/readable:297:9)
        at Socket.Readable.push (node:internal/streams/readable:234:10)
        at TCP.onStreamRead (node:internal/stream_base_commons:190:23)
        at TCP.callbackTrampoline (node:internal/async_hooks:130:17)
        at PostgresConnection.executeQuery (/Users/seeden/Documents/git/quizana-api/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:86:69)
        at processTicksAndRejections (node:internal/process/task_queues:95:5)
        at PostgresConnection.connection.executeQuery (/Users/seeden/Documents/git/quizana-api/node_modules/kysely/dist/cjs/driver/runtime-driver.js:76:24)
        at /Users/seeden/Documents/git/quizana-api/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:36:28
        at DefaultConnectionProvider.provideConnection (/Users/seeden/Documents/git/quizana-api/node_modules/kysely/dist/cjs/driver/default-connection-provider.js:12:20)
        at DefaultQueryExecutor.executeQuery (/Users/seeden/Documents/git/quizana-api/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:35:16)
        at SelectQueryBuilder.execute (/Users/seeden/Documents/git/quizana-api/node_modules/kysely/dist/cjs/query-builder/select-query-builder.js:634:24)
        at SelectQueryBuilder.executeTakeFirst (/Users/seeden/Documents/git/quizana-api/node_modules/kysely/dist/cjs/query-builder/select-query-builder.js:642:26)

Kysely version: 0.21.6

[seeden]

Problem is with sqlNOW() + INTERVAL '${maxExpireIn} HOURS' which is not working correctly. Do you have any idea how to write it correctly?

[koskimas]

The problem isn't a query with a ' character. '1 HOURS' is just a string and kysely definitely supports strings. The problem in your query is that you've put a parameter inside a string, which will lead Postgres to not consider it as a parameter.

There is no way for Kysely to automatically "fix" this, since it would mean parsing the SQL around the parameter to figure out if it's inside a string. And that would mean having a full-blown Postgres SQL parser in javascript, which would be tens of megabytes of code.

INTERVAL doesn't seem to accept bindings at all so you need to use a string literal:

const maxExpiresInHours = sql.literal(`${maxExpireIn} HOURS`)

db.selectFrom('verifyCodes')
      .where('userId', '=', userId)
      .where('name', '=', name)
      .where('used', '=', false)
      .where('expireAt', '>', sql`NOW() + INTERVAL ${maxExpiresInHours}`)
      .selectAll('verifyCodes')
      .executeTakeFirst()

https://koskimas.github.io/kysely/interfaces/Sql.html#literal

[noppa]

Side note, interval doesn't accept bindings, but you can do ${binding}::interval instead, which may be safer if the interval value is somehow coming from user input. Altho in this case literal should be fine if we know maxExpireIn is a number.