Creating Sequelize Migrations from an Existing Database

Sequelize ORM has migration features to manage changes to your database schema over time. You probably have an existing database that you want to add Sequelize migrations to it. In this tutorial, you will learn to create Sequelize migrations from an existing database.

The Concept of Creating Sequelize Migrations from an Existing Database

If you’re creating migrations for an existing database using Sequelize, you have to reverse-engineer your existing database schema. The scheme will then be used by Sequelize to generate migrations from it.

Sequelize doesn’t have a built-in way to directly generate migrations from an existing database. However, worry note, we can still achieve database reverse engineering and let Sequelize get ready migrations manually.

Here is what you need to get Sequelize ready to create existing database migrations:

• Reverse Engineer the existing database schema to understand its structure.
• Create migration files for each table while representing its schema.
• Write Sequelize migration code to represent the changes you want to make to the database schema.

Ready? Let’s now learn the step-by-step process to achieving this task.

Prerequisites

Before you start using Sequelize to create existing database migrations, you must have:

• Node.js and npm. If you have Node.js installed, you are good to go.
• Install a database client for your database system. If you’re using MySQL, install MySQL Workbench or any other MySQL client. At the same time, you need to have an existing database. with an appropriate table.
• Only use a test database here. Just to be safe if this setup creates an expected behavior in your database.

Setting up Sequelize

In your existing Node.js app (npm init -y), install Sequelize CLI globally with the following command:

npm install -g sequelize-cli

Install these packages to your app:

npm install sequelize async

Initialize Sequelize Migrations Structure:

sequelize init

This command creates a migrations folder in your project directory. This folder will hold all your migration files. Now create an initial migration file. You will use this file to create the SequelizeMeta table and keep track of executed migrations.

npx sequelize migration:create --name initial

Go ahead and create another migration for creating tables. You should create a new migration file for creating tables. This file is needed to define the structure of our tables based on the existing database schema.

npx sequelize migration:create --name create-tables

Reverse Engineering and Dumping Database Schema

To generate migrations from an existing database, you need to dump the database schema without the data. This way, you need to use the mysqldump command for MySQL databases:

mysqldump -d --compact --compatible=mysql323 ${dbname} | egrep -v "(^SET|^/\*\!)" > initial.sql

Note: Replace ${dbname} with the name of your database. Remove the `SET

Once you run it, the command dumps the schema of your database to an initial.sql file without including data. This is the file you need to run so Sequelize migrations can recognize this existing database.

This means you must save the Dumped Schema initial.sql file to the migrations folder in your project directory.

Updating Sequelize Migrations to Run Existing Database

Now you have a dump on your existing database. Sequelize needs to recognize the initial.sql file and run migrations through it. Now, the command npx sequelize migration:create --name create-tables created a XXXXXXXXXXXXXX-create-tables.js file in your migrations.

You will update this file and include the logic for creating tables based on the dumped schema as follows:

    // async module for handling asynchronous operations
    const async = require('async'); 
    // Importing fs module to read initial.sql
    const fs = require('fs');

    module.exports = {
      up: (migration, DataTypes, done) => {
        // Getting sequelize instance from migration object
        const db = migration.migrator.sequelize; 

        async.waterfall([
          (cb) => {
            // Reading initial SQL file
            fs.readFile(__dirname + '/initial.sql', (err, data) => {
              if (err) throw err;
              cb(null, data.toString());
            });
          },

          (initialSchema, cb) => {
            // Splitting SQL script into individual tables
            const tables = initialSchema.split(';'); 

            // Function to create each table
            const createTable = (tableSql, doneInsert) => {
              db.query(tableSql); // Executing query to create table
              doneInsert(); // Indicating completion of table creation
            };

            // Iterating through each table to create it
            async.each(tables, createTable, cb); 
          }
        ], done); // Callback to signal done migrations
      },

      down: (migration, DataTypes, done) => {
        // Showing all tables in the database
        migration.showAllTables().then((tableNames) => {
          const tables = tableNames.filter((name) => name.toLowerCase() !== 'sequelizemeta');

          // Function to drop each table
          const dropTable = (tableName, cb) => {
            migration.dropTable(tableName); // Dropping the table
            cb(); // Indicating table drop as done
          };

            // Iterating through each table to drop it
          async.each(tables, dropTable, done); 
        });
      }
    };

Here:

• The async.waterfall will orchestrate asynchronous calls in the up function.
• You will then run the initial.sql file and split it into individual CREATE TABLE queries.
• For each query, you execute it using db.query and use async.each to run each of these queries.
• The down function drops all tables except SequelizeMeta to revert the migrations changes.

Conclusion

You’ve learned to create Sequelize migrations from an existing database. Give credits to bulkan: Using Sequelize migrations with an existing database.