How do you chain queries in order using knex.js?

Question

I'm having some trouble understanding how the promises in Knex.js work (uses Bluebird.js for promises). I'm trying to do something pretty simple, execute different insert statements one after another in order, but I haven't been able to get it to work.

Here's the code I have so far, which is meant to execute an insert on the authentication_type table, then an insert on the user_table, and then an insert on the category table.

// Import database connection
var knex = require('./db-connection.js');

// Add a row to authentication_type table so that user's can be created
function add_authentication_type() {
    return knex('authentication_type')
    .insert({id: 1, name: 'Internal'})
}

// Add a 'default' user with nil uuid
// Anything added without a user must link back to this user
function add_default_user() {
    return knex('user_table')
    .insert({user_table_id: knex.raw('uuid_nil()'),
            authentication_type: 1,
            authentication_token: "default"})
}

// Add categories so that locations can be created
function add_categories() {
    return knex('category')
    .insert([
    {name: "Hospital",
    description: "Where people go to get healed"},
    {name: "Police Dept",
    description: "Where people go when there’s trouble"},
    {name: "Fire Dept",
    description: "Where all the fire trucks are"}])
}

// Run the functions in the necessary order to fit constraints
add_authentication_type()
.then(add_default_user()
    .then(add_categories()))

I need these inserts to happen in the correct order, from top to bottom, so I don't violate the constraints of my database. That's what I was attemping to do with the last few lines by chaining calls in the .then() portion of each call. I thought this would make the first query happen, then the second, then the third, but that doesn't appear to be the case since I get constraint violation errors when running this code.

I've been reading through the Knex and Bluebird pages, but I just can't get a grasp on it. What is the proper way to go about executing this kind of sequential querying with Knex?

Answer 1

The knex query builder just returns a promise, so this is just a matter of correctly chaining those promises.

TL;DR: Do this:

add_authentication_type()
  .then(add_default_user)
  .then(add_categories)

Promise Chaining

The key to getting your code to work is understanding these four lines do different things:

// A
.then(add_default_user)
// B
.then(() => add_default_user())
// C
.then(add_default_user())
// D
.then(() => add_default_user)

then will call whatever function is passed as an argument to it after the preceding promise resolves. In A it calls add_default_user, which returns a promise. In B, it calls that entire function, which itself returns a promise-returning function. In both of these cases, then calls a function that eventually returns a promise, which is how you correctly chain promises.

C will not work as expected, because you're not passing a function to then, but the result of the function call. Because promises, like callbacks, are asynchronous, this returns undefined and also immediately calls that function, instead of waiting for the previous promise to resolve.

D won't work because the function you're passing in to then doesn't actually call add_default_user!

Flattening the Chain

If you're not careful, you can end up with functional, but not exactly readable code (a "promise hell" similar to callback hell).

foo()
  .then((fooResult) => bar(fooResult)
    .then((barResult)=> qux(barResult)
      .then((quxResult)=> baz(quxResult)
      )
    )
  )

This works, but is unnecessarily messy. If the function passed to then returns a promise, the first then call can be followed up with a second one. The value the promise inside the first then resolves to will then be passed to the function inside the second then. That means the above can be flattened to:

foo()
  .then((fooResult) => bar(fooResult))
  .then((barResult)=> qux(barResult))
  .then((quxResult)=> baz(quxResult))

**PROTIP:**If you're anal about lining up your calls, you can also kick off your promise chain with a Promise.resolve() like this:

Promise.resolve()
  .then(() => knex('table1').del())
  .then(() => knex('table2').del())
  .then(() => knex('table3').del())