Introducing Knex.js#

Knex.js is a lightweight SQL query builder that makes it easy to create, read, update and delete data in databases from a Node.js application. We will use Knex for this purpose, but it also has a key feature called migrations. Migrations can be thought of as "source control for your database". They allow you to define sets of schema changes that you can apply to any database and bring it up to date, e.g., migrate it to the desired state.

By using migrations, we'll keep a running history of all changes to our database in a set of source migration files. Migrations can be used for all database structural changes such as adding and dropping tables and columns. The Knex migration tooling automatically tracks and applies migrations that are set up in migration files.

Installing Knex#

To use Knex migrations, we need to install Knex globally with the -g flag, which will allow us to directly run knex from the command line. We also need to install Knex locally into the project and we need the Postgres driver, pg.

Go ahead and install these tools now in the backend project.

$ npm install knex -g

$ npm install knex pg

In order for Knex to be able to connect to a database, it needs some configuration such as database hostname and password. You store this information in a file called knexfile.js. Create this file locally in the /school-lunch/backend/ root directory. Make sure to replace <your superuser password> with the password you specified when installing Postgres.

module.exports = {

  development: {

    client: 'postgresql',

    connection: {

      database: 'school_lunch',

      host: '127.0.0.1', // equates to `localhost`, e.g., the local machine

      user: 'postgres',

      password: '<your superuser password>',

    },

    pool: {

      min: 2,

      max: 10,

    },

    migrations: {

      tableName: 'knex_migrations',

    },

  },

  production: {

    client: 'postgresql',

    connection: {

      database: 'to-be-determined',

      user: 'username',

      password: 'password',

    },

    pool: {

      min: 2,

      max: 10,

    },

    migrations: {

      tableName: 'knex_migrations',

    },

  },

}

Important note: Since this file contains secrets, make sure to set it to "ignore" for any source control repository you are using. (For example, for Git, add the file to your .gitignore file so that Git won't track it.)

Your First Knex Migration#

Now that we have installed Knex and created a knexfile, we can create our first migration. Currently our database has no tables, so our first step will be to create the lunch_week table.

Using migrations is a three-step process:

1. Run the command $ knex migrate:make <migration-short-description>. This will stub out a migration file in the /migrations directory.

2. Edit the migration file with the actions you want to achieve, such as creating a table.

3. Run $ knex migrate:up. This command will check the database and apply any missing migrations.

Let's give it a try. In the back-end root directory, run the following:

$ knex migrate:make create_lunch_week_table

Using environment: development

FS-related option specified for migration configuration. This resets migrationSource to default FsMigrations

Created Migration: /school-lunch/backend/migrations/20200803193351_create_lunch_week_table.js

As per the output, a migration file was created. Open the new migration file in VSCode. It has upand down functions stubbed out:

exports.up = function (knex) {}

exports.down = function (knex) {}