How to migrate m:n association table ids from integer to uuid in sequelize with postgres?-CodePudding

I have two models, book and author, with a many to many association. I originally implemented both of them to have auto-incrementing integer ids but now I want to switch both to UUID. I added the UUID datatype to the associations:

// models/book.js
const { Model } = require('sequelize')

module.exports = (sequelize, DataTypes) => {
  class Book extends Model {
    static associate(models) {
      Book.belongsToMany(models.Author, {
        as: 'authors',
        through: 'BookAuthors',
        foreignKey: {
          allowNull: false,
          name: 'bookId',
          type: DataTypes.UUID
        }
      })
    }
  }
  
  Book.init(
    {
      title: {
        type: DataTypes.STRING,
        allowNull: false
      }
    },
    {
      sequelize,
      modelName: 'Book'
    }
  )
  return Book
}

// models/author.js
const { Model } = require('sequelize')

module.exports = (sequelize, DataTypes) => {
  class Author extends Model {
    static associate(models) {
      Author.belongsToMany(models.Book, {
        through: 'BookAuthors',
        foreignKey: {
          allowNull: false,
          name: 'authorId',
          type: DataTypes.UUID
        }
      })
    }
  }
  Author.init(
    {
      name: {
        type: DataTypes.STRING(75),
        allowNull: false,
        unique: true
      },
      biography: DataTypes.TEXT
    },
    {
      sequelize,
      modelName: 'Author'
    }
  )

  return Author
}

And in the migrations to set up the database I changed both id attributes to:

id: {
  allowNull: false,
  defaultValue: Sequelize.UUIDV4,
  primaryKey: true,
  type: Sequelize.UUID
}

I don't care about the data in the tables, I already dropped the table and recreated it with the new code, but starting the server then fails with the following error:

Key columns "authorId" and "id" are of incompatible types: integer and uuid.

while executing the statement:

CREATE TABLE IF NOT EXISTS "BookAuthors" ("createdAt" TIMESTAMP WITH TIME ZONE NOT NULL, "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, "authorId" INTEGER NOT NULL REFERENCES "Authors" ("id") ON DELETE CASCADE ON UPDATE CASCADE, "bookId" UUID NOT NULL REFERENCES "Books" ("id") ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY ("authorId","bookId"));

Afaik, the association table is automatically created by Sequelize while calling db.sequelize.sync(). So then why does it accept UUID for books, but not for authors?

CodePudding user response：

You didn't indicate PKs in models explicitly because now you want to define them with other than the default DB type (integer):

Book model:

Book.init(
    {
      id: {
        allowNull: false,
        defaultValue: Sequelize.UUIDV4,
        primaryKey: true,
        type: Sequelize.UUID
      },
      title: {
        type: DataTypes.STRING,
        allowNull: false
      }
    },
    {
      sequelize,
      modelName: 'Book'
    }
  )

Author model:

Author.init(
    {
      id: {
        allowNull: false,
        defaultValue: Sequelize.UUIDV4,
        primaryKey: true,
        type: Sequelize.UUID
      },
      name: {
        type: DataTypes.STRING(75),
        allowNull: false,
        unique: true
      },
      biography: DataTypes.TEXT
    },
    {
      sequelize,
      modelName: 'Author'
    }
  )