Migration 数据库操作管理工具

浅浅的花香味﹌ 2024-03-24 12:28 47阅读 0赞

## 什么是 Migration？ ##

migration 用谷歌翻译是移民的意思，将 Migration 用在数据库上就理解为数据库迁移咯。在migration开发之前，我们都是手写SQL创建表语句，创建成功之后需要手动在数据库执行，项目初始化光数据库的创建就花费很多时间，

另外在多人团队开发中，如果要求每个开发人员都在本地使用数据库那么我们通常都是将数据库备份成SQL文件互相传递，这还并不是很繁琐，繁琐的在于如果数据库某个表的字段出现变动那么就需要将这个变动的SQL语句传给每个开发小伙伴让他们在本地都手动的更新下，一次可以这样，但是多次呢？

一个 migration 对应一次独立的数据库变更操作，可以兼容 postgres 和 mysql

migration 一旦建立好,就不要修改! 不要删除! 要修改数据库,就建立新的migration

每个文件名都由两部分组成: 时间戳 + 事件.

1642134998494-create_table_xxxxx
    1644561349279-change_xxx_table_type_default_value
    1645423100786-add_columns_into_xxxxx_table

所以，当团队中，任意一个新手，加入的话，不需要你提供给他任何sql文件。让他直接运行 $ rake db:migrate就可以了。

如果一个项目，没有migration的话，这个项目就特别难于开发，原因在于：

*  数据库结构难以获取
 *  开发成员之间的表结构难以统一

## 创建表 ##

const TABLE_NAME = 'xxxx'
    export class createTableXXX1642000000000 implements MigrationInterface {
        
        public async up(queryRunner: QueryRunner): Promise<void> {
        
            const exist = await isTableExist(TABLE_NAME, queryRunner);
            if (exist) {
        
                return
            }
            await queryRunner.createTable(
                new Table({
        
                    name: TABLE_NAME,
                    columns: [
                        ID,
                        {
        
                            name: 'createdAt',
                            type: 'bigint',
                            comment: '创建时间',
                            default: Date.now(),
                            ...NOT_NULL,
                        },
                        {
        
                            name: 'updatedAt',
                            type: 'bigint',
                            comment: '创建时间',
                            default: Date.now(),
                            ...NOT_NULL,
                        }, {
        
                            name: 'version',
                            length: '32',
                            ...VARCHAR,
                            ...NOT_NULL
                        }]
                }))
        }
    
        public async down(queryRunner: QueryRunner): Promise<void> {
        
            await queryRunner.dropTable(TABLE_NAME);
        }
    
    }

## 新增表字段 ##

export class addColumnXXXXX1657526300000
      implements MigrationInterface {
        
      TABLE_NAME = 'xxxxx';
      COLUMN_NAME = 'xxxxxx';
      public async up(queryRunner: QueryRunner): Promise<void> {
        
        const exist = await isColumnExist(
          this.COLUMN_NAME,
          this.TABLE_NAME,
          queryRunner,
        );
        if (exist) {
        
          return;
        } else {
        
          await queryRunner.addColumn(
            this.TABLE_NAME,
            new TableColumn({
        
              name: this.COLUMN_NAME,
              comment: 'xxx',
              type: 'varchar',
              length: '32',
              default: "'xxxxxx'",
              ...NOT_NULL,
            }),
          );
        }
      }
    
      public async down(queryRunner: QueryRunner): Promise<void> {
        
        await queryRunner.dropColumn(this.TABLE_NAME, this.COLUMN_NAME);
      }
    }

## 修改表字段 ##

如果使用 PG 需要在 SQL 语句中使用 $\{this.OLD\_COLUMN\_NAME\} 时 需要在两边加上双引号，告诉 PG 区分大小写，否则 PG 会自动转换为小写进行查找

export class alterXXXX1660197330000 implements MigrationInterface {
        
    
      type = getManager().connection.options.type;
      TABLE_NAME = "xxxx";
      OLD_COLUMN_NAME = "parentCode";
      NEW_COLUMN_NAME = "parent_code";
    
    
      public async up(queryRunner: QueryRunner): Promise<void> {
        
        if (this.type === "mysql") {
        
          await queryRunner.query(
            `ALTER TABLE  ${
          this.TABLE_NAME} CHANGE  ${
          this.OLD_COLUMN_NAME}  ${
          this.NEW_COLUMN_NAME} varchar`
          );
          return;
        }
        if (this.type === "postgres") {
        
          await queryRunner.query(
            `ALTER TABLE ${
          this.TABLE_NAME} RENAME  COLUMN "${
          this.OLD_COLUMN_NAME}" TO ${
          this.NEW_COLUMN_NAME}`
          );
          return;
        }
        throw new Error("不支持的数据库，无法执行 migration");
      }
    
      public async down(queryRunner: QueryRunner): Promise<void> {
        
        if (this.type === "mysql") {
        
          await queryRunner.query(
            `ALTER TABLE  ${
          this.TABLE_NAME} CHANGE  ${
          this.OLD_COLUMN_NAME} ${
          this.NEW_COLUMN_NAME} varchar`
          );
          return;
        }
        if (this.type === "postgres") {
        
          await queryRunner.query(
            `ALTER TABLE ${
          this.TABLE_NAME} RENAME  COLUMN "${
          this.OLD_COLUMN_NAME}" TO ${
          this.NEW_COLUMN_NAME}`
          );
          return;
        }
        throw new Error("不支持的数据库，无法执行 migration");
      }
    
    }

## 单次修改原有字段的默认值、注释等情况（不会删除原有字段，仅更改现有字段类型） ##

export class alterXXXXX1658132100000
      implements MigrationInterface {
        
      type = getManager().connection.options.type;
      TABLE_NAME = 'xxx';
      COLUMN_NAME = 'xxxxxx';
    
      public async up(queryRunner: QueryRunner): Promise<void> {
        
        if (this.type === 'mysql') {
        
          await queryRunner.query(
            `ALTER TABLE ${
          this.TABLE_NAME} MODIFY ${
          this.COLUMN_NAME}  SET DEFAULT '6'`,
          );
          await queryRunner.query(
            `COMMENT ON COLUMN ${
          this.TABLE_NAME}.${
          this.COLUMN_NAME} is 'xxxxx'`,
          );
          return;
        }
        if (this.type === 'postgres') {
        
          await queryRunner.query(
            `ALTER TABLE ${
          this.TABLE_NAME} ALTER COLUMN ${
          this.COLUMN_NAME} SET  DEFAULT '6' `,
          );
          await queryRunner.query(
            `COMMENT ON COLUMN ${
          this.TABLE_NAME}.${
          this.COLUMN_NAME} is 'xxxxx'`,
          );
          return;
        }
        throw new Error('不支持的数据库，无法执行 migration');
      }
    
      public async down(queryRunner: QueryRunner): Promise<void> {
        
        if (this.type === 'mysql') {
        
          await queryRunner.query(
            `ALTER TABLE ${
          this.TABLE_NAME} MODIFY ${
          this.COLUMN_NAME}  SET DEFAULT '6'`,
          );
          await queryRunner.query(
            `COMMENT ON COLUMN ${
          this.TABLE_NAME}.${
          this.COLUMN_NAME} is 'xxxxx'`,
          );
          return;
        }
        if (this.type === 'postgres') {
        
          await queryRunner.query(
            `ALTER TABLE ${
          this.TABLE_NAME} ALTER COLUMN ${
          this.COLUMN_NAME} SET  DEFAULT '6'`,
          );
          await queryRunner.query(
            `COMMENT ON COLUMN ${
          this.TABLE_NAME}.${
          this.COLUMN_NAME} is 'xxxxx'`,
          );
          return;
        }
        throw new Error('不支持的数据库，无法执行 migration');
      }
    }

## 单次删除原有字段并重新添加原有字段（changeColumn） ##

export class changeXXXXXX1657873200000
      implements MigrationInterface {
        
      TABLE_NAME = 'xxx';
      COLUMN_NAME = 'xxxxxxx';
      public async up(queryRunner: QueryRunner): Promise<void> {
        
        const exist = await isColumnExist(
          this.COLUMN_NAME,
          this.TABLE_NAME,
          queryRunner,
        );
        if (!exist) {
        
          return;
        } else {
        
          await queryRunner.changeColumn(
            this.TABLE_NAME,
            this.COLUMN_NAME,
            new TableColumn({
        
              comment: 'xxxx',
              default: 6,
              ...NOT_NULL,
              ...INT,
              name: this.COLUMN_NAME,
            }),
          );
        }
      }
      public async down(queryRunner: QueryRunner): Promise<void> {
        
        await queryRunner.changeColumn(
          this.TABLE_NAME,
          this.COLUMN_NAME,
          new TableColumn({
        
            comment: 'xxxx',
            default: 6,
            ...NOT_NULL,
            ...INT,
            name: this.COLUMN_NAME,
          }),
        );
      }
    }