Vercel Postgres bulk insert, building sql query dynamically from array

Question

I am trying to create an SQL bulk insert query using sql helper from @vercel/postgres. I intend to build the query from array of objects. Objects has number/string/date typed properties. Creating a string and passing to sql helper won't work, I believe due to parametrized query. So, is there really any way to build a query dynamically and run using Vercel's Postgres package (without using an ORM) ?

I tried something like this, which obviously doesn't work:

import { sql } from "@vercel/postgres";


// Adds same charge for multiple users
export async function bulkInsert(userIds, charge) {
  const values = userIds.map((id) => `(${id}, ${charge.created_date}, ${charge.amount}, ${charge.note})`
  );

  await sql`INSERT INTO charges (user_id, date_created, amount, note)
            VALUES ${values.join(",")}`;
}

Answer 1

So I figured sql.query() method can be used for this kind of cases. And with the help of json_populate_recordset() here is how my code looks now:

import { sql } from "@vercel/postgres";

// Adds same charge for multiple users
export async function bulkInsert(userIds, charge) {
  const arr = userIds.map((uid) => ({ ...charge, user_id: uid }));

  sql.query(
    `INSERT INTO charges (user_id, amount, note, created_at)
     SELECT user_id, amount, note, created_at FROM json_populate_recordset(NULL::charges, $1)`,
    [JSON.stringify(arr)]
  );

Thanks to @Bergi for directing to the this and that answer, where I referred to solve this.