Knex.js multiple chained queries

Question

I'm currently working on a project in express and I'm using knex.js to handle migrations and queries.

I'm still trying to grasp the concept of promises and how I can run multiple queries with knex.

I have the following code which inserts a new record into my database, this is located in my Unit model file.

this.addUnit = function(unit_prefixV, unit_nameV, unit_descriptionV, profile_id) {
         return new Promise(function(resolve, reject) {
             knex.insert({ unit_prefix: unit_prefixV, unit_name: unit_nameV, unit_description: unit_descriptionV })
                .into('units').then(function(unit) {
                    resolve(unit)
                }).catch(function(error) {
                    reject(error)
                })
         })
    }

In my routes.js file I then call this on a post request, like so:

app.post('/dashboard/unit/add', ensureAuthenticated, function(req, res) {
        let postErrors = []
        if (req.body.unit_name.trim() == "") {
            postErrors.push('Unit name cannot be empty.')
        }

        if (req.body.unit_prefix.trim() == "") {
            postErrors.push('Unit prefix cannot be empty.')
        }

        if (req.body.unit_description.trim() == "") {
            postErrors.push('Unit description cannot be empty.')
        }

        if (postErrors.length > 0) {
            res.render('addUnit', { errors: postErrors, user: req.user })
        } else {
            unitModel.addUnit(req.body.unit_prefix.trim(), req.body.unit_name.trim(), req.body.unit_description.trim(), req.session.passport.user.id).then(function(unit) {
                res.redirect('/dashboard')
            })
        }
    })

This successfully inserts a new record into my units table, however, I would like to select the user id from the users table with the matching profile_id and then insert another record into my users_units table. All within the this.addUnit function.

For reference my users table consists of:

• id
• google_id

my users_units table consists of:

• user_id
• unit_id

I've made an attempt to chain the queries but it only executed the initial insert query and not the others. Here is that rather ugly attempt:

this.addUnit = function(unit_prefixV, unit_nameV, unit_descriptionV, profile_id) {
         return new Promise(function(resolve, reject) {
             knex.insert({ unit_prefix: unit_prefixV, unit_name: unit_nameV, unit_description: unit_descriptionV })
                .into('units').then(function(unit) {
                    knex('users').where({ "google_id": profile_id }).select('id').then(function(uid) {
                        knex.insert({ user_id: uid, unit_id: unit }).into('users_units').then(function(user_units) {
                            resolve(user_unit)
                        }).catch(function(error) {
                            reject(error)
                        })
                        resolve(uid)
                    })
                    console.log(unit)
                    resolve(unit)
                }).catch(function(error) {
                    reject(error)
                })
         })
    }

Any help will be greatly appreciated!

Answer 1

You're nearly there. There are just a few simple point to grasp :

• A Promise can be reolved only once
• An explicit Promise is not needed anyway because a naturally occurring promise can be returned
• return a Promise at each stage ...
• ... until the innermost stage, from which the returned value is the finally delivered result.
• Errors needn't be eplicitly handled unless you want to inject your own custom error messages or take remedial action.

Having taken all that on board, you might write :

this.addUnit = function(unit_prefixV, unit_nameV, unit_descriptionV, profile_id) {
    return knex.insert({ 'unit_prefix':unit_prefixV, 'unit_name':unit_nameV, 'unit_description':unit_descriptionV }).into('units')
 // ^^^^^^
    .then(function(unit) {
        return knex('users').where({ 'google_id':profile_id }).select('id')
     // ^^^^^^
        .then(function(uid) {
            return knex.insert({ 'unit_id':unit, 'user_id':uid }).into('users_units')
         // ^^^^^^
            .then(function(user_units) {
                return { 'unit_id':unit, 'user_id':uid, 'user_units':user_units };
             // ^^^^^^
            });
        });
    });
}

If the caller is interested only in success/failure of the process and not the full { unit, uid, user_units } object, then the innermost .then() can be omitted :

this.addUnit = function(unit_prefixV, unit_nameV, unit_descriptionV, profile_id) {
    return knex.insert({ 'unit_prefix':unit_prefixV, 'unit_name':unit_nameV, 'unit_description':unit_descriptionV }).into('units')
    .then(function(unit) {
        return knex('users').where({ 'google_id':profile_id }).select('id')
        .then(function(uid) {
            return knex.insert({ 'unit_id':unit, 'user_id':uid }).into('users_units');
        });
    });
}

The promise returned by .addUnit() will still deliver user_units, which the caller can use or ignore.

There's a major proviso to these solutions (and others); a multi-stage update query like this should really be wrapped in a transaction - ie something that allows earlier stages to be rolled back. Otherwise a failure part way through is likely to leave the database in some indeterminate state. This answer is as good a starting point as any.