typeorm#QueryRunner TypeScript Examples

public async down(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.query(`ALTER TABLE "project" DROP CONSTRAINT "FK_fb50f2e406b10942dbef83748a6"`);
    await queryRunner.query(`ALTER TABLE "project" DROP CONSTRAINT "FK_290d3fcaa1d1ad5185793b93bec"`);
    await queryRunner.query(`ALTER TABLE "project" DROP CONSTRAINT "FK_178d59cf51fe4406fa362c0b2b4"`);
    await queryRunner.query(`ALTER TABLE "system_configuration" DROP CONSTRAINT "UQ_806be749d87a705a20a6e17512c"`);
    await queryRunner.query(`ALTER TABLE "obligation_type" DROP CONSTRAINT "UQ_ac507b3470a2ce56a2b5478aac5"`);
    await queryRunner.query(`ALTER TABLE "output_format_type" DROP CONSTRAINT "UQ_4274f453356a1de900db4cc66a4"`);
    await queryRunner.query(
      `ALTER TABLE "project" ADD CONSTRAINT "FK_fb50f2e406b10942dbef83748a6" FOREIGN KEY ("output_format_code") REFERENCES "output_format_type"("code") ON DELETE SET NULL ON UPDATE NO ACTION`,
    );
    await queryRunner.query(`ALTER TABLE "project_status_type" DROP CONSTRAINT "UQ_8bcf7f2067cfadd174a2bd22053"`);
    await queryRunner.query(
      `ALTER TABLE "project" ADD CONSTRAINT "FK_178d59cf51fe4406fa362c0b2b4" FOREIGN KEY ("project_status_code") REFERENCES "project_status_type"("code") ON DELETE SET NULL ON UPDATE NO ACTION`,
    );
    await queryRunner.query(`ALTER TABLE "deployment_type" DROP CONSTRAINT "UQ_60428f364f625bde1e51171168f"`);
    await queryRunner.query(
      `ALTER TABLE "project" ADD CONSTRAINT "FK_290d3fcaa1d1ad5185793b93bec" FOREIGN KEY ("deployment_type_code") REFERENCES "deployment_type"("code") ON DELETE SET NULL ON UPDATE NO ACTION`,
    );
  }

public async up (queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query("CREATE TABLE `user_nominations_nomination` (`userID` int NOT NULL, `nominationID` int NOT NULL, INDEX `IDX_c39e90811e78d3a759f9f75e37` (`userID`), INDEX `IDX_2dc37a0c6b4f99e6e20c4903ae` (`nominationID`), PRIMARY KEY (`userID`, `nominationID`)) ENGINE=InnoDB");
        await queryRunner.query("INSERT INTO `user_nominations_nomination` (`userID`, `nominationID`) SELECT `nominatorID`, `ID` FROM `nomination`");

        await queryRunner.query("ALTER TABLE `nomination` DROP FOREIGN KEY `FK_29960f8747f6f51afcf0d5eaf76`");
        await queryRunner.query("ALTER TABLE `nomination` DROP COLUMN `nominatorID`");
        await queryRunner.query("ALTER TABLE `user_nominations_nomination` ADD CONSTRAINT `FK_c39e90811e78d3a759f9f75e37c` FOREIGN KEY (`userID`) REFERENCES `user`(`ID`) ON DELETE CASCADE ON UPDATE NO ACTION");
        await queryRunner.query("ALTER TABLE `user_nominations_nomination` ADD CONSTRAINT `FK_2dc37a0c6b4f99e6e20c4903ae0` FOREIGN KEY (`nominationID`) REFERENCES `nomination`(`ID`) ON DELETE CASCADE ON UPDATE NO ACTION");
    }

public async down(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.query(`ALTER TABLE "license_scan_result" DROP CONSTRAINT "FK_46141bcc36c5c469fcb25fb7e92"`);
    await queryRunner.query(`ALTER TABLE "license_scan_result" DROP COLUMN "scanId"`);
    await queryRunner.query(`ALTER TABLE "license_scan_result" DROP COLUMN "json_results"`);
    await queryRunner.query(`ALTER TABLE "license_scan_result" DROP COLUMN "scan_tool"`);
    await queryRunner.query(`ALTER TABLE "license_scan_result" DROP COLUMN "updated_at"`);
    await queryRunner.query(`ALTER TABLE "license_scan_result" DROP COLUMN "created_at"`);
    await queryRunner.query(`ALTER TABLE "license_scan_result" DROP CONSTRAINT "PK_84357efbde1881e508da9007d83"`);
    await queryRunner.query(`ALTER TABLE "license_scan_result" DROP COLUMN "id"`);
  }

public async up (queryRunner: QueryRunner): Promise<void> {
        // Old migration code, super slow and doesn't work for restricted users
        if (process.env.NODE_ENV === "production") {
            await queryRunner.query("ALTER TABLE `user` ADD `country` tinytext NOT NULL");

            const users = await User
                .createQueryBuilder("user")
                .getMany();

            await Promise.all(users.map(async user => {
                const apiUser = (await osuClient.user.get(user.osu.userID)) as APIUser;
                user.country = apiUser.country.toString();
                return user.save();
            }));
        } else {
            const bigSql = await streamToString(createReadStream(resolve(__dirname, "1631140092497-userCountries.sql.gz")).pipe(createGunzip()));
            const sqlInstructions = bigSql.split("\n").filter(sql => sql.trim().length !== 0);
            for(const sqlInstruction of sqlInstructions)
                if(sqlInstruction.trim().length !== 0)
                    await queryRunner.query(sqlInstruction);
        }
    }

public async up(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.query(`ALTER TABLE "security_scan_result" ADD "id" SERIAL NOT NULL`);
    await queryRunner.query(
      `ALTER TABLE "security_scan_result" ADD CONSTRAINT "PK_923566ed79959eb5a8c928d5746" PRIMARY KEY ("id")`,
    );
    await queryRunner.query(`ALTER TABLE "security_scan_result" ADD "created_at" TIMESTAMP NOT NULL DEFAULT now()`);
    await queryRunner.query(`ALTER TABLE "security_scan_result" ADD "updated_at" TIMESTAMP DEFAULT now()`);
    await queryRunner.query(`ALTER TABLE "security_scan_result" ADD "scan_tool" character varying NOT NULL`);
    await queryRunner.query(`ALTER TABLE "security_scan_result" ADD "json_results" jsonb`);
    await queryRunner.query(`ALTER TABLE "security_scan_result" ADD "html_results" text`);
    await queryRunner.query(`ALTER TABLE "security_scan_result" ADD "scanId" integer`);
    await queryRunner.query(
      `ALTER TABLE "security_scan_result" ADD CONSTRAINT "FK_dd9014ea526791fce4b9bd4928a" FOREIGN KEY ("scanId") REFERENCES "scan"("id") ON DELETE CASCADE ON UPDATE NO ACTION`,
    );
  }

public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'users',
        columns: [
          {
            name: 'id',
            type: 'uuid',
            isPrimary: true,
            generationStrategy: 'uuid',
            default: 'uuid_generate_v4()',
          },
          {
            name: 'name',
            type: 'varchar',
          },
          {
            name: 'email',
            type: 'varchar',
            isUnique: true,
          },
          {
            name: 'password',
            type: 'varchar',
          },
          {
            name: 'created_at',
            type: 'timestamp',
            default: 'now()',
          },
          {
            name: 'updated_at',
            type: 'timestamp',
            default: 'now()',
          },
        ],
      }),
    );
  }

public async up(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.query(`ALTER TABLE "bom_manual_license" DROP CONSTRAINT "FK_3f2421a7019cbdb4aad56444a32"`);
    await queryRunner.query(`ALTER TABLE "bom_security_exception" DROP CONSTRAINT "FK_67c83cf33f2709de403df49f89c"`);
    await queryRunner.query(`ALTER TABLE "bom_license_exception" DROP CONSTRAINT "FK_9d18dfc6aeb1aff9bb0df49b537"`);
    await queryRunner.query(`ALTER TABLE "bom_manual_license" RENAME COLUMN "billOfMaterialsId" TO "projectId"`);
    await queryRunner.query(`ALTER TABLE "bom_security_exception" RENAME COLUMN "billOfMaterialsId" TO "projectId"`);
    await queryRunner.query(`ALTER TABLE "bom_license_exception" RENAME COLUMN "billOfMaterialsId" TO "projectId"`);
    await queryRunner.query(
      `ALTER TABLE "bom_manual_license" ADD CONSTRAINT "FK_efcbc10954a1e46a0dbc7dbc2b6" FOREIGN KEY ("projectId") REFERENCES "project"("id") ON DELETE CASCADE ON UPDATE NO ACTION`,
    );
    await queryRunner.query(
      `ALTER TABLE "bom_security_exception" ADD CONSTRAINT "FK_ccb1aa482b82d4a6fc8cc8a1290" FOREIGN KEY ("projectId") REFERENCES "project"("id") ON DELETE CASCADE ON UPDATE NO ACTION`,
    );
    await queryRunner.query(
      `ALTER TABLE "bom_license_exception" ADD CONSTRAINT "FK_b2fd00f4cad38fd50c850ec3027" FOREIGN KEY ("projectId") REFERENCES "project"("id") ON DELETE CASCADE ON UPDATE NO ACTION`,
    );
  }

public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.addColumn(
      'users',
      new TableColumn({
        name: 'avatar',
        type: 'varchar',
        isNullable: true,
      }),
    );
  }

public async up(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.createTable(new Table({ name: 'bill_of_material' }));
    await queryRunner.createTable(new Table({ name: 'license' }));
    await queryRunner.createTable(new Table({ name: 'license_scan_result' }));
    await queryRunner.createTable(new Table({ name: 'license_scan_result_item' }));
    await queryRunner.createTable(new Table({ name: 'obligation' }));
    await queryRunner.createTable(new Table({ name: 'project' }));
    await queryRunner.createTable(new Table({ name: 'scan' }));
    await queryRunner.createTable(new Table({ name: 'security_scan_result' }));
  }

public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.addColumn(
      'appointments',
      new TableColumn({
        name: 'user_id',
        type: 'uuid',
        isNullable: true,
      }),
    );

    await queryRunner.createForeignKey(
      'appointments',
      new TableForeignKey({
        name: 'AppointmentUser',
        columnNames: ['user_id'],
        referencedTableName: 'users',
        referencedColumnNames: ['id'],
        onDelete: 'SET NULL',
        onUpdate: 'CASCADE',
      }),
    );
  }

public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(new Table({
      name: 'images',
      columns: [
        {
          name: 'id',
          type: 'integer',
          unsigned: true,
          isPrimary: true,
          isGenerated: true,
          generationStrategy: 'increment',
        },
        {
          name: 'path',
          type: 'varchar',
        },
        {
          name: 'orphanage_id',
          type: 'integer',
        },
      ],
      foreignKeys: [
        {
          name: 'ImageOrphanage',
          columnNames: ['orphanage_id'],
          referencedTableName: 'orphanages',
          referencedColumnNames: ['id'],
          onUpdate: 'CASCADE',
          onDelete: 'CASCADE',
        }
      ],
    }))
  }

public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'categories',
        columns: [
          {
            name: 'id',
            type: 'uuid',
            isPrimary: true,
            generationStrategy: 'uuid',
            default: 'uuid_generate_v4()',
          },
          {
            name: 'title',
            type: 'varchar',
          },
          {
            name: 'created_at',
            type: 'timestamp',
            default: 'now()',
          },
          {
            name: 'updated_at',
            type: 'timestamp',
            default: 'now()',
          },
        ],
      }),
    );
  }

public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropForeignKey('appointments', 'appointment_customer_id');

    await queryRunner.dropColumn(
      'appointments',
      new TableColumn({
        name: 'customer_id',
        type: 'uuid',
        isNullable: true,
      }),
    );
  }

public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.createTable(
      new Table({
        name: 'customers',
        columns: [
          {
            name: 'id',
            type: 'uuid',
            isPrimary: true,
            generationStrategy: 'uuid',
            default: 'uuid_generate_v4()',
          },
          {
            name: 'name',
            type: 'varchar',
          },
          {
            name: 'email',
            type: 'varchar',
            isUnique: true,
          },
          {
            name: 'created_at',
            type: 'timestamp',
            default: 'now()',
          },
          {
            name: 'updated_at',
            type: 'timestamp',
            default: 'now()',
          },
        ],
      }),
    );
  }

public async down(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.dropColumn(
      'users',
      new TableColumn({
        name: 'is_provider',
        type: 'boolean',
        default: false,
      }),
    );
  }

public async down(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.query(`ALTER TABLE "bill_of_materials" DROP CONSTRAINT "FK_8b1c08773411a8690be8246e30c"`);
    await queryRunner.query(`ALTER TABLE "bom_security_exception" DROP CONSTRAINT "FK_67c83cf33f2709de403df49f89c"`);
    await queryRunner.query(`ALTER TABLE "bom_security_exception" DROP CONSTRAINT "FK_e429afe5ea2e0227f27f7980878"`);
    await queryRunner.query(`ALTER TABLE "bom_manual_license" DROP CONSTRAINT "FK_7070dc3d60715112084b678c77d"`);
    await queryRunner.query(`ALTER TABLE "bom_manual_license" DROP CONSTRAINT "FK_3f2421a7019cbdb4aad56444a32"`);
    await queryRunner.query(`ALTER TABLE "bom_manual_license" DROP CONSTRAINT "FK_97156ea5aa5f5e2cb546b129192"`);
    await queryRunner.query(`ALTER TABLE "bom_license_exception" DROP CONSTRAINT "FK_ade79c0f6f835ab86677dea17d0"`);
    await queryRunner.query(`ALTER TABLE "bom_license_exception" DROP CONSTRAINT "FK_9d18dfc6aeb1aff9bb0df49b537"`);
    await queryRunner.query(`ALTER TABLE "bom_license_exception" DROP CONSTRAINT "FK_133a2ae9bfb55d46a03d079073c"`);
    await queryRunner.query(`DROP TABLE "bill_of_materials"`);
    await queryRunner.query(`DROP TABLE "bom_security_exception"`);
    await queryRunner.query(`DROP TABLE "bom_manual_license"`);
    await queryRunner.query(`DROP TABLE "bom_license_exception"`);
  }

public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.manager.getRepository(RuleType).save([
            new RuleType('recent'),
            new RuleType('repeat'),
            new RuleType('author'),
            new RuleType('attribution'),
            new RuleType('history'),
            new RuleType('regex'),
            new RuleType('repost'),
            new RuleType('sentiment')
        ]);
        await queryRunner.manager.getRepository(ActionType).save([
            new ActionType('comment'),
            new ActionType('lock'),
            new ActionType('remove'),
            new ActionType('report'),
            new ActionType('approve'),
            new ActionType('ban'),
            new ActionType('flair'),
            new ActionType('usernote'),
            new ActionType('message'),
            new ActionType('userflair'),
            new ActionType('dispatch'),
            new ActionType('cancelDispatch'),
            new ActionType('contributor'),
            new ActionType('modnote')
        ]);
        await queryRunner.manager.getRepository(InvokeeType).save([
            new InvokeeType('system'),
            new InvokeeType('user'),
        ]);
        await queryRunner.manager.getRepository(RunStateType).save([
            new RunStateType('running'),
            new RunStateType('paused'),
            new RunStateType('stopped'),
        ]);
    }

public async up(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.query(`ALTER TABLE "deployment_type" ADD "sort_order" integer DEFAULT 0`);
    await queryRunner.query(`ALTER TABLE "output_format_type" ADD "sort_order" integer DEFAULT 0`);
    await queryRunner.query(`ALTER TABLE "package_manager" ADD "sort_order" integer DEFAULT 0`);
    await queryRunner.query(`ALTER TABLE "project_status_type" ADD "sort_order" integer DEFAULT 0`);
    await queryRunner.query(`ALTER TABLE "obligation_type" ADD "sort_order" integer DEFAULT 0`);
    await queryRunner.query(`ALTER TABLE "security_scan_result_item_status_type" ADD "sort_order" integer DEFAULT 0`);
    await queryRunner.query(`ALTER TABLE "system_configuration" ADD "sort_order" integer DEFAULT 0`);
    await queryRunner.query(`ALTER TABLE "project_scan_status_type" ADD "sort_order" integer DEFAULT 0`);
    await queryRunner.query(
      `ALTER TABLE "security_scan_result_item_status_type" ADD CONSTRAINT "UQ_a1098c4b4b28e6bf002517083da" UNIQUE ("code")`,
    );
  }

public async up(queryRunner: QueryRunner): Promise<void> {
    await queryRunner.addColumn(
      'recover_password_requests',
      new TableColumn({
        name: 'expires_at',
        type: 'timestamp',
        default: 'now()',
      }),
    );
  }

public async up(queryRunner: QueryRunner): Promise<any> {
    await queryRunner.query(
      `CREATE TABLE "system_configuration" ("code" character varying(64) NOT NULL, "created_at" TIMESTAMP NOT NULL DEFAULT now(), "updated_at" TIMESTAMP DEFAULT now(), "description" text NOT NULL DEFAULT '', "is_default" boolean NOT NULL DEFAULT false, "spdx_license_list_version" character varying, CONSTRAINT "UQ_806be749d87a705a20a6e17512c" UNIQUE ("code"), CONSTRAINT "PK_806be749d87a705a20a6e17512c" PRIMARY KEY ("code"))`,
    );
  }