Below is the code snippet for handling a variable-length list of tags and inserting data into the database:
// JSON object from req.body
{
"title": "title",
"reference": "1213",
"noteType": "type_1",
"tags": [3, 4],
"text": "Lorem ipsum dolor sit amet."
}
// Inserting data into notecards table and retrieving 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);
// Adding records to junction table using the ID
await db.query(`insert into tags_notecard(
tag_id,
notecard_id
) values
( $1, $2 )
( $3, $4 );`,
[
tags[0], notecard_id,
tags[1], notecard_id
])
Since tags is dynamic in length, there are two possible approaches mentioned below:
- Iterating over the tags array and calling separate queries for each record insertion.
- Concatenating all values into a single query string and sending one parameterized query with multiple sets of values.
The first approach involves making multiple requests by looping through the tags array. This may lead to performance issues if there are constraints on input/output operations:
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])
}
Alternatively, the second approach requires constructing a concatenated query string and parameters list for efficient insertions into the junction table:
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);
Are these strategies efficient for scenarios where there are significant I/O constraints? Are there superior methods that can be employed in such situations?