Feathers.js / Sequelize -> Service with relations between two models

Question

I've got feathers.js functioning with mysql via sequelize. This is working, I can collect data from tables. Next step is to define 'joins' in the models.

I have a table with a column 'status_id' and 'country_id'. These columns reference to an id in a metadata table. In SQL I would right:

SELECT status.description, country.description, detail 
FROM details 
INNER JOIN metadata status 
    ON (details.status_id = status.id AND status.type = 'status' 
INNER JOIN metadata country 
    ON (details.country_id =country.id AND country.type = 'country')

This metadata table won't be big in this case so hence this approach. It does give flexibility I need.

What do I need to do to make this in feathters.js?

Answer 1

Having helped a lot of people with this same issue, I have learned that the solution comes in two parts:

#1 - embrace the ORM
Most of people's problems come from a lack of understanding of sequelize. In order to help you, you will first need to have an understanding how sequelize associations work and how to perform queries using the "include" option (aka "eager loading"). I recommend reading all of the contents of those links a couple times, and then one more time for good measure; this is the steepest part of the sequelize learning curve. If you have never used an ORM, let it do a lot of the heavy lifting for you!

#2 - setting sequelize options from a feathers hook
Once you understand how the "include" option works with sequelize, you will want to set that option from a "before" hook in feathers. Feathers will pass the value of hook.params.sequelize as the options parameter for all sequelize method calls. This is what your hook might look like:

// GET /my-service?name=John&include=1
function (hook) {
   if (hook.params.query.include) {
      const AssociatedModel = hook.app.services.fooservice.Model;
      hook.params.sequelize = {
         include: [{ model: AssociatedModel }]
      };
      // delete any special query params so they are not used
      // in the WHERE clause in the db query.
      delete hook.params.query.include;
   }
   return Promise.resolve(hook);
}

Underneath the hood, feathers will call your models find method sort of like this:

// YourModel is a sequelize model
const options = Object.assign({ where: { name: 'John' }}, hook.params.sequelize);
YourModel.findAndCount(options);

Noteworthy:
The old v1.x feathers generators (before March 2017) do not generate code which is friendly for sequelize. This has been fixed in the new v2.x generators. If you are pretty far into your project from before March 2017, then do not use the new generators. Please join the Slack Channel and join the sequelize room for help. I keep an eye on things there and can help you. If you just started your project and haven't gotten very far, then I highly suggest starting fresh with the new generators. Run this command (and follow these instructions):

$ feathers --version              # see what version you are using
$ npm install -g @feathersjs/cli    # install latest version of the CLI

Answer 2

As of v4 of Sequelize, the classMethods property has been removed See this reference. This means that the example in @Edgar's answer will not work.

The associate method must be added directly to the model, rather than being wrapped in a classMethods property. From the Sequelize docs:

Previous:

const Model = sequelize.define('Model', {
    ...
}, {
    classMethods: {
        associate: function (model) {...}
    },
    instanceMethods: {
        someMethod: function () { ...}
    }
});

New:

const Model = sequelize.define('Model', {
    ...
});

// Class Method
Model.associate = function (models) {
    ...associate the models
};

// Instance Method
Model.prototype.someMethod = function () {..}