Database Migrations in Node.js: Tool Overview and Examples

Database Migrations in JavaScript (Node.js): Tools and Practice

Developing modern web applications with Node.js rarely goes without working with databases. As a project grows, the structure of tables, indexes, and relationships inevitably changes. Manually executing SQL scripts on the server is a path to chaos and errors. The solution to this problem is database migrations. This is a mechanism for versioning schema changes, allowing the development team to update the database structure synchronously and safely.

In the JavaScript ecosystem, there are several mature tools for working with migrations. Let's look at three key approaches using popular libraries as examples.

1. Knex.js — Universal Query Builder

Knex.js is not just an SQL builder, but also a full-fledged tool for migrations and seeding (populating with test data). It supports PostgreSQL, MySQL, SQLite, and other DBMS. The main advantage is that you write migrations in JavaScript, and Knex translates them into the dialect of the specific database.

Installation and Setup

npm install knexnpm install pg  # for PostgreSQL

Create a configuration file knexfile.js:

module.exports = {  development: {    client: 'pg',    connection: {      host: 'localhost',      user: 'user',      password: 'pass',      database: 'mydb'    },    migrations: {      directory: './migrations'    }  }};

Creating and Running a Migration

Let's create a migration for the users table:

npx knex migrate:make create_users_table

A file with a timestamp will appear in the migrations folder. Edit it:

exports.up = function(knex) {  return knex.schema.createTable('users', (table) => {    table.increments('id').primary();    table.string('username', 100).notNullable().unique();    table.string('email').notNullable();    table.timestamps(true, true); // created_at and updated_at  });};

exports.down = function(knex) {  return knex.schema.dropTableIfExists('users');};

Apply the migration:

npx knex migrate:latest

Rollback the last migration:

npx knex migrate:rollback

Knex also allows you to execute raw SQL within migrations if specific logic is required.

2. Sequelize — ORM with a Powerful Migration System

Sequelize is a popular ORM for Node.js, supporting PostgreSQL, MySQL, MariaDB, SQLite, and MSSQL. Its CLI provides convenient commands for generating and managing migrations, tightly integrated with models.

Getting Started

npm install sequelize sequelize-cli pgnpx sequelize-cli init

Create a model and migration with one command:

npx sequelize-cli model:generate --name User --attributes username:string,email:string

This will generate two files: a model models/user.js and a migration in migrations/. Example migration:

'use strict';module.exports = {  up: async (queryInterface, Sequelize) => {    await queryInterface.createTable('Users', {      id: {        allowNull: false,        autoIncrement: true,        primaryKey: true,        type: Sequelize.INTEGER      },      username: {        type: Sequelize.STRING(100),        allowNull: false,        unique: true      },      email: {        type: Sequelize.STRING,        allowNull: false      },      createdAt: {        allowNull: false,        type: Sequelize.DATE      },      updatedAt: {        allowNull: false,        type: Sequelize.DATE      }    });  },  down: async (queryInterface, Sequelize) => {    await queryInterface.dropTable('Users');  }};

Applying and rolling back:

npx sequelize-cli db:m