mirror of https://github.com/certd/certd
126 lines
5.9 KiB
Markdown
126 lines
5.9 KiB
Markdown
|
# midway-flyway-js
|
|||
|
|
|
|||
|
|
[English](./README.md) | [简体中文](./README_zhCN.md)
|
|||
|
|
|
|||
|
|
|
|||
|
|
`midway-flyway-js`是基于typeorm的flyway的js实现。
|
|||
|
|
本项目被构建为midway组件，可与midway无缝集成。
|
|||
|
|
|
|||
|
|
# flyway
|
|||
|
|
flyway是一款java版本的数据库升级迁移解决方案。
|
|||
|
|
它能在server启动时自动检查脚本目录，执行sql升级脚本，记录执行历史。
|
|||
|
|
|
|||
|
|
本项目根据类似flyway的思路实现数据库升级迁移方案
|
|||
|
|
|
|||
|
|
# 快速开始
|
|||
|
|
|
|||
|
|
## 1. 准备
|
|||
|
|
* nodejs环境
|
|||
|
|
* midway项目
|
|||
|
|
* [配置typeorm](https://www.yuque.com/midwayjs/midway_v2/orm)
|
|||
|
|
|
|||
|
|
## 2. 安装
|
|||
|
|
```
|
|||
|
|
npm install midway-flyway-js
|
|||
|
|
# or
|
|||
|
|
yarn add midway-flyway-js
|
|||
|
|
```
|
|||
|
|
## 3. 集成
|
|||
|
|
```js
|
|||
|
|
import * as orm from 'typeorm';
|
|||
|
|
import * as flyway from 'midway-flyway-js';
|
|||
|
|
@Configuration({
|
|||
|
|
imports: [
|
|||
|
|
orm, // 加载 orm 组件
|
|||
|
|
flyway, //加载flyway组件
|
|||
|
|
],
|
|||
|
|
})
|
|||
|
|
export class ContainerConfiguration {}
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
|
|||
|
|
## 4. 配置参数【可选】
|
|||
|
|
`/src/config/config.default.js`文件
|
|||
|
|
```js
|
|||
|
|
export const flyway ={
|
|||
|
|
// 脚本目录
|
|||
|
|
// 默认值 "./db/migrition"
|
|||
|
|
scriptDir:"./db/migrition",
|
|||
|
|
// 基线，基线脚本及之前的脚本都跳过不执行
|
|||
|
|
// 默认值：null
|
|||
|
|
// 如果你原本就是空数据库，那么不需要配置此项
|
|||
|
|
baseline: 'v1__init.sql',
|
|||
|
|
// 执行记录表名
|
|||
|
|
// 默认值 flyway_history
|
|||
|
|
flywayTableName:'flyway_history',
|
|||
|
|
// 是否允许hash值不同
|
|||
|
|
// 默认值：false
|
|||
|
|
// 相同名称sql文件被改动后，hash会变化
|
|||
|
|
// 此时运行会报hash conflict错误
|
|||
|
|
// 配置此参数为true，将忽略hash conflict错误
|
|||
|
|
allowHashNotMatch:false
|
|||
|
|
}
|
|||
|
|
|
|||
|
|
```
|
|||
|
|
## 5. 编写升级sql
|
|||
|
|
|
|||
|
|
将你的sql升级脚本，放到 `/src/db/migrition`目录下
|
|||
|
|
|
|||
|
|
建议命名规则`v{version}__{name}.sql`,例如`v1__init.sql`
|
|||
|
|
|
|||
|
|
|
|||
|
|
## 6. 启动你的midway服务
|
|||
|
|
```
|
|||
|
|
npm run dev
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
## 7. 运行效果
|
|||
|
|
以下效果为midway自动启动后，自动执行`v1__init.sql`脚本的记录
|
|||
|
|
```
|
|||
|
|
2021-06-26 15:45:39,630 INFO 12245 [ midfly ] start-------------
|
|||
|
|
query: SELECT * FROM "sqlite_master" WHERE "type" = 'table' AND "name" = 'flyway_history'
|
|||
|
|
query: CREATE TABLE "flyway_history" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "timestamp" bigint NOT NULL, "name" varchar NOT NULL, "hash" varchar, "success" boolean)
|
|||
|
|
query: BEGIN TRANSACTION
|
|||
|
|
query: SELECT "FlywayHistory"."id" AS "FlywayHistory_id", "FlywayHistory"."name" AS "FlywayHistory_name", "FlywayHistory"."hash" AS "FlywayHistory_hash", "FlywayHistory"."timestamp" AS "FlywayHistory_timestamp", "FlywayHistory"."success" AS "FlywayHistory_success" FROM "flyway_history" "FlywayHistory" WHERE "FlywayHistory"."name" = ? AND "FlywayHistory"."success" = ? LIMIT 1 -- PARAMETERS: ["v1__init.sql",1]
|
|||
|
|
2021-06-26 15:45:39,664 INFO 12245 need exec script file:
|
|||
|
|
2021-06-26 15:45:39,666 INFO 12245 [ midfly ] exec
|
|||
|
|
query: -- 表：sys_permission
|
|||
|
|
CREATE TABLE "sys_permission" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "title" varchar(100) NOT NULL, "permission" varchar(100), "parent_id" integer NOT NULL DEFAULT (-1), "sort" integer NOT NULL, "create_time" datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP), "update_time" datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP));
|
|||
|
|
query: INSERT INTO sys_permission (id, title, permission, parent_id, sort, create_time, update_time) VALUES (1, '系统管理', 'sys', -1, 1, 1, 1624085863636);
|
|||
|
|
query: -- 表：sys_role
|
|||
|
|
CREATE TABLE "sys_role" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "name" varchar(100) NOT NULL, "create_time" datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP), "update_time" datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP));
|
|||
|
|
query: INSERT INTO sys_role (id, name, create_time, update_time) VALUES (1, '管理员', 1, 1623749138537);
|
|||
|
|
query: -- 表：sys_role_permission
|
|||
|
|
CREATE TABLE "sys_role_permission" ("role_id" integer NOT NULL, "permission_id" integer NOT NULL, PRIMARY KEY ("role_id", "permission_id"));
|
|||
|
|
query: INSERT INTO sys_role_permission (role_id, permission_id) VALUES (1, 1);
|
|||
|
|
query: -- 表：sys_user
|
|||
|
|
CREATE TABLE "sys_user" ("id" integer PRIMARY KEY AUTOINCREMENT NOT NULL, "username" varchar(100) NOT NULL, "password" varchar(50) NOT NULL, "nick_name" varchar(50), "avatar" varchar(255), "phone_code" varchar(20), "mobile" varchar(20), "email" varchar(100),"remark" varchar(100), "status" integer NOT NULL DEFAULT (1), "create_time" datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP), "update_time" datetime NOT NULL DEFAULT (CURRENT_TIMESTAMP));
|
|||
|
|
query: INSERT INTO sys_user (id, username, password, nick_name, avatar, phone_code, mobile, email, status, create_time, update_time,remark) VALUES (1, 'admin', 'e10adc3949ba59abbe56e057f20f883e', 'admin', NULL, NULL, NULL, NULL, 1, 2011123132, 123132,NULL);
|
|||
|
|
query: -- 表：sys_user_role
|
|||
|
|
CREATE TABLE "sys_user_role" ("role_id" integer NOT NULL, "user_id" integer NOT NULL, PRIMARY KEY ("role_id", "user_id"));
|
|||
|
|
query: INSERT INTO sys_user_role (role_id, user_id) VALUES (1, 1);
|
|||
|
|
query: -- 索引：IDX_223de54d6badbe43a5490450c3
|
|||
|
|
CREATE UNIQUE INDEX "IDX_223de54d6badbe43a5490450c3" ON "sys_role" ("name");
|
|||
|
|
query: -- 索引：IDX_9e7164b2f1ea1348bc0eb0a7da
|
|||
|
|
CREATE UNIQUE INDEX "IDX_9e7164b2f1ea1348bc0eb0a7da" ON "sys_user" ("username");
|
|||
|
|
query: DELETE FROM "flyway_history" WHERE "name" = ? -- PARAMETERS: ["v1__init.sql"]
|
|||
|
|
query: INSERT INTO "flyway_history"("id", "name", "hash", "timestamp", "success") VALUES (NULL, ?, ?, ?, ?) -- PARAMETERS: ["v1__init.sql","0c661bd7afebac224bbaa60bc5bb56e9",1624693539781,1]
|
|||
|
|
query: SELECT "FlywayHistory"."id" AS "FlywayHistory_id", "FlywayHistory"."success" AS "FlywayHistory_success" FROM "flyway_history" "FlywayHistory" WHERE "FlywayHistory"."id" = ? -- PARAMETERS: [1]
|
|||
|
|
query: COMMIT
|
|||
|
|
2021-06-26 15:45:39,800 INFO 12245 [ midfly ] end-------------
|
|||
|
|
```
|
|||
|
|
|
|||
|
|
# 注意事项
|
|||
|
|
1. 升级sql文件最后一行请不要有注释，应该以一条sql语句的分号结尾。
|
|||
|
|
|
|||
|
|
# 他们在用
|
|||
|
|
* [fs-server-js](https://github.com/fast-crud/fs-server-js)
|
|||
|
|
|
|||
|
|
# 参考项目
|
|||
|
|
* [flyway](https://github.com/flyway/flyway) : java版flyway
|
|||
|
|
* [flyway-js](https://github.com/wanglihui/flyway-js) : Sequelize版flyway
|
|||
|
|
|
|||
|
|
感谢以上项目
|
|||
|
|
|
|||
|
|
|