Sequelize Automatic Database Migration and Seeding on Heroku

Database migration is important to make sure database schema is synced across environment. Seeding is important too to provide initial data on database, especially user data when authentication is needed. I’ll give you walk-through to do it with Sequelize and run it on local and Heroku.

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.

Migration Tutorial

We assume you already have a Node.js project. If you haven’t, you can search tutorial online. We will use Express.js as node.js web app framework.

Setup

Install sequelize and database driver (we use postgresql as example).

npm install --save sequelize
npm install --save pg pg-hstore # Postgres

Init sequelize. This command will create four folders (config, models, migrations, and seeders) and folder path configuration named .sequelizerc.

sequelize init

Configurate you db on config/config.json .

module.exports = {
  development: {
    username: 'your_db_username',
    password: 'your_db_password',
    database: 'your_project_name_development',
    host: '127.0.0.1',
    dialect: 'postgres',
    operatorsAliases: '0',
  },
  test: {
    username: 'your_db_username',
    password: 'your_db_password',
    database: 'your_project_name_test',
    host: '127.0.0.1',
    dialect: 'postgres',
    operatorsAliases: '0',
  },
  production: {
    username: 'your_db_username',
    password: 'your_db_password',
    database: 'your_project_name_production',
    host: '127.0.0.1',
    dialect: 'postgres',
    operatorsAliases: '0',
  },
};

Before continue, make sure you already make a database named your_project_name_development and your_project_name_test on your machine with same username and password with config above. This database needed to interact with database on development and test environment.

Create Model

Model is a schema of database table using native language (i.e. javascript).

Create your first model.

# model/Superuser.js
module.exports = (sequelize, DataTypes) => {
  const Superuser = sequelize.define('superuser', {
    email: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
    },
    password: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    role: {
      type: DataTypes.ENUM('admin', 'restricted'),
      allowNull: false,
    },
  }, {
    freezeTableName: true, # Prevent table name change to plural
  });
  return Superuser;
};

Create Migrations

Migrations is a script to run SQL command to create / change database table schema.

Create your migrations file manually. Writeup method to create or change table schema and down method to undo it.

# migrations/xxxx-create-model-superuser.js
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('user', {
      "id": {
          "type": Sequelize.INTEGER,
          "field": "id",
          "autoIncrement": true,
          "primaryKey": true,
          "allowNull": false
      },
      "email": {
          "type": Sequelize.STRING,
          "field": "email",
          "unique": true,
          "allowNull": false
      },
      "encryptedPassword": {
          "type": Sequelize.STRING,
          "field": "encryptedPassword",
          "allowNull": false
      },
      "role": {
          "type": Sequelize.ENUM('admin', 'restricted'),
          "field": "role",
          "allowNull": false
      },
      "createdAt": {
          "type": Sequelize.DATE,
          "field": "createdAt",
          "allowNull": false
      },
      "updatedAt": {
          "type": Sequelize.DATE,
          "field": "updatedAt",
          "allowNull": false
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('superuser');
  }
};

You can also create skeleton model and migrations with this command.

sequelize model:create --name superuser --attributes email:string, password:string, role:enum

Well, creating migrations manually is very WET (Waste Everybody Time). The structure actually similar to models. Instead of creating it manually, you can use sequelize-auto-migrations. It is actually a dead package but you can install the specific PR that works.

npm install --save github:scimonster/sequelize-auto-migrations#a063aa6535a3f580623581bf866cef2d609531ba

Edit package.json

"scripts": {
  ...
  "db:makemigrations": "./node_modules/sequelize-auto-migrations/bin/makemigration.js",
}

After create or edit model, you can automatically make migrations file.

npm run db:makemigrations

But there’s a known gotchas while renaming a column. Read here to learn more.

Run Migrations

By running migrations, it will run SQL command generated from migrations file to your database. Therefore, your database schema will be synced with model you created.

npx sequelize db:migrate

You can check your local database if your model is already created into table.

You can also undo migrations.

npx sequelize db:migrate:undo # Undo most recent migration
npx sequelize db:migrate:undo:all # Undo all migrations
npx sequelize db:migrate:undo --to path/to/migration/file.js # Undo all migrations until specific file

Seeder Tutorial

Seeding means providing database initial data or records. It can be achieved by creating seeders script that will run SQL command to create specified records.

Create Seeders

Create seeds skeleton.

npx sequelize seed:generate --name default-superuser

Edit seeds file. Write up method to insert data and down method to undo it.

# seeders/xxxxxxx-default-superuser.js
'use strict';
module.exports = {
  up: async (queryInterface, Sequelize) => {
    const password =  process.env.SEED_SUPERUSER_PASSWORD || 'defaultpassword';
return queryInterface.bulkInsert('superuser', [{
      email: 'admin@agenkan.com',
      password: 'yourpasswordehre',
      role: 'admin',
      createdAt: new Date(),
      updatedAt: new Date()
    }]);
  },
down: (queryInterface, Sequelize) => {
    return queryInterface.bulkDelete('superuser', { email: 'admin@agenkan.com' }, {});
  }
};

Run Seeders

Run seeds means run SQL command to insert data provided in seeder files.

To run all seeders:

npx sequelize db:seed:all

You can check your local database if your model is already created into table.

You can also undo migrations.

npx sequelize db:seed:undo # Undo most recent seed
npx sequelize db:seed:undo:all # Undo all seeds
npx sequelize db:seed:undo --seed path/to/seed/file.js # Undo specific seeds

Migrating and Seeding on Heroku

On Heroku, your app has its own environment usually called production. To manually migrating and seeding on heroku, you can install Heroku CLI so you can access your heroku app like your local machine.

I won’t go into how to install it. You can read it on their official getting started page. We also assumed you already deploy your project to heroku and create. If you haven’t, you can read the documentation or tutorial online. I recommended this tutorial.

To access you heroku app. Login to your account.

heroku login

Navigate to your project directory and set heroku as remote to easily access the heroku app.

heroku git:remote -a [app_name]

Access to your heroku app through Heroku CLI.

heroku run bash

Type ls and you will see the same files like your local/remote repository.

To manually run migrations and seeds, just run the same command like it were on local.

npx sequelize db:migrate:all
npx sequelize db:seed:all

To automatically run migrations and seeders before the heroku app built, create Procfile contains migrate and seed command.

# Procfile
web: npx sequelize db:migrate:all && npx sequelize db:seed:all

That’s all you need to do to create and run migrations and seeders manually and automatically on local and production. (So many ‘and’, lol)

Reference:

Membuat RESTful API menggunakan Express dan Sequelize

Heroku toolbelt command requires app-name

The Heroku CLI