Syntax Error with @vercel/postgres Template Literal When Interpolating Values

Question

I'm trying to use the @vercel/postgres library in my Next.js API route to execute a SQL query with interpolated values, but I'm encountering a "syntax error at or near $1" error. Here's my code:

import { sql } from '@vercel/postgres';

export default async function handler(request, response) {
  try {
    const { page, limit, short, search, category, dateSort } = request.query;
    const pageNumber = parseInt(page) || 1;
    const offset = (pageNumber - 1) * limit;

    let query = sql`
      SELECT c.*, u.first_name, u.last_name, u.profile_photo, COUNT(e.id) AS enrolments, cat.name AS category_name
      FROM courses c
      LEFT JOIN users u ON c.user_id = u.id
      LEFT JOIN enrolments e ON c.id = e.course_id
      LEFT JOIN categories cat ON c.category_id = cat.id
      WHERE c.approved = true
    `;

    if (search) {
      query = sql`${query} AND (c.title LIKE ${sql.literal(`%${search}%`)} OR c.short_desc LIKE ${sql.literal(`%${search}%`)})`
    }

    if (category) {
      query = sql`${query} AND c.category_id = ${category}`;
    }

    let orderBy = [];
    if (short) {
      orderBy.push(`c.latest_price ${short}`);
    }

    const currentDate = new Date();
    if (dateSort) {
      switch (dateSort) {
        case 'recent':
          orderBy.push('c.created_at DESC');
          break;
        case 'in_month':
          query = sql`${query} AND c.created_at >= ${sql`${currentDate.getFullYear()}-${currentDate.getMonth() + 1}-01`}`;
          break;
        case 'in_year':
          query = sql`${query} AND c.created_at >= ${sql`${currentDate.getFullYear()}-01-01`}`;
          break;
      }
    } else {
      orderBy.push('c.created_at ASC');
    }

    if (orderBy.length > 0) {
      query = sql`${query} ORDER BY ${sql.join(orderBy, ', ')}`;
    }

    query = sql`${query} LIMIT ${limit} OFFSET ${offset}`;

    const { rows: courses, rowCount: coursesCount } = await query;

    const totalPages = Math.ceil(coursesCount / limit);

    return response.status(200).json({ courses, totalPages, coursesCount });
  } catch (error) {
    return response.status(500).json({ message: error.message });
  }
}

I'm using the sql tagged template literal to construct the SQL query and interpolating values like search, category, limit, and offset. I'm also using sql.literal to escape string values like search.

However, when I run this code, I get the following error:

⨯ unhandledRejection: NeonDbError: syntax error at or near "$1"

at execute (file:///Users/nic/Desktop/proj-beta/node_modules/@neondatabase/serverless/index.mjs:1539:48)

at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {

code: '42601',

sourceError: undefined

} GET /api/all-courses/ 500 in 654ms

I've tried different approaches, like using sql.value instead of interpolating directly, but I still get the same error. What am I doing wrong, and how can I properly interpolate values into the SQL query using @vercel/postgres?

What did you try and what were you expecting? we did mentioned way

Rashing · Accepted Answer · 2025-04-14 15:59:54Z

0

Probably it's because you can't use $1 parameters to replace any part of sql query. It can be use for parameters values only. For example:

UPDATE Users SET username = $1 WHERE user_id = $2

Will work fine
But:

UPDATE $1 SET $2 = 'new-name` WHERE user_id = $3

Will give an error because we trying to replace table and column name with parameters

answered Apr 14 at 15:59

Rashing

111 bronze badge

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax Error with @vercel/postgres Template Literal When Interpolating Values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related