Advance Topics

Migration Skeleton

The following skeleton shows a typical migration file.

module.exports = {
  up: (queryInterface, Sequelize) => {
    // logic for transforming into the new state
  },
  down: (queryInterface, Sequelize) => {
    // logic for reverting the changes
  }
}

We can generate this file using migration:generate. This will create xxx-migration-skeleton.js in your migration folder.

$ npx sequelize-cli migration:generate --name migration-skeleton

The passed queryInterface object can be used to modify the database. The Sequelize object stores the available data types such as STRING or INTEGER. Function up or down should return a Promise. Let's look at an example:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Person', {
        name: Sequelize.STRING,
        isBetaMember: {
          type: Sequelize.BOOLEAN,
          defaultValue: false,
          allowNull: false
        }
      });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Person');
  }
}

The following is an example of a migration that performs two changes in the database, using a transaction to ensure that all instructions are successfully executed or rolled back in case of failure:

module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.sequelize.transaction((t) => {
            return Promise.all([
                queryInterface.addColumn('Person', 'petName', {
                    type: Sequelize.STRING
                }, { transaction: t }),
                queryInterface.addColumn('Person', 'favoriteColor', {
                    type: Sequelize.STRING,
                }, { transaction: t })
            ])
        })
    },
    down: (queryInterface, Sequelize) => {
        return queryInterface.sequelize.transaction((t) => {
            return Promise.all([
                queryInterface.removeColumn('Person', 'petName', { transaction: t }),
                queryInterface.removeColumn('Person', 'favoriteColor', { transaction: t })
            ])
        })
    }
};

The next is an example of a migration that has a foreign key. You can use references to specify a foreign key:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Person', {
      name: Sequelize.STRING,
      isBetaMember: {
        type: Sequelize.BOOLEAN,
        defaultValue: false,
        allowNull: false
      },
      userId: {
        type: Sequelize.INTEGER,
        references: {
          model: {
            tableName: 'users',
            schema: 'schema'
          }
          key: 'id'
        },
        allowNull: false
      },
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Person');
  }
}

The next is an example of a migration that has uses async/await where you create an unique index on a new column:

module.exports = {
  async up(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();
    try {
      await queryInterface.addColumn(
        'Person',
        'petName',
        {
          type: Sequelize.STRING,
        },
        { transaction }
      );
      await queryInterface.addIndex(
        'Person',
        'petName',
        {
          fields: 'petName',
          unique: true,
        },
        { transaction }
      );
      await transaction.commit();
    } catch (err) {
      await transaction.rollback();
      throw err;
    }
  },
  async down(queryInterface, Sequelize) {
    const transaction = await queryInterface.sequelize.transaction();
    try {
      await queryInterface.removeColumn('Person', 'petName', { transaction });
      await transaction.commit();
    } catch (err) {
      await transaction.rollback();
      throw err;
    }
  },
};

The .sequelizerc File

This is a special configuration file. It lets you specify following options that you would usually pass as arguments to CLI:

• env: The environment to run the command in
• config: The path to the config file
• options-path: The path to a JSON file with additional options
• migrations-path: The path to the migrations folder
• seeders-path: The path to the seeders folder
• models-path: The path to the models folder
• url: The database connection string to use. Alternative to using —config files
• debug: When available show various debug information

Some scenarios where you can use it.

• You want to override default path to migrations, models, seeders or config folder.
• You want to rename config.json to something else like database.json

And a whole lot more. Let's see how you can use this file for custom configuration.

For starters, let's create an empty file in the root directory of your project.

$ touch .sequelizerc

Now let's work with an example config.

const path = require('path');
module.exports = {
  'config': path.resolve('config', 'database.json'),
  'models-path': path.resolve('db', 'models'),
  'seeders-path': path.resolve('db', 'seeders'),
  'migrations-path': path.resolve('db', 'migrations')
}

With this config you are telling CLI to

• Use config/database.json file for config settings
• Use db/models as models folder
• Use db/seeders as seeders folder
• Use db/migrations as migrations folder

Dynamic Configuration

Configuration file is by default a JSON file called config.json. But sometimes you want to execute some code or access environment variables which is not possible in JSON files.

Sequelize CLI can read from both JSON and JS files. This can be setup with .sequelizerc file. Let see how

First you need to create a .sequelizerc file in the root folder of your project. This file should override config path to a JS file. Like this

const path = require('path');
module.exports = {
  'config': path.resolve('config', 'config.js')
}

Now Sequelize CLI will load config/config.js for getting configuration options. Since this is a JS file you can have any code executed and export final dynamic configuration file.

An example of config/config.js file

const fs = require('fs');
module.exports = {
  development: {
    username: 'database_dev',
    password: 'database_dev',
    database: 'database_dev',
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  test: {
    username: 'database_test',
    password: null,
    database: 'database_test',
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  production: {
    username: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    host: process.env.DB_HOSTNAME,
    dialect: 'mysql',
    dialectOptions: {
      ssl: {
        ca: fs.readFileSync(__dirname + '/mysql-ca-master.crt')
      }
    }
  }
};

Using Babel

Now you know how to use .sequelizerc file. Now let's see how to use this file to use babel with sequelize-cli setup. This will allow you to write migrations and seeders with ES6/ES7 syntax.

First install babel-register

$ npm i --save-dev babel-register

Now let's create .sequelizerc file, it can include any configuration you may want to change for sequelize-cli but in addition to that we want it to register babel for our codebase. Something like this

$ touch .sequelizerc # Create rc file

Now include babel-register setup in this file

require("babel-register");
const path = require('path');
module.exports = {
  'config': path.resolve('config', 'config.json'),
  'models-path': path.resolve('models'),
  'seeders-path': path.resolve('seeders'),
  'migrations-path': path.resolve('migrations')
}

Now CLI will be able to run ES6/ES7 code from migrations/seeders etc. Please keep in mind this depends upon your configuration of .babelrc. Please read more about that at babeljs.io.

Using Environment Variables

With CLI you can directly access the environment variables inside the config/config.js. You can use .sequelizerc to tell CLI to use config/config.js for configuration. This is explained in last section.

Then you can just expose file with proper environment variables.

module.exports = {
  development: {
    username: 'database_dev',
    password: 'database_dev',
    database: 'database_dev',
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  test: {
    username: process.env.CI_DB_USERNAME,
    password: process.env.CI_DB_PASSWORD,
    database: process.env.CI_DB_NAME,
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  production: {
    username: process.env.PROD_DB_USERNAME,
    password: process.env.PROD_DB_PASSWORD,
    database: process.env.PROD_DB_NAME,
    host: process.env.PROD_DB_HOSTNAME,
    dialect: 'mysql'
  }
};

Specifying Dialect Options

Sometime you want to specify a dialectOption, if it's a general config you can just add it in config/config.json. Sometime you want to execute some code to get dialectOptions, you should use dynamic config file for those cases.

{
    "production": {
        "dialect":"mysql",
        "dialectOptions": {
            "bigNumberStrings": true
        }
    }
}

Production Usages

Some tips around using CLI and migration setup in production environment.

1) Use environment variables for config settings. This is better achieved with dynamic configuration. A sample production safe configuration may look like.

const fs = require('fs');
module.exports = {
  development: {
    username: 'database_dev',
    password: 'database_dev',
    database: 'database_dev',
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  test: {
    username: 'database_test',
    password: null,
    database: 'database_test',
    host: '127.0.0.1',
    dialect: 'mysql'
  },
  production: {
    username: process.env.DB_USERNAME,
    password: process.env.DB_PASSWORD,
    database: process.env.DB_NAME,
    host: process.env.DB_HOSTNAME,
    dialect: 'mysql',
    dialectOptions: {
      ssl: {
        ca: fs.readFileSync(__dirname + '/mysql-ca-master.crt')
      }
    }
  }
};

Our goal is to use environment variables for various database secrets and not accidentally check them in to source control.

Storage

There are three types of storage that you can use: sequelize, json, and none.

• sequelize : stores migrations and seeds in a table on the sequelize database
• json : stores migrations and seeds on a json file
• none : does not store any migration/seed

Migration Storage

By default the CLI will create a table in your database called SequelizeMeta containing an entryfor each executed migration. To change this behavior, there are three options you can add to theconfiguration file. Using migrationStorage, you can choose the type of storage to be used formigrations. If you choose json, you can specify the path of the file using migrationStoragePathor the CLI will write to the file sequelize-meta.json. If you want to keep the information in thedatabase, using sequelize, but want to use a different table, you can change the table name usingmigrationStorageTableName. Also you can define a different schema for the SequelizeMeta table byproviding the migrationStorageTableSchema property.

{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "mysql",
    // Use a different storage type. Default: sequelize
    "migrationStorage": "json",
    // Use a different file name. Default: sequelize-meta.json
    "migrationStoragePath": "sequelizeMeta.json",
    // Use a different table name. Default: SequelizeMeta
    "migrationStorageTableName": "sequelize_meta",
    // Use a different schema for the SequelizeMeta table
    "migrationStorageTableSchema": "custom_schema"
  }
}

Note:The none storage is not recommended as a migration storage. If you decide to use it, beaware of the implications of having no record of what migrations did or didn't run.

Seed Storage

By default the CLI will not save any seed that is executed. If you choose to change this behavior (!),you can use seederStorage in the configuration file to change the storage type. If you choose json,you can specify the path of the file using seederStoragePath or the CLI will write to the filesequelize-data.json. If you want to keep the information in the database, using sequelize, you canspecify the table name using seederStorageTableName, or it will default to SequelizeData.

{
  "development": {
    "username": "root",
    "password": null,
    "database": "database_development",
    "host": "127.0.0.1",
    "dialect": "mysql",
    // Use a different storage. Default: none
    "seederStorage": "json",
    // Use a different file name. Default: sequelize-data.json
    "seederStoragePath": "sequelizeData.json",
    // Use a different table name. Default: SequelizeData
    "seederStorageTableName": "sequelize_data"
  }
}

Configuration Connection String

As an alternative to the —config option with configuration files defining your database, you canuse the —url option to pass in a connection string. For example:

$ npx sequelize-cli db:migrate --url 'mysql://root:password@mysql_host.com/database_name'

Passing Dialect Specific Options

{
    "production": {
        "dialect":"postgres",
        "dialectOptions": {
            // dialect options like SSL etc here
        }
    }
}

Programmatic use

Sequelize has a sister library for programmatically handling execution and logging of migration tasks.