前言

sequelize-v6 版本是本人非常喜欢的ORM方案.
https://sequelize.org/v6/
借鉴数据迁移方案官方提供的方案使用umzug进行处理利用ts的特点降低开发难度!
https://github.com/sequelize/umzug
本次框架使用midway-v2
https://www.yuque.com/midwayjs/midway_v2/introduction
感谢🙇

使用方案 seed 与 db相同

• 创建db model

# 创建
$ yarn umzug:c --name app-user.ts
# 迁移
$ yarn umzug:u [--name]
# 回滚
$ yarn umzug:d [--name]

• package

# sequelize
$ yarn add @midwayjs/sequelize sequelize sequelize-typescript mysql2 -S 
# umzug
$ yarn add umzug -D

• 引入模块
  在 文件中

import { App, Configuration } from '@midwayjs/decorator';
...
import * as sequlize from '@midwayjs/sequelize';

@Configuration({
  imports: [sequlize],
  importConfigs: [join(__dirname, './config')],
})
export class ContainerLifeCycle implements ILifeCycle {
  @App()
  app: Application;

  async onReady() {
    
  }
}

• 模板代码
  package.json

{
  "scripts": {
    "umzug:h": "node database/migrator.js -h",
    "umzug:u": "node database/migrator.js up",
    "umzug:d": "node database/migrator.js down",
    "umzug:c": "node database/migrator.js create",
    "seed:h": "node database/migrator-seed.js -h",
    "seed:u": "node database/migrator-seed.js up",
    "seed:d": "node database/migrator-seed.js down",
    "seed:c": "node database/migrator-seed.js create",
  }
}

database/migrator.js

// eslint-disable-next-line node/no-unpublished-require
require('ts-node/register');

require('./umzug').migrator.runAsCLI();

database/migrator-seed.js

// eslint-disable-next-line node/no-unpublished-require
require('ts-node/register');

require('./umzug-seed').migrator.runAsCLI();

database/config.json
数据库配置

{
  "development": {
    "port": 53306,
    "host": "rm-xxx.mysql.zhangbei.rds.aliyuncs.com",
    "database": "xxx_dev",
    "username": "root",
    "password": "xxx",
    "dialect": "mysql",
    "define": {
      "charset": "utf8"
    },
    "logging": false
  },
  "dev": {
    "port": 53306,
    "host": "rm-xxx.mysql.zhangbei.rds.aliyuncs.com",
    "database": "xxx_stage",
    "username": "root",
    "password": "xxx",
    "dialect": "mysql",
    "define": {
      "charset": "utf8"
    },
    "logging": false
  },
  "pre": {
    "port": 53306,
    "host": "rm-xxx.mysql.zhangbei.rds.aliyuncs.com",
    "database": "xxx_release",
    "username": "root",
    "password": "xxx",
    "dialect": "mysql",
    "define": {
      "charset": "utf8"
    }
  },
  "production": {
    "port": 53306,
    "host": "rm-xxx.mysql.zhangbei.rds.aliyuncs.com",
    "database": "xxx_prd",
    "username": "root",
    "password": "xxx",
    "dialect": "mysql",
    "define": {
      "charset": "utf8"
    }
  },
  "test": {
    "username": "root",
    "password": null,
    "database": "egg-sequelize-doc-unittest",
    "host": "127.0.0.1",
    "dialect": "mysql"
  }
}

database/umzug-seed.ts

import { Umzug, SequelizeStorage } from 'umzug';
// eslint-disable-next-line node/no-unpublished-import
import { Sequelize } from 'sequelize';
// config 加载
import config from './config.json';
import { get, snakeCase, replace } from 'lodash';
import path from 'path';
import fs from 'fs';

const rtlEnv = process.env.NODE_ENV;
const sequelizeConfig: any = get(config, rtlEnv || 'development');
console.log(`[umzug]:db:${sequelizeConfig.database}`);
const sequelize = new Sequelize(sequelizeConfig);

/**
 * 默认模版替换
 * @param filepath 路径
 * @returns
 */
const findTemplate = (filepath: string) => {
  const temp = fs
    .readFileSync(path.join('database/template/seed.ts'))
    .toString();
  const names = filepath.split('.');
  const name = snakeCase(names[names.length - 2]);
  return replace(temp, /\[tableName\]/g, name);
};

export const migrator = new Umzug({
  migrations: {
    glob: ['seeds/*.ts', { cwd: __dirname }],
  },
  create: {
    template: filepath => [[filepath, findTemplate(filepath)]],
    folder: path.join('database/seeds/'),
  },
  context: sequelize,
  storage: new SequelizeStorage({
    sequelize,
    modelName: 'SequelizeData',
  }),
  logger: console,
});

export type Migration = typeof migrator._types.migration;

databse/umzug.ts

import { Umzug, SequelizeStorage } from 'umzug';
// eslint-disable-next-line node/no-unpublished-import
import { Sequelize } from 'sequelize';
// config 加载
import config from './config.json';
import { get, snakeCase, replace } from 'lodash';
import path from 'path';
import fs from 'fs';

const rtlEnv = process.env.NODE_ENV;
const sequelizeConfig: any = get(config, rtlEnv || 'development');
console.log(`[umzug]:db:${sequelizeConfig.database}`);
const sequelize = new Sequelize(sequelizeConfig);

/**
 * 默认模版替换
 * @param filepath 路径
 * @returns
 */
const findTemplate = (filepath: string) => {
  const temp = fs
    .readFileSync(path.join('database/template/table.ts'))
    .toString();
  const names = filepath.split('.');
  const name = snakeCase(names[names.length - 2]);
  return replace(temp, /\[tableName\]/g, name);
};

export const migrator = new Umzug({
  migrations: {
    glob: ['migrations/*.ts', { cwd: __dirname }],
  },
  create: {
    template: filepath => [[filepath, findTemplate(filepath)]],
    folder: path.join('database/migrations/'),
  },
  context: sequelize,
  storage: new SequelizeStorage({
    sequelize,
  }),
  logger: console,
});

export type Migration = typeof migrator._types.migration;

database/utils/default-columns.ts

// eslint-disable-next-line node/no-unpublished-import
import Sequelize from 'sequelize';

const { DATE, STRING, INTEGER } = Sequelize;

export default {
  id: { type: STRING(50), primaryKey: true },
  // Creating two objects with the same value will throw an error. The unique property can be either a
  // boolean, or a string. If you provide the same string for multiple columns, they will form a
  created_at: {
    type: DATE,
    defaultValue: Sequelize.fn('now'),
    comment: '创建时间',
  },
  created_id: {
    type: STRING(50),
    defaultValue: '',
    comment: '创建人id',
  },
  updated_at: {
    type: DATE,
    defaultValue: Sequelize.fn('now'),
    comment: '修改时间',
  },
  updated_id: {
    type: STRING(50),
    comment: '修改人id',
  },
  deleted_at: { type: DATE, comment: '删除时间' },
  deleted_id: {
    type: STRING(50),
    comment: '删除人id',
  },
  business_code: {
    type: STRING(500),
    comment: '业务编码权限用',
  },
  remark: {
    type: STRING(500),
    comment: '备注',
  },
  version: {
    type: INTEGER,
    comment: 'BaseTable.version',
  },
  enable_flag: {
    type: INTEGER,
    comment: '状态 1启用 0停用默认1',
    defaultValue: 1,
  },
};

export const references = (tableName: string, keyName = 'id') => {
  if (process.env.NODE_ENV === 'production') {
    return undefined;
  }
  return {
    model: {
      tableName,
    },
    keyName,
  };
};

database/template/seed.ts

/* eslint-disable @typescript-eslint/no-unused-vars */
/* eslint-disable node/no-unpublished-import */
import { MigrationFn } from 'umzug';
import { Sequelize } from 'sequelize/types/lib/sequelize';
// import { DataTypes } from 'sequelize';

export const up: MigrationFn<Sequelize> = async ({ context: sequelize }) => {
  return await sequelize.getQueryInterface().bulkInsert('[tableName]', [{}]);
};

export const down: MigrationFn<Sequelize> = async ({ context: sequelize }) => {
  return await sequelize.getQueryInterface().bulkDelete('[tableName]', {
    where: {
      id: [],
    },
  });
};

database/template/table.ts

/* eslint-disable @typescript-eslint/no-unused-vars */
/* eslint-disable node/no-unpublished-import */
import { MigrationFn } from 'umzug';
import { Sequelize } from 'sequelize/types/lib/sequelize';
// import { DataTypes } from 'sequelize';
import defaultCloumns from '../utils/default-cloumns';

export const up: MigrationFn<Sequelize> = async ({ context: sequelize }) => {
  return await sequelize.getQueryInterface().createTable('[tableName]', {
    ...defaultCloumns,
  });
};

export const down: MigrationFn<Sequelize> = async ({ context: sequelize }) => {
  return await sequelize.getQueryInterface().dropTable('[tableName]');
};

database/migrations -- model first
database/seeds -- 种子迁移