Database Schema Migrations (with Nodejs)

One of the essential practices of software development is version control, and with that, we also need to retain database compatibility with a specific version of our application.

With each release, we should be able to do a fast, reliable database update without compromising existing data and have a possibility of rollback in case of the worst-case scenario.

In the Nodejs project, this can be achieved with different libraries like Sequelize and db-migrate, Knex ...

Out of the three, Knex seems to be the most mature, which best fits the migration requirements.

Knex is multi-platform SQL query builder with built-in migration framework. It will keep track of executed migration scripts and rollback the unsuccessful migrations if needed.

To do a simple POC, we will need:
Npm, Node, Postgres.

In this tutorial the following versions were used:
npm: '7.5.1',
node: '15.8.0',
postgres '13.0',
knex: '0.21.17'.

First, we will install Knex globally, so we can easily use it from the command line:
npm install knex -g

Once the Knex is installed, we need to initialise it in our project:
knex init

A configuration knexfile.js will be created for different environments (development, staging, production). We need to update the database credentials inside of knexfile. And in case there is a need, we could use different databases in different environments. For example, in development, we could use a simple in-memory database like SQLite.

Now that we have configuration knexfile, we have to create an initial migration file (initial schema).
knex migrate:make --help

knex migrate:make 001_initial_schema

This will generate a new directory (migrations) and migration JavaScript file prefixed with the current timestamp.
It is a good practice to include the release version into the name, which corresponds with the application version. In that way, we can easily group and manage migration files.

Inside of 001_initial_schema.js file we have 2 functions.

exports.up = function(knex) {
  
};

exports.down = function(knex) {
  
};

Up will be executed in application rollout, and down will be executed in case of deployment/migration fails, and we want to do a rollback.

For the sake of POC, we will populate this method with a simple DDL (data definition language) query.

In case you don't already have a schema and user, you will need to create one manually: Login into postgres with a user who has the right privileges: psql -d postgres -U <user>

And then execute the following queries:

CREATE DATABASE dev;
CREATE USER dev WITH ENCRYPTED PASSWORD 'dev';
GRANT ALL PRIVILEGES ON DATABASE dev TO dev;

Potentially we could also do this with Knex, but usually, since it is a one time job, it is done out of knex/application scope.

Now that we have a schema and user, we can populate the migration script as needed. In our example we will just create a new table in up function and drop it in down.

exports.up = function(knex) {
  await knex.raw(`CREATE TABLE test.test (coltest varchar(20))`);
};

exports.down = function(knex) {
  await knex.raw(`DROP TABLE test.test`);
};

To start the migration, we can execute knex migrate:latest.
To be double sure, we can list all migrations knex migrate:list and see their status.

In case something would go wrong, we could still rollback the latest migration with: knex migrate:rollback

There are also other Knex commands based on a scenario we want to achieve (ref).

To rollback all the completed migrations:
knex migrate:rollback --all
To run the next migration that has not yet been run:
knex migrate:up
To run the specified migration that has not yet been run:
knex migrate:up 001_migration_name.js
To undo the last migration that was run:
knex migrate:down
To undo the specified migration that was run:
knex migrate:down 001_migration_name.js

And that is pretty much it. We can add these commands to different scripts, like for example package.json, and execute them as needed. Or run them as part of different CI/CD pipelines.