Exposing MongoDB collections on the node.js backend

When we released the .NET backend, it came with native support for different kinds of storage layer: SQL (via Entity Framework), Azure Table Storage and MongoDB. The node.js backend has always supported SQL Azure, and I’ve seen a couple of posts explaining how to expose Azure Table Storage to mobile services clients. I already wrote about using MongoDB from the .NET backend, so this post will start closing the loop and talk about a way to expose a MongoDB collection as a “table” in a node.js-backed Azure Mobile Service.

The approach I’ll use in this post is to create a “virtual” table in the node backend – first create a table (either via the Azure portal or via the Command-Line Interface), then update its CRUD (create / read / update / delete) scripts to redirect all the requests to your MongoDB collection. In this post I’ll implement the insert / update / delete operations, as well as a simple read (either lookup a a single item or read all items). In the next post on this topic I’ll go over the steps required for implementing a full read operation.

Database setup

Those are exactly the same instructions as in the post about the .NET backend, but I’m copying it here so that this post can be self-contained. If you already have a MongoDB account with some collection created, feel free to skip this step (just make sure to take a note of your connection string, we’ll need it later). I’ll also use a collection named “orders” in this post – you don’t need to create one, the backend will create one if it doesn’t exist.

If you’re starting from scratch, for this post I’m using a Mongo Labs database, which we can get for free (for the sandboxed, developer edition) on the Azure portal. To create an account, you can go to the Azure portal, select “New” –> “Store”, and select the MongoLab add-on, where you can sign up for an account.

001-CreateMongoDB

Once you have the account set up, click the “Connection info” button to copy the URI to connect to the database and save that value. The name you gave to the account is the name of the database, which we’ll also use later.

002-MongoConnectionInfo

Now the Mongo database is set; we don’t need to create a collection, it will be created when we first try to access it via the backend.

Service setup

If you haven’t yet, create a new mobile service to use in this example. Create a table called “orders” in the portal, and we’ll use the scripts from that table to relay the requests from clients to the Mongo database. Finally, go to “configure” tab in the portal to add a new app setting (called “MongoConnectionString”) which we’ll use to store the connection string you obtained in the previous section. It’s a good practice to store connection strings (and other secrets) as application settings instead of having them in the code directly, as multiple people may have access to the code (developers) but only administrators of the service itself will have access to the production portal.

003-ConnectionStringAsAppSetting

Importing the MongoDB SDK

There’s a node.js package which allows us to talk to a Mongo DB (aptly called mongodb), and we’ll use it in this post. To use a custom node package, you’ll need to have source control enabled as described at http://azure.microsoft.com/en-us/documentation/articles/mobile-services-store-scripts-source-control/. It’s possible that your service already has this feature enabled (it’s the default for newly created services). Once that’s done, clone the service repository locally.

C:\temp\blog\MongoDbFromNodeBackend>git clone https://blog20140618.scm.azure-mobile.net/blog20140618.git
Cloning into 'blog20140618'...
Username for 'https://blog20140618.scm.azure-mobile.net': carlosfigueira
Password for 'https://carlosfigueira@blog20140618.scm.azure-mobile.net':
remote: Counting objects: 92, done.
remote: Compressing objects: 100% (84/84), done.
remote: Total 92 (delta 38), reused 0 (delta 0)
Unpacking objects: 100% (92/92), done.

Once that’s done, you can go to the service folder of your repository, and use npm to install the mongodb module in your service (if you haven’t done it yet, you’ll need to install node.js, which you can do by visiting its website). Notice that you can use the “–save” option to add the module in the packages.json file, after which you can “git-ignore” the package files in the repository, as was mentioned in this blog post.

C:\temp\blog\MongoDbFromNodeBackend\blog20140618\service>npm install --save mongodb
npm WARN package.json blog20140618@1.0.0 No README.md file found!
npm http GET https://registry.npmjs.org/mongodb
npm http 200 https://registry.npmjs.org/mongodb
npm http GET https://registry.npmjs.org/mongodb/-/mongodb-1.4.6.tgz
npm http 200 https://registry.npmjs.org/mongodb/-/mongodb-1.4.6.tgz
...

If you are using the package.json support, add a .gitignore file with the following line:

node_modules/

And “git add” the .gitignore and the package.json files. If not, then “git add” the node_modules folder to your repository. And we’re ready to start adding code to the table operations that will talk to MongoDB.

CRUD operations

In order to use the mongodb package and work with collections, all operations will need to first need to open a connection to the DB, create a collection object (which will create a collection in the DB if it doesn’t exist, or return a reference to an existing one otherwise) and then perform the operation. Let’s go one by one.

Inserts

Let’s start with the insert operation. As I mentioned before, we’ll connect, get the collection, and then perform the actual insert:

function insert(item, user, request) {
    var collectionName = tables.current.getTableName();
    var MongoClient = require('mongodb').MongoClient;
    var connectionString = process.env['MongoConnectionString'];
    MongoClient.connect(connectionString, function(err, db) {
        if (err) {
            console.log('Error connecting to the MongoDB: ', err);
            request.respond(500, { error: err });
        } else {
            db.createCollection(collectionName, function(err, collection) {
                if (err) {
                    console.log('Error creating collection: ', err);
                    request.respond(500, { error: err });
                } else {
                    collection.insert(item, { w: 1 }, function(err, result) {
                        if (err) {
                            console.log('Error inserting into the collection: ', err);
                            request.respond(500, { error: err });
                        } else {
                            // item has been inserted!
                            request.respond(201, result);
                        }
                    });
                }
            });
        }
    });
}

And we can now test it. I’ll use Fiddler, but any other HTTP client would work fine:

POST https://blog20140618.azure-mobile.net/tables/orders HTTP/1.1
User-Agent: Fiddler
Content-Type: application/json
x-zumo-application: LuKpHqHmHgHBwuqpUzXonKnIdGOZBk59
Host: blog20140618.azure-mobile.net
Content-Length: 221

{
    "client":"John Doe",
    "orderDate":"2014-06-12T00:00:00.000Z",
    "items":[
        { "name" : "bread", "quantity" : 1, "price" : 1.99 },
        { "name" : "milk", "quantity" : 1, "price" : 2.99 }
    ]
}

And if everything is working fine, you should get a response similar to this one (some headers removed and line break added for clarity):

HTTP/1.1 201 Created
Content-Length: 190
Content-Type: application/json
Server: Microsoft-IIS/8.0
Date: Thu, 12 Jun 2014 23:51:22 GMT

[
    {
        "client":"John Doe",
        "orderDate":"2014-06-12T00:00:00.000Z",
        "items":[
            {"name":"bread","quantity":1,"price":1.99},
            {"name":"milk","quantity":1,"price":2.99}
        ],
        "_id":"539a3cfaad2df8d85e780e81"
    }
]

The insertion operation worked – the item was inserted to the DB as you can see if you open the management console for your MongoDB. However, the response has some properties which will be problematic for mobile service clients:

  • The return type is an array, not an object
  • The identifier of the created object is defined as “_id”, not “id” as is expected by the client SDKs.

We have to deal with those issues. The first one is actually a feature of MongoDB – you can “bulk-insert” multiple items at once, and the “insert” method of the collection object supports that as well. Since the mobile services client SDKs don’t support (at least not yet) bulk inserts, let’s only deal with the single items insert. For the id, we need to normalize it before returning to the client. But we also need to think about the case that, when we’re inserting an item, we can pass the item’s id as part of the payload – we need to normalize the id before inserting the item as well.

function insert(item, user, request) {
    var collectionName = tables.current.getTableName();
    var MongoClient = require('mongodb').MongoClient;
    var connectionString = process.env['MongoConnectionString'];
    MongoClient.connect(connectionString, function(err, db) {
        if (err) {
            console.log('Error connecting to the MongoDB: ', err);
            request.respond(500, { error: err });
            return;
        }
        db.createCollection(collectionName, function(err, collection) {
            if (err) {
                console.log('Error creating collection: ', err);
                request.respond(500, { error: err });
                return;
            }
            if (Array.isArray(item)) {
                request.respond(400, { error: 'Bulk inserts not supported' });
                return;
            }

            // Normalize the id to what MongoDB expects
            mobileServiceIdToMongoId(item);

            collection.insert(item, { w: 1 }, function(err, result) {
                if (err) {
                    console.log('Error inserting into the collection: ', err);
                    request.respond(500, { error: err });
                    return;
                }

                // Unwrap the inserted item
                result = result[0];

                // Normalize the id to what the mobile service client expects
                mongoIdToMobileServiceId(result);

                request.respond(201, result);
            });
        });
    });
}

function mobileServiceIdToMongoId(item) {
    var itemId = item.id;
    delete item.id;
    if (itemId) {
        item._id = itemId;
    }
}

function mongoIdToMobileServiceId(item) {
    var itemId = item._id;
    delete item._id;
    if (itemId) {
        item.id = itemId;
    }
}

And if we send the same request again, we should get the response as expected by the client:

HTTP/1.1 201 Created
Content-Length: 187
Content-Type: application/json
Location: https://blog20140618.azure-mobile.net/tables/orders/539a41da134eac902318577e
Server: Microsoft-IIS/8.0
Date: Fri, 13 Jun 2014 00:12:10 GMT

{
    "client":"John Doe",
    "orderDate":"2014-06-12T00:00:00.000Z",
    "items":[
        {"name":"bread","quantity":1,"price":1.99},
        {"name":"milk","quantity":1,"price":2.99}
    ],
    "id":"539a41da134eac902318577e"
}

Notice that we even get the “Location” HTTP header now – we get that for free from the node.js runtime (since we returned a 201 Created with an object with an id).

Reads

Like in the insert case, we’ll need to connect to the database and obtain the collection prior to executing the actual operation we want. Since we’d end up repeating the code, it’s time to move some of it into a shared script. In the service/shared folder at the root of the git repository, create a file called “mongoHelper.js” and add the code shown below. In the functions to convert between the mongo and the mobile services id I’m adding an extra parameter to decide whether the id will be converted or removed in the object, which will be used later.

exports.connectAndGetCollection = function(collectionName, callback) {
    var MongoClient = require('mongodb').MongoClient;
    var connString = process.env["MongoConnectionString"];
    MongoClient.connect(connString, function(err, db) {
        if (err) {
            callback(err, null);
        } else {
            db.createCollection(collectionName, function(err, collection) {
                if (err) {
                    callback(err, null);
                } else {
                    callback(null, collection);
                }
            });
        }
    });
}

exports.mobileServiceIdToMongoId = function(item, remove) {
    var itemId = item.id;
    delete item.id;
    if (itemId && !remove) {
        item._id = itemId;
    }

    return itemId;
}

exports.mongoIdToMobileServiceId = function(item, remove) {
    var itemId = item._id;
    delete item._id;
    if (itemid && !remove) {
        item.id = itemId;
    }

    return itemId;
}

Now on to the read script. Create a file under service/tables/orders.read.js with the content shown below. Like for “regular” tables, there are two types of read requests: lookup (or read for a single element; a GET request to /tables/<tableName>/<item id>); and a “general” read, which returns a list of items. We can differentiate between the two calls by checking the “id” property of the query object which is passed to the function. In the code below, to make it more readable I split the two cases in their own functions.

function read(query, user, request) {
    var collectionName = tables.current.getTableName();
    var mongoHelper = require('../shared/mongoHelper');
    mongoHelper.connectAndGetCollection(collectionName, function(err, collection) {
        if (err) {
            console.log('Error creating collection: ', err);
            request.respond(500, { error: err });
            return;
        }

        if (query.id) {
            findSingleObject(collection, query.id, mongoHelper, request);
        } else {
            returnMultipleObjects(collection, query, mongoHelper, request);
        }
    });
}

When we want to find a single object, we can use the “findOne” method in the collection object. The way that we search for the object id, however, is different: if we’re using a MongoDB’s ObjectID (which is the type of the identifier which we’d get if we were inserting an item without passing its id), then we need to search by using the ObjectID object itself (or its JSON projection, { $oid: <actual id>}). Otherwise we can just search at the “_id” property itself. The id coming from the client’s request is always a string, so we can check whether it “looks like” a valid object id then we search both for object ids and for “simple” ids. Otherwise we know that it’s not an object id so we can make a simpler search by the _id value.

function findSingleObject(collection, itemId, mongoHelper, request) {
    // Lookup operation: get for a single element
    var callback = function(err, item) {
        if (err) {
            console.log('error querying collection: ', err);
            request.respond(500, { error: err });
        } else {
            if (item) {
                mongoHelper.mongoIdToMobileServiceId(item);
                request.respond(200, item);
            } else {
                request.respond(404);
            }
        }
    };
    var ObjectID = require('mongodb').ObjectID;
    if (ObjectID.isValid(itemId)) {
        // Maybe its a MongoDB object id; maybe it just looks like one
        collection.findOne({ _id: { $in: [ itemId, new ObjectID(itemId) ] } }, callback);
    } else {
        // It's not an object id; may have been created by the client
        collection.findOne({ _id: itemId }, callback);
    }
}

In the general read case, the server will return multiple items. For now we’ll just return all items in the collection (ignoring any paging / filtering / sorting options passed by the client); in the next post on this object I’ll go into more details on the query object to see how the full querying capabilities can be implemented.

function returnMultipleObjects(collection, query, mongoHelper, request) {
    // TODO: look at query parameters. For now, return all items.
    collection.find().toArray(function(err, items) {
        if (err) {
            console.log('error querying collection: ', err);
            request.respond(200, { error: err });
        } else {
            items.forEach(function(item) {
                mongoHelper.mongoIdToMobileServiceId(item);
            });
            request.respond(200, items);
        }
    });
}

We can now insert and query for items. Moving on to the remaining operations…

Updates

In a mobile service table, the update script is called when a HTTP PATCH request is sent to the server. The semantics of a PATCH is that only the properties specified in the request body should be modified, and this maps well to the findAndModify method of the collection object. And since we need to do query based on the id and account for the two cases as mentioned in the previous section, now it’s a good time to move that logic to the shared code. Add this additional export to the file service/shared/mongoHelper.js

exports.queryForId = function(id) {
    /// <summary>
    /// Returns a query object which can be used to find an object with the given id.
    ///  the query will be a simple query by "_id" based on the value of the id if
    ///  the id is not a valid ObjectID, or a query by the id value or the ObjectID
    ///  value otherwise.
    /// </summary>
    /// <param name="id" type="String">The id to create the query for.</param>
    /// <returns>An object which can be used as the query parameter in MongoDB's
    ///  collection methods "findOne" or "update".</returns>
    if (ObjectID.isValid(id)) {
        return { _id: { $in: [ id, new ObjectID(id) ] } };
    } else {
        return { _id: id };
    }
}

And on the update function we can use it. When the result of “findAndModify” is passed to the callback, if there are no errors and the object was updated, then the updated object is passed in the “result” parameter, which we can return to the caller. If there was no item which matches the filter criteria (in our case, based on the id), then that value will be null / undefined, in which case we return a 404 Not Found to the client. In the update method we will use the “remove” parameter of the function used to convert between the mobile services and the node.js format, as we don’t need to set the id of the object when applying the updates.

function update(item, user, request) {
    var collectionName = tables.current.getTableName();
    var mongoHelper = require('../shared/mongoHelper');
    mongoHelper.connectAndGetCollection(collectionName, function(err, collection) {
        if (err) {
            console.log('Error creating collection: ', err);
            request.respond(500, { error: err });
            return;
        }

        // Normalize the id to what MongoDB expects and remove it from the object.
        var itemId = mongoHelper.mobileServiceIdToMongoId(item, true);

        var params = {
            query: mongoHelper.queryForId(itemId),
            sort: [],
            update: { $set: item },
            options: { new: true }
        };

        collection.findAndModify(params.query, params.sort, params.update, params.options, function(err, result) {
            if (err) {
                console.log('Error updating in the collection: ', err);
                request.respond(500, { error: err });
                return;
            }

            if (result) {
                request.respond(200, result);
            } else {
                request.respond(404);
            }
        });
    });
}

And the patch semantics is done.

Deletes

The last operation is fairly simple – we get the id from the request, and remove items which match that id.

function del(itemId, user, request) {
    var collectionName = tables.current.getTableName();
    var mongoHelper = require('../shared/mongoHelper');
    mongoHelper.connectAndGetCollection(collectionName, function(err, collection) {
        if (err) {
            console.log('Error creating collection: ', err);
            request.respond(500, { error: err });
            return;
        }

        collection.remove(mongoHelper.queryForId(itemId), { w: 1 }, function(err, result) {
            if (err) {
                console.log('Error deleting item in the collection: ', err);
                request.respond(500, { error: err });
                return;
            }

            if (result) {
                request.respond(204);
            } else {
                request.respond(404);
            }
        });
    });
}

And with that we conclude the basic implementation of the CRUD operations to expose a MongoDB collection as an Azure Mobile Services table.

Wrapping up

I showed that, even though there is no “out-of-the-box” support for consuming MongoDB collections from the node.js backend (unlike in the .NET backend), adding basic support is not too complex. In my next post I’ll expand on this scenario to implement those complex querying operations, including paging, sorting and filtering. Also, if you want to get the code for this post you can find it at https://github.com/carlosfigueira/blogsamples/tree/master/AzureMobileServices/MongoDbOnNodeBackend/CRUDTests/SimpleOperations.

And as always, feel free to leave your comments / suggestions / bug reports in the comments section of this post, or in our MSDN forums.