Migrating from using auto increment id to uuid with sequelize

Question

All right, I'm trying out some different ORM's, currently working on Sequelize. I've defined two models, asset and asset_category, where each asset has a foreign key relation to an asset_category. All good! Now, I want to change from using auto increment id to uuid, because it's better for security. So I have data in a database and wrote the following (superlong!) migration:

up: (queryInterface, Sequelize) => {
    return queryInterface
      .addColumn("asset_categories", "uuid", {
        // Add uuid column
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4,
        allowNull: false,
      })
      .then(_ => {
        return queryInterface.sequelize.query(
          // Fill uuid column
          `
          UPDATE asset_categories SET uuid=uuid()
          `,
        );
      })
      .then(_ => {
        return queryInterface.addColumn("assets", "category_uuid", {
          // Add assets reference uuid column
          type: Sequelize.UUID,
          allowNull: false,
        });
      })
      .then(_ => {
        return queryInterface.sequelize.query(
          // Fill assets reference uuid column
          `
          UPDATE assets
          SET category_uuid= (SELECT ac.uuid FROM asset_categories ac WHERE ac.id = assets.category_id)
        `,
        );
      })
      .then(_ => {
        // Remove assets category_id column
        return queryInterface.removeColumn("assets", "category_id");
      })
      .then(_ => {
        // Remove asset_categories id column
        return queryInterface.removeColumn("asset_categories", "id");
      })
      .then(_ => {
        // Rename asset_categories uuid to id
        return queryInterface.renameColumn("asset_categories", "uuid", "id");
      })
      .then(_ => {
        // Rename assets category_uuid to category_id
        return queryInterface.renameColumn(
          "assets",
          "category_uuid",
          "category_id",
        );
      })
      .then(_ => {
        return queryInterface.changeColumn("asset_categories", "id", {
          // Add primary key to asset_categories id field
          type: Sequelize.UUID,
          defaultValue: Sequelize.UUIDV4,
          allowNull: false,
          primaryKey: true,
        });
      })
      .then(_ => {
        // Add index to asset_categories id field
        return queryInterface.addIndex("asset_categories", ["id"]);
      })
      .then(_ => {
        // Add index to assets category_id field
        return queryInterface.addIndex("assets", ["category_id"]);
      });

And:

  up: (queryInterface, Sequelize) => {
    // Add uuid column
    return queryInterface
      .addColumn("assets", "uuid", {
        type: Sequelize.UUID,
        defaultValue: Sequelize.UUIDV4,
        allowNull: false,
        primaryKey: false,
      })
      .then(_ => {
        // Fill uuid column
        return queryInterface.sequelize.query(
          `
      UPDATE assets SET uuid=uuid()
      `,
        );
      })
      .then(_ => {
        // Remove assets id column
        return queryInterface.removeColumn("assets", "id");
      })
      .then(_ => {
        // Rename assets uuid to id
        return queryInterface.renameColumn("assets", "uuid", "id");
      })
      .then(_ => {
        // Add primary key to uuid column
        return queryInterface.changeColumn("assets", "id", {
          type: Sequelize.UUID,
          defaultValue: Sequelize.UUIDV4,
          allowNull: false,
          primaryKey: true,
        });
      })
      .then(_ => {
        // add foreign key on assets category_id
        return queryInterface.changeColumn("assets", "category_id", {
          type: Sequelize.UUID,
          allowNull: false,
          references: {
            model: {
              tableName: "asset_categories",
            },
            key: "id",
          },
        });
      });

Issue though is I get an error in the last step: ERROR: Referencing column 'category_id' and referenced column 'id' in foreign key constraint 'assets_ibfk_1' are incompatible.

It seems that along all these steps the asset_category.id changed to id char(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,

Whilst asset.category_id is category_id char(36) COLLATE utf8mb4_general_ci NOT NULL,

I'm assuming this is the reason I can't add the foreign key back, cause the columns are not exactly the same.

For starters: how does this happen? What causes a column to get a different character set or collate? And also: is there a better way to approach such a migration, cause compared to using Ruby on Rails + Active Record, this seems like utter garbage...

Answer 1

• DROP all the FKs involving the ids that you will be removing. Add FKs for the uuids after finishing the conversion.
• Uuids should be CHAR(36) CHARACTER SET ascii COLLATION ascii_general_ci.
• If your table is big, you should consider converting to BINARY(16) to save a bunch of disk space.
• Any columns that currently point to ids in other table need to become CHAR(36)... also.
• The order of actions to take is critical.

Something like:

1. Backup the entire dataset -- a screwup below may not be recoverable.
2. DROP FKs. Do this for all tables before moving on to the next step.
3. ADD COLUMN uuid for each id-like column; possibly multiple ones in each table.
4. Populate uuids that will become the PKs with fresh values.
5. Populate other uuids by doing a multi-table UPDATE based on the existing ids.
6. Change client code to reference uuids; test it.
7. For tables where the PK is changing, DROP PRIMARY KEY and ADD PRIMARY KEY(uuid).
8. DROP COLUMN for the ids and id-like columns.
9. ADD FKs for the uuids, where appropriate.

If you choose to use the smaller uuid format, that modifies the details of steps 4,5,6.

Ugh.