本次升級，主要應(yīng)用場景，用于采用.env配置方案，自動執(zhí)行數(shù)據(jù)腳本遷移時候的應(yīng)用。
ORM采用sequelize
遷移采用umzug
數(shù)據(jù)庫基于mysql
不說廢話，上干貨。
# 安裝依賴 生產(chǎn)環(huán)境需要自動運行所以 不再是 --save-dev安裝
$ yarn add @nestjs/sequelize sequelize sequelize-typescript mysql2
$ yarn add umzug
$ yarn add @types/sequelize
$ yarn add cross-env
# 創(chuàng)建遷移文件夾
$ mkdir database
數(shù)據(jù)庫遷移配置 跟隨.env或者 nacos等動態(tài)配置即可不需要設(shè)置config.json、config.js 可以根據(jù)實際情況改造
- database/config.json
以下代碼保留僅供參考，已經(jīng)沒有實際運行意義，項目已經(jīng)刪除此文件了。
{
"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": {
...
}
}
執(zhí)行文件
升級umzug.ts文件進行直接運行的執(zhí)行方式
- 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) // 從第三個元素開始是傳遞的參數(shù)
const namedArgs: {
type: 'ts' | 'js'
cli: (typeof cliValue)[number]
temp: 'table' | 'seed'
} = { type: 'js' } as any
// 參數(shù)處理
for (let i = 0; i < args.length; i++) {
const arg = args[i]
const [name, value] = arg.split('=')
// // 判斷參數(shù)是否重復(fù)
// if ((namedArgs as any)[name]) {
// console.error(`Error: Duplicate parameter found: ${name}`)
// process.exit(1)
// }
;(namedArgs as any)[name] = value || true
}
// 判斷是否包含 type 參數(shù)
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 參數(shù)
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)
/**
* 默認(rèn)模版替換
* @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
默認(rèn)列 本地開發(fā)主外鍵設(shè)計
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: '創(chuàng)建時間',
},
created_id: {
type: STRING(50),
defaultValue: '',
comment: '創(chuàng)建人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: '業(yè)務(wù)編碼權(quán)限用',
},
remark: {
type: STRING(500),
comment: '備注',
},
version: {
type: INTEGER,
comment: 'BaseTable.version',
},
enable_flag: {
type: INTEGER,
comment: '狀態(tài) 1啟用 0停用默認(rèn)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 結(jié)構(gòu)遷移
seed 數(shù)據(jù)遷移
-h = 幫助
-up = 升級
-down = 降級
-create = 創(chuàng)建
type: ts、js
cli: up、down、create
temp: table、seed 對應(yīng)/database/template/.ts
其他參數(shù) 直接傳遞
cli=create 之后的參數(shù)直接傳遞
/*
- 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/萬達(dá)項目組/好房推薦官/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
......
