Slonik dynamic insert query via sql tag

Question

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:

${sql.array(keys, 'text')}
${sql.join(keys, sql, )}
or plain ${keys.join(',')

But none are allowed by the sql query builder tag.

score 1 · Accepted Answer · answered Apr 02 '20 at 15:58

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.

Slonik dynamic insert query via sql tag

1 Answers1