Database Migrations in Node

Apr 6, 2024 node migrations bbc

I've been trying to find a decent database migrator for a few years in Node and eventually found Umzug from the folks over at Sequelize.

In smaller projects I normally have a folder structure for database migrations which are simple SQL scripts:

🗂️ app
  📄 index.ts
🗂️ migrations
  📄 2024-01-01-0900-create-first-table.sql
  📄 2024-01-01-0901-create-second-table.sql

With each migration file containing some simple SQL like:

CREATE TABLE first_table (
  id int,
  name varchar(255),
  email varchar(255)
);

The problem is there is no way to roll back the changes without working the differences and reverting manually. A database migration tool solves this for you, providing up and down commands needed when you have to roll back a deployment.

Database frameworks wrap this functionaly up for you and either provide a way to inject raw SQL or abstract the creation so it can be applied to multiple SQL dialects like Sqlite, MySQL or MSSQL. The pattern is generally via up() and down() functions in separate migration files:

// pseudo code for file called migrations/2020-01-02-0102-create-beans-table.ts

async function up(context: MigrationContext): Promise<void> {
  // changes on the way forward go here
  await context().schema().addTable("beans").withColumn("id").unique();
}

async function down(context: MigrationContext): Promise<void> {
  // changes on the way back roll back the table creation
  await context().schema().truncateTable("beans");
  await context().schema().dropTable("beans");
}

A few packages I've used before include:

Umzug

I mainly work in Node environments at the moment and find the npx CLI tooling easy to use. I want to be able to rock up to a migration solution, install any dependencies, configure my current environment, then execute the migrations ideally with a migrate up command.

After a few weeks digging recently I found Umzug which is written in Typescript, supports a fluent interface, supports async, has a CLI and can also be used programatically.

I don't expect I'll ever use that internal node interface though yet but it's interesting to see.

First create a Sequelize context:

const sequelize = new Sequelize({
  dialect: "mysql",
  host: "127.0.0.1",
  username: "basket",
  password: "basket",
  database: "basket",
});

Then pass that to an Umzug instance:

export const migrator = new Umzug({
  migrations: {
    glob: ["migrations/*.ts", { cwd: __dirname }],
  },
  context: sequelize,
  logger: console,
});

Which needs to be exposed as a CLI if you wish to use it that way (otherwise you could use it programatically):

migrator.runAsCLI(); // expose the CLI

Here's an example migration file migrations/002_basket_changes.ts:

import { DataTypes } from "sequelize";
import { Migration } from "../index";

export const up: Migration = async ({ context: sequelize }) => {
  await sequelize.getQueryInterface().addColumn("basket", "created_utc", {
    type: DataTypes.INTEGER,
  });
};

export const down: Migration = async ({ context: sequelize }) => {
  await sequelize.getQueryInterface().removeColumn("basket", "created_utc");
};

Then to migrate up:

npm run migrate up

And to migrate down:

npm run migrate down

I've wrapped this up with an example project over at:

Knex Migrations

The Knex team have produced a first class product Knex Migrations which is similar to Umzug except with far better documentation.

This right now is probably the tool of choice over Umzug.

Other migrators

There are some other notable mentions in this space including:

  • db-migrate - This is an old project but still gets the job done. It hasn't been updated in many years. It's pretty simple.
  • Sequelize Migrations - from the Sequelize team, this has two modes - one that is tightly integrated into the Sequelize ORM way, or the Umzug way. Interesting to note that Sequelize uses Umzug internally.