Using MongoDB and Sails.js with a basic one-to-many association

We are playing around with writing a Node.js app. We decided to use the Sailsjs framework around the Express engine. The basic app we are creating will be a basic one where we record ideas. We will use two simple models: idea and user. An idea will be associated with one user and a user can be associated with many ideas. This is a one-to-many relationship in database-speak.

Our idea will have at least two fields we need to record: title and detail. Our user will have at least the following fields: email and password.

A word about _id and id in regards to MongoDB and Waterline.js

Waterline.js is a generic ORM (Object Relationship Mapping) framework built into sails.js that allows us to define our models and query our databases without having to know or deal with what type of database is underneath (mySQL, MongoDB, etc). So our app could easily be switched to use mySQL if we wanted without changing our models or queries…for the most part…at least in theory. That all said.. we did have issues when we changed from the default file-based database built in Sails.js to MongoDB. Most of the problems were related to the id field. Most databases uses a field that is the primary key, a unique identifier for a record. MongoDB is no exception and uses a field it creates automatically named ‘_id’. It is not an integer nor a string but a type called ObjectId. When you query records using waterline, this field changes it’s name to ‘id’ and it’s type becomes of type string. But if you look at your records in MongoDB using some MongoDB client like Robomongo, the records show a field named ‘_id’ and it’s of type ObjectId. For example:

{
    "_id" : ObjectId("57bc96ec01653e3414d9dd38"),
    "title" : "Amazing title",
    "detail" : "Lots of detail here",
    "createdAt" : ISODate("2016-08-23T18:33:16.710Z"),
    "updatedAt" : ISODate("2016-08-24T14:08:57.913Z")
}

{
  "id": "57bc96ec01653e3414d9dd38",
  "title": "Amazing title",
  "detail": "Lots of detail here",
  "createdAt": "2016-08-23T18:33:16.710Z",
  "updatedAt": "2016-08-24T14:08:57.913Z"
}

We found out the hard way that as long as you don’t define the id field in your model (we’ll get to defining the model next), Waterline.js will take care of dealing with the id field for you.

‘id’ in model

We started out defining ‘id’ in the model and giving it a type of ‘string’ but we ran into errors when we tried to query for a record using this string. Once we changed id to type ‘objectid’, this problem went away. Later, when we tried to associate an idea to a user (user.ideas.add(idea); user.save();), we would get the error: Invalid attributes sent to User: id [object Object]. The model’s validation process was complaining about ‘id”s type. Once we took out the definition of ‘id’ in the model, everything just worked.

Defining your models

Let’s define our models. This is easy database design that lets Sails.js actually configure the structure of the database for us. On a command line, we can have Sails.js create our model files for us by using the command sails generate api idea and sails generate api user. This creates our model files under the directory projectname/api/models. Now we have two js files: Idea.js and User.js. It is customary to use uppercase for models. Here’s our model files in full with comments added to explain more.

module.exports = {

  attributes: {
    title: {
      type: 'string'
    },
    detail: {
      type: 'string'
    },
    // This defines one-half of our association between idea and 
    //  user.  This is the 'one' side of the 'one-to-many' 
    //  relationship.  An idea will be associated with only one user.
    userId: {
      model: 'user'
    }
  }
};

var bcrypt = require('bcryptjs');

module.exports = {

  attributes: {
    name: {
      type: 'string'
    },
    email: {
      type: 'string',
      unique: true,
      required: true,
      contains: '@'
    },
    password: {
      type: 'string',
      minLength: 6,
      required: true
    },
    // This defines the other half of our association 
       with ideas.  This is the 'many' side.
    ideas: {
      collection: 'idea',
      via: 'userId'
    },
    // This tells Sails.js anytime we call the toJSON function to return 
    //   the user object, take out the password field.  We don't want that 
    //   flying around the Internet!
    toJSON: function() {
      var obj = this.toObject();
      delete obj.password;
      return obj;
    }
  },
  
  // This is a hook that is called evertime a user is created.  It will hash 
  // the password so the password isn't stored insecurely in the database.

  beforeCreate: function(user, cb) {
    bcrypt.genSalt(10, function(err, salt) {
      bcrypt.hash(user.password, salt, function(err, hash) {
        if (err) {
          console.log(err);
          cb(err);
        } else {
          user.password = hash;
          cb();
        }
      });
    });
    
  },
};

If you are interested in setting up authentication with Sails. This article was most helpful for us.

Our models in the database vs. results from a query

We’ve already shown the difference with the ‘id’ field now lets see the difference when we have this one-to-many association. When we query for a user in our Sails.js app, we will get the user fields plus an array of ideas associated with that person (as long as the query contains the command: .populate('ideas')).

{
    "email": "john@doe.com",
    "createdAt": "2016-08-19T12:43:39.667Z",
    "updatedAt": "2016-08-23T19:13:04.899Z",
    "id": "57b6fefb7c1af13f114448d5",
    "ideas": [
      {
        "title": "Amazing title",
        "detail": "Lots of detail here",
        "createdAt": "2016-08-23T18:33:16.710Z",
        "updatedAt": "2016-08-24T14:08:57.913Z",
        "userId": "57b6fefb7c1af13f114448d5",
        "id": "57bc96ec01653e3414d9dd38"
      },
      {
        "title": "A lot of stuff",
        "detail": "This is the detail",
        "createdAt": "2016-08-23T18:35:54.545Z",
        "updatedAt": "2016-08-23T18:35:54.790Z",
        "userId": "57b6fefb7c1af13f114448d5",
        "id": "57bc978ae8afa07a14ab169f"
      }
    ]
  }

If we go and look directly at how this user record is saved in MongoDB we will see that it looks like the following:

{
    "_id" : ObjectId("57b6fefb7c1af13f114448d5"),
    "email" : "john@doe.com",
    "password" : "$2a$10$TuBYInGCx1mngv25mHzGZ.wfAWJypfmoeTM0N43BJrPKLY57gzrYa",
    "createdAt" : ISODate("2016-08-19T12:43:39.667Z"),
    "updatedAt" : ISODate("2016-08-23T19:13:04.899Z")
}

There is no data here to show the relationship between user and idea in the user record. Now let’s see what a query in Sails.js for an idea returns:

  {
    "title": "Amazing title",
    "detail": "Lots of detail here",
    "createdAt": "2016-08-23T18:33:16.710Z",
    "updatedAt": "2016-08-24T14:08:57.913Z",
    "id": "57bc96ec01653e3414d9dd38"
    "userId": {
      "email": "john@doe.com",
      "createdAt": "2016-08-19T12:43:39.667Z",
      "updatedAt": "2016-08-23T19:13:04.899Z",
      "id": "57b6fefb7c1af13f114448d5"
    }
  }

In MongoDB the idea record will look like this:

{
    "_id" : ObjectId("57bc96ec01653e3414d9dd38"),
    "title" : "Amazing title",
    "detail" : "Lots of detail here",
    "createdAt" : ISODate("2016-08-23T18:33:16.710Z"),
    "updatedAt" : ISODate("2016-08-24T14:08:57.913Z"),
    "userId" : ObjectId("57b6fefb7c1af13f114448d5")
}

This looks like it is supposed to. The ‘userId’ field provides the reference in an idea record of who the idea belongs to.

Creating our CRUD actions for the Idea model

The rest of this article will be showing our controller actions for creating, reading, updating and deleting ideas in our Sails.js app. Thankfully, waterline supports promises which we will be using (Otherwise you can use the ‘.exec(callback function here)’ syntax). We will also use the new ES6 arrow function syntax to reduce code clutter. Node.js fully supports this already, you don’t need to ‘babel’ or anything.

Most of our actions here will be using POST instead of GET. So the data submitted will be in the request.body object. We are also using passport.js for authentication which puts the logged in user in request.user. This is put in the request object by passport middleware before any of our actions are called.

Create

This action looks to see if ‘title’ or ‘detail’ was posted in the request. It assigns a couple local variables with those values or assigned them as undefined if not posted. It then checks to see if the user is logged in by checking for the existence of the user object in the request object.

In the future, we will use Sails.js policies to check for a logged in user so this if statement will not be needed later.

Next it makes sure either ‘title’ or ‘detail’ contain data. If all that passes, then it creates the idea using the posted data and adds the idea to the logged in user object. Lastly, we save the user object. It does seem counter intuitive that we are saving the user object when its data in the actual database isn’t changing. What it actualy does is add the ‘userId’ field to the idea record and gives that field the value of the user’s id. The database record being changed is the idea record.

    addIdea: function (req, res) {  /* POSTed data */
        var title  = req.body ? req.body.title : undefined,
            detail = req.body ? req.body.detail : undefined;
        //technically - once policies in place, this if can be removed as this action couldn't be called unless the user is logged in.
        if ( ! req.user ) {
            return res.badRequest("Cannot add idea without a logged in user");
        } else if ( ! title && ! detail) {
            return res.badRequest("Need a title or detail to create idea");
        } else {
            Idea.create({ title: title || '', detail: detail || ''})
                .then( (idea) => {
                    req.user.ideas.add(idea);
                    req.user.save()
                        .then ( () => res.json(idea) )
                        .catch( (err) => { res.serverError(err) });
                })
                .catch( (err) => res.serverError(err));
            }
        }

Read

This is two actions. One action will look for the id of an idea in the posted data and return a single idea. The other action will query all ideas and produce a list. It actually uses GET and reads in any query variables added to the URL. This is stored in the query object which is in the request object, request.query. Our query variables here will help us with pagination in the future. We look to see if they exist in the query object and if they do place them in the locals object in the response object, response.local. Anything in the locals object is available in any view you create in Sails. We use the find() method and apply paginate() to give us a subset of the ideas and populate('userId') to fill in the associated user model in each idea record.

    getIdea: function(req, res) {
        var ideaId = req.body ? req.body.ideaId : undefined;
        if (! ideaId) {
            return res.badRequest("Need id of idea");
        } else {
            Idea.findOne( { id : ideaId }).populate('userId')
                .then( (idea) => {
                    if(! idea) { 
                        return res.notFound( "Idea not found");
                    } else {
                        res.locals.idea = idea;
                        return res.json(idea);
                    }
                })
                .catch((err)=> res.serverError(err));
        }

        
    },

    getIdeas: function(req, res) {
        res.locals.paged = req.query.paged || 1;
        res.locals.posts_per_page = req.query.posts_per_page || 20;
        Idea.find()
            .paginate({page:res.locals.paged, limit: res.locals.posts_per_page})
            .populate('userId')
            .then( function(ideas) {
                if (! ideas) {
                    return res.notFound("Ideas not found");
                } else {
                    return res.json(ideas);
                }
            })
            .catch((err)=> res.serverError(err));
        
    }

Update

This action will update an existing idea with new values of any field. It will need the id of the idea along with any field it would like changed. This updateIdea action will first look for and create local variables for posted data. It then checks to confirm an idea id was passed in. If not, then return. It then checks to see if any fields are posted. If there are none, we have nothing to update. If all that passes, then it finds the idea using the ideaId. If it is not found, then return. It then checks to confirm the logged in user is the same user who created the idea. (We don’t want all users to be able to change other user ideas). If all is good, it changes the idea fields and saves it to the database. Once it is saved, it returns the json of the new idea.

    updateIdea: function(req, res){
        var ideaId = req.body ? req.body.ideaId : undefined,
            title  = req.body ? req.body.title : undefined,
            detail = req.body ? req.body.detail : undefined;
        if ( ! ideaId ) {
            return res.badRequest("Need id of idea to update");
        } else if ( ! title && ! detail) {
            return res.badRequest("Need a title or detail to update idea");
        } else {
            Idea.findOne( { id : ideaId })
                .then( (idea) => {
                    if( ! idea ) {
                        return res.badRequest("idea not found");
                    } else {
                        if (idea.userId != req.user.id ) {   //TODO OR logged in user does not have admin role
                            return res.forbidden("Idea not yours to update");
                        }
                        idea.title = title?title:idea.title; //if title not undefined, then give it the value of title else give it its existing value
                        idea.detail = detail?detail:idea.detail;
                        idea.save()
                            .then ( () => res.json(idea) )
                            .catch((err)=>res.serverError(err));
                    }
                })
                .catch((err)=>res.serverError(err));
        }
    }

Delete

This action will remove an idea. It will use an id of an idea that is posted to it. First it checks to make sure an id was passed in. If so, then it looks for that idea. If is not found, then it returns. If the userId on the idea is not equal to the user id of the user logged in, then it returns. Otherwise, then it uses the destroy() method to remove the idea. The idea in the then function will be an array of deleted ideas. We check the array’s length to make sure it’s not zero which would indicate nothing was deleted.

    removeIdea: function(req, res){
        var ideaId = req.body ? req.body.ideaId : undefined;
        if (! ideaId) {
            return res.badRequest("Need id of idea");
        } else {
            Idea.findOne( { id : ideaId } )
                .then( (idea) => {
                    if (!idea) {
                        return res.notFound("Idea not found");
                    } else if (idea.userId != req.user.id ) {   //TODO OR logged in user not admin role
                        return res.forbidden("Idea not yours to remove");
                    } else {
                        Idea.destroy( { id : ideaId })
                            .then( (idea) => {
                                if(idea.length === 0) {
                                    return res.notFound("Idea not deleted");
                                }
                                return res.json(idea);
                            })
                            .catch((err)=> res.serverError(err));
                    }
                })
                .catch((err)=> res.serverError(err));
        }
    }

Those are the main CRUD functions. One can alter how the data is given to the action: query, form, or json. And one can alter how it is output: json or html (render a view). Overall, we hope this helps one understand some basic Sails.js / Waterline.js querying with an association.

Comment to let me know if this article was helpful.