How do I refactor this query to not have so many variables?

Question

I have a form capturing information from a registration, it is a dust layout with html form, part of a react app:

app.post('/add', function(req, res) {
    // PG Connect
     const { Pool, Client } = require('pg')
     const connectionString = 'postgres://wol_admin:lolipop8@localhost/wol_master'
     const pool = new Pool({
        connectionString,
     })  

    pool.query('SELECT * from customers');
    pool.query('INSERT INTO customers (childfirstname, childmiddlename, childlastname, childgender, childage, childbirthdate, childgrade) VALUES ($1, $2, $3, $4, $5, $6, $7)',[req.body.childfirstname, req.body.childmiddlename, req.body.childlastname, req.body.childgender, req.body.childage, req.body.childbirthdate, req.body.childage], (err, result) => {
        if(err) {
            return console.error('error running query', err);
        }
        console.log('added to table', result);
        
        res.redirect('/');
        pool.end()
    });
});

Is there a way I can pass the variables into the query as an object or some other type?

Answer 1

You can collect all the variables under an Object but it wont much change the stack.

1. First of all you better put your imports into the top.

2. You shouldn't pass your parameters directly from the object and it behaves like a dynamic query. It will be vulnerable to SQL Injection and many attacks. You can collect all the variables that can be used in a query and you can use Spread Sytax (...). You need to validate the inputs.

I would prefer to format my code as down below for security.

   const {
      Pool,
      Client
   } = require('pg');

   const connStr = 'postgres://wol_admin:lolipop8@localhost/wol_master';

   app.post( '/add' , function (req, res) {

    const {
        childfirstname,
        childmiddlename,
        childlastname,
        childgender,
        childage,
        childbirthdate,
        childage
    } = req.body;

    const childData = [
        childfirstname,
        childmiddlename,
        childlastname,
        childgender,
        childage,
        childbirthdate,
        childage
    ];

    const pool = new Pool({
        connectionString: connStr,
    });

    pool.query('SELECT * from customers');

    pool.query('INSERT INTO customers (childfirstname, childmiddlename, childlastname, childgender, childage, childbirthdate, childgrade) VALUES ($1, $2, $3, $4, $5, $6, $7)', [...childData], (err, result) => {
        if (err) {
            return console.error('error running query', err);
        }
        console.log('added to table', result);
        res.redirect('/');
        pool.end()
    });
});

Answer 2

IMO, there is no reason to decrease the number of variables, because you should put the values in the absolute that order if I understood it right. The best way to decrease amount of code is to destruct the object of req.body and take all the variables out of there.

Answer 3

According to this issue, node-postges does not support INSERT queries directly from an array or object. As mentioned there, pg-format can provide a tidier way to build query strings.

In your case, you should also be using the PostgreSQL function json_populate_record.

Your code will go something like this:

var format = require('pg-format');

pool.query(format('INSERT INTO customers VALUES json_populate_record(%L)', req.body));