Migrating from using auto increment id to uuid with sequelize

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...

Submitted February 07, 2020 at 04:14PM by Koekenbakker28