Next.js Discord

Discord Forum

Help me with embedding a sql query inside backsticks `` - My head is exploding

Answered
Bonga shad posted this in #help-forum
Open in Discord
Bonga shadOP
I am using vercel's postgres db and its SDK. Getting a headache trying to embed my sql query inside the sql sdk.

import format from 'pg-format'; // use the pg-format library



export async function fetchAutoCompleteSearchAreas(query: string) {
    console.log(query);
    const sqll = format("SELECT id, geo_name, resource_name, geo_type FROM geo_areas WHERE LOWER(geo_name) LIKE LOWER('%s%')", query);
    console.log('query: ' + sqll);
    const autoCompleteAreas = await sql<AutoCompleteSearchArea>`
    ${sqll}
    `;
    return autoCompleteAreas.rows;
}


I keep getting this syntax error:

POST /map 500 in 288ms
  │ POST https://ep-broad-leaf-m4azxee4-pooler.us-east-1.aws.neon.tech/sql 400 in 264ms (cache: SKIP)
  │  │  Cache missed reason: (fetchCache = force-no-store)
Alaba
query: SELECT id, geo_name, resource_name, geo_type FROM geo_areas WHERE LOWER(geo_name) LIKE LOWER('Alaba%')
 ⨯ node_modules/@neondatabase/serverless/index.mjs (1539:27) @ execute
 ⨯ NeonDbError: syntax error at or near "$1"
    at async fetchAutoCompleteSearchAreas (./app/lib/data.ts:159:31)
Answered by Bonga shad
turns out quite simple, use sql.query() instead
View full answer

13 Replies

Bonga shadOP
If I ran the printed out query SELECT id, geo_name, resource_name, geo_type FROM geo_areas WHERE LOWER(geo_name) LIKE LOWER('Alaba%') it succeeded, so I think the problem is on how I embed it in the sql method
Bonga shadOP
turns out quite simple, use sql.query() instead
Answer
American Crow
Does it work without the % at the end of Alaba%.? If yes you prob have to escape that%.
Bonga shadOP
Hi @American Crow again, thank you for chiming in and helping out!
American Crow
Well you solved it in this second :q
Bonga shadOP
yes that % in Alaba% is to match only the prefix, it's a sql syntax.
Well, I'll see you around 🙂
American Crow
I know it is
I was just wondering if its causing the problem
@Bonga shad Well, I'll see you around 🙂
American Crow
Mark your own message as solution.
Mugimaki Flycatcher
Bonga shadOP
Hi @Mugimaki Flycatcher what do you mean?
@Bonga shad Hi <@415204287589908491> what do you mean?
Mugimaki Flycatcher
You highlight code syntax by adding an extension