前言
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 -- 种子迁移
