1

I'm trying to create a unique constraint on multiple columns in my postgres DB using sequelize. The instructions here are pretty clear but they don't work. Here's what my migration file looks like:

module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('scores', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      opp: {
        type: Sequelize.INTEGER,
        allowNull: false,
        unique: 'compositeScoreIndex'
      },
      oppt: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: 'compositeScoreIndex'
      },
      tree: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: 'compositeScoreIndex'
      },
      version: {
        type: Sequelize.INTEGER,
        allowNull: false,
        unique: 'compositeScoreIndex'
      },
      score: {
        type: Sequelize.JSON,
        allowNull: false
      }
    }, {
      indexes: [{
          unique: true,
          fields: ['opp', 'oppt', 'tree', 'version']
      }]
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('scores');
  }
};

This should be equivalent to this query:

CREATE TABLE scores (id INT PRIMARY KEY NOT NULL, opp INT NOT NULL,
oppt TEXT NOT NULL, tree TEXT NOT NULL, version INT NOT NULL, score
JSON NOT NULL, CONSTRAINT uniq_scores UNIQUE(opp, oppt, tree, version));

But the sequelize migration fails to create the constraint. I'm able to create multiple rows with the same values in opp, oppt, tree and version. Anyone else seeing this?

UPDATE: I was able to have the migration print out log statements of the SQL queries. Here's the CREATE TABLE query it runs with the above migration file:

CREATE TABLE IF NOT EXISTS "scores" ("id"   SERIAL , "oppId" INTEGER NOT NULL,
"oppType" VARCHAR(255) NOT NULL, "tree" VARCHAR(255) NOT NULL,
"version" INTEGER NOT NULL, "score" JSON NOT NULL, "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
"updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL, PRIMARY KEY ("id"));

Clearly there is no mention of a unique constraint. Anyone know why Sequelize isn't behaving as expected here?

b.lyte
  • 6,518
  • 4
  • 40
  • 51
  • Checking the pg_indexes table I don't see a unique constraint on the multiple columns when running the sequelize migration, but I do see it when I run the sql query above manually.It'd be nice if I could log the sql query that ran during the migration. – b.lyte Jul 07 '18 at 20:21
  • https://stackoverflow.com/questions/21427501/how-can-i-see-the-sql-generated-by-sequelize-js – P Ackerman Jul 07 '18 at 20:55
  • Ackerman, thanks however that doesn't work for the CLI. To make it work for the CLI I had to add a logging:true option to db.json (as explained here: https://stackoverflow.com/questions/33948072/logging-sequelize-migrations). – b.lyte Jul 08 '18 at 20:41
  • I was having the same problem, the table was not created with the primary key in the postgres DB. I discovered a typo in my field definition. Sequelize did not show an error and created the table without the primaryKey. I'm using `` sequelize-cli ":" ^ 4.0.0 "` – Anderson Silva Jul 28 '18 at 01:19

0 Answers0