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)

The second example doesn't work. When doing something like products = await sql`${query}, it returns syntax error at or near "$1" — Hubert
– Hubert, Commented Aug 8, 2023 at 20:04
If it's all in one variable, query, do you even need the template literals? I'm not familiar with postgresql or vercel, but what about products = await sql query; — mykaf
– mykaf, Commented Aug 8, 2023 at 20:07
Yes, unfortunately it does require using the template literals syntax when running the query. — Hubert
– Hubert, Commented Aug 8, 2023 at 20:09
First, you need to end your SQL query with a semicolon so use await sql`${query};`. Second, you might try inspecting your query before running it using sqlTemplate as shown in their tests: github.com/vercel/storage/blob/main/packages/postgres/src/… — TestWell
– TestWell, Commented Aug 8, 2023 at 21:01

Bergi · Accepted Answer · 2023-08-09 13:59:43Z

6

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.

edited Aug 9, 2023 at 13:59

answered Aug 9, 2023 at 4:23

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

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

4 Comments

Hubert Over a year ago

Fantastic, that did the trick - thanks! I tried using Prisma and while it worked I found it painfully slow and also not very useful when having to build more complex queries. I have yet to find an ORM that justifies the additional overhead. Just my personal taste.

Elnoor Over a year ago

@Bergi, do you have an idea how bulk insertion would work ? I couldn't find an example within the docs. Thank you!

Bergi Over a year ago

@Elnoor stackoverflow.com/questions/24008668/…

Elnoor Over a year ago

@Bergi thank you! stackoverflow.com/a/77093839/5435780

Collectives™ on Stack Overflow

Dynamically building a SQL query (postgres and javascript)

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related