Add example for model relations

[kulakowka]

Can somebody show an example of creating relations between the models?

I want to relate a model Tutorial and Comment. How can i do that?

I make it so:

'use strict'

const Sequelize = require('sequelize')

module.exports = function (sequelize) {
  const comment = sequelize.define('comment', {
    content: {
      required: true,
      type: Sequelize.TEXT,
      allowNull: false
    },
    tutorialId: {
      required: true,
      type: Sequelize.INTEGER
    }
  }, {
    timestamps: true,
    freezeTableName: true
  })

  comment.belongsTo(sequelize.models.tutorial)

  comment.sync({
    force: true
  })

  return comment
}

But sometimes, when i start my application i have error:

Unhandled rejection SequelizeDatabaseError: relation "tutorial" does not exist

I used to work only with mongoDB. But I think I need to learn sequelize.

It would be great if we had more examples that show how to work with the database, how to set up the relationship between models and between services. I read the documentation, but it very much and I'm not like I can not configure it to work properly.

[kulakowka]

It seems I figured.

[ekryski]

This is more of a documentation issue but I've re-opened for visibility and created this issue to track actual progress: feathersjs-ecosystem/docs#51

[DesignByOnyx]

I am running into a similar issue as @kulakowka - I keep getting a SequelizeUniqueConstraintError: Validation Error when starting with an empty database. My code is nearly identical to kulakowka's, except in place of comment.belongsTo(sequelize.models.tutorial) I am doing the following because the tutorial does not exist yet:

const tutorial = sequelize.import( path.join(__dirname, "../tutorial/tutorial-model") );

... I'm not even calling .belongsTo yet - I'm just trying to import the model and I get the error. An example on how to set up relationships before the model is sync()'d would be great.

[silvestreh]

I'm kinda lost here… Disclaimer: I'm a total noob when it comes to SQL and relational databases, I have some experience with Mongo and, like @kulakowka, I want to learn some sequelize.

Say I have a model for a artists and another for events. Events can have many artists associated to it…

module.exports = function(sequelize) {
    const event = sequelize.define('events', {
        date: {
            type: Sequelize.DATE,
            defaultValue: Sequelize.NOW,
            allowNull: false
        },
        title: {
            type: Sequelize.STRING,
            allowNull: false
        }
    }, {
        freezeTableName: true
    });

    setImmediate(() => {
        // This is probably wrong, but otherwise most models are undefined
        // Also this, I believe, runs after event.sync() which might be a problem

        event.hasMany(sequelize.models.artists, { as: 'artists' });
    });

    event.sync();

    return event;
};

It's my understanding that { as: 'artists' } should create a column which can hold many integers (an array maybe?) referring to different artists; yet I fail to see this column showing up on my (empty) table.

Is there something I'm missing? I believe I would need to ALTER the table to add the column, would that happen automagically when I add a new event? I'm… well… confused and lost. I'm asking here because Sequelize's documentation doesn't quite match Feathers services approach.

[lopezjurip]

What's the correct way to do this?

[silvestreh]

@mrpatiwi the problem is using the sync() method right after the model definition. You could run sync only once inside server/src/services/index.js at the end of the file (and don't forget to remove the line that calls this method inside the model definition file)

I opted to create a new file server/src/services/relationships.js where I defined the relationships between models and then ran the sync() method.

EDIT: Please note that my experience with both Feathers and Sequelize is extremely limited and what I did is, in no way, recommended nor endorsed by the Feathers team. Hell, I'm not even sure what I did is considered a best practice or anything like that, it just made sense to me.

[ekryski]

I'm working on an example this evening.

[lopezjurip]

My current approach:

// src/services/organization/organization-model.js

const Sequelize = require('sequelize');

module.exports = function(sequelize) {
  const organization = sequelize.define('organization', {
    name: {
      type: Sequelize.STRING,
      allowNull: false,
    },
  }, {
    classMethods: {
      associate(models) {
        organization.hasMany(models.user);
      },
    },
  });

  return organization;
};

// src/services/index.js

const Sequelize = require('sequelize');

const authentication = require('./authentication');
const user = require('./user');
const organization = require('./organization');

module.exports = function() {
  const app = this;

  const sequelize = new Sequelize(app.get('postgres'), {
    dialect: 'postgres',
    logging: false, // console.log,
  });
  app.set('sequelize', sequelize);

  app.configure(authentication);
  app.configure(user);
  app.configure(organization);

  // Setup relationships
  const models = sequelize.models;
  Object.values(models)
    .filter(model => model.associate)
    .forEach(model => model.associate(models));

  sequelize.sync();
};

[ekryski]

@mrpatiwi solid. Very similar to @DesignByOnyx's solution he put over here.

[wojohowitz]

I would think @mrpatiwi approach is in line with the squelize cli generator, right?
I would personally would like migrations instead of sync().
Leveraging the sequelize cli could take care of that as well as seeders. Could all that be required is to have the generator create a .sequelizerc with the proper paths for config, migrations, and the model locations (noting that the model locations would be the trick as they are in their own service folders)?

[againer]

Thanks @mrpatiwi.

[DesignByOnyx]

FYI - the new generators will build all of the association code for you. Among other things it puts models in their own folder (de-coupled from services) and will generate the code which calls associate() on the model definitions. If you have a project with the old generators, then do not use the new ones. If you are starting a new project, try out the new ones. I have been using them with success. (pre release docs)

$ npm install feathers-cli@pre -g

[ekryski]

Yup @DesignByOnyx is totally right. I think we can actually close this now as that is the recommended way to set up model relations with Sequelize.

[silvestreh]

Will the new generators introduce any issues with existing MongoDB projects?

[frastlin]

Would it be possible to have an example using the new generator of what goes in the associate function in the .model.js files?
My code keeps throwing an odd unnamed error starting in the forEach in sequelize.js.
In the below code, ptest2 belongs to ptest1.
Here are my model files:

ptest1.model.js

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
    const sequelizeClient = app.get('sequelizeClient');
    const ptest1 = sequelizeClient.define('ptest_1', {
    title: {
        type: DataTypes.STRING,
        allowNull: false
    },
        message: {
            type: DataTypes.TEXT,
            allowNull: false
        },
        secret: DataTypes.STRING(100)
    }, {
        hooks: {
            beforeCount(options) {
                options.raw = true;
            }
        }
    });

    // eslint-disable-next-line no-unused-vars
    ptest1.associate = function (models) {
    };
    return ptest1;
};

ptest2.model.js:

const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;
module.exports = function (app) {
  const sequelizeClient = app.get('sequelizeClient');
  const ptest2 = sequelizeClient.define('ptest_2', {
    name: DataTypes.STRING,
    text: {
      type: DataTypes.STRING,
      allowNull: false
    }
  }, {
    hooks: {
      beforeCount(options) {
        options.raw = true;
      }
    }
  });

  ptest2.associate = function (models) {
    ptest2.belongsTo(models['ptest1'])
  };
  return ptest2;
};

[daffl]

models will contain the models keyed by the string used in sequelizeClient.define(name. In your case

  ptest2.associate = function (models) {
    this.belongsTo(models.ptest_1)
  };

[frastlin]

Adding the underline solved the problem!

…

On Tue, Jun 4, 2019 at 10:17 AM David Luecke ***@***.***> wrote: models will contain the models keyed by the string used in sequelizeClient.define(name. In your case ptest2.associate = function (models) { this.belongsTo(models.ptest_1) }; — You are receiving this because you commented. Reply to this email directly, view it on GitHub <#20?email_source=notifications&email_token=ACPEUFJVODVURLJ4IBPQ45DPY2PTJA5CNFSM4B4UAB62YY3PNVWWK3TUL52HS4DFVREXG43VMVBW63LNMVXHJKTDN5WW2ZLOORPWSZGODW5IMWY#issuecomment-498763355>, or mute the thread <https://github.com/notifications/unsubscribe-auth/ACPEUFPUY3LXO7DXCNT5P7LPY2PTJANCNFSM4B4UAB6Q> .

[frastlin]

That underline solved the problem!
Now I am working to get the include hook working. There are two problems I'm having:

1. I have the associated model populated, but I would like it to be in an object rather than an oddly named attribute like "ptest_1.id".
   I get:

   {"id":2,"name":"Cara","text":"hum","createdAt":"2019-06-04T19:15:19.519Z","updatedAt":"2019-06-04T19:15:19.519Z","ptest1Id":1,"ptest_1.id":1,"ptest_1.title":"Stuff to eat","ptest_1.message":"Cheese and potatos","ptest_1.secret":null,"ptest_1.createdAt":"2019-06-04T17:47:11.681Z","ptest_1.updatedAt":"2019-06-04T17:47:11.681Z"}

I would like it to be:

{"id":2,"name":"Cara","text":"hum","createdAt":"2019-06-04T19:15:19.519Z","updatedAt":"2019-06-04T19:15:19.519Z","ptest1Id":1,"ptest_1":{"id":1,"title":"Stuff to eat","message":"Cheese and potatos","secret":null,"createdAt":"2019-06-04T17:47:11.681Z","updatedAt":"2019-06-04T17:47:11.681Z"}}

Does Sequelize or feathers do this through some kind of config setting, or should I do it by looping through the keys of each populated object, and creating the object myself?

2. I need to add an associated model in the hook. Is there any way for me to add the hook to any of my sequelize services and just populate any associated model that may be there? Or if there is more than one associated model, populate all those models? For example, if my entry is:

   {"name":"Sally","carId":3,"houseId":9,"workId":2}

can I populate all those ids? Then can I have a hook that populates any model from any Sequelise service?

(BTW, I would really love if the generator made this include hook when you create a Sequelize model, then add it to any Sequelize service by default or through a prompt. I'm pretty sure getting associations is something just about everyone using Sequelize will do eventually. If they don't, then they could just not use that hook or delete it.)

[perryhoffman]

@frastlin Not sure if you figured either out yet. But for the first question, you need to provide the nest: true option to the Sequelize params. See the docs explaining this hook.

      context => {
        const sequelize = context.app.get("sequelizeClient");
        const { User } = sequelize.models;

        context.params.sequelize = {
          include: [{ model: User }],
          nest: true
        };

        return context;
      }

[frastlin]

That "nest" worked perfectly!
I finally did a systematic search of the Sequelize documentation for "nest", as it was not on any of the pages I had been seeing. I found it on the Model Usage page, where all the syntax that would go into this hook are given. It would help immeasurably if that page could be floating around the include hook section of the readme.
To answer my second question, I need to replace the:

include: [{ model: AssociatedModel }]

with:

include: [{ all: true }]

For future reference, here is my file:

include-hook.js (on find and get hooks):

module.exports = function (options = {}) {
	return async context => {
		if (context.params.query.include) {
			context.params.sequelize = {
				include: [{ all: true }],
				 nest: true
			};
			// delete any special query params so they are not used
			// in the WHERE clause in the db query.
			delete context.params.query.include;
	 }

	 return Promise.resolve(context);
}
};

[frastlin]

OK, now I am having the same problem as @silvestreh and I am unable to add or get hasMany to add any ids to the hasMany list, and the hasMany list is not a list, it's a single object, similar to belongsTo. Are there any examples how to fix this? I followed all the syntax outlined in the manual but I still get "tests":{...null}

Rather than creating a list of 2 entries, I get a repeated object with one entry in each object. This is not what I want at all. I also only seem to be able to add the parent id to the child, I can't add the child ID to the parent. I don't know if this is normal, but it makes associations a little funny.

Here's my code:

module.exports = function (app) {
	const sequelizeClient = app.get('sequelizeClient');
	const ptest3 = sequelizeClient.define('ptest_3', {
		text: {
			type: DataTypes.STRING,
			allowNull: false
		}
	}, {
		hooks: {
			beforeCount(options) {
				options.raw = true;
			}
		}
	});

	ptest3.associate = function (models) {
		    ptest3.hasMany(models.ptest_2, {
		as: 'tests'
	})
	};

	return ptest3;
};

[frastlin]

One more question:
Is there a way to specify belongsTo within the same table? For example, if I have a table that looks like:

const peopleTable = [
	{
		"id": 1,
		"name": "grandpa",
		"parentId": null,
	},
	{
		"id": 2,
		"name": "Mother",
		"parentId": 1,
	},
	{
		"id": 3,
		"name": "Baby",
		"parentId": 2
	}
]

Can I have Sequelize handle this kind of relationship?

When I try doing: ptest3.belongsTo(ptest3), it gives the error:

error: GeneralError: table name "ptest_3" specified more than once

Here's my model file:

// See http://docs.sequelizejs.com/en/latest/docs/models-definition/
// for more of what you can do here.
const Sequelize = require('sequelize');
const DataTypes = Sequelize.DataTypes;

module.exports = function (app) {
	const sequelizeClient = app.get('sequelizeClient');
	const ptest3 = sequelizeClient.define('ptest_3', {
		text: {
			type: DataTypes.STRING,
			allowNull: false
		},
		names: {
			type: DataTypes.ARRAY(DataTypes.STRING)
		},
	}, {
		hooks: {
			beforeCount(options) {
				options.raw = true;
			}
		}
	});

	// eslint-disable-next-line no-unused-vars
	ptest3.associate = function (models) {
		// Define associations here
		// See http://docs.sequelizejs.com/en/latest/docs/associations/
		ptest3.hasMany(models.ptest_2)
		//here is the line that is throwing the error:
		ptest3.belongsTo(models.ptest_3)
		//this throws an error as well:
		//ptest3.belongsTo(ptest3)
	};

	return ptest3;
};

Edit: I figured out that if you do:

ptest3.hasOne(models.ptest_3, {as:"tester"})

rather than

ptest3.belongsTo(ptest3)

it will work.

[frastlin]

@silvestreh This is probably not the best way to do this, but to achieve the has many relationship in a list, I created an after hook that runs on get and find. The hook only runs if a before hook adds a "includes" attribute to context.

function arrayContains(arr, key, value){
	return arr.filter(o=>o[key] === value)
}

module.exports = function (options = {}) {
	return async context => {
		if(!context.include) return context
		const data = context.result.data
		const newData = []
		data.forEach(obj=>{
			const id = obj.id
			const hasManyObj = arrayContains(newData, 'id', id)[0]
			if(hasManyObj){
				const keys = Object.keys(hasManyObj)
				keys.forEach(key=>{
					if(typeof(hasManyObj[key]) === 'object' && !Array.isArray(hasManyObj[key])){
						hasManyObj[key] = Array.isArray(hasManyObj[key]) ? hasManyObj[key] : [hasManyObj[key]]
						hasManyObj[key].push(obj[key])
					}
				})
			} else {
				newData.push(obj)
			}
		})

		context.result.data = newData
		return context;
	};
};

[Orbitkorbi]

@frastlin Are you now more confident how the association/relation stuff works? I have serious trouble in understanding what I am doing wrong. I set up a test repo with my code https://github.com/Orbitkorbi/feathers-sequelize-test hoping you may have the one or other minute to have short look into it. Thanks