Vendure v1.0.0-beta.1 migration

example-mysql-migration.ts

import {MigrationInterface, QueryRunner} from "typeorm";
import { addToDefaultChannel, migratePaymentMethods } from '../migration-utils';

export class v100Beta11614933697219 implements MigrationInterface {

   public async up(queryRunner: QueryRunner): Promise<any> {
        await queryRunner.query("CREATE TABLE `tag` (`createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6), `updatedAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6), `value` varchar(255) NOT NULL, `id` int NOT NULL AUTO_INCREMENT, PRIMARY KEY (`id`)) ENGINE=InnoDB", undefined);
        await queryRunner.query("CREATE TABLE `asset_tags_tag` (`assetId` int NOT NULL, `tagId` int NOT NULL, INDEX `IDX_9e412b00d4c6cee1a4b3d92071` (`assetId`), INDEX `IDX_fb5e800171ffbe9823f2cc727f` (`tagId`), PRIMARY KEY (`assetId`, `tagId`)) ENGINE=InnoDB", undefined);
        await queryRunner.query("CREATE TABLE `asset_channels_channel` (`assetId` int NOT NULL, `channelId` int NOT NULL, INDEX `IDX_dc4e7435f9f5e9e6436bebd33b` (`assetId`), INDEX `IDX_16ca9151a5153f1169da5b7b7e` (`channelId`), PRIMARY KEY (`assetId`, `channelId`)) ENGINE=InnoDB", undefined);
        await queryRunner.query("CREATE TABLE `facet_channels_channel` (`facetId` int NOT NULL, `channelId` int NOT NULL, INDEX `IDX_ca796020c6d097e251e5d6d2b0` (`facetId`), INDEX `IDX_2a8ea404d05bf682516184db7d` (`channelId`), PRIMARY KEY (`facetId`, `channelId`)) ENGINE=InnoDB", undefined);
        await queryRunner.query("CREATE TABLE `facet_value_channels_channel` (`facetValueId` int NOT NULL, `channelId` int NOT NULL, INDEX `IDX_ad690c1b05596d7f52e52ffeed` (`facetValueId`), INDEX `IDX_e1d54c0b9db3e2eb17faaf5919` (`channelId`), PRIMARY KEY (`facetValueId`, `channelId`)) ENGINE=InnoDB", undefined);
        await queryRunner.query("CREATE TABLE `payment_method_channels_channel` (`paymentMethodId` int NOT NULL, `channelId` int NOT NULL, INDEX `IDX_5bcb569635ce5407eb3f264487` (`paymentMethodId`), INDEX `IDX_c00e36f667d35031087b382e61` (`channelId`), PRIMARY KEY (`paymentMethodId`, `channelId`)) ENGINE=InnoDB", undefined);


        // Group all the "payment_method" queries and ensure the DROP COLUMN is at the end
        // _after_ the new columns have been added and the migratePaymentMethods() funtion has been run.
        await queryRunner.query("ALTER TABLE `payment_method` ADD `name` varchar(255) NOT NULL DEFAULT ''", undefined);
        await queryRunner.query("ALTER TABLE `payment_method` ADD `description` varchar(255) NOT NULL DEFAULT ''", undefined);
        await queryRunner.query("ALTER TABLE `payment_method` ADD `checker` text NULL", undefined);
        await queryRunner.query("ALTER TABLE `payment_method` ADD `handler` text NOT NULL", undefined);
        await migratePaymentMethods(queryRunner);
        await queryRunner.query("ALTER TABLE `payment_method` DROP COLUMN `configArgs`", undefined);


        await queryRunner.query("ALTER TABLE `product_option_group` ADD `deletedAt` datetime NULL", undefined);
        await queryRunner.query("ALTER TABLE `product_option` ADD `deletedAt` datetime NULL", undefined);
        await queryRunner.query("ALTER TABLE `tax_category` ADD `isDefault` tinyint NOT NULL DEFAULT 0", undefined);
        await queryRunner.query("ALTER TABLE `order_item` ADD `initialListPrice` int NULL", undefined);

        await queryRunner.query("ALTER TABLE `payment_method` CHANGE `code` `code` varchar(255) NOT NULL DEFAULT ''", undefined);
        await queryRunner.query("ALTER TABLE `product_review` CHANGE `response` `response` text NULL DEFAULT NULL", undefined);
        await queryRunner.query("ALTER TABLE `product_review` CHANGE `responseCreatedAt` `responseCreatedAt` datetime NULL DEFAULT NULL", undefined);
        await queryRunner.query("ALTER TABLE `asset_tags_tag` ADD CONSTRAINT `FK_9e412b00d4c6cee1a4b3d920716` FOREIGN KEY (`assetId`) REFERENCES `asset`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `asset_tags_tag` ADD CONSTRAINT `FK_fb5e800171ffbe9823f2cc727fd` FOREIGN KEY (`tagId`) REFERENCES `tag`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `asset_channels_channel` ADD CONSTRAINT `FK_dc4e7435f9f5e9e6436bebd33bb` FOREIGN KEY (`assetId`) REFERENCES `asset`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `asset_channels_channel` ADD CONSTRAINT `FK_16ca9151a5153f1169da5b7b7e3` FOREIGN KEY (`channelId`) REFERENCES `channel`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `facet_channels_channel` ADD CONSTRAINT `FK_ca796020c6d097e251e5d6d2b02` FOREIGN KEY (`facetId`) REFERENCES `facet`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `facet_channels_channel` ADD CONSTRAINT `FK_2a8ea404d05bf682516184db7d3` FOREIGN KEY (`channelId`) REFERENCES `channel`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `facet_value_channels_channel` ADD CONSTRAINT `FK_ad690c1b05596d7f52e52ffeedd` FOREIGN KEY (`facetValueId`) REFERENCES `facet_value`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `facet_value_channels_channel` ADD CONSTRAINT `FK_e1d54c0b9db3e2eb17faaf5919c` FOREIGN KEY (`channelId`) REFERENCES `channel`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `payment_method_channels_channel` ADD CONSTRAINT `FK_5bcb569635ce5407eb3f264487d` FOREIGN KEY (`paymentMethodId`) REFERENCES `payment_method`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);
        await queryRunner.query("ALTER TABLE `payment_method_channels_channel` ADD CONSTRAINT `FK_c00e36f667d35031087b382e61b` FOREIGN KEY (`channelId`) REFERENCES `channel`(`id`) ON DELETE CASCADE ON UPDATE NO ACTION", undefined);

        await addToDefaultChannel(queryRunner, 'asset', 'assetId');
        await addToDefaultChannel(queryRunner, 'facet', 'facetId');
        await addToDefaultChannel(queryRunner, 'facet_value', 'facetValueId');
        await addToDefaultChannel(queryRunner, 'payment_method', 'paymentMethodId');
   }

   public async down(queryRunner: QueryRunner): Promise<any> {
        // omitted
   }

}

migration-utils.ts

import { QueryRunner } from 'typeorm';

export async function migratePaymentMethods(queryRunner: QueryRunner) {
    const paymentMethods = await q(
        queryRunner,
        {
            mysql: 'SELECT `id`, `code`, `configArgs` from `payment_method`',
            postgres: 'SELECT "id", "code", "configArgs" from "payment_method"',
        },
        [],
    );
    for (const method of paymentMethods) {
        const handler = JSON.stringify({
            code: method.code,
            args: JSON.parse(method.configArgs),
        });
        const result = await q(
            queryRunner,
            {
                mysql: 'UPDATE `payment_method` SET `handler` =  ?, `name` = ? WHERE `id` = ?',
                postgres: 'UPDATE "payment_method" SET "handler" =  $1, "name" = $2 WHERE "id" = $3',
            },
            [handler, method.code, method.id],
        );
        const a = result;
    }
}

export async function addToDefaultChannel(queryRunner: QueryRunner, tableName: string, idName: string) {
    const channelTableName = `${tableName}_channels_channel`;
    const result = await q(
        queryRunner,
        {
            mysql:
                'INSERT INTO `' +
                channelTableName +
                '` (' +
                idName +
                ", channelId) SELECT id, (SELECT id from `channel` WHERE `code` = '__default_channel__') FROM `" +
                tableName +
                '`',
            postgres:
                'INSERT INTO "' +
                channelTableName +
                '" ("' +
                idName +
                '", "channelId") SELECT "id", (SELECT "id" from "channel" WHERE "code" = \'__default_channel__\') FROM "' +
                tableName +
                '"',
        },
        [],
    );
    const a = result;
}

function q(queryRunner: QueryRunner, query: { mysql: string; postgres: string }, params: any[] = []) {
    return queryRunner.query(isPostgres(queryRunner) ? query.postgres : query.mysql, params);
}

function isPostgres(queryRunner: QueryRunner): boolean {
    const { type } = queryRunner.connection.options;
    return type === 'postgres' || type === 'aurora-data-api-pg' || type === 'cockroachdb';
}