Introduction to the Knex.js Database Module

Sharing is Caring

Knex is a JavaScript / Node.js library that is designed to make working with relational databases easier. Knex.js is a SQL query builder.

The main reason to use a query builder is that it allows us to write our queries in JavaScript instead of using plain SQL. The reason you would want to do that is because it becomes a lot easier to swap out the particular database if there’s ever a requirement and developers also don’t necessarily have to be SQL masters.

Knex.js is built on top of various npm modules in order to be able to work with lots of different databases. It can support Aurora, MySQL, Orale, Postgres, and others.

And then, built ontop of knex there’s quite a few different object relational mapping libraries like Bookshelf, and Objection.

Installing Knex.js

I recommend installing Knex globally because it has some pretty strong and very useful command line (CLI) tools.

To install it globally you would run a command like this from the terminal:

npm i --g knex

If you are going to use MySQL with Knex, you’ll need to make sure that you have the mysql module installed too! To do that you would just do

npm i mysql

If you’re using a relatively up to date version of npm your package.json should be updated to now include both dependencies.

Configuring Knex

When we use a query builder we need to make sure that we give it the details to connect to the relevant database.

The client is the type of database to use, it will need to point to the particular npm library to use. I normally use MySQL, so I would put ‘mysql’ as an option.

The connection object is all of the details needed to connect to the database. It contains things like host which is the ip address or ui to connect to the database, the username and password.

If you set debug to true, in the knexfile or during initalization you can see all of the queries that are running and various details about them. I like to use it when tests are running and there’s a known problem.

Configuring knex can be done manually through a call to knex like this:

const knex = require('knex')

const db = knex({
    client: 'mysql',
    useNullAsDefault: true,
    connection: {
      port: '3306',
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD
    }
})

Or it can be done through a file called a knexfile. Separation of concerns is really important for creating easy to modify code. Our files should really only do one thing and do that well. They shouldn’t all be connecting to the database.

I recommend using the knexfile as it’s so much easier when you need to update something and won’t require changing a lot of files. The knexfile is used for setting up any configurations that will be used to connect to the database or handle any data. Normally the knexfile is stored in the root of the project.

Here’s an example knexfile

module.exports = {
    client: 'mysql',
    useNullAsDefault: true,
    connection: {
      port: '3306',
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD
    },
    debug: false
}

The knexfile should then be used by using an approach like this:

// database.js
const knex = require('knex')
const config = require('../knexfile.js')

module.exports = knex(config)

If you need to connect to multiple databases that can be easily done by making the knexfile’s export an object with multiple keys have different names. For example, dev and production could be declared like this:

module.exports = {
dev: {
    client: 'mysql',
    useNullAsDefault: true,
    connection: {
      port: '3306',
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD
    },
    debug: true
}, prod: {
    client: 'mysql',
    useNullAsDefault: true,
    connection: {
      port: '3306',
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD
    },
    debug: false
}

Migrations

Knex.js has this concept of migrations which are basically lines of JavaScript that are translated into SQL and then run during the “installation” / “upgrade” process. Basically, if you’re doing continuous integration this would be part of your build script.

Using migrations is a really good practice because it lets you know the state of the database at any time and avoids a manual script not being appropriately applied.

Not to mention if you need to construct a new database it can be really easily done, especially if you are practicing tdd!

By default migrations exist in a folder off the project root called “migrations”. Migrations can be created pretty easily using the knex cli as long as it has been installed globally by running the following command:

knex migrate:make someName

Knex creates a new file that begins with a timestamp so that when you run multiple migrations, it know which order to execute them in. Don’t worry knex also keeps track of the migrations that are being run by tracking them in a database table called “knex_migrations”.

The migration files export an up and a down function that should be returning a promise. The up function is the changes we want to make to the database. The down function is the actions to do if the migration fails or needs to be rolled back.

When you need to do a database migration it can be done really easily by running the following command from the terminal

knex migrate:latest

As mentioned above, migrations are run in the order they have been created in by using the timestamp from the beginning of the name. It’s really important that files not be modified after they’ve been run because knex won’t try to run them again when you do the next migration.

Knex.js Seed Files

Seeds are a good way to populate data with test data or data that won’t be changed very often. For example, in production I would probably use it for a list of countries, states, or user permissions. Basically anything that has to be in the database for the app to be useful should probably be in a seed file.

Seed files are executed in alphabetical order and will execute every time that the seed run command is completed.

To get around this, there’s basically two approaches that could be done: truncate the table or I like to modify my seed files to check to see if records already exist in the table and to potentially do nothing if they do.

Using Knex.js

I don’t normally work directly with Knex.js, I normally like to use an object relationship mapping like Objection.js because it greatly simplifies a lot of the coding.

Sharing is Caring

Brian is a software architect and technology leader living in Niagara Falls with 13+ years of development experience. He is passionate about automation, business process re-engineering, and building a better tomorrow.

Brian is a proud father of four: two boys, and two girls and has been happily married to Crystal for more than ten years. From time to time, Brian may post about his faith, his family, and definitely about technology.