Knex Routes

When we start out with Express, we often create routes that show static (unchanging) data, or we've used them to load and save from the filesystem. We can use a very similar layout to load and save data from a database using Knex.js.

Promises

The main difference of course is that Knex functions return promises. We won't actually have any data to work with unless we wait for the promises to resolve or reject. For example, this sort of thing won't work:

router.get('/users', (req, res) => {
  const users = conn('users').select()
  res.send(users)
}

Instead, we'll need to make use of the .then() and .catch() functions to ensure that the data is available for us to use (and grab any errors that might occur):

router.get('/users', (req, res) => {
  conn('users')
    .select()
    .then(data => {
      res.send(data)
    })
    .catch(err => {
      console.error(err.message)
      res.status(500).send("Couldn't show you the users!")
    })
}

Extracting the database details to one place

Following the Single Responsibily Principle, it's much cleaner to have the routes focus on the request and response and extract the details of database access into a separate module. Here's how you could extract them:

// db.js

const config = require('./knexfile').development
const conn = require('knex')(config)

function getUsers () {
  return conn('users').select()
}

// An example user object: {name: 'feroze', email: 'feroze@gmail.com'}
function insertUser (user) {
  return conn('users').insert(user)
}

module.exports = {
  getUsers,
  insertUser
}

// routes.js
// ...

const db = require('./db')

router.get('/users', (req, res) => {
  db.getUsers()
    .then(users => {
      res.send(users)
    })
    .catch(err => {
      console.error(err.message)
      res.status(500).send("Can't display users!")
    })
})

router.post('/users', (req, res) => {
  const newUser = {
    name:  req.body.name,  // name stored in a submitted form body
    email: req.body.email
  }

  db.insertUser(newUser)
    .then(() => {
      res.redirect('/') // or res.sendStatus(200)
    })
    .catch((err) => {
      console.error(err.message)
      res.status(500).send("Couldn't insert a new user.")
    })
})