How to match req.body with update query?

Question

The contents of a POST req.body are dynamic (unchecked checkboxes are not sent). SQL update and insert queries are hard coded, albeit with placeholders. Either I have to pad the req.body to match my hard-coded query, or create a query dynamically from req.body:

<script>
let sql = "UPDATE table SET ";
let data = {};
for (let [key, value] of Object.entries(req.body)) {
    if (key != 'id' && key != 'submit') {
        sql += "$" + key + "=? ,";
    }
}
sql += " WHERE $id=" + req.body.id;

for (let [key, value] of Object.entries(req.body)) {
    if (key != 'id' && key != 'submit') {
        let newKey = "$" + key;
        data.newKey = value;
    }

}
</script>

What is best, rebuild my data to match query or build a query to match my data?

Answer 1

You can install query builder.

1. Install

npm install express knex sqlite3

2. Apply

const express = require('express');
const knex = require('knex');
const bodyParser = require('body-parser');

const app = express();
app.use(bodyParser.json());

const db = knex({
  client: 'sqlite3',
  connection: {
    filename: './data.sqlite3'
  },
  useNullAsDefault: true
});

app.put('/update', (req, res) => {
  const data = req.body;
  const id = data.id;
  delete data.id;
  delete data.submit;

  db('table')
    .where('id', id)
    .update(data)
    .then(() => {
      res.status(200).json({ message: 'Successful!' });
    })
    .catch(error => {
      res.status(500).json({ message: 'Failed', error });
    });
});

const PORT = process.env.PORT || 3000;
app.listen(PORT, () => {
  console.log(`Server running on ${PORT}`);
});