skip to Main Content

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?

2

Answers


  1. 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

    Login or Signup to reply.
  2. 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]
    );  
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search