Database Migrations in Node.js: Sequelize, Knex and TypeORM

Database Migrations in JavaScript (Node.js): A Complete Tool Overview

Database migrations are a way to make controlled and versioned changes to a database schema. In the Node.js world, there are several powerful libraries that allow you to automate this process. In this article, we will look at three of the most popular tools: Sequelize, Knex.js and TypeORM.

What are migrations and why are they needed?

Migrations solve the problem of synchronizing the database structure between developers and environments (dev, staging, production). Instead of manually executing SQL scripts, you describe the changes in JavaScript/TypeScript code. Advantages:

• Automatic application of changes
• Ability to rollback to a previous version
• Storing change history in a repository
• Support for different DBMS (PostgreSQL, MySQL, SQLite, etc.)

1. Sequelize — ORM with built-in migration system

Sequelize is one of the most popular ORMs for Node.js. It provides a CLI tool for managing migrations. Installation:

npm install --save sequelizenpm install --save-dev sequelize-clinpx sequelize-cli init

After initialization, the migrations, models, seeders folders and the config/config.json configuration file are created.

Creating a migration

npx sequelize-cli migration:generate --name create-users-table

Example migration content:

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

Applying and rolling back

npx sequelize-cli db:migratenpx sequelize-cli db:migrate:undo

2. Knex.js — SQL query builder with migrations

Knex.js is not an ORM, but a powerful query builder. Its migration system is considered one of the most flexible. Installation:

npm install knex --savenpm install pg --save  # or mysql, sqlite3npx knex init

Creating and writing a migration

npx knex migrate:make create_posts_table

Example code:

exports.up = function(knex) {  return knex.schema.createTable('posts', (table) => {    table.increments('id').primary();    table.string('title', 255).notNullable();    table.text('content');    table.integer('user_id').unsigned().references('id').inTable('users');    table.timestamps(true, true);  });};

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

Commands for working

npx knex migrate:latestnpx knex migrate:rollbacknpx knex migrate:status  # view status

Knex also supports seeds for initial data population:

npx knex seed:make 01_demo_usersnpx knex seed:run

3. TypeORM — migrations for TypeScript projects

TypeORM is a modern ORM that supports both JavaScript and TypeScript. Its migration system is tightly integrated with entity decorators. Installation:

npm install typeorm reflect-metadata --savenpm in