Ejs form, send array of data that can be converted into postgresql update queries

Question

My app includes a scoresheet grid where each cell represents a student's score in one topic. The teacher can enter scores in each cell before clicking a submit button that sends them all at once.

Here is the ejs form that I have right now:

scoresheet.ejs

<tbody>
  <% students.forEach((student, i) => { %>
    <tr>
      <td class="student-cell right">
        <%= student.last_name %>, <%= student.first_name[0] %>
      </td>

      <% topics.forEach(topic=> { %>
        <td class="score-cell center">
          <input type="text" class="score-input" name="scores_<%= student.id %>_<%= topic.id %>">
        </td>
      <% }); %>
    </tr>
   <% }) %>
  </tbody>

This form produces a req.body that looks something like this:

scores_1_2: '75',
scores_1_3: '92',
scores_1_4: '100',
scores_1_5: '100',
scores_1_6: '',
scores_2_1: '65',
scores_2_2: '60',
scores_2_3: '50',
scores_2_4: '35',

I'm trying to take this data and convert it into Postgresql query (or mutiple queries).

For example, the line scores_2_4: '35' would become

UPDATE scores SET points = 35 WHERE student_id = 2 AND topic_id = 4

The scores table is a many-to-many join table to connect students and topics.

I suspect that I still have a bit of work to do with my form. I'm probably not sending this data in an ideal way. This is my best solution so far to include a student_id and topic_id along with the teacher's score input.

If this approach is acceptable, then I also need a hint about how to convert all of this data into an update statement.

I'm using current versions of postgresql, nodejs, express, ejs and the node-postgres package.

Thank you in advance for any insight.

Answer 1

This is my best solution so far to include a student_id and topic_id along with the teacher's score input.

Yes, it's fine. You just have to parse the scores_${student_id}_${topic_id} format on the server back into the data structure you expect.

A more customary encoding is to use bracket notation instead of underscores though. Many parsers for application/x-www-form-urlencoded POST bodies can automatically transform this into a nested object, see e.g. Can not post the nested object json to node express body parser and How to get nested form data in express.js?.

 <input type="text" class="score-input" name="scores[<%= student.id %>][<%= topic.id %>]">

I also need a hint about how to convert all of this data into an update statement.

Use multiple UPDATE statements for simplicity:

const { scores } = req.body;
for (const studentId in scores) {
    const studentScores = scores[studentId];
    for (const topicId in studentScores) {
        const points = studentScores[topicId];
        // TODO: check for permission (current user is a teacher who teaches the topic to the student)
        await pgClient.query(
            'UPDATE scores SET points = $3 WHERE student_id = $1 AND topic_id = $2',
            [studentId, topicId, points]
        );
    }
}

You might want to throw in a parseInt or two with proper input validation for the studentId, topicId and points if you need them to be integers instead of strings; otherwise postgres will throw an exception.