CODEWITHSUNDEEP
sqlnode.jspostgresqlnode-postgres
aharonhillel
aharonhillel

Reputation: 59

Node Postgres insert multiple rows based on array and static variable

I am trying to insert multiple rows into a PostgresSQL server from my Nodejs server based on an array. I have a static variable, user_id, which would be the same for all entries but I want to change the filter_name based off an array. My goal is to not make multiple SQL insert calls. 

arrayOfFilters = ["CM", "CBO", "SA", "EPS", "AD"]

await db.query(
            "INSERT INTO filters(filter_name, requests_id)VALUES($1, $2)",
            [arrayOfFiltersParams, user_id]);

I am hoping to have a row in the filters table for each one of the filters found in the arrayOfFilters with a matching user_id key for each entry (aka 5 rows for this example). Thanks so much!

Upvotes: 2

Views: 5154

Answers (1)

Lin Du
Lin Du

Reputation: 102257

Write a helper function expand to build Parameterized query. Here is a solution:

import { pgclient } from '../../db';

function expand(rowCount, columnCount, startAt = 1) {
  var index = startAt;
  return Array(rowCount)
    .fill(0)
    .map(
      (v) =>
        `(${Array(columnCount)
          .fill(0)
          .map((v) => `$${index++}`)
          .join(', ')})`,
    )
    .join(', ');
}
function flatten(arr) {
  var newArr: any[] = [];
  arr.forEach((v) => v.forEach((p) => newArr.push(p)));
  return newArr;
}

(async function test() {
  try {
    await pgclient.connect();
    // create table
    await pgclient.query(`
      CREATE TABLE IF NOT EXISTS filters (
        id serial PRIMARY KEY,
        filter_name VARCHAR(10),
        requests_id INTEGER
      )
    `);
    // test
    const arrayOfFilters = ['CM', 'CBO', 'SA', 'EPS', 'AD'];
    const user_id = 1;
    const arrayOfFiltersParams: any[] = arrayOfFilters.map((el) => [el, user_id]);
    await pgclient.query(
      `INSERT INTO filters(filter_name, requests_id) VALUES ${expand(arrayOfFilters.length, 2)}`,
      flatten(arrayOfFiltersParams),
    );
  } catch (error) {
    console.log(error);
  } finally {
    pgclient.end();
  }
})();

After executed above code, check the database:

node-sequelize-examples=# select * from "filters";
 id | filter_name | requests_id
----+-------------+-------------
  1 | CM          |           1
  2 | CBO         |           1
  3 | SA          |           1
  4 | EPS         |           1
  5 | AD          |           1
(5 rows)

Upvotes: 4

Related Questions