Uniqueness constraint on multiple attribute with WaterlineJS

Question

I've created the following join table, to link users and game model.

I would like to create an uniqueness constraint on (user,foot).

Here is my code:

module.exports = {

  attributes: {
    foot:{
        type: 'integer',
        foreignKey: true,
        references: 'foot',
        on: 'id'
    },
    user:{
        type: 'integer',
        foreignKey: true,
        references: 'user',
        on: 'id'        
    },
    statut:{
        type: 'integer', // 0 invited, 1 playing, 2 denied, 3 organisator
        defaultsTo: 0
    }
  }
};

I'm using "sails-mysql" adapter.

Does waterline handle that or should i do it directly in my DB?

Thank you !

Answer 1

Had not done anything like this before. Your problem seemed interesting to me. So I researched a bit. There are multiple issues for this problem in github repository of waterline. One is issue#244 and another is issue#221. None of them has any indication of solid solution to your problem and I think it still is impossible to do with Waterline ORM.

But as you are using mysql, you can do it by executing raw SQL query from your database adapter. In your config/bootstrap.js you can write script to check if this constraint is available in your database. If it's available then you will not do anything but if it's not available then you will create new constraint to enforce the uniqueness.

I think this is not the solution you were looking for but I am afraid this is the best solution you have at this moment. Hope it helps.