ORM采用sequelize
迁移采用umzug
数据库基于mysql
不说废话，上干货。
# 安装依赖
$ yarn add @nestjs/sequelize sequelize sequelize-typescript mysql2
$ yarn add umzug -D
$ yarn add @types/sequelize -D
$ yarn add cross-env -D
# 创建迁移文件夹
$ mkdir database
数据库迁移配置
- 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": {
...
}
}
执行文件
使用ts-node执行ts脚本
- database/migrator.js
require('ts-node/register');
// eslint-disable-next-line @typescript-eslint/no-var-requires
require('./umzug').migrator.runAsCLI();
- database/migrator-seed.js
require('ts-node/register');
// eslint-disable-next-line @typescript-eslint/no-var-requires
require('./umzug-seed').migrator.runAsCLI();
- database/umzug.ts
import { Umzug, SequelizeStorage } from 'umzug';
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/umzug-seed.ts
import { Sequelize } from 'sequelize';
import { Umzug, SequelizeStorage } from 'umzug';
// 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;
- 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 defaultCloumns from '../utils/default-column';
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/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 = 创建
{
"script": {
"umzug:h": "cross-env NODE_ENV=local node database/migrator.js -h",
"umzug:u": "cross-env NODE_ENV=local node database/migrator.js up",
"umzug:d": "cross-env NODE_ENV=local node database/migrator.js down",
"umzug:c": "cross-env NODE_ENV=local node database/migrator.js create",
"seed:h": "cross-env NODE_ENV=local node database/migrator-seed.js -h",
"seed:u": "cross-env NODE_ENV=local node database/migrator-seed.js up",
"seed:d": "cross-env NODE_ENV=local node database/migrator-seed.js down",
"seed:c": "cross-env NODE_ENV=local node database/migrator-seed.js create",
"umzug-dev:u": "cross-env NODE_ENV=development node database/migrator.js up",
"umzug-dev:d": "cross-env NODE_ENV=development node database/migrator.js down",
"umzug-prod:u": "cross-env NODE_ENV=production node database/migrator.js up",
"umzug-prod:d": "cross-env NODE_ENV=production node database/migrator.js down",
"seed-prod:u": "cross-env NODE_ENV=production node database/migrator-seed.js up",
"seed-prod:d": "cross-env NODE_ENV=production node database/migrator-seed.js down"
}
}
创建命令
$ yarn umzug:c --name user.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
......
网友评论