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

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 = db('users').select()
  res.send(users)
}

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

router.get('/users', async (req, res) => {
  try {
    const data = await db('users').select()
    res.send(data)
  } catch (err: unknown) {
    if (err instanceof Error) {
      console.error(err.message);
    } else {
      console.error('Unknown error:', err);
    }
    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.ts
import knex from 'knex'
import config from './knexfile.js'

const db = knex(config.development)

export function getUsers () {
  return db('users').select()
}

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

// routes.ts
// ...
import express from 'express'
import * as db from '../db/db.ts'

const router = express.Router()

router.get('/users', async (req, res) => {
  try {
    const users = await db.getUsers()
    res.send(users)
  } catch (err) {
    if (err instanceof Error) {
      console.error(err.message);
    } else {
      console.error('Unknown error:', err);
    }
    res.status(500).send("Can't display users!")
  }
})

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

    await db.insertUser(newUser)
    res.redirect('/') // or res.sendStatus(200)
  } catch (err) {
    if (err instanceof Error) {
      console.error(err.message);
    } else {
      console.error('Unknown error:', err);
    }
    res.status(500).send("Couldn't insert a new user.")
  }
})