CODEWITHSUNDEEP
javascriptsqlnode.jsnode-postgresslonik
Hoetmaaiers
Hoetmaaiers

Reputation: 3503

Slonik dynamic insert query via sql tag

Via Slonik I try to create a sql statement in a dynamic way. I have a list of keys I want to use for my insert statement and to create a list of values.

This is an example of my setup. Though the definition of my insert columns always fails.

  const keys = [
    'type',
    'substance_group',
    'substance',
    'unit',
    'zone_id',
    'zone',
    'sector',
    'sub_sector',
    'sum_bruto',
    'sum_netto',
  ];
  const emissionValues = values<string>(pick(emission, keys));

  //  (type, substance_group, substance, unit, zone_id, zone, sector, sub_sector, sum_bruto, sum_netto)
  const query = sql`
    INSERT INTO api.emissions
    (${sql.array(keys, 'text')})
     VALUES (${emissionValues.join(',')})
  `;

I tried these forms:

But none are allowed by the sql query builder tag.

Upvotes: 3

Views: 2793

Answers (1)

Gajus
Gajus

Reputation: 73908

This is covered in the documentation, but you need to use sql.identifier for identifiers, e.g.

const identifiers = keys.map((key) => {
  return sql.identifier([key]);
});

sql`
  INSERT INTO api.emissions
  (${sql.join(identifiers, sql`, `)})
  VALUES
  (${sql.join(values, sql`, `)})
`

If you are inserting multiple rows, then have a look at sql.unnest.

Upvotes: 1

Related Questions