I recently had a problem where I wanted to store in a MySQL data store a series. (I'm re-writing Narrator, my story engine.) While testing out the import, I realized that I wanted to be able to look up the author on the fly, using the authorname rather than the author ID. I also wanted to be able to be able to insert one series as the child series of another, but I wanted to be able to do so by the parent series slug rather than the parent's ID.

Doing this is rather complicated, in that it involves a series of callbacks that may or may not be necessary. First, I'm going to write two functions, one to look up an author's ID based on his username, and a similar one to return the series ID based on the author's ID and the series slug.

    authorLookup = (authorname, cb) ->
        connection.querySingle 'SELECT id FROM storycore_authors WHERE username = ?', [authorname], (err, res) ->
            if err
                return cb(err, null)
            if not res
                return cb("Author does not exist", null)
            cb(err, res.id)

    seriesLookup = (slug, user_id, cb) ->
        connection.querySingle 'SELECT id FROM storycore_slug WHERE slug = ? and user_id = ?', [slug, user_id], (err, res) ->
            if err
                return cb(err, null)
            if not res
                return cb("Series does not exist", null)
            cb(err, res.id)

Note that each of these functions takes some arguments and a callback, and then calls the callback with the argument pair (error, id). So the following arguments basically say, "If the lookup is necessary, then return the function that will return the lookup, otherwise return a function with the exact same signature as the lookup function but that immediately calls the callback with the success argument."

    aLookup = (user_id) ->
        if typeof(user_id) == 'string' then authorLookup else (user_id, cb) -> cb(null, user_id)

    sLookup = (series) ->
         if typeof(series) == 'string' then seriesLookup else (series, user_id, cb) -> cb(null, series)

Now the magic. With the arguments passed into addSeries(), I analyze the two that might be numeric IDs or string "slugs". The two functions immediately above return the function appropriate to handling each.

So now we have three functions: one that gets the author ID regardless of input type, one that gets the series ID regardless of input type, and finally a call that uses mySQL-simple's "nonQuery" method to call a SQL function to insert the series. (I used a SQL function because there's some heavy duty hierarchical magic going on the behind the scenes.)

The async library has a method, waterfall(), which runs a collection of functions in series, passing the result to the next function in the series. Here, alookup, regardless of whether it blocks on the mySQL call or passes right through, takes the original user ID and the callback supplied by async, and returns a single value-- which is then passed to plookup, which then performs its magic, and then passes those on to the connection function.

By falling through the necessary steps in this way, each lookup is assured. Waterfall will call the final callback with an error if any function in its list provides something other than null to the err parameter of its callback.

    addSeries = ({user_id, title, slug, parent, sortpos, blurb}, cb) ->
        alookup = aLookup(user_id)
        plookup = pLookup(parent)
        async.waterfall [
            ((ncb) -> alookup user_id, ncb),
            ((user_id, ncb) -> plookup parent, user_id, (err, parent) -> ncb(err, user_id, parent)),
            ((user_id, parent, ncb) ->
                connection.nonQuery('CALL storycore_insert_series (?, ?, ?, ?, ?, ?)', [
                    user_id
                    title
                    slug
                    parent
                    sortpos
                    blurb
                ], ncb))], cb

And that's it. When done, the cb callback the user supplied to addSeries is called, either with an (error, null) message, or (null, success). And it's a lot more compact than my first draft!