Node.jsの「Sequelize」の「migration機能」でよく使うパターンまとめ

Node.jsの「Sequelize」の「migration機能」でよく使うパターンまとめ（備忘録）


# migration下準備
## mysqlを触るのに最低限必要なやつインストール

```bash
npm i sequelize -D
npm i sequelize-cli -D
npm i mysql2 -D
```

今回追加でインストールしたもの

```bash
npm i path -D
npm i dayjs -D 
```


## Sequelizeの各ファイルのパス情報
各設定ファイルの保管パスを設定できます。
※プロジェクトのrootフォルダに設置

```javascript:.sequelizerc
const path = require("path");
module.exports = {
  config: path.resolve("sequelize", "config.json"),
  "models-path": path.resolve("sequelize", "sequelize-models"),
  "seeders-path": path.resolve("sequelize", "seeders"),
  "migrations-path": path.resolve("sequelize", "migrations"),
};
```


## Mysql 接続設定


```json:sequelize/config.json
{
  "development": {
    "host": "localhost",
    "port": "3306",
    "username": "root",
    "password": "root",
    "database": "test",
    "dialect": "mysql",
    "timezone": "+09:00",
    "operatorsAliases": 0,
    "logging": false
  }
}

```



## SequelizeのMigrationでよく使用する関数をまとめたもの

```javascript:sequelize/com-func.js
/*-------------------------------------------------
マイグレーションで使用する関数群
-------------------------------------------------*/
const ComFunc = {
  /**
   * マイグレーションファイルから下記のようにして使用する
   * const con = ComFunc.GetConnection(queryInterface);
   * const sql = 'select * from m_code';
   * const ret = await con.query(sql, { raw: false });
   */
  GetConnection: function (queryInterface) {
    const Sequelize = require("sequelize");
    const SequelizeConf = {
      host: queryInterface.sequelize.options.host,
      port: queryInterface.sequelize.options.port,
      dialect: "mysql",
      timezone: "+09:00",
      logging: false, // ← 追記
      dialectOptions: {
        timezone: "+09:00",
        dateStrings: true,
        typeCast: function (field, next) {
          if (field.type === "DATETIME") {
            return field.string();
          }
          return next();
        },
      },
    };
    return new Sequelize(
      queryInterface.sequelize.config.database,
      queryInterface.sequelize.options.username,
      queryInterface.sequelize.options.password,
      SequelizeConf
    );
  },
  IndexKey: {
    Add: async function (queryInterface, tableName, setIndexList) {
      const setIndexListCount = setIndexList.length;
      for (let i = 0; i < setIndexListCount; i++) {
        if (setIndexList[i].length === 0) continue;
        const indexName = setIndexList[i][0] + "_index";
        const indexList = [];
        const setIndexListChildCount = setIndexList[i].length;
        for (let j = 0; j < setIndexListChildCount; j++) {
          if (setIndexList[i][j] !== "") {
            indexList.push(setIndexList[i][j]);
          }
        }
        await queryInterface.addIndex(tableName, indexList, {
          name: indexName,
        });
      }
    },
    Remove: async function (queryInterface, tableName, setIndexList) {
      const setIndexListCount = setIndexList.length;
      for (let i = 0; i < setIndexListCount; i++) {
        if (setIndexList[i].length === 0) continue;
        const indexName = setIndexList[i][0] + "_index";
        await queryInterface.removeIndex(tableName, indexName);
      }
    },
  },
  UniqueKey: {
    Add: async function (queryInterface, tableName, setIndexList) {
      const setIndexListCount = setIndexList.length;
      for (let i = 0; i < setIndexListCount; i++) {
        if (setIndexList[i].length === 0) continue;
        const indexName = setIndexList[i][0] + "_uq_index";
        const indexList = [];
        const setIndexListChildCount = setIndexList[i].length;
        for (let j = 0; j < setIndexListChildCount; j++) {
          if (setIndexList[i][j] !== "") {
            indexList.push(setIndexList[i][j]);
          }
        }
        await queryInterface.addIndex(tableName, indexList, {
          name: indexName,
          unique: true,
        });
      }
    },
    Remove: async function (queryInterface, tableName, setIndexList) {
      const setIndexListCount = setIndexList.length;
      for (let i = 0; i < setIndexListCount; i++) {
        if (setIndexList[i].length === 0) continue;
        const indexName = setIndexList[i][0] + "_uq_index";
        await queryInterface.removeIndex(tableName, indexName);
      }
    },
  },
  Column: {
    Change: async function (queryInterface, tableName, scheme) {
      const keys = Object.keys(scheme);
      for (let i = 0; i < keys.length; i++) {
        await queryInterface.changeColumn(tableName, keys[i], scheme[keys[i]]);
      }
    },
    Add: async function (queryInterface, tableName, scheme) {
      const keys = Object.keys(scheme);
      for (let i = 0; i < keys.length; i++) {
        await queryInterface.addColumn(tableName, keys[i], scheme[keys[i]]);
      }
    },
    Remove: async function (queryInterface, tableName, scheme) {
      const keys = Object.keys(scheme);
      for (let i = 0; i < keys.length; i++) {
        await queryInterface.removeColumn(tableName, keys[i]);
      }
    },
  },
};
module.exports = ComFunc;
```


あとはSequelizeのコマンドでMigrationファイルを作って実行するだけ


# Migration
## Migration ファイルの作成コマンド

```
npx sequelize migration:generate --name create_table_name
```
--name 以降がmigrationのファイル名に付与される

## Migration ファイル

実行したら以下のファイルが生成されます。（ファイル名は生成日に --name以降の文字列が付与される

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3270885/7c36a140-6dc0-0ba0-0ebc-ba4b5c2ae183.png)

```javascript
'use strict';

/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up (queryInterface, Sequelize) {
    /**
     * Add altering commands here.
     *
     * Example:
     * await queryInterface.createTable('users', { id: Sequelize.INTEGER });
     */
  },

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

```

migrationファイルの準備ができたら以下コマンドでDBへ反映できる
```bash
npx sequelize db:migrate --env development
```


修正したいときで一つ前にmigrationを戻す時
```bash
npx sequelize db:migrate:undo --env development
```

全部戻す場合
```bash
npx sequelize db:migrate:undo:all --env development
```



## Migration テーブル追加サンプル


```javascript
/* eslint-disable */

const { DataTypes, QueryTypes } = require("sequelize");
const ComFunc = require("../com-func");
//--------------------------------------
const tableName = "table_name";
const tableComments = "tableComments";
/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    const columns = {
      id: {
        type: DataTypes.INTEGER.UNSIGNED,
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        comment: "ID",
      },
      user_e_mail: {
        type: DataTypes.STRING(200),
        allowNull: false,
        comment: "ユーザーE-Mail",
      },
      user_name: {
        type: DataTypes.STRING(200),
        allowNull: false,
        comment: "ユーザー名",
      },
      obj: {
        type: DataTypes.JSON,
        allowNull: false,
        comment: "obj",
      },
      //------------------
      activate: {
        type: DataTypes.TINYINT,
        allowNull: true,
        defaultValue: 1,
        comment: "ユニーク制御フラグ",
      },
      //------------------
      created_at: {
        type: DataTypes.DATE(3), //ミリ秒
        allowNull: false,
        comment: "ミリ秒まで管理",
      },
      updated_at: {
        type: DataTypes.DATE, //秒
        allowNull: false,
        comment: "秒まで管理",
      },
      date_only: {
        type: DataTypes.DATEONLY, //日付のみ
        allowNull: false,
        comment: "日付のみ",
      },
    };
    await queryInterface.createTable(tableName, columns, {
      comment: tableComments,
      collate: "utf8mb4_general_ci",
    });
    await ComFunc.IndexKey.Add(queryInterface, tableName, [
      ["user_name"],
    ]);

    await ComFunc.UniqueKey.Add(queryInterface, tableName, [
      ["user_e_mail", "activate"],
    ]);
  },
  async down(queryInterface, Sequelize) {
    await queryInterface.dropTable(tableName);
  },
};


```

**実行結果**
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3270885/db216e28-4ce9-afd7-7559-9e745a31ce95.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3270885/a7e568d4-ae8b-62da-e6f8-7b0d4e022151.png)


## Migration テーブルへのデータ追加サンプル
※Seed機能を使わないケースなど
※定数管理テーブルなどはMigrationの方が運用上便利に感じたので


```javascript
"use strict";
/* eslint-disable */
const ComFunc = require("../com-func");
const dayjs = require("dayjs");
//--------------------------------------
const tableName = "table_name";
//--------------------------------------
/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.bulkDelete(tableName, null, {});
    const con = ComFunc.GetConnection(queryInterface);
    const sql = "ALTER TABLE `" + tableName + "` auto_increment = 1";
    await con.query(sql, { raw: false });
    const list = [
      {
        user_e_mail: "test1@example.com",
        user_name: "test1",
        obj: JSON.stringify({ message: "messsssseageeee" }),
        activate: 1,
        created_at: dayjs().format("YYYY-MM-DD HH:mm:ss.SSS"),
        updated_at: dayjs().format("YYYY-MM-DD HH:mm:ss.SSS"),
        date_only: dayjs().format("YYYY-MM-DD HH:mm:ss.SSS"),
      },
    ];
    await queryInterface.bulkInsert(tableName, list, {});
  },

  async down(queryInterface, Sequelize) {
    await queryInterface.bulkDelete(tableName, null, {});
    const con = ComFunc.GetConnection(queryInterface);
    const sql = "ALTER TABLE `" + tableName + "` auto_increment = 1";
    await con.query(sql, { raw: false });
    const list = [];
    if (list.length !== 0) {
      await queryInterface.bulkInsert(tableName, list, {});
    }
  },
};

```

**実行結果**

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3270885/4b0a41b5-25ed-2c4d-4d67-59d00254d5c1.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3270885/e178e607-5b34-a464-bf79-3624ab82fca5.png)



## Migration テーブルへのカラム編集サンプル


```javascript
"use strict";
/* eslint-disable */
const { DataTypes, QueryTypes } = require("sequelize");
const ComFunc = require("../com-func");
//--------------------------------------
const tableName = "table_name";
/** @type {import('sequelize-cli').Migration} */
module.exports = {
  async up(queryInterface, Sequelize) {
    await ComFunc.IndexKey.Remove(queryInterface, tableName, [
      ["user_name"],
    ]);
    const columns = {
      note: {
        type: DataTypes.STRING(200),
        allowNull: false,
        comment: "備考",
      },
    };
    await ComFunc.Column.Add(queryInterface, tableName, columns);
    await ComFunc.IndexKey.Add(queryInterface, tableName, [
      ["user_name", "note"],
    ]);
  },
  async down(queryInterface, Sequelize) {
    //逆方向で実行すれば元通り。
    await ComFunc.IndexKey.Remove(queryInterface, tableName, [
      ["user_name", "note"],
    ]);
    const columns = {
      note: {
        type: DataTypes.STRING(200),
        allowNull: false,
        comment: "備考",
      },
    };
    await ComFunc.Column.Remove(queryInterface, tableName, columns);
    await ComFunc.IndexKey.Add(queryInterface, tableName, [
      ["user_name"],
    ]);
  },
};
```


コピペで安全にmigrationを管理できるよう関数を考えては・・・いるつもり。
ただ、複雑な使い方はしていない。

なので詳しくは以下公式までどうぞ

https://sequelize.org/docs/v6/other-topics/migrations/




# 一応 GitHubにサンプル上げてます

https://github.com/hare-systems-ryo/node-sequelize-migration


> サンプルはマイグレーションファイルのフォルダを分けています。
> npx sequelize db:migrate --env development 
> をすると「migrationsフォルダ」にあるmigrationファイルが全部実行されてしまうので・・・
> フォルダを「migrations」へ移動すればDBへの反映ができるようになります。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3270885/e5764654-8888-296e-c227-ce1d4ebc3aa8.png)


# TypeScriptでORMとしてSequelizeを使う例はこちら

https://qiita.com/hare-systems-ryo/items/5c389f11818784973d54