SailsJS is awesome - it has many features and is mostly fun to work with. It makes writing realtime applications easier. But there's one thing I don't like about it: it's built-in ORM, called Waterline.

Through assocations are kinda silly

Imagine the following scenario: you've got a database with projects and users. To know which user belongs to which project you've also got an association table called contributors. Let's keep these tables as simple as possible:

projects:
	id 				(int, auto increment)
	name			(string)

users:
	id 				(int, auto increment)
	name 			(string)

contributors:
	id 				(int, auto increment)
	project 		(int)
	user     		(int)
	project_leader 	(boolean)

When querying your projects it's likely that you want the response to look like this:

[
	{
		"name": "The first project",
		"contributors": [
			{
				"name": "UserA",
				"project_leader": "false"
			},
			{
				"name": "UserB",
				"project_leader": "true"
			}
		]
	}
]

That would be super awesome; we'd get all the projects with all the contributors and get to know if they're project leader or not.

But: this is not possible in Waterline. Why?

Sails through associations do not support additional information on populated tables, as stated in the note at the bottom of the docs:

Currently if you would like to add additional information to the through table it will not be available when calling .populate. To do this you will need to query the through model manually.

I'm not using a relational database to query things manually.

Deep populations are not possible

Another big flaw is that there's no possibility for "deep population". What is "deep population"?

Let's extend the example above and add a customers table.

customers:
	id				(int, auto increment)
	name			(string)

Now we want to get the same response as above - but grouped by customers. Hence it should look like this:

[
	{
		"customer": "John Bob Joe",
		"projects": [
			{
				"name": "The first project",
				"contributors": [
					{
						"name": "UserA",
						"project_leader": "false"
					},
					{
						"name": "UserB",
						"project_leader": "true"
					}
				]
			}
		]
	}
]

Sadly, this is also not possible with Waterline. You cannot populate the contributors attribute, since it would be a "deep population". Speaking in code it would look like this:

Customers.findAll().populate('projects').populate('projects.contributors').exec()

But again, this is not possible. There's no way to populate projects.contributors at this point, which means you need to manually query contributors and attach them to your result - which sucks.

But why?

According to this GitHub PR it's most likely due to Sails' design philosophy. Waterline can be used for relational databases and non-relational databases - which causes some problems when it comes to certain features.

To be honest I dont completely understand why you need to put down such important features (I mean, come on, associations of any kind should be absolutely no problem for any ORM) just for the sake of support. This makes it kinda useless in at least one supposed way.

So... what?

Two possible solutions: the first one is rather simple by using raw statements. This makes the usage of an ORM kinda useless, but well, we're talking about a workaround here.

The second solution is a bit harsher: getting rid of Waterline. For example you can go with Sequelize, another ORM for NodeJS.

Using Sequelize within SailsJS is pretty easy; there's a hook, sails-hook-sequelize, for it. Just install the hook and disable waterline.

But should you do that? Probably, yes. I'm not saying Waterline is a bad ORM, but it's kinda useless if you're working with relational databases and a mediocre complex database.

Honestly I guess it'll be better sometime in the future. The framework - and its ORM - are still pretty young.