Node.jsでumzug+SequelizeによるDBマイグレーション

今回は、Node.jsでデータベースマイグレーションツールであるumzugについて整理したいと思います。umzugを使うと、アプリケーション内やコマンドラインからDBマイグレーションを実行するプログラムを作成することができます。

Sequelizeのmigration
umzug
テスト環境の準備
最小サンプル
TypeScriptファイルの雛形からマイグレーションファイルを生成する
マイグレーションファイルのOrder
その他の使用方法
まとめ
参考リンク

Sequelizeのmigration

Node.jsのORMとして有名なOSSの１つとしてSequelizeがあります。そして、sequelizeにはsequelize-cliというコマンドラインインターフェースがあり、初期データを投入するためのseedや、データベースのスキーマ移行をするためのmigrationがあります。ただ、sequelize-cliが生成してくれるテンプレートはJavaScriptであり、私がGitHubを眺めていた時点では（本記事作成時点）TypeScriptへの対応はまだされていないようでした。

例えば、以下のようになコマンドでUser modelを生成してみます。

$ ./node_modules/.bin/sequelize-cli model:create --name User --attributes name:string
Sequelize CLI [Node: 16.13.2, CLI: 6.4.1, ORM: 6.17.0]
New model was created at /Users/guest/workspace/umzug/migration/models/user.js .
New migration was created at /Users/guest/workspace/umzug/migration/migrations/20220227081854-create-user.js

$ ./node_modules/.bin/sequelize-cli model:create --name User --attributes name:string

Sequelize CLI [Node: 16.13.2, CLI: 6.4.1, ORM: 6.17.0]

New model was created at /Users/guest/workspace/umzug/migration/models/user.js .

New migration was created at /Users/guest/workspace/umzug/migration/migrations/20220227081854-create-user.js

生成されたUser modelの中身は以下のようになっています。

'use strict';
const {
Model
} = require('sequelize');
module.exports = (sequelize, DataTypes) => {
class User extends Model {
/**
* Helper method for defining associations.
* This method is not a part of Sequelize lifecycle.
* The `models/index` file will call this method automatically.
*/
static associate(models) {
// define association here
}
}
User.init({
name: DataTypes.STRING
}, {
sequelize,
modelName: 'User',
});
return User;
};

'use strict';

const {

Model

} = require('sequelize');

module.exports = (sequelize, DataTypes) => {

class User extends Model {

/**

* Helper method for defining associations.

* This method is not a part of Sequelize lifecycle.

* The `models/index` file will call this method automatically.

static associate(models) {

// define association here

}

User.init({

}, {

sequelize,

modelName: 'User',

});

return User;

};

sequelize-cliをtypescript対応にしているnpmパッケージもありましたが、あまり更新が活発ではないようでした。

umzug

umzugは、sequelize-cliの内部で使われているマイグレーションツールフレームワークです。sequelize-cliのソースを見ると、マイグレーション実行部分はumzugが担っていることが分かります。sequelize-cliは、umzugを使いやすくしたツールと言えます。umzugはTypeScriptに対応しています。

テスト環境の準備

手元の環境は以下の通りです。

OS	macOS Catalina 10.15.7
Node.js	v16.13.2
sequelize	6.17.0
sequelize-cli	6.4.1
umzug	3.0.0
TypeScript	4.5.5
ts-node	10.5.0
sqlite3	5.0.2

テスト環境の初期化

テスト環境の初期化を行ないます。今回は、ts-nodeを使ってTypeScriptコードをNode.jsから実行できるようにします。

npm install -D typescript
npm install -D @types/node
npx tsc --init
npm install -D ts-node

npm install -D typescript

npm install -D @types/node

npx tsc --init

npm install -D ts-node

こんなコードを書いて、実行できることを確認しておきます。

import * as fs from "fs";
import * as path from "path";
import { Sequelize } from "sequelize";
import { Umzug, SequelizeStorage } from "umzug";
console.log("hello umzug");

import * as fs from "fs";

import * as path from "path";

import { Sequelize } from "sequelize";

import { Umzug, SequelizeStorage } from "umzug";

console.log("hello umzug");

ついでにpackage.jsonに書いて、npm runできるようにしておきます。

{
"name": "migration",
"version": "1.0.0",
"description": "",
"main": "index.js",
"scripts": {
"migrate": "ts-node migrate.ts"
},
"author": "",
"license": "ISC",
"dependencies": {
"sequelize": "^6.17.0",
"sequelize-cli": "^6.4.1",
"sqlite3": "^5.0.2",
"umzug": "^3.0.0"
},
"devDependencies": {
"@types/node": "^17.0.21",
"ts-node": "^10.5.0",
"typescript": "^4.5.5"
}
}

{

"name": "migration",

"version": "1.0.0",

"description": "",

"main": "index.js",

"scripts": {

"migrate": "ts-node migrate.ts"

"author": "",

"license": "ISC",

"dependencies": {

"sequelize": "^6.17.0",

"sequelize-cli": "^6.4.1",

"sqlite3": "^5.0.2",

"umzug": "^3.0.0"

"devDependencies": {

"@types/node": "^17.0.21",

"ts-node": "^10.5.0",

"typescript": "^4.5.5"

}

npm runコマンドを実行してみます。

$ npm run migrate
> migration@1.0.0 migrate
> ts-node migrate.ts
hello umzug

$ npm run migrate

> migration@1.0.0 migrate

> ts-node migrate.ts

hello umzug

よさそうです。

最小サンプル

雰囲気を掴むため、小さなサンプルを見てみたいと思います。以下は、GitHubに掲載されているサンプルと同じです。

import { Sequelize } from "sequelize";
import { Umzug, SequelizeStorage } from "umzug";
const sequelize = new Sequelize({ dialect: "sqlite", storage: "./db.sqlite" });
const umzug = new Umzug({
migrations: { glob: "migrations/*.{js,ts}" },
context: sequelize,
storage: new SequelizeStorage({ sequelize }),
logger: console,
});
(async () => {
// Checks migrations and run them if they are not already applied. To keep
// track of the executed migrations, a table (and sequelize model) called SequelizeMeta
// will be automatically created (if it doesn't exist already) and parsed.
await umzug.up();
})();

import { Sequelize } from "sequelize";

import { Umzug, SequelizeStorage } from "umzug";

const sequelize = new Sequelize({ dialect: "sqlite", storage: "./db.sqlite" });

const umzug = new Umzug({

migrations: { glob: "migrations/*.{js,ts}" },

context: sequelize,

storage: new SequelizeStorage({ sequelize }),

logger: console,

});

(async () => {

// Checks migrations and run them if they are not already applied. To keep

// track of the executed migrations, a table (and sequelize model) called SequelizeMeta

// will be automatically created (if it doesn't exist already) and parsed.

await umzug.up();

})();

Sequelizeインスタンスを作成
UmzugのsequelizeインスタンスをDBドライバとして渡す
upで上位レベルに向かってマイグレーションを実行

migrationsディレクトリが空でも実行可能です。実行すると以下のような結果になります。

$ npm run migrate
> migration@1.0.0 migrate
> ts-node migrate.ts
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;

$ npm run migrate

> migration@1.0.0 migrate

> ts-node migrate.ts

Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)

Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)

Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;

SequelizeMetaはマイグレーションのメタデータを管理するテーブルです。

TypeScriptファイルの雛形からマイグレーションファイルを生成する

続いて、TypeScriptでマイグレーションファイルを生成するようにしてみます。

migrate.tsファイルを以下のように書き換えます。

import * as fs from "fs";
import * as path from "path";
import { Sequelize } from "sequelize";
import { Umzug, SequelizeStorage } from "umzug";
const sequelize = new Sequelize({ dialect: "sqlite", storage: "./db.sqlite" });
const umzug = new Umzug({
migrations: { glob: "migrations/*.{js,ts}" },
context: sequelize,
storage: new SequelizeStorage({ sequelize }),
logger: console,
create: {
folder: path.join(__dirname, "migrations"),
template: (filepath) => [
[
filepath,
fs
.readFileSync(path.join(__dirname, "templates/sampleMigration.ts"))
.toString(),
],
],
},
});
(async () => {
await umzug.runAsCLI();
})();

import * as fs from "fs";

import * as path from "path";

import { Sequelize } from "sequelize";

import { Umzug, SequelizeStorage } from "umzug";

const sequelize = new Sequelize({ dialect: "sqlite", storage: "./db.sqlite" });

const umzug = new Umzug({

migrations: { glob: "migrations/*.{js,ts}" },

context: sequelize,

storage: new SequelizeStorage({ sequelize }),

logger: console,

create: {

folder: path.join(__dirname, "migrations"),

[

filepath,

.readFileSync(path.join(__dirname, "templates/sampleMigration.ts"))

.toString(),

});

(async () => {

await umzug.runAsCLI();

})();

違いは、以下の２点です。

Umzugの引数にcreateプロパティを追加
umzug.up()をrunAsCLI()に変更

runAsCLI()は、umzug ver3で使用可能なcli実行インターフェースです。こうしておくことで、npm run migrate create -- --name setup.tsのように引数を渡してumzugに渡して実行することができます。

テンプレートファイルは以下のようにしてみます。

import { DataTypes, Sequelize } from "sequelize";
import { MigrationFn } from "umzug";
export const up: MigrationFn<Sequelize> = async ({ context: sequelize }) => {
const t = await sequelize.transaction();
// eslint-disable-next-line @typescript-eslint/no-unused-vars
const queryInterface = sequelize.getQueryInterface();
try {
await sequelize.query("raise fail('up migration not implemented')");
await t.commit();
} catch (error) {
await t.rollback();
}
};
export const down: MigrationFn<Sequelize> = async ({ context: sequelize }) => {
const t = await sequelize.transaction();
// eslint-disable-next-line @typescript-eslint/no-unused-vars
const queryInterface = sequelize.getQueryInterface();
try {
await sequelize.query("raise fail('down migration not implemented')");
await t.commit();
} catch (error) {
await t.rollback();
}
};

import { DataTypes, Sequelize } from "sequelize";

import { MigrationFn } from "umzug";

export const up: MigrationFn<Sequelize> = async ({ context: sequelize }) => {

const t = await sequelize.transaction();

// eslint-disable-next-line @typescript-eslint/no-unused-vars

const queryInterface = sequelize.getQueryInterface();

try {

await sequelize.query("raise fail('up migration not implemented')");

await t.commit();

} catch (error) {

await t.rollback();

}

};

export const down: MigrationFn<Sequelize> = async ({ context: sequelize }) => {

const t = await sequelize.transaction();

// eslint-disable-next-line @typescript-eslint/no-unused-vars

const queryInterface = sequelize.getQueryInterface();

try {

await sequelize.query("raise fail('down migration not implemented')");

await t.commit();

} catch (error) {

await t.rollback();

}

};

では、migrateターゲットでcreateコマンドを実行してみます。

$ npm run migrate create -- --name setup.ts
> migration@1.0.0 migrate
> ts-node migrate.ts "create" "--name" "setup.ts"
{
event: 'created',
path: '/Users/guest/workspace/umzug/migration/migrations/2022.02.28T15.49.12.setup.ts'
}
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;

$ npm run migrate create -- --name setup.ts

> migration@1.0.0 migrate

> ts-node migrate.ts "create" "--name" "setup.ts"

{

event: 'created',

path: '/Users/guest/workspace/umzug/migration/migrations/2022.02.28T15.49.12.setup.ts'

}

Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)

Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)

Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;

migrationsディレクトリにsetup.tsファイルが作成されることを確認できます。

ついでに、usersテーブルを作成するマイグレーションを実行してみましょう。

import { DataTypes, Sequelize } from "sequelize";
import { MigrationFn } from "umzug";
export const up: MigrationFn<Sequelize> = async ({ context: sequelize }) => {
const t = await sequelize.transaction();
const queryInterface = sequelize.getQueryInterface();
try {
await queryInterface
.createTable("users", {
id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },
username: { type: DataTypes.STRING(32), allowNull: false },
email: { type: DataTypes.STRING(255), allowNull: false },
password: { type: DataTypes.STRING(255), allowNull: false },
})
.then(() => {
return queryInterface.addIndex("users", {
fields: ["username"],
unique: true,
});
})
.then(() => {
return queryInterface.addIndex("users", {
fields: ["email"],
unique: true,
});
});
await t.commit();
} catch (error) {
await t.rollback();
}
};
export const down: MigrationFn<Sequelize> = async ({ context: sequelize }) => {
const t = await sequelize.transaction();
const queryInterface = sequelize.getQueryInterface();
try {
await queryInterface.dropTable("users");
await t.commit();
} catch (error) {
await t.rollback();
}
};

import { DataTypes, Sequelize } from "sequelize";

import { MigrationFn } from "umzug";

export const up: MigrationFn<Sequelize> = async ({ context: sequelize }) => {

const t = await sequelize.transaction();

const queryInterface = sequelize.getQueryInterface();

try {

await queryInterface

.createTable("users", {

id: { type: DataTypes.INTEGER, autoIncrement: true, primaryKey: true },

username: { type: DataTypes.STRING(32), allowNull: false },

email: { type: DataTypes.STRING(255), allowNull: false },

password: { type: DataTypes.STRING(255), allowNull: false },

})

.then(() => {

return queryInterface.addIndex("users", {

fields: ["username"],

unique: true,

});

})

.then(() => {

return queryInterface.addIndex("users", {

fields: ["email"],

unique: true,

});

await t.commit();

} catch (error) {

await t.rollback();

}

};

export const down: MigrationFn<Sequelize> = async ({ context: sequelize }) => {

const t = await sequelize.transaction();

const queryInterface = sequelize.getQueryInterface();

try {

await queryInterface.dropTable("users");

await t.commit();

} catch (error) {

await t.rollback();

}

};

生成したsetupマイグレーションファイルを上記のように修正します。そして、再びマイグレーションを実行します。

$ npm run migrate up
> migration@1.0.0 migrate
> ts-node migrate.ts "up"
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;
{ event: 'migrating', name: '2022.02.28T15.49.12.setup.ts' }
Executing (86537b2f-f180-4b52-9afd-d2c1487f319b): BEGIN DEFERRED TRANSACTION;
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `username` VARCHAR(32) NOT NULL, `email` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL);
Executing (default): CREATE UNIQUE INDEX `users_username` ON `users` (`username`)
Executing (default): CREATE UNIQUE INDEX `users_email` ON `users` (`email`)
Executing (86537b2f-f180-4b52-9afd-d2c1487f319b): COMMIT;
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)
Executing (default): INSERT INTO `SequelizeMeta` (`name`) VALUES ($1);
{
event: 'migrated',
name: '2022.02.28T15.49.12.setup.ts',
durationSeconds: 0.182
}
{ event: 'up', message: 'applied 1 migrations.' }
$ sqlite3 db.sqlite 
SQLite version 3.28.0 2019-04-15 14:49:49
Enter ".help" for usage hints.
sqlite> .schema
CREATE TABLE `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
CREATE TABLE `users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `username` VARCHAR(32) NOT NULL, `email` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL);
CREATE TABLE sqlite_sequence(name,seq);
CREATE UNIQUE INDEX `users_username` ON `users` (`username`);
CREATE UNIQUE INDEX `users_email` ON `users` (`email`);
sqlite> select * from SequelizeMeta;
2022.02.28T15.49.12.setup.ts

$ npm run migrate up

> migration@1.0.0 migrate

> ts-node migrate.ts "up"

Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)

Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)

Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;

{ event: 'migrating', name: '2022.02.28T15.49.12.setup.ts' }

Executing (86537b2f-f180-4b52-9afd-d2c1487f319b): BEGIN DEFERRED TRANSACTION;

Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `username` VARCHAR(32) NOT NULL, `email` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL);

Executing (default): CREATE UNIQUE INDEX `users_username` ON `users` (`username`)

Executing (default): CREATE UNIQUE INDEX `users_email` ON `users` (`email`)

Executing (86537b2f-f180-4b52-9afd-d2c1487f319b): COMMIT;

Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)

Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)

Executing (default): INSERT INTO `SequelizeMeta` (`name`) VALUES ($1);

{

event: 'migrated',

durationSeconds: 0.182

}

{ event: 'up', message: 'applied 1 migrations.' }

$ sqlite3 db.sqlite

SQLite version 3.28.0 2019-04-15 14:49:49

Enter ".help" for usage hints.

sqlite> .schema

CREATE TABLE `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

CREATE TABLE `users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `username` VARCHAR(32) NOT NULL, `email` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL);

CREATE TABLE sqlite_sequence(name,seq);

CREATE UNIQUE INDEX `users_username` ON `users` (`username`);

CREATE UNIQUE INDEX `users_email` ON `users` (`email`);

sqlite> select * from SequelizeMeta;

2022.02.28T15.49.12.setup.ts

無事にusersテーブルが作成されました。

マイグレーションファイルのOrder

辞書でソートされた順にファイルが読み込まれる。タイムスタンプをプリフィックスに元ファイル名にすると良いと思います。

m1.js、m2、…、m10.jsの場合、m1.js、m10.js、…となります。m2.jsよりm10.jsが先にくるということになります。

その他の使用方法

GitHubの公式のドキュメントを見ながら試してみましょう。マイグレーションフォルダには２つの定義を入れています。

$ ls  migrations/
2022.02.28T15.49.12.setup.ts                 2022.02.28T15.49.13.addColumnsToUserTable.ts

1 2	$ ls migrations/ 2022.02.28T15.49.12.setup.ts 2022.02.28T15.49.13.addColumnsToUserTable.ts

ペンディング中のマイグレーションの取得

pendingコマンドを実行します。

$ ./node_modules/.bin/ts-node migrate.ts pending
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;
2022.02.28T15.49.12.setup.ts
2022.02.28T15.49.13.addColumnsToUserTable.ts

$ ./node_modules/.bin/ts-node migrate.ts pending

Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)

Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)

Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;

2022.02.28T15.49.12.setup.ts

2022.02.28T15.49.13.addColumnsToUserTable.ts

未適用の2件が表示されます。

適用済みのマイグレーションの取得

executedコマンドの実行前にDBを空にして、upコマンド実行後に再度executedコマンドを実行してみます。

$ ./node_modules/.bin/ts-node migrate.ts executed
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;
$ ./node_modules/.bin/ts-node migrate.ts up
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;
{ event: 'migrating', name: '2022.02.28T15.49.12.setup.ts' }
Executing (9212aec3-4ede-4ea6-821d-ea73c72607dc): BEGIN DEFERRED TRANSACTION;
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `username` VARCHAR(32) NOT NULL, `email` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL);
Executing (default): CREATE UNIQUE INDEX `users_username` ON `users` (`username`)
Executing (default): CREATE UNIQUE INDEX `users_email` ON `users` (`email`)
Executing (9212aec3-4ede-4ea6-821d-ea73c72607dc): COMMIT;
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)
Executing (default): INSERT INTO `SequelizeMeta` (`name`) VALUES ($1);
{
event: 'migrated',
name: '2022.02.28T15.49.12.setup.ts',
durationSeconds: 0.173
}
{
event: 'migrating',
name: '2022.02.28T15.49.13.addColumnsToUserTable.ts'
}
Executing (97939936-9fef-43a4-823d-85bdde73aaa7): BEGIN DEFERRED TRANSACTION;
Executing (default): ALTER TABLE `users` ADD `status` INTEGER;
Executing (97939936-9fef-43a4-823d-85bdde73aaa7): COMMIT;
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)
Executing (default): INSERT INTO `SequelizeMeta` (`name`) VALUES ($1);
{
event: 'migrated',
name: '2022.02.28T15.49.13.addColumnsToUserTable.ts',
durationSeconds: 0.24
}
{ event: 'up', message: 'applied 2 migrations.' }
$ ./node_modules/.bin/ts-node migrate.ts executed
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;
2022.02.28T15.49.12.setup.ts
2022.02.28T15.49.13.addColumnsToUserTable.ts

$ ./node_modules/.bin/ts-node migrate.ts executed

Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)

Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)

Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;

$ ./node_modules/.bin/ts-node migrate.ts up

Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)

Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)

Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;

{ event: 'migrating', name: '2022.02.28T15.49.12.setup.ts' }

Executing (9212aec3-4ede-4ea6-821d-ea73c72607dc): BEGIN DEFERRED TRANSACTION;

Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER PRIMARY KEY AUTOINCREMENT, `username` VARCHAR(32) NOT NULL, `email` VARCHAR(255) NOT NULL, `password` VARCHAR(255) NOT NULL);

Executing (default): CREATE UNIQUE INDEX `users_username` ON `users` (`username`)

Executing (default): CREATE UNIQUE INDEX `users_email` ON `users` (`email`)

Executing (9212aec3-4ede-4ea6-821d-ea73c72607dc): COMMIT;

Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)

Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)

Executing (default): INSERT INTO `SequelizeMeta` (`name`) VALUES ($1);

{

event: 'migrated',

durationSeconds: 0.173

}

{

event: 'migrating',

}

Executing (97939936-9fef-43a4-823d-85bdde73aaa7): BEGIN DEFERRED TRANSACTION;

Executing (default): ALTER TABLE `users` ADD `status` INTEGER;

Executing (97939936-9fef-43a4-823d-85bdde73aaa7): COMMIT;

Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)

Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)

Executing (default): INSERT INTO `SequelizeMeta` (`name`) VALUES ($1);

{

event: 'migrated',

durationSeconds: 0.24

}

{ event: 'up', message: 'applied 2 migrations.' }

$ ./node_modules/.bin/ts-node migrate.ts executed

Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)

Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)

Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;

2022.02.28T15.49.12.setup.ts

2022.02.28T15.49.13.addColumnsToUserTable.ts

マイグレーション実行後に2件の適用済みリストが表示されます。

マイグレーションの巻き戻し

最後に適用したマイグレーションを巻き戻してみます。

$ ./node_modules/.bin/ts-node migrate.ts down
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)
Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;
{
event: 'reverting',
name: '2022.02.28T15.49.13.addColumnsToUserTable.ts'
}
Executing (e684de26-fa9d-4912-83a2-ed2b90f43e4f): BEGIN DEFERRED TRANSACTION;
Executing (default): PRAGMA TABLE_INFO(`users`);
Executing (default): PRAGMA INDEX_LIST(`users`)
Executing (default): PRAGMA INDEX_INFO(`users_username`)
Executing (default): PRAGMA INDEX_INFO(`users_email`)
Executing (default): PRAGMA foreign_key_list(`users`)
Executing (default): CREATE TABLE IF NOT EXISTS `users_backup` (`id` INTEGER PRIMARY KEY, `username` VARCHAR(32) NOT NULL UNIQUE, `email` VARCHAR(255) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL);
Executing (default): INSERT INTO `users_backup` SELECT `id`, `username`, `email`, `password` FROM `users`;
Executing (default): DROP TABLE `users`;
Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER PRIMARY KEY, `username` VARCHAR(32) NOT NULL UNIQUE, `email` VARCHAR(255) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL);
Executing (default): INSERT INTO `users` SELECT `id`, `username`, `email`, `password` FROM `users_backup`;
Executing (default): DROP TABLE `users_backup`;
Executing (e684de26-fa9d-4912-83a2-ed2b90f43e4f): COMMIT;
Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);
Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)
Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)
Executing (default): DELETE FROM `SequelizeMeta` WHERE `name` = '2022.02.28T15.49.12.addColumnsToUserTable.ts'
{
event: 'reverted',
name: '2022.02.28T15.49.13.addColumnsToUserTable.ts',
durationSeconds: 0.143
}
{ event: 'down', message: 'reverted 1 migrations.' }

$ ./node_modules/.bin/ts-node migrate.ts down

Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)

Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)

Executing (default): SELECT `name` FROM `SequelizeMeta` AS `SequelizeMeta` ORDER BY `SequelizeMeta`.`name` ASC;

{

event: 'reverting',

}

Executing (e684de26-fa9d-4912-83a2-ed2b90f43e4f): BEGIN DEFERRED TRANSACTION;

Executing (default): PRAGMA TABLE_INFO(`users`);

Executing (default): PRAGMA INDEX_LIST(`users`)

Executing (default): PRAGMA INDEX_INFO(`users_username`)

Executing (default): PRAGMA INDEX_INFO(`users_email`)

Executing (default): PRAGMA foreign_key_list(`users`)

Executing (default): CREATE TABLE IF NOT EXISTS `users_backup` (`id` INTEGER PRIMARY KEY, `username` VARCHAR(32) NOT NULL UNIQUE, `email` VARCHAR(255) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL);

Executing (default): INSERT INTO `users_backup` SELECT `id`, `username`, `email`, `password` FROM `users`;

Executing (default): DROP TABLE `users`;

Executing (default): CREATE TABLE IF NOT EXISTS `users` (`id` INTEGER PRIMARY KEY, `username` VARCHAR(32) NOT NULL UNIQUE, `email` VARCHAR(255) NOT NULL UNIQUE, `password` VARCHAR(255) NOT NULL);

Executing (default): INSERT INTO `users` SELECT `id`, `username`, `email`, `password` FROM `users_backup`;

Executing (default): DROP TABLE `users_backup`;

Executing (e684de26-fa9d-4912-83a2-ed2b90f43e4f): COMMIT;

Executing (default): CREATE TABLE IF NOT EXISTS `SequelizeMeta` (`name` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY);

Executing (default): PRAGMA INDEX_LIST(`SequelizeMeta`)

Executing (default): PRAGMA INDEX_INFO(`sqlite_autoindex_SequelizeMeta_1`)

Executing (default): DELETE FROM `SequelizeMeta` WHERE `name` = '2022.02.28T15.49.12.addColumnsToUserTable.ts'

{

event: 'reverted',

durationSeconds: 0.143

}

{ event: 'down', message: 'reverted 1 migrations.' }

最後に適用したaddColumnsToUserTableが打ち消されます。

まとめ

umzugを使うと、Sequelizeを使ったDBマイグレーションのプログラムを独自に実装できます。
umzug#runAsCLI()を使うと、コマンドラインのマイグレーションプログラムを容易に実行することができます。

参考リンク

Sequelize ORM
- https://sequelize.org
umzug
- https://github.com/sequelize/umzug
sequelize/cli
- https://github.com/sequelize/cli
- https://sequelize.org/master/manual/migrations.html