Knex.js multiple chained queries

Matthew Kent
Matthew Kent used Ask the Experts™
on
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)
                    })
             })
        }

Open in new window


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')
                })
            }
        })

Open in new window


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)
                    })
             })
        }

Open in new window

Any help will be greatly appreciated!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial