Tag

orm

Browsing

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.

I have been working with Sails.JS for a little while now and would like to share a bit of information on how to use the Models and Waterline/ORM (Object Relational Mapper) when working with your database. An ORM is basically an API to access your database and perform queries against it. Waterline as an ORM has built-in adapters that allows you to use MS SQL Server, MongoDB, Redis among a host of others. It simplifies the process of working with a database since you’re using the same API regardless of what database you’re using (see Waterline on GitHub to see if there’s an adapter available for your database).

You can use Waterline in any flavor of Node.JS but in this post, I’ll be discussing Waterline in the context of Sails.JS although the implementation and concepts are pretty much the same. The adapter should not be relevant in most cases but certain databases have other functionalities that other databases doesn’t so this is something that you need to be aware of. A simple example of this would be if you’re using a relational database such as SQL Server that allows you to add precision or size to the column property (ie. nvarchar(128).

{
    name: { 
        type: 'string',
        size: 128
    }
}

To keep things simple, I’m also going to use from the NoSQL perspective as the context of the post although like I have mentioned, the examples will handle most cases and scenarios.

Models

A model essentially describes a table/collection in your database and makes up the definition of properties or attributes and what it can contain. Model usually maps to your database table or collection. By having defined models, you are adding constraints on what properties/fields that can be saved.

Each model consists of one or more attributes which can be of type string, int, etc. (see more below). A model is synonymous with Collection as each collection can contain many Models.

Globals

All models assuming that they’re stored (by convention) where they need to be, is accessible globally by default. A model such as User that is in /api/models/User.js can be accessed anywhere simply by doing User.findById(1). This Sails.JS feature can be configured to be turned off in the /config/globals.js.

Attributes

Here’s an extensive list of all the attributes that you can use but I’d like to go over some basics. The most common types that you’ll probably going to be using are string, datetime, email, boolean, integer, array and an object (I’ll discuss this below when I go over creating relationships).

Let’s say that we have a user registration system where we need to store some basic user information. You can define a User model such as:

model.exports = {
    name: {
        type: 'string',
        required: true
    },
    lastLoggedIn: {
        type: 'datetime',
        defaultsTo: new Date()
    },
    favoriteNumbers: {
        type: 'array'
    },
    email: {
        type: 'email',
        unique: true
    }
}

I have included a variety of options so you can see what is possible and give you a sense of what that can look like.

The name property is set to a type of "string" which allows all character types that is set to required. The lastLoggedIn is of a "datetime" and defaults to current date if not provided. The favoriteNumbers property is set to an array which basically allows you to pass in array. Most of the property types are very basic and determined by your applications requirements including defaults, etc.

The email property in this case is of type "email" and is set to unique (meaning an error will be thrown if you insert the same email twice). Being an "email" type validates that this the string passed in is in the form of an email address but under the covers, it’s still a "string" type.

If you’re working against a NoSQL database backend, setting the property as unique isn’t enough (the only thing that is unique is its id object, so it has to be configured in the /config/bootstrap.js to ensure that the database indeed does not allow duplicate email by (see code below).

// Bootstrap.JS
module.exports.bootstrap = function(cb) {
  User.native(function(err, collection) {
    collection.ensureIndex('email', {
      unique: true
    }, function(err, result) {
      if (err) {
        sails.log.error(err);
      }
    });
  });

  // Trigger the callback to finish up the bootstrap process
  cb();

  _.extend(sails.hooks.http.app.locals, sails.config.http.locals);
};

Next up, I’d like to go over the database relationships as this is essential in more complex applications.

1-to-Many Relationship

NoSQL databases in general are document based so you can essentially store all data that pertains to that collection in a single document (nested data). There’s also scenarios where having your data in separate documents might be a good idea depending on your application’s needs. Storing data in different documents can be a good idea if you do a lot of "writes" but if your application is mostly "reads" then it makes more sense to go with a single document approach.

Since we’re in discussion of relationships, we will work with an example that consists of multiple models/collections. I’ll go over an example of 1-to-many relationship then further extend the example into the many-to-many relationship type.

For example:
A model User has UserTypes associated with it.

You can build out your models such as:

// User.JS
module.exports = {
    identity: 'User',
    attributes: {
        name: {
            type: 'string'
        }
        userTypes: {
            collection: 'UserTypes',
            via: 'user'
        }
    }
}
// UserTypes.js
module.exports = {
    identity: 'UserTypes',
    attributes: {
        user: {
            model: 'User'
        },
        userType: {
            type: 'string'
        }
    }
}

If you look at the defined models, User has an identity which is an optional property that allows you to set the name of the model to other than the default. You can set "User" to "foo" if you’d like to better describe the model. The attribute of userTypes. userTypes is specified as a collection of UserTypes model and points to the UserTypes via the user property. This defines a 1-many relationship between the User and UserTypes model.

On the other hand, UserTypes model simply just have the property of user which maps back to the User model. Using the example model above, the querying can be illustrated in the next section for ORM queries and how we can leverage this relationship.

Many-to-Many Relationship

Using the same models as above, we can define a many-to-many relationship using the concept of "followers" as an example. Twitter has this concept of allowing a user to follow other users and vice versa, hence the many-to-many relationship. Let’s look at an example how that model relationship looks like when building the models.

// User.JS
module.exports = {
    identity: 'User',
    attributes: {
        name: {
            type: 'string'
        }
        userTypes: {
            collection: 'UserTypes',
            via: 'user'
        },

        // Reference to user followers
        userFollowers: {
            collection: 'UserFollower',
            via: 'user'
        },

        // Reference to users that the user is following
        userFollowing: {
            collection: 'UserFollower',
            via: 'follower'
        }
    }
}
// UserFollower.JS
module.exports = {
    identity: 'UserFollower',
    attributes: {
        user: {
            model: 'User'
        },

        follower: {
            model: 'User'
        }
    }
}

In the example above, I have extended the User model with 2 new properties namely userFollowers and userFollowing. The property definition are both set to the same collection UserFollower and uses the via object property that points to the UserFollower properties.

On the other hand, UserFollower have 2 properties as well that has a model specified as the User that points back to the User model.

In the next post (part 2), we will be exploring the ORM piece and re-use the examples in this post to perform basic and more advanced queries. Please let me know if you find this post useful. Feel free to comment as well for feedback and share it with others in your social media. Thanks for reading.

Thank you and keep shipping!