Querying Postgres with multiple parameters Node.js

Question

I'm just starting with Node.js to build a Node.js server with Postgres db, I have in place a few routes and methods for CRUD operations :

router.get('/products', productController.listAllProducts);

exports.listAllProducts = async (req, res) => {
  const response = await db.query('SELECT * FROM products ORDER BY productName ASC');
  if (response.rowCount > 0) {
    res.status(200).send(response.rows);
  } else {
    res.status(404).send({
      message: "No products found"
    });
  }
};

and in Postman the url localhost:3000/api/products works just fine. Now I'm trying to include 3 parameters in the query city, region and country

router.get('/products', productController.findCityProducts); // correct route??

exports.findCityProducts = async (req, res) => {
  const city = req.query.city;
  const region = req.query.region;
  const country = req.query.country;
  const response = await db.query('SELECT * FROM products WHERE city = $1 AND region = $2 AND country = $3', [city,region,country]);
  if (response.rowCount > 0) {
    res.status(200).send(response.rows);
  } else {
    res.status(404).send({
      message: 'Product not found'
    });
  }
};

and in Postman I add the 3 parameters so the url is localhost:3000/api/products?city=Bologna&region=Emilia-Romagna&country=Italy but the results are the same as for the listAllProducts method.. and there is no record that satisfies the query..so I guessed the '/products' route can't have more than one method assigned..and is calling listAllProducts..I commented it out and indeed now is calling findCityProduct`.

How would I to enable both the listAllProducts and findCityProduct queries? Many thanks.

Answer 1

As you found out, you can't have two routes with the same path and HTTP method (GET /products) defined using different controller functions (listAllProducts, findCityProducts).

One simple approach you could take here is to adapt your controller function to behave differently depending on the existence of parameters in the URL.

Also, it is worth noting that you should be sanitizing any external input before using them in SQL queries to avoid injections.

router.get('/products', productController.listProducts);


exports.listProducts = async (req, res) => {

  const { city, region, country } = req.query
  let response

  if (city && region && country) {
    response = await db.query('SELECT * FROM products WHERE city = $1 AND region = $2 AND country = $3', [city,region,country]);
  } else {
    response = await db.query('SELECT * FROM products ORDER BY productName ASC'); 
  }

  if (response.rowCount > 0) {
     res.status(200).send(response.rows);
  } else {
     res.status(404).send({ message: "No products found" });
  }
};