Create Table Migrations

Welcome to this hands-on tutorial on creating table migrations with Sequelize! Imagine you are the architect of a building. Just as an architect creates detailed blueprints and construction logs to document every change made to the design, migrations allow you to document and implement changes to your database schema in a controlled and reversible manner.

In this session, we will learn how to generate a migration file to create a table in your database using the Sequelize CLI. This process ensures that every change is version-controlled and that you have the ability to roll back to previous states, which is crucial when working on real-world projects.

Generating a Migration File

The first step is to generate a migration file that will serve as your record of changes. This file is automatically timestamped, so you can always tell when a particular change was made.

To generate a migration file, open your terminal in your project’s directory and run the following command:

npx sequelize-cli migration:generate --name <name_of_table>

Replace <name_of_table> with the descriptive name of the table you plan to create. For example, if you are creating a table for users, you might run:

npx sequelize-cli migration:generate --name create_users

This command creates a migration file inside your migrations folder with a filename that looks like: 20230412123456-create_users.js — where the numbers represent the timestamp of generation.

Understanding the Migration File

Open the generated file to see its structure. You will find two main functions:

up function: Contains the commands to apply the migration (e.g., create the table).
down function: Contains the commands to revert the migration (e.g., drop the table).

Here is a simplified example of what the migration file might look like:

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    /**
     * Add altering commands here.
     * Example: await queryInterface.createTable('Users', { id: Sequelize.INTEGER });
     */
  },

  down: async (queryInterface, Sequelize) => {
    /**
     * Add reverting commands here.
     * Example: await queryInterface.dropTable('Users');
     */
  }
};

The queryInterface object provided by Sequelize gives you methods to create and drop tables, add or remove columns, and more. These methods are the building blocks for managing your database schema.

Step-by-Step Follow-Along Exercise

Let's build a migration to create a Users table. Follow these steps:

Step 1: Generate a migration file by running:

npx sequelize-cli migration:generate --name create_users

This creates a new file in your migrations folder.

Step 2: Open the generated file and edit the up function to create the table. Add definitions for columns like id, username, and email.

'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,
        allowNull: false,
        unique: true
      },
      email: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE,
        defaultValue: Sequelize.literal('CURRENT_TIMESTAMP')
      }
    });
  },

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

Step 3: Save the file and run your migration to apply the changes to your database:

npx dotenv sequelize db:migrate

This command executes the up function, creating the Users table.

Step 4: If you need to undo the migration for any reason, run:

npx dotenv sequelize db:migrate:undo

This command executes the down function, removing the table.

When and Why to Use Migrations

Migrations are essential for several reasons:

They provide version control for your database schema. As your project evolves, your schema needs to change. Migrations let you track every change and roll back if something goes wrong.
They support collaboration by ensuring every team member’s local database is updated consistently through a series of ordered changes.
They protect user data by applying schema changes incrementally. Instead of dropping tables and losing data, migrations update your database safely.

Additional Topics to Explore

Once you’re comfortable with basic migrations, consider diving deeper into:

Creating complex migrations that modify multiple columns or tables.
Writing migrations that adjust constraints or perform data transformations.
Integrating migrations into a continuous integration/continuous deployment (CI/CD) workflow.
Best practices for seeding data after migrations.

Conclusion

In this tutorial, you learned how to generate a migration file using the Sequelize CLI and edit it to create a table. Migrations are a powerful tool that provide a version control system for your database schema, support collaboration, and protect critical user data by allowing safe, incremental updates.

With this knowledge, you’re ready to use migrations to manage your database changes effectively. As you continue building your applications, remember that migrations help ensure your schema evolves smoothly, just like maintaining detailed blueprints for a complex building project.