Is there any Better way to insert a row into MYSQL database in node.js?

Question

So i am going with mysql2 driver to use Mysql with node. Let's say i have 25 to 30 maybe more fields in a row, would i have to manually write every column after extracting it from body. My current approach is as below, here's the complete controller for better understanding.

exports.registerUser = async (req, res, next) => {
    let errors = validationResult(req)
    if (!errors.isEmpty()) {
        let err = new Error()
        err.errors = errors
        err.status = 400
        next(err)
    }
    console.log('entered')
    let { user_role_id, name, email_id, password,
        mobile_no, work_exp_years, work_exp_month,
        resume, current_location } = req.body

    try {
        let query = `INSERT INTO user_account_details 
        (user_role_id, name, email_id, password, 
            mobile_no, work_exp_years, work_exp_month, 
            resume, current_location) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`
        let [insertRow] = await mysql.execute(query, [user_role_id, name, email_id, password,
            mobile_no, work_exp_years, work_exp_month,
            resume, current_location])
        if (!insertRow) {
            throw new Error('Insert error')
        }
        return res.status(200).json({ result: insertRow })
    } catch (err) {
        let error = new Error()
        error.errors = err
        next(error)
    }
}

So, my only concern is the part where i am inserting fields in a row, i feel like i am writing column names a lot of times, is there any way i can just take the body and insert it into row like in mongoose?

You can leave out the column names entirely if every column is assigned a value any the order of the values matches the order of columns. — gph, Jul 10 '20 at 13:31
[tag:mysql2] is a Ruby gem. How is it related to the question? — axiac, Jul 10 '20 at 13:48
@gph i am leaving a few columns, such as created_at and a couple of others as well which are set to default and need not be changed generally. Any other better approach? It's like writing the columns three times. Any way to directly take the body and insert it as it is? — Sumair, Jul 10 '20 at 13:48
@axiac It's a node driver as well used to connect node with mysql db. — Sumair, Jul 10 '20 at 13:49
The SO tag [tag:mysql2] is apparently used for the Ruby gem. — axiac, Jul 10 '20 at 13:50
This feels hack-ish but could you use a temp table with just the columns you provide then use a trigger or call a sproc to move the data over? — gph, Jul 10 '20 at 18:15
Also, you could create a sproc that accepts an XML or json string then parse the string in a sproc. You'd have to list the columns in the sproc but it would give you more flexibility going forward instead of having to update the column list everywhere in node — gph, Jul 10 '20 at 18:22

score 0 · Answer 1 · answered Mar 08 '21 at 21:47

I know this is late, but since it sounds like you're a fan of Mongoose, you could abstract away the MySQL statements completely using one of the packages that provide a Mongoose-like interface to MySQL. Search npmjs.com for "mysql mongoose." The results include @aponica/mysqlgoose-js, mysqloose and hyena.

Is there any Better way to insert a row into MYSQL database in node.js?

1 Answers1