NeonDbError: scanner_yyerror on using a constant in the sql string template

[ManuelTS]

I get

{"name":"NeonDbError","severity":"ERROR","code":"42601","position":"13","file":"scan.l","line":"1241","routine":"scanner_yyerror"}

when I use this simple code in an nuxt API endpoint

import { HTTP_STATUS_CODE_OK, KEY_QUOTES } from '~/utils/constants';
import { EventHandlerRequest, H3Event } from 'h3';
import { apiErrorHandler } from '~/utils/apiUtils';
import { sql } from '@vercel/postgres';

export default defineEventHandler((event: H3Event<EventHandlerRequest>): Promise<string> =>
  sql`DELETE FROM ${KEY_QUOTES};`
    .then((): string => {
      setResponseStatus(event, HTTP_STATUS_CODE_OK);
      return 'ok';
    })
    .catch(e => apiErrorHandler(event, e)) // Just logs and returns the error as JSON
);

// constants.ts
//...
export const KEY_QUOTES = 'quotes';

and my setup is

dependencies": {
    "@nuxt/image": "1.7.0",
    "@nuxtjs/i18n": "8.3.1",
    "@nuxtjs/robots": "4.0.0",
    "@nuxtjs/sitemap": "5.3.4",
    "@vercel/postgres": "0.9.0",
    "nuxt": "3.11.2",
    "nuxt-schema-org": "3.3.8",
    "vue": "3.4.27",
    "vue-router": "4.4.0"
  },
  "devDependencies": {
    "@nuxtjs/critters": "0.7.1",
    "@vue/eslint-config-prettier": "9.0.0",
    "nuxt-purgecss": "2.0.0",
    "sass": "1.77.8",
    "vercel": "35.2.1"
  },
  "engines": {
    "node": "20.16.0",
    "npm": "10.8.1"
  }

When I change the const in the string template to the const value as in

import { HTTP_STATUS_CODE_OK, KEY_QUOTES } from '~/utils/constants';
import { EventHandlerRequest, H3Event } from 'h3';
import { apiErrorHandler } from '~/utils/apiUtils';
import { sql } from '@vercel/postgres';

export default defineEventHandler((event: H3Event<EventHandlerRequest>): Promise<string> =>
  sql`DELETE FROM quotes;`
    .then((): string => {
      setResponseStatus(event, HTTP_STATUS_CODE_OK);
      return 'ok';
    })
    .catch(e => apiErrorHandler(event, e)) // Just logs and returns the error as JSON
);

it works as it should

[vvo]

@ManuelTS Postgres doesn't allow to dynamically craft table names as seen here: https://github.com/brianc/node-postgres/issues/1426

What you could do, if you're 100% sure the table name comes from your code and not user input:

sql.query(`DELETE FROM ${KEY_QUOTES};`);

Or use something like pg-format https://stackoverflow.com/a/51154958/147079

Thanks!

Documentation: https://node-postgres.com/features/queries#parameterized-query

[ManuelTS]

Thanks for the tip, now it works @vvo. I got this from https://vercel.com/docs/storage/vercel-postgres/quickstart#preparing-your-local-project so their documentation is utterly wrong on that subject. They use the sql method with string templates all the time in there.