Referencing a composite primary key in a Sequelize.js seed model

Question

Is it possible to reference composite primary keys in Sequelize?

I'm working on a web-app that helps organize kitchen waste. The restaurant organizes its weeks and months into 'periods' where the first week of September would be '9.1'. For every period, I need to create a new batch of ingredient objects that can keep track of what their prices and quantities were for that period. I figure it would be best to make the period primary keys their combined month and week, as that will be unique in the database. I may add year on later, but that doesn't change my problem.

The database I'm working with is Postgres.

This is my period table model in my sequelize seed file:

.then(() => queryInterface.createTable('periods', {
      month: {
        type: Sequelize.INTEGER,
        validate: {
          max: 12,
          min: 1
        },
        unique: "monthWeekConstraint",
        primaryKey: true
      },
      week: {
        type: Sequelize.INTEGER,
        validate: {
          max: 4,
          min: 1
        },
        unique: "monthWeekConstraint",
        primaryKey: true
      },
      createdAt: {
        type: Sequelize.DATE
      },
      updtedAt: {
        type: Sequelize.DATE
      }
    }))

I'd like to reference the periods stored in the above table in my periodItems table, which I have (incorrectly) looking like:

.then(() => queryInterface.createTable('periodItems', {
  periodMonth: {
    type: Sequelize.INTEGER,
    references: {model: 'periods', key: 'monthWeekConstraint'}
  },
  periodWeek: {
    type: Sequelize.INTEGER,
    references: {model: 'periods', key: 'monthWeekConstraint'}
  },
  day: {
    type: Sequelize.INTEGER,
    validate: {
      min: 1,
      max: 7
    }
  },
...other irrelevant fields...
}))

I'm definitely new to databases, so I apologize if I'm way off. I've gotten a few other tables doing what I'd like, but I've been stuck on this problem for a few days.

Answer 1

While it is possible to create composite primary keys in Sequelize by specifying primaryKey: true against more than one column (as you have already done above), Sequelize doesn't currently support composite foreign keys, so there is no way to reference a model/table which has composite primary keys.

See https://github.com/sequelize/sequelize/issues/311 for a discussion on the subject.

Answer 2

model/product.js:
const Product = sequelize.define("product", {
  sku: { type: Sequelize.STRING, allowNull: false, primaryKey: true },
  title: { type: Sequelize.STRING, allowNull: false },
  availability: {
    type: Sequelize.STRING,
    allowNull: false,
    defaultValue: false,
  }
});

model/Attribute.js:
const Attribute = sequelize.define("attribute", {
  key: { type: Sequelize.STRING, allowNull: false, primaryKey: true },
  productSku: { type: Sequelize.STRING, allowNull: false, primaryKey: true },
  value: { type: Sequelize.STRING, allowNull: false },
});


After importing to app.js:
product.hasMany(attribute, { foreignKey: "productSku", sourceKey: "sku" });
attribute.belongsTo(product, { foreignKey: "productSku", targetKey: "sku" });


Explanation:
Product.sku is exported as foreign key to Attibute.productSku. Attribute table has a composite foreign (key + productSku), and a ForeignKey(productSku) from product.sku;