« Back to home

Introduction to Sails.JS ORM queries, part 2

In my previous post, I wrote about Sails.JS ORM which includes model definitions, joins, attributes and such. In this post, I will go over some simple and more advanced queries that will use the previous examples as a basis for our ORM queries. For a more comprehensive list of queries, check out the GitHub documentation for details.

Sails.js uses Waterline out of the box as its ORM (object relational mapper) and it's basically an object-based way of querying the database. We created the models with a set of attributes, types, etc so it can be used as a 1:1 object representation of table/document in our database. The attributes are used to specify the name of the column/field, length and what type of data it can hold.

For our example, we will be using MongoDB as the database (each database implementation has its own unique of types and querying mechanism) but the ORM allows us to not care about the underlying the technology. Just take note that there might be some slight variance in how certain things are handled.

Basic queries

By default, querying is case-insensitive unless you use the query method. The main keys used to perform queries are where, skip, limit, and sort.
For example, you can use

User  
    .find({ where: { name: 'Dennis' }, sort: 'name desc' });

Or use the short-hand

User  
    .find({ name: 'Dennis'})
    .sort('name desc');

From the previous example, you noticed that you can perform method chainining which performs additional functions on previous result sets. If you wish to omit the key where, the alternative is to use the method chaining approach and that's just a matter of preference.

Sort, limit, skip

In addition to where, additional query options can be used to in order to further filter down the result set and paginate.
For instance:

User  
    .find({ limit: 10, skip 5 })
    .sort('name desc')

The query limits the returned recoords to 10, skips 5 records, as well as sort it by name. As an alternative, you can also write the same query using the "helper" method approach which can be written as follows.

User  
    .find({ name: 'Dennis' })
    .paginate({ page: 1, limit: 10 })

It queries the "User" column/document that has the name of Dennis and only returns 10 records found on the 1st page.

Advanced queries

In this next section, we'll go over some of the more advanced features when working against the database records.

Create, update, delete

Creating a new record is as easy as simply passing in the object and calling exec() to perform the insert. The exec takes in a callback that returns an error as the first parameter (if any) and the new record as the second parameter. Always check if an error is not a null and return something meaningful to the user or log if necessary.

Create
User  
    .create({ name: 'Dennis', age: 34 })
    .exec(function(err, user){
        if (err) return res.negotiate(err); // or do something with it
    });

You can also use findOrCreate which performs a check if a record exists or not and performs a create if a record wasn't found

User  
    .findOrCreate({ name: 'Dennis' })
    .exec(function(err, user){
        // user will be either the existing or new record
    });
Update

Updating is a multi-process which involves looking up the document by its index and performing an update on the returned data. The example below uses the findOneById() which is a useful helper by Waterline to quickly find a single record by id.

User  
    .findOneById(1234)
     .exec(function(err, user){
        user.name = 'Dennis';
        user.age = 34;

        user.save(function(err, savedUser){
        });
    });
Delete

Deleting or "destroying" a record is similar to find where you supply the object criteria of the record(s) that you want deleted. In addition, you need to also use exec to execute the delete and perform the operation. The exec accepts a callback that has 1 parameter on whether the operation succeeded.

User  
    .destroy({ name: 'Dennis' })
    .exec(function(err){
        if (err) return res.negotiate(err);
    });
Populate

Another useful function that comes with Waterline is the populate method which basically includes additional objects that are associated with the queried document.

For example, let's say that you have the model User.js and UserFollower.js that has the following definition.

// User.js
module.exports = {  
    attributes: {
        name: {
            type: 'string'
        },
        followers: {
            collection: 'UserFollower',
            via: 'user'
        }
    }
}
// UserFollower.js
module.exports = {  
    attributes: {
        // Reference to the user object
        user: {
            model: 'User'
        }
    }
}

The definition above for the UserFollower essentially includes the ObjectId which is a property that references the id for the user. What this means is that, when you query the User document, you have the option of including any of the other documents that is referenced which includes UserFollower.

If you need to include additional objects as part of the query, you can use populate() to include the object as part of the result set.

var user = User  
            .findOne({ id: 1234})
            .populate('followers');

This concludes this post and covers 70-80% of the standard operations and queries that you will encounter in the wild. There's tons of other Waterline functionalities so be sure to browse through the documentation and experiment with some of the ORM queries that I have mentioned. If you have any suggestions on which topic you'd like to see next (or perhaps a continuation) of this write-up, please feel free to comment or email me. Thanks for reading.

Comments

comments powered by Disqus