How does one access relational data (sql azure) through azure mobile services?

Question

I have been looking for good tutorials and documentation on how azure mobile services works for the past 3-4 hours. I keep running into tutorials that show how to create a mobile service, connect an existing database or create a new one, and access data stored in a table created in the mobile service itself. No where can I find how exactly to access an actual database itself as opposed to the storage tables made in the mobile service. I don't understand why data cannot be accessed directly from a database as opposed to this "mobile service" unless this thing actually works the way an OData service is supposed to work in respect with providing an abstract data model to secure the underlying data structure.

My question is: How exactly can I access my Sql Azure Db (relational structure) via my azure mobile service?

Answer 1

As Herve Roggero already pointed out, the Mobile Services RESTful API is the suggested way to access your tables in SQL Azure. And the whole idea of Mobile Services it to take the burden with data access and modification from your application and put it to the shoulders of a cloud-hosted service.

Should you nonetheless want to access the data tables "directly", there are the following ways:

In your server scripts you can use the global mssql to run SQL queries against your database, retrieve the data, modify or insert them.

mssql.query('select top 1 * from statusupdates',    
     { success: function(results) 
            {
            console.log(results); 
            } 
        }
); 

If you have another cloud-hosted services (web- or worker roles) and you activated the firewall access for internal requests, you can access the SQL Azure Server using TDS protocol and manipulate the data according to your likes and dislikes.

And, finally, you can open the firewall on your SQL Azure so that you can connect to it from external network and again, use TDS protocol on your client for DB communication.

But none of these three methods I would call "via mobile services", but rather "from within" (1) and "bypassing them" (2,3).

Answer 2

Adrian,

The answer is in your question. The mobile service is an abstract layer; that's why you can't access the tables directly. The request from the phone comes to the mobile service, which allows you to run logic, then turns around and accesses the database on your behalf. It's best that ways for many reasons, including connection pooling and security. For details on how the operations work, check out the MSDN documentation on the Mobile Service REST protocol that makes this work.

Thanks Herve

Answer 3

Here's blog post explaining process of exposing your existing SQL database from Windows Azure Mobile Services Custom API

You can make SQL queries right from the javascript side.

exports.get = function(request, response) {
   var id = request.query.id;
   request.service.mssql.query(
      'select * from Person join Orders on Person.ID = Orders.PersonID where Person.id = ' + id, {
      success: function(results) {
         response.send(200, results);
      }
   });
};

More documentation about mssql object.