CODEWITHSUNDEEP
javascriptpostgresqlexpressjunction-table
STEVE J
STEVE J

Reputation: 21

How to insert variable rows to a Postgres junction table: concatenate a query string or make a many queries or other?

Here is the code:

// the JSON of req.body  
{
    "title": "title",
    "reference": "1213",
    "noteType": "type_1",
    "tags": [3, 4], // Will be variable length. 
    "text": "Lorem ipsum dolor sit amet."
}      

// Create the row and get the id
const response = await db.query(`insert into notecards( 
        title, 
        reference, 
        note_type, 
        main_text
    ) values ( 
        $1, 
        $2, 
        $3, 
        $4 
    ) returning notecard_id;`, [
        title,
        reference,
        noteType,
        text
])
const notecard_id = Number(response.rows[0].notecard_id);

// Use the id to add various rows to junction table      
await db.query(`insert into tags_notecard( 
        tag_id, 
        notecard_id
    ) values 
        ( $1, $2 )
        ( $3, $4 );`, 
    [
        tags[0], notecard_id,
        tags[1], notecard_id 
])

Because tags is a variable length list, the second query will be a variable length.

I see two approaches: make a new query for each new row in tags_notecard or send one pre-concatenated query string.

I can do this with the downside of making many requests:

for (let i = 0; i < tags.length; i++) {
        await db.query(`insert into tags_notecard(tag_id, notecard_id) 
            values ( $1, $2 );`, [tags[i], notecard_id])
    }

Or I can could concatenate all the values onto a single string, and then send the appropriate query and parameters. The downside is having to build a string for each junction table insert:

 queryString = "insert into tags_notecard(tag_id, notecard_id) values"
 paramsList = []
 for (let i = 0, j =1 ; i < tags.length; i++, j+=2) {
     if (i !== tags.length - 1) {
          queryString = queryString + "($" + (j) + ", $" + (j+1)+ "),";
     } else {
         queryString = queryString + "($" + (j) + ", $" + (j+1)+ ");";   
     }
     paramsList.push(tags[i]);
     paramsList.push(notecard_id);
}
await db.query(queryString, paramsList);

QUESTIONS: are these good approaches where there are real i/o bound constrains? Is there a better way beyond these?

Upvotes: 1

Views: 29

Answers (2)

Bergi
Bergi

Reputation: 665455

You can send an array value as a parameter and unnest it:

await db.query(
  `INSERT INTO tags_notecard(tag_id, notecard_id)
  SELECT unnest($1::int[]), $2;`, 
  [tags, notecard_id]
);

Upvotes: 0

Mike Organek
Mike Organek

Reputation: 12494

I would use the jsonb data type and associated functions to perform these inserts in a single statement:

with invars as (
  select '{
    "title": "title",
    "reference": "1213",
    "noteType": "type_1",
    "tags": [3, 4], 
    "text": "Lorem ipsum dolor sit amet."
  }'::jsonb as req_body
), insert_notecard as (
  insert into notecards (title, reference, note_type, main_text)
  select req_body->>'title', req_body->>'reference', req_body->>'noteType',
         req_body->>'text'
    from invars
  returning notecard_id
), insert_tags as (
  insert into tags_notecard (tag_id, notecard_id)
  select t.tag_id::int, n.notecard_id
    from insert_notecard n
         cross join invars i
         cross join lateral 
           jsonb_array_elements_text(i.req_body->'tags') t(tag_id)
  returning *
)
select * from insert_tags;

Working fiddle

Upvotes: 1

Related Questions