Dynamically building a SQL query (postgres and javascript)

Question

I have a javascript function that needs to return a list of products. It's using postgres to retrieve the list of products. The function is passed a categoryId and an optional typeId. So I need to build a SQL query based on these.

Of course I could do something like this:

async function getProducts(categoryId, typeId = false) {
  let products;
  if (typeId) {
    products = await sql`select * from products where categoryId=${categoryId} and typeId=${typeId}`
  }
  else {
    products = await sql`select * from products where categoryId=${categoryId}`
  }
  return products;
}

But how could I somehow dynamically "build" the SQL query with the template literals syntax?

Something like this:

async function getProducts(categoryId, typeId = false) {
let query = `select * from products where categoryId=${categoryId}`;
if (typeId) {
  query += ` AND typeId=${typeId}`;
}
products = await sql`${query}` // doesn't work, so how could I generate a dynamic query like this?
return products;
}

(Note: this is using @vercel/postgres)

Answer 1

It's not documented by Vercel but their @vercel/postgres package is just a tight wrapper around @neondatabase/serverless so you can use their methods as well. The sql`…`, pool.sql`…` and client.sql`…` template tags cannot be used in any other way, they will throw an exception when not used with a template string. However, you can use the .query() method which takes the query test and the parameter values as separate arguments:

async function getProducts(categoryId, typeId) {
  let query = `select * from products where categoryId=$1`;
  let params = [categoryId];
  if (typeId != null) {
    query += ` AND typeId=$2`;
    params.push(typeId);
  }
  const products = await sql.query(query, params);
  return products;
}

Of course, building SQL strings manually is risky - it easily leads to syntax errors and SQL injection security vulnerabilities in your software. Make sure to always use parameterised queries and never interpolate dynamic values into your queries.

For common use cases (like dynamic conditions with various fields) consider using an ORM or query builder library instead, they come equipped with tried-and-tested helper functions for these things.