本次升级，主要应用场景，用于采用.env配置方案，自动执行数据脚本迁移时候的应用。
ORM采用sequelize
迁移采用umzug
数据库基于mysql
不说废话，上干货。

# 安装依赖 生产环境需要自动运行所以 不再是 --save-dev安装
$ yarn add @nestjs/sequelize sequelize sequelize-typescript mysql2
$ yarn add umzug
$ yarn add @types/sequelize
$ yarn add cross-env
# 创建迁移文件夹
$ mkdir database

数据库迁移配置 跟随.env或者 nacos等动态配置即可不需要设置config.json、config.js 可以根据实际情况改造

• database/config.json
  以下代码保留仅供参考，已经没有实际运行意义，项目已经删除此文件了。

{
  "local": {
    "port": 3306,
    "host": "127.0.0.1",
    "database": "xxxxx",
    "username": "root",
    "password": "xxxx",
    "dialect": "mysql",
    "define": {
      "charset": "utf8"
    },
    "logging": false
  },
  "development": {
    ...
  },
  "stage": {
    ...
  },
  "production": {
    ...
  },
  "test": {
    ...
  }
}

执行文件

升级umzug.ts文件进行直接运行的执行方式

• database/umzug.ts

// 修正读取 .env文件

import { Umzug, SequelizeStorage, UmzugOptions } from 'umzug'
import { Dialect, Sequelize } from 'sequelize'
// config 加载
// import config from './config.json'
import { snakeCase, replace, get } from 'lodash'
import path from 'path'
import fs from 'fs'
import * as dotenv from 'dotenv'
dotenv.config()

// #region args
/**
 * up:MigrateUpOptions
 * down:MigrateUpOptions
 * create: {
        name: string;
        folder?: string;
        prefix?: 'TIMESTAMP' | 'DATE' | 'NONE';
        allowExtension?: string;
        allowConfusingOrdering?: boolean;
        skipVerify?: boolean;
    }
 */
const cliValue = ['up', 'down', 'create']

const args = process.argv.slice(2) // 从第三个元素开始是传递的参数
const namedArgs: {
  type: 'ts' | 'js'
  cli: (typeof cliValue)[number]
  temp: 'table' | 'seed'
} = { type: 'js' } as any

// 参数处理
for (let i = 0; i < args.length; i++) {
  const arg = args[i]

  const [name, value] = arg.split('=')
  // // 判断参数是否重复
  // if ((namedArgs as any)[name]) {
  //   console.error(`Error: Duplicate parameter found: ${name}`)
  //   process.exit(1)
  // }

  ;(namedArgs as any)[name] = value || true
}

// 判断是否包含 type 参数
if (!namedArgs.type || !['ts', 'js'].includes(namedArgs.type)) {
  console.error('Error: Missing required parameter: type, and type must `ts` or `js` ')
  process.exit(1)
}
// 判断是否包含 type 参数
if (!namedArgs.cli || !cliValue.includes(namedArgs.cli)) {
  console.log(namedArgs)
  console.error('Error: Missing required parameter: cli ')
  process.exit(1)
}
// #endregion

/**
 * 配置文件读取
 */
const config = {
  port: process.env.MYSQL_PORT_ADMIN as unknown as number,
  host: process.env.MYSQL_HOST_ADMIN,
  database: process.env.MYSQL_DATABASE_ADMIN,
  username: process.env.MYSQL_USERNAME_ADMIN,
  password: process.env.MYSQL_PASSWORD_ADMIN,
  dialect: 'mysql' as Dialect,
  define: {
    charset: 'utf8',
  },
  logging: false,
}

const rtlEnv = process.env.NODE_ENV
const sequelizeConfig = config
console.log(`[umzug]:db:${sequelizeConfig.database}`)
const sequelize = new Sequelize(sequelizeConfig)

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

const migrationsConfig: UmzugOptions<Sequelize> = {
  migrations: {
    glob: [`migrations/*.${namedArgs.type || 'ts'}`, { cwd: __dirname }],
  },
  create: {
    template: (filepath: string) => [[filepath, findTemplate(filepath)]],
    folder: path.join('database/migrations/'),
  },
  context: sequelize,
  storage: new SequelizeStorage({
    sequelize,
    tableName: process.env.UMZUG_TABLE_NAME || 'sequelize_umzug',
  }),
  logger: console,
}

const umzug: any = new Umzug(migrationsConfig)

umzug[namedArgs.cli]?.(namedArgs)?.then(() => {
  console.log(`Umzug success!`)
  process.exit(0)
})

• database/utils/default-column.ts
  默认列 本地开发主外键设计

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/table.ts

import { MigrationFn } from 'umzug';
import { Sequelize } from 'sequelize';
// import { DataTypes } from 'sequelize';
import defaultColumns from '../utils/default-column';

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

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

• database/template/seed.ts

/* eslint-disable @typescript-eslint/no-unused-vars */
import { MigrationFn } from 'umzug';
import { Sequelize } from '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: [],
    },
  });
};

命令

umzug 结构迁移
seed 数据迁移
-h = 帮助
-up = 升级
-down = 降级
-create = 创建
type: ts、js
cli: up、down、create
temp: table、seed 对应/database/template/.ts
其他参数 直接传递
cli=create 之后的参数直接传递
/*

• up:MigrateUpOptions
• down:MigrateUpOptions
• create: name: string;
  folder?: string;
  prefix?: 'TIMESTAMP' | 'DATE' | 'NONE';
  allowExtension?: string;
  allowConfusingOrdering?: boolean;
  skipVerify?: boolean;
  */

$ npm run umzug -- type=ts cli=create name=user.ts
$ npm run umzug -- type=ts cli=up   
$ npm run umzug -- type=ts cli=down

{
  "script": {
        "umzug-prd": " node lib/database/umzug.js -- type=js cli=up",
        "umzug": " ts-node database/umzug.ts"
  }
}

error

yarn run v1.22.19
$ cross-env NODE_ENV=local node database/migrator.js create --name user.ts
/Users/wuzhanchao/Documents/万达项目组/好房推荐官/source/wd-nest-manager/node_modules/ts-node/src/index.ts:859
    return new TSError(diagnosticText, diagnosticCodes, diagnostics);
           ^
TSError: ⨯ Unable to compile TypeScript
TSError: ⨯ Unable to compile TypeScript:
database/umzug.ts:4:20 - error TS2732: Cannot find module './config.json'. Consider using '--resolveJsonModule' to import module with '.json' extension.

4 import config from './config.json';

tsconfig.json
增加配置项

{
  "compilerOptions": {
    "resolveJsonModule": true
    "esModuleInterop": true
    ......