Sequelize Migrations: Adding a foreign key constraint to a column on same table

Question

So I'm trying to create a table with foreign key constraints to itself in migrations file.

I tried what I could following the sequelize docs and down below is the code I've tried, and I've also tried to move the foreign key references up to where the attributes were defined but it does not work there as well. Is there a way to do what I want to do here?

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('comments', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      root_id: {
        defaultValue: null,
        type: Sequelize.INTEGER
      },
      parent_id: {
        defaultValue: null,
        type: Sequelize.INTEGER
      },
    }).then(() => queryInterface.addConstraint(
      'comments',
      ['root_id'],
      {
        type: 'foreign key',
        name: 'root_id_fk',
        references: {
          table: 'comments',
          field: 'root_id'
        },
        onDelete: 'cascade',
        onUpdate: 'cascade'
      }
    )).then(() => queryInterface.addConstraint(
      'comments',
      ['parent_id'],
      {
        type: 'foreign key',
        name: 'parent_id_fk',
        references: {
          table: 'comments',
          field: 'parent_id'
        },
        onDelete: 'cascade',
        onUpdate: 'cascade'
      }
    ))
  },

Answer 1

Shashikant Pandit's answer is good, but it is not cross-db compatible.

I ran into a problem using that migration because I have a PostgreSQL as the main DB, and an in-memory SQLite database for my tests. Running the migrations in the test environment (the tests start with a blank DB and run the migrations to get to current) produced an error in SQLite.

Here is a version that uses Sequelizes addConstraint built-in and should be cross-db compatible.

module.exports = {
  up: (queryInterface, Sequelize) => queryInterface
    .addConstraint('app_users', {
      type: 'UNIQUE',
      fields: ['email', 'column2', 'column3'],
      name: 'unique_user_email',
    }),
  down: (queryInterface, Sequelize) => queryInterface
    .removeConstraint('app_users', 'unique_user_email'),
};

Answer 2

I figured out what I was doing wrong, I was trying to create a foreign key to reference itself on the same table when I was supposed to be referencing a different column. Woops!

Answer 3

For anyone looking for an answer on how to add foreign keys, use this Sequelize Constraints