How to add dynamically the values to a PostgreSQL query based on columns length

Question

I'm building a helper in my NodeJS API where dynamically the query can be built based on client inputs.

The query right now works in this way:

insertOne(table, columns, params) {
        return query(
            `INSERT INTO ${table}
                        (${columns})
                        VALUES ($1, $2, $3, $4, $5, $6)
                        RETURNING*`,
            params
        );
    }

The method takes 3 arguments the table name, the columns, the params to pass into the query.

I'm able to pass the table name and columns but I'm unable to get it done how to have the values $1 $2 ... $n based on the length of the columns.

I mean when for example client wrote 3 columns to pass I should generate $1 $2 $3 dynamically.

I was trying a for loop but I got stuck:

let i = 0;

for (const propName in columns) {
    q = propName + " = $" + (i ++);
    params.push(q[propName]);
    i++;
}

I'm calling this method in this way now:

async createOne(req, res) {
        console.log({ ...req.body });
        try {
            const product = await Queries.insertOne(
                "products",
                [
                    "productname",
                    "description",
                    "brand",
                    "price",
                    "category",
                    "imageurl"
                ],
                [
                    req.body.productname,
                    req.body.description,
                    req.body.brand,
                    req.body.price,
                    req.body.category,
                    req.body.imageurl
                ]
            );
            res.json({ msg: "Product added", data: product.rows });
        } catch (err) {
            return res.json({ msg: "POST Something went wrong!", err: err });
        }
    },

The helper should in this above situation see that 6 columns are passed and generates the values from $1 to $6 dynamically.

The idea is to use this with different tables in which the number of columns can change.

I hope to get myself clear and please make comments if you need something more from me.

Your `columns` is an array, so [don't use a `for…in` enumerations on it](https://stackoverflow.com/q/500504/1048572)! — Bergi, Jan 19 '20 at 18:37

score 1 · Accepted Answer · answered Jan 19 '20 at 11:58

All you need to do is return a comma delimited $n for each element in the params array.

You could do it with something like this:

function createValuesIds(vals) {
  let ids = '';
  for (let i = 0; i < vals.length; i++) {
    ids += i === 0 ? '$1' : `, $${i + 1}`;
  }
  return ids;
}

Calling it with something like createValuesIds(['a','b','c','d','e','f']) returns $1, $2, $3, $4, $5, $6

Then you just use it in your function:

insertOne(table, columns, params) {
  return query(
      `INSERT INTO ${table}
                  (${columns})
                  VALUES (${createValuesIds(params)})
                  RETURNING *`,
      params
  );
}

And it will build your query correctly.

Thank you as this resolved my issue :) I would like to ask you the last help I have similar to this but for the UPDATE This is the snippet: https://pastebin.com/fyP0xkFN I would like also here to make a loop as was done in your example but it is a biit different hope you could help :D — Jakub, Jan 19 '20 at 22:31

How to add dynamically the values to a PostgreSQL query based on columns length

1 Answers1