How to set Primary keys in Sails js waterline database relationships

Question

I have been studying relationships with sails JS waterline database from the official documentation. I however been having difficulty understanding how I am supposed to set my foreign keys just like I do in normal mysql relationships. Please note that I have read the documentation here https://sailsjs.com/documentation/concepts/models-and-orm/associations before asking this question.

Let's say I have a model PersonalInfo.js

module.exports = {

  attributes: {

    fullName:{
      type: 'string',
      required: true
    },

    phone:{
     type: 'string',
     required: true
   },

   location:{
     type: 'string',
     required: true
   },

   age:{
     type: 'integer',
     required: true
   },

   email:{
     type: 'string',
     required: true
   },

   gender:{
    type: 'string',
    required: true
  },

  userId:{
    type: 'integer',
    required: true,
  }
  
  },

};

And I have another model Archived.js which looks like this

module.exports = {

  attributes: {
    userId: {
      type: 'number',
      required: true,
      //unique: true,
    },
    comment:{
      type: 'string',
      required: true
    },
    createdBy:{
      type: 'number',
      required: true
    }
    
  },

};

An archived item has a personalInfo. Knowing fully well that both models contain userId property, I want to fetch archived items with the related personalInfo like this, how do I relate the primary keys?

var archived = Archived.find().populate('personal');

Answer 1

By default sails will generate primary key id if you don't specify any.

If you want custom data as your primary key, you can override the id attribute in the model and give a columnName

id: {
  type: 'string',
  columnName: 'email_address',
  required: true
}

You can then find a record using:

await User.find({ id: req.param('emailAddress' });

Reference

In your case, it seems like each archived has a personalInfo. So that's one to one from archived side, but, one to many from personalInfo side. To model these relationships, in sails you can do something like:

personalInfo.js

module.exports = {

  attributes: {

    fullName:{
      type: 'string',
      required: true
    },

    phone:{
     type: 'string',
     required: true
   },

   location:{
     type: 'string',
     required: true
   },

   age:{
     type: 'integer',
     required: true
   },

   email:{
     type: 'string',
     required: true
   },

   gender:{
    type: 'string',
    required: true
  },

  userId:{
    type: 'integer',
    required: true,
  },
  archives: {
    collection: 'archived',
    via: 'info'
  }

  },

};

archived.js

module.exports = {

  attributes: {
    userId: {
      type: 'number',
      required: true,
      //unique: true,
    },
    comment:{
      type: 'string',
      required: true
    },
    createdBy:{
      type: 'number',
      required: true
    },

    info: {
      model: 'personalinfo'  // sails works with small cases internally for models
    }

  },

};

Once you do this, creating an archive would be:

await Archive.create({
  ...

  // Set the User's Primary Key to associate the info with the archive.
  info: 123
});

Now you would finally be able to populate the info while querying.

var archived = Archived.find().populate('info');