How to create Database migration in node js

Creating migrations in Node.js typically involves setting up a way to manage database schema changes in a versioned and controlled manner. There are several tools to help with this process, with Knex.js and Sequelize being two of the most popular ones. Each of these provides migration functionality to help you keep track of database schema changes. node js db migrate is an important step for database connectivity

Here, I’ll walk you through setting up Migration in Node js using Knex.js and Sequelize.

Option 1: Using Knex.js for node js database migration

Knex.js is a SQL query builder for Node.js, and it has built-in support for migrations. Here’s how you can set it up and create migrations.

1. Install Knex.js and a Database Client

First, you need to install Knex.js and the database client for your specific database (e.g., PostgreSQL, MySQL).

npm install knex pg # For PostgreSQL
# Or for MySQL:
# npm install knex mysql2

2. Initialize Knex

After installing, you need to create a Knex configuration file that defines your database connection settings.

Create a file called knexfile.js in the root of your project:

// knexfile.js
module.exports = {
  client: 'pg', // Or 'mysql' or 'sqlite3'
  connection: {
    host: '127.0.0.1',
    user: 'your_db_user',
    password: 'your_db_password',
    database: 'your_db_name',
  },
};

3. Create Migrations

Knex provides a command line interface (CLI) to create and run migrations. To use the CLI, you need to install Knex globally or use it locally via npx.

To install Knex globally:

npm install -g knex

Or use npx for local use:

npx knex migrate:make migration_name

This will create a new migration file in the migrations directory with a timestamp to keep track of changes.

4. Write the node js database migration

In the migration file, you’ll define the up and down methods. The up method defines what changes should be applied (e.g., creating tables, adding columns), and the down method defines how to undo those changes.

Example of a migration file:

// migrations/20241110123456_create_users_table.js
exports.up = function (knex) {
  return knex.schema.createTable('users', (table) => {
    table.increments('id').primary();
    table.string('name');
    table.string('email').unique();
    table.timestamps(true, true); // created_at, updated_at
  });
};

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

5. Run Migrations

After writing your Migration in Node js , you can run it with the following command:

npx knex migrate:latest

This will apply all pending migrations.

6. Rollback Migrations

If you need to undo a Migration in Node js, you can use:

npx knex migrate:rollback

This will revert the most recent batch of migrations.

Option 2: Using Sequelize for Migrations

Sequelize is an ORM for Node.js that also provides a powerful migration system.

1. Install Sequelize and a Database Client

First, install Sequelize and the appropriate database client:

npm install sequelize pg pg-hstore # For PostgreSQL
# Or for MySQL:
# npm install sequelize mysql2

2. Initialize Sequelize

You need to set up Sequelize and its configuration. You can generate a Sequelize config by running:

npx sequelize-cli init

This will create several folders like config, models, migrations, and seeders in your project directory.

3. Configure Database Connection

In the config/config.json file, configure your database connection:

{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "database_test",
    "host": "127.0.0.1",
    "dialect": "postgres"
  },
  "production": {
    "username": "root",
    "password": null,
    "database": "database_production",
    "host": "127.0.0.1",
    "dialect": "postgres"
  }
}

4. Create Migrations

To create a new migration, use the following command:

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

This will create a new migration file under the migrations folder.

5. Write Migration Code

In the migration file, you’ll define the up and down methods, similar to Knex.

Example Migration in Node js to create a users table:

// migrations/20241110123456-create-users.js
'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER,
      },
      name: {
        type: Sequelize.STRING,
      },
      email: {
        type: Sequelize.STRING,
        unique: true,
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
      },
    });
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Users');
  },
};

6. Run Migrations

To apply the migration, run the following command:

npx sequelize-cli db:migrate

7. Rollback Migrations

To undo a Migration in Node js:

npx sequelize-cli db:migrate:undo

You can also undo all migrations:

npx sequelize-cli db:migrate:undo:all

Conclusion

Both Knex.js and Sequelize provide tools to handle migrations in Node.js.

• Knex.js gives you more control over the SQL you write and can be used with multiple databases, offering flexibility if you want to write raw queries.
• Sequelize, on the other hand, is an ORM, so it provides an abstraction layer on top of your database, making it easier to work with models and relationships, but it also comes with more overhead.

Choose the one that fits your project’s needs and your preferred workflow!

• What is FastAPI – A detailed Introduction
• How to Call a Third-Party API in Laravel
• FastAPI vs Django: A Comparative Analysis for Web Development