Achieve Single Table Inheritance with Sequelize

Question

Is there a way to use sequelize to create single table inheritance?

I would like to have a STI for a Purchase and PartialPurchase model where I would have a type field which would be "Purchase" or "PartialPurchase" and classes Purchase and PartialPurchase which would each inherit from an Operation class.

I don't see this as supported by sequelize, but is an implementation possible?

Answer 1

I think this functionality is not supported yet (09/2021). But, I am working now in a solution for this. The following example resolves two needs:

1. We have two entities in the same table (inheriting and adding new attributes) and;
2. There's a way for we have common attributes for all models:

const ModelDiscriminators = {
  Dish: 1,
  Item: 2,
  ItemWithEmail: 3
}

//Basic field attributes for all entities
const BasicModelAttributes = { 
  id: { type: DataTypes.INTEGER, primaryKey: true, autoIncrement: true } 
};

//Table dish have id, name, veg, discriminator attributes
const Dish = sequelize.define('dish', Object.assign( {}, BasicModelAttributes, {   
  name: { type: DataTypes.STRING },
  veg: { type: DataTypes.BOOLEAN },
  discriminator: { type: DataTypes.INTEGER, defaultValue: () => ModelDiscriminators.Dish }
} ) );

//Two entities sharing same table. Table Item has id, name, type, discriminator e email(see model ItemWithEmail) atributes
//Model Item
const Item = sequelize.define('item', Object.assign( {}, BasicModelAttributes, {   
  name: { type: DataTypes.STRING },
  type: { type: DataTypes.STRING },
  discriminator: { type: DataTypes.INTEGER, defaultValue: () => ModelDiscriminators.Item }
} ) );

//Model ItemWithEmail inherits all Item attributes and define a new one: e-mail
//We use the discriminator attribute to query the right records when we make a query or .findAll(+where)
const ItemWithEmail = sequelize.define('item', Object.assign({}, Item.rawAttributes, { 
  email: { type: DataTypes.STRING },
  discriminator: { type: DataTypes.INTEGER, defaultValue: () => ModelDiscriminators.ItemWithEmail }
} ) );

I think that this approach could be a default way supported by Sequelize. We could have a inherits relation, so we could write:

ItemWithEmail.inherits( Item )

Any doubt, I would be glad to help you again. heltongoncalves@gmail.com

Answer 2

Hey so I know you didn't get an answer to this, in a reasonable time, but you can use a type column that's an enum in your db like you said and then use sequelize's scope feature. Scope's work in everything from queries to model definitions and associations. It works out pretty well. I implemented something similar for a messages table with requests and responses.

module.exports = function(sequelize, DataTypes) {
  var Message = sequelize.define('Message', {
    id: {
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
      type: DataTypes.INTEGER,
    },
    type: {
      type: DataTypes.ENUM('REQUEST', 'RESPONSE', 'FILE'),
      allowNull: false
    },
    text: {
      type: DataTypes.TEXT,
      allowNull: false,
    },
    state: {
      type: DataTypes.JSONB,
      allowNull: true,
    },
    userTeamId: {
      type: DataTypes.INTEGER,
      allowNull: false,
    }
  }, );
  Message.associate = (models) => {
    Message.belongsTo(models.UserTeam, { foreignKey: 'userTeamId', targetKey: 'tag'})
  }
  return Message;
};

And then this is how I do associations.

module.exports = function(sequelize, DataTypes) {
  var UserTeam = sequelize.define('UserTeam', {
    tag: { type: DataTypes.UUID, allowNull: false, unique: true},
    active: { type: DataTypes.BOOLEAN, defaultValue: false },
    activationNonce: { type: DataTypes.STRING },
    UserId: { type: DataTypes.INTEGER, },
    TeamId: { type: DataTypes.INTEGER, },
    type: DataTypes.ENUM('OWNER', 'ADMIN', 'MEMBER', 'GUEST'),
  },);
  UserTeam.associate = (models) => {
    UserTeam.hasMany(models.Message, { as: 'responses', foreignKey: 'userTeamId', sourceKey: 'tag', scope: {type: 'RESPONSE'}})
    UserTeam.hasMany(models.Message, { as: 'requests', foreignKey: 'userTeamId', sourceKey: 'tag', scope: {type: 'REQUEST'}})
    UserTeam.hasMany(models.Message, { as: 'files', foreignKey: 'userTeamId', sourceKey: 'tag', scope: {type: 'FILE'}})
    UserTeam.hasOne(models.Team, {foreignKey: 'id', sourceKey: 'TeamId'})
    UserTeam.belongsTo(models.User, {foreignKey: 'UserId', sourceKey: 'id'})
  }
  return UserTeam;
};

The nice thing about the associations is with that you can do things with an instance of UserTeam like 'userteam.getFiles()' to get all associated files.