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
I would use the jsonb data type and associated functions to perform these inserts in a single statement:
Working fiddle
You can send an array value as a parameter and
unnest
it: