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(",")}`;
}

Elnoor · Accepted Answer · 2023-09-13 07:57:13Z

6

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.

edited Sep 13, 2023 at 7:57

answered Sep 13, 2023 at 3:20

Elnoor

3,8224 gold badges32 silver badges44 bronze badges

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

2 Comments

Austin Over a year ago

This saved me a lot of headache, thank you for answering your own question!

Mix Master Mike Over a year ago

I spent 2 days looking for a solution like this. Nice find with sql.query(). Thanks!

Joe Osborne · Accepted Answer · 2024-06-03 00:03:31Z

I found a way to do this without having to use json_populate_recordset. By dynamically creating placeholders you can also avoid interpolation as well:

export async function bulkInsert(userIds, charge) {
  
  const data = userIds.map(id => [id, charge.created_date, charge.amount, charge.note]);

 
  const numColumns = data[0].length;

  // Map over your data to create the correct number of placeholders
  const valuesPlaceholders = data.map(
    (_, rowIndex) => `(${Array.from({ length: numColumns }, (_, colIndex) => `$${rowIndex * numColumns + colIndex + 1}`).join(', ')})`
  ).join(', ');

  // Flatten the data
  const values = data.flat();

  const query = `INSERT INTO charges (user_id, date_created, amount, note) VALUES ${valuesPlaceholders}`;

  await sql.query(query, values);
}

Collectives™ on Stack Overflow

Vercel Postgres bulk insert, building sql query dynamically from array

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related