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! — Bergi
– Bergi, Commented Jan 19, 2020 at 18:37

404 · Accepted Answer · 2020-01-19 11:58:25Z

1

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.

answered Jan 19, 2020 at 11:58

404

8,7522 gold badges33 silver badges53 bronze badges

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

1 Comment

Jakub Over a year ago

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: 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

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related