Configuring Knex in an Express application

How to use a Knex configuration file in a REST API.

We have seen in a previous article how to generate a configuration file in order to connect Knex to a SQLite3 database.
The file, named knexfile.js, was generated with the knex init command in our terminal, and placed at the root of our application, with some initial default configurations.

We also created a second file, called db-config.js, with code that creates and exports a Javascript object that we can use to actually issue queries to the database.
This is the code in db-config.js

// db-config.js

const knex = require('knex');

const config = require('../knexfile.js');

const db = knex(config.development);

module.exports = db;

As you can see, we first require Knex, then the knexfile. We then create a new db variable and assign to it whatever is returned by a call to knex() with the development configuration defined in knexfile.

Finally, we export the db object.
Once we have a db object, we import it into a router file into our Express application.

Route handler

Let's imagine we have an application that provides an API with data about fruits. In our application we have configured a router file in fruits/fruit-router.js

This file defines the route handlers for access to our API endpoints. The route handlers will use the db object to query the database.
At the top of fruit-router.js we create a const named db and assign to it the object exported by db-config.js:

const db = require('../data/db-config.js');

Once we have this object, we use it to query the database. Let's create a route handler that responds to a GET request and returns all the fruit records stored in the database.

As you might have guessed, we use the get() method of Express, because this is a GET request.
We make sure we use the async/await syntax because we are dealing with promises:

router.get('/', async (req, res) => {
  try {
    const fruits = await db('fruits');
    res.status(200).json(fruits);
  } catch (err) {
    res.status(500).json({message: "Problem getting fruits"});
  }
})

The code db('fruits') is all we need to connect to the database and return all records from the fruits table.

These records are in turn sent back to the client that made the request. In case of an error, we handle it with an error message.

If we try out this request with the Insomia REST client, we confirm that everything is working correctly:

A possible issue

It is considered a best practice to put the knexfile at the root of our application.
We have seen how we can create a knexfile.js by issuing the knex init command at the terminal.

Before we issue this command, though, we need to make sure we are at the root of the project, because knex init will create a knexfile in the current location, which may inadvertently be inside a sub-folder in the project.

If you actually intend to put the knexfile in some other location besides the application root, you can do it, but make sure the paths that reference this file are adjusted accordingly.

Now that we have seen how to create and configure a knexfile, let's take a look at how to create database tables with knex migrations.

We'll see how to set up a migration file in the next article.

I write daily about web development. If you like this article, feel free to share it with your friends and colleagues.

You can receive articles like this in your inbox by subscribing to my newsletter.