Sequelize.js: How to Alter/Change a Table

Introduction

Sequelize is a promise-based Node.js ORM for Postgres, MySQL, MariaDB, SQLite, and Microsoft SQL Server. It features solid transaction support, relations, eager and lazy loading, read replication and more. Altering or changing a table in Sequelize involves modifying the existing table schema using migration scripts, which allows developers to add, remove, or modify the columns in a particular table without losing data.

In this tutorial, we will explore how to alter or change tables using Sequelize. We’ll cover everything from setting up migrations to executing complex alterations, ensuring that you have the tools needed to handle your database schema changes like a pro.

Setting up Sequelize Migrations

Before altering a table, you must set up Sequelize to handle migrations. Migrations are a way to keep track of changes to the database structure over time, much like a version control system for your database schema. Here’s how to set up migrations in Sequelize:

// First, install the Sequelize CLI globally
npm install -g sequelize-cli

// Then, initialize migrations in your project
cd your_project_directory
sequelize init:migrations

This will create a new ‘migrations’ folder in your project which will host all your migration scripts.

Creating a Migration Script

Once you have initialized migrations, you need to create a new migration for altering the table:

sequelize migration:create --name alter-user-table

This command creates a new migration file in the ‘migrations’ directory, prefixed with a timestamp and containing the name ‘alter-user-table’.

Writing a Migration Script to Alter a Table

Open the migration file that was created and you will find two empty methods: up and down. The ‘up’ method is used for applying the migration (making the change), while the ‘down’ method is for reversing it (undoing the change).

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.addColumn(
      'Users', // name of Target model
      'isAdmin', // name of the key we're adding
      {
        type: Sequelize.BOOLEAN,
        defaultValue: false
      }
    );
  },

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

In the example above, we are adding a new boolean column called ‘isAdmin’ to the ‘Users’ table with a default value of false when running the migration. The ‘down’ method removes the ‘isAdmin’ column, which will be executed if we need to revert the migration.

Executing the Migration

To apply the migration to your database and alter the table, run the following command:

sequelize db:migrate

This will execute the ‘up’ method in your migration script and alter the ‘Users’ table accordingly. To revert the migration, use the ‘db:migrate:undo’ command:

sequelize db:migrate:undo

This will execute the ‘down’ method in your migration script and undo the alteration to the ‘Users’ table.

Advanced Table Alterations

Beyond adding or removing columns, you may need to perform more complex alterations such as changing a column’s data type, adding indexes, or renaming columns. Sequelize’s queryInterface object provides various methods to accomplish these tasks:

// Change a column's data type
await queryInterface.changeColumn('Users', 'age', {
  type: Sequelize.INTEGER,
  allowNull: false
});

// Add an index to a column
await queryInterface.addIndex('Users', ['username']);

// Rename a column
await queryInterface.renameColumn('Users', 'isAdmin', 'isAdministrator');

Each of these operations can be done within the ‘up’ and ‘down’ methods of your migration script, allowing for precise control over how your database schemas evolve over time.

Best Practices for Altering Tables

When altering tables in a production database, you should always:

• Make a backup of your database before running migrations.
• Test migrations in a development environment first.
• Write the ‘down’ method for each migration to ensure that any changes can be safely undone.
• Coordinate with your team to avoid conflicts especially when multiple migrations are involved.

Conclusion

Altering a table in Sequelize.js is an essential skill for any developer working with Node.js backends. By following the steps outlined in this tutorial, you can safely modify your database schemas using migrations. Remember to follow best practices, such as backing up your database, testing migrations thoroughly, and communicating with your team to ensure smooth transitions between schema versions.