JavaScript array of objects to PostgreSQL array

Question

I have an object with the structure of RsvpData

export interface RsvpData {
  guests: Guest[];
  email: string;
  phone?: string;
  extraInfo?: string;
}

export interface Guest {
  name: string;
  dietaryRequirements: string;
  canAttend: string;
}

I want to add a record to the party database with the parties email, phone and extraInfo, I then want to add each guest to the attendees table with a foriegn key of party_id.

I am using vercel's new PostgreSQL database and @vercel/postgresql. I am using the following code.

const formattedGuests = req.body.guests
      .map((guest) => {
        return `(${guest.name}, ${guest.dietaryRequirements}, ${guest.canAttend})`;
      })
      .join(",");

try {
    await client.sql`WITH inserted_party AS (
    INSERT INTO parties (email, phone, extra_info)
    VALUES (${req.body.email}, ${req.body.phone}, ${req.body.extraInfo})
    RETURNING PartyId
  )
  INSERT INTO attendees (party_id, name, dietary_requirements, can_attend)
    SELECT PartyId, name, dietary_requirements, can_attend
    FROM unnest(ARRAY[${formattedGuests}]::guest[]) AS guest(name, dietary_requirements, can_attend),
 inserted_party;`;
}

If guests only contains one guest everything works fine, if it contains multiple guests I get the following error message.

malformed record literal: "(user1, No, true),(test, Maybe, false)"

I have a suspicion it is because the sql method does not know what values to parameterise in the as I am mapping the guests into a string earlier on, but I am unsure if that is correct, or how to resolve it. This may be a question for the Vercel GitHub, but I am also unsure if the SQL is correct.

no it expects strings marked by single quotes, which the array doesn't provide — nbk, May 08 '23 at 13:16
This is not quite correct, as that would be injecting values into a plain SQL statement, which allows for SQL injection. — Ollie Pugh, May 09 '23 at 10:42
your mal formed records tells you that something is wrong aith the array values and i tould what — nbk, May 09 '23 at 10:43
I see your point, but that isn't the actual issue at hand, the way above was not making use of paramterised queries and was just injecting directly into a string. — Ollie Pugh, May 09 '23 at 10:45
see if that can help you https://stackoverflow.com/questions/15778572/preventing-sql-injection-in-node-js — nbk, May 09 '23 at 11:51

score 0 · Answer 1 · answered May 09 '23 at 10:44

From looking through the @vercel/storage package, the sql method ensures that all variables in the template are primitives, which arrays do not fall under. I am assuming this is due to the database itself needing to know the type for each variable when creating the parameterised query, and using an array does not indicate what types the array contains.

The only solution I could come up with was to create multiple queries to the database, one for each guest.

I did try to dynamically create a TemplateStringArray to pass the the sql method but this was proving very difficult due to their readonly nature.

JavaScript array of objects to PostgreSQL array

1 Answers1