Function Calls That Require Single Quoted Parameters Doesn't Work

[muratgozel]

The postgres internal function pg_get_serial_sequence(table_name, col_name) is a bit sensitive with quoting. Both table_name and col_name should be sent single-quoted. The following query works when executed from the psql:

select nextval( pg_get_serial_sequence('auth_session', 'id') ) as new_id;
# returns new_id = [number]

There is no way to achieve this in slonik.

Case:

const tableToken = sql.identifier(['auth_session'])
const fieldToken = sql.identifier(['id'])
const query = sql`select nextval(pg_get_serial_sequence(${tableToken}, ${fieldToken})) as new_id`
const result = await this.pgpool.query(query)

Expected Behavior

The result should return {rows: [{new_id: 1}]}

Current Behavior

It raises an error:

error: column "auth_session" does not exist
  at Parser.parseErrorMessage (/app/node_modules/pg-protocol/dist/parser.js:287:98)
  at Parser.handlePacket (/app/node_modules/pg-protocol/dist/parser.js:126:29)
  at Parser.parse (/app/node_modules/pg-protocol/dist/parser.js:39:38)
  at Socket.<anonymous> (/app/node_modules/pg-protocol/dist/index.js:11:42)
  at Socket.emit (node:events:390: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:199:23)

The query object:

query: {
  sql: 'select nextval(pg_get_serial_sequence("auth_session", "id")) as new_id',
  type: 'SLONIK_TOKEN_SQL',
  values: []
}

[nponiros]

I don't know if this is a bug or not (doesn't look like one to me) but I believe that there is a way to get this to work.

First off if I understand the documentation correctly the column name should be quoted as a string literal so you shouldn't use sql.identifier for it.

For the table name you are using you also don't need sql.identifier. You could try the below:

const tableToken = 'auth_session';
const fieldToken = 'id';
const query = sql`select nextval(pg_get_serial_sequence(${tableToken}, ${fieldToken})) as new_id`

In case you need to quote the table name or use a schema you could try the below:

const tableToken = sql.identifier(['auth_session']);
const fieldToken = 'id';
const query = sql`select nextval(pg_get_serial_sequence('${tableToken}', ${fieldToken})) as new_id`

Note the single quotes around ${tableToken}.

[gajus]

If the value is safe (e.g. comes from predefined list of values), then you can just use sql.raw – that's what it is created for.

[gajus]

Duplicate of #150

[gajus]

🎉 This issue has been resolved in version 24.2.0 🎉

The release is available on:

• npm package (@latest dist-tag)
• GitHub release

Your semantic-release bot 📦🚀

[gajus]

You can now use sql.literalValue.

[muratgozel]

@nponiros thanks for the suggestions but I already tried them and they weren't working. Now I better understand that this is more of a feature request/missing feature than a bug. 😌

@gajus sql.literalValue saved me. thanks. 😌

[nponiros]

@muratgozel strange it did work for me or at least I thought it did.. I didn’t get an error but I tested it without nextval only with pg_get_serial_sequence so maybe that was the issue or maybe I just had table/column names that happened to work. In any case now there is a better way to do it :)