1

I'm currently developing a back end for a mobile system using Azure Mobile Apps with Node.js and Azure Cosmos DB (Document DB).

I'm using the example that was given here:

https://github.com/Azure/azure-mobile-apps-node/blob/master/samples/custom-api-sql-stmt/api/completeall.js

So far so good - however, whenever I run this SQL, I get:

TypeError: provider.read is not a function at Function.module.exports.api.execute (x:\x\node_modules\azure-mobile-apps\src\data\index.js:32:18

The SQL I need to run looks like this:

SELECT c.id, c.Email FROM Profile c JOIN d in c.DeviceIDs WHERE d.DeviceID = @deviceId

This is because my data is nested, like this (the Profile collection in Cosmos) - I need to get the records where the DeviceID matches:

[ { "id": "2ca572d0-858d-4376-8537-c228a8379638", "Email": "test@user.com", "Name": "Test User 1", "OrgRoles": null, "DeviceIDs": [ { "DeviceID": "123445555555", "DeviceCode": "1234" } ], "UpdatedDate": "2017-11-10T13:18:32.0110724Z", "CreatedDate": "2017-11-10T13:18:27.220764Z", "IsDeleted": true }]

I would love to be able to do this the other way, using something like this:

context.tables('Profile').where({ DeviceID: id })

but I cannot figure out how on earth to accomplish joins using this method, let alone joins on the same table.

Can anyone help with either method? I get the feeling the Azure Mobile Apps Document DB driver hasn't been finished properly by MS, but I can't seem to get a hold of anyone to see if there's any way I can help.

UPDATE: We have managed to do this via the document db driver directly, but it means that we will need to have some of our api methods connecting to the database one way and some others connecting a different way. Seems a shame, Microsoft should fully invest in Cosmos - it's quite good imho.

Ross Vernal
  • 483
  • 1
  • 5
  • 20

1 Answers1

1

As the Azure Mobile Apps Node.js SDK doesn't support Cosmos DB driver, you would need to integrate the SDK azure-documentdb-node into your mobile app. 

var documentClient = require("documentdb").DocumentClient;

var endpoint = 'https://<account_name>.documents.azure.com:443/';
var primaryKey = '<primary_key>';
var client = new documentClient(endpoint, { "masterKey": primaryKey });

var databaseUrl = `dbs/<database_name>`;    
var collectionUrl = `${databaseUrl}/colls/Profile`;

var querySpec = {
    'query': 'SELECT c.id, c.Email FROM Profile c JOIN d in c.DeviceIDs WHERE d.DeviceID = @deviceId',

    "parameters": [
        { "name": "@deviceId", "value": '123445555555' }
    ]
}

client.queryDocuments(collectionUrl, querySpec).toArray(function(err, results) {
    if(err) return console.log(err);

    console.log(results);
});
Aaron Chen
  • 9,835
  • 1
  • 16
  • 28
  • Thanks for the answer - it means things are a little messy in our code with a mix of two different ways of getting the data. What would it take to make this work with Cosmos? https://github.com/Azure/azure-mobile-apps-node/blob/master/samples/custom-api-sql-stmt/api/completeall.js We are quite invested in this, and it would be good if it worked - I'd be happy to help out with the open source project, but it seems that my suggestions go ignored, like here: https://github.com/jsandersrocks/azure-mobile-apps-documentdb-driver/issues/7 – Ross Vernal Nov 29 '17 at 10:14