Welcome!

Blog Feed Post

Getting started with Azure App Service and Easy Tables

Azure App Service makes it easy to build a back-end for a mobile application.  One of my favorite features of it is Easy Tables.  Easy Tables makes it easy to create tables in a SQL Azure database and comes with an SDK for several mobile platforms that make it easy to access it.  You can make calls to it using REST, but there are APIs for Apache Cordova, Xamarin, as well as native development.  Some of the APIs even support offline synchronization.  If you are looking to prove out an idea, Easy Tables makes it well, easy.

There is some great documentation out there for getting started with Easy Tables as well.  However, I ran into a few stumbling points in my early experiences that I thought I would share.  These instructions assume, you have created an Azure App Service app.  If you don’t have one just look for App Services in your Azure portal and click New.  In our case, we are going to be working with a node.js back-end.

Setting up the database connection

Setting up the database connection should be simple, but unfortunately, it’s not due to issues in the configuration UI.  Before you go to set up the connection, you will need to create a SQL Server to host your database if you don’t have one already.  This isn’t terribly complicated just try to pick a region close to the one hosting your App Service App.  After your database is set up, you need to create the database to host your data.  You can use the free pricing tier too to try things out if you haven’t created a free database in this particular region yet (you only get one per region).

AppServiceSQLFreePricingTier

Once you have your database set up, you are ready to configure a connection to from Azure App Service.  Effectively, you are creating a connection string.  To set up the connection, go to your App Service App and click on the Easy Tables link under Mobile.

AppServiceEasyTableMenu

It will then initialize Easy Tables if needed and prompt you to click continue to set it up.

AppServiceEasyTablesPromptToConfigure

Here is where, it will prompt you to connect to a database.

AppServiceEasyTablesStep1 

After this, it will prompt you to select your existing SQL Server and then database.  The final step is to enter in the username and password you set on your SQL Server and database.  Specify a connection string name (whatever you want) along with the credentials.  Click ok and hope for the best.  I mentioned I had problems with the UI right?  The first time I did this, I couldn’t get it to save my password because it had a semicolon (;) in it.  Remember, how I said it is building a connection string underneath?  Semicolon is a delimiter for connection strings and the UI doesn’t handle that at all.  It just fails.  Hopefully this step works for you.  If it doesn’t there is a way that you can manually set the connection string using Resource Explorer.  That’s more detail than I want to go into today.  If you run into it though, feel free to ping me or leave me a comment and I’ll provide the details.

Once your database connection has been set up, click on the Initialize App button.  This will destroy any files that may already exist on your App Service App so don’t click it if there is something you need there.  Effectively this sets up folders such as /tables and /api as well as some of the basic node.js configuration.  It also creates a test table called TodoItem.

Creating new tables

There are a few ways to create new tables but not all of them have the desired results:

  • Create the appropriate files in the tables folder
  • Create the table through the Azure Portal in the Easy Tables section
  • Manually create the table in SQL Server

I would say the preferred way to create the table is by creating the appropriate files in the tables folder.  The node.js back-end will create new SQL table for any .js file you add to the tables folder (assuming the appropriate supporting files are present).  For example if you create a file named Event.js, you will get a SQL table called Event.  If you create a file named Contact.js, you will get a SQL table called Contact.  You get the idea.  There is a little bit more to it though.  Let’s look at the steps involved. 

First, you need to be able to edit the files of your node.js App Service app.   You have several ways to do this.  I recommend setting up a local git repository and pushing files up to your App Service App.  You can configure that along with credentials in the Deployment source setting of your App Service App.  You can find more about setting up the node.js back-end and deployment in this article.  However, you can also just edit the files directly from Easy Tables.  If you already have a table created.  Click on the table and then click the Edit script button to edit the files directly in Visual Studio Online.

AppServiceEasyTableEditScript

Here you can edit the files directly and they are saved immediately.  I am going to start by create a table to store event information named Event.  That means we need to create a file named event.js.  Here is what the starting file looks like.

var azureMobileApps = require('azure-mobile-apps');
 
var table = azureMobileApps.table();
 
module.exports = table;

According to the documentation that is all that is required to get started with a new table.  Now, you might be wondering where are the column names and types?  Technically, they aren’t required.  You see Easy Tables will create new columns on the fly when you make your first insert.  This is great to try things out but not really what you want to do in a production environment.  So I like to specify my own columns.  You can use types such as string, date, boolean, and number.  More complex types aren’t supported by the API.  To create your columns put them in an object and assign to the columns property of table.  Then be sure and set dynamicSchema to false so that it won’t create new columns on the fly.  Set these values before calling module.exports.

table.columns = {
	"title": "string",
	"start_date": "date",
	"end_date": "date",
	"description": "string",
	"image_url": "string",
	"cost": "string",
	"event_type": "string",
	"send_notification": "boolean"
};
 
table.dynamicSchema = false;

Don’t worry about creating columns for things like and Id, created and modified dates, or even soft delete.  Easy Tables will create those columns for you automatically.

Before Easy Tables goes and creates your back-end table, there are a few more steps.  First, you need a corresponding .json file.  In our case it would be Event.json.  This contains some basic properties such as if soft-delete is enabled and whether certain operations require authentication.  I found no documentation whatsoever that said this file was required.  However, in the TodoItem samples out there on git hub the file was always there.  Here is what it looks like.

{   "softDelete" : true,   "autoIncrement": false,   "read": {     "access": "anonymous"   },   "insert": {     "access": "anonymous"   },   "update": {     "access": "anonymous"   },   "delete": {     "access": "anonymous"   },   "undelete": {     "access": "anonymous"   }
}

If you want to authentication to be required, you can change the access for the corresponding operation to “authenticated”.  However, we’ll cover that in a different post.

At this point, you should see an entry for your new table in the Easy Tables section of the Azure portal.  However, the underlying SQL table does not exist yet.  In fact, it doesn’t get created until you make your first API call to it.  That part took me a while to figure out.  Maybe there is a way to automate it getting created but in my experience it doesn’t happen until then.  There is actually a parameter called seed that will let you put sample data into your table, but I have never successfully gotten it to work.

Calling the API using JavaScript

In my example, I am using Apache Cordova.  It’s easy to get started with the Cordova SDK and Azure App Service.  Just add the plugin and create a client object and start querying.  Take a look at this post for more details.  If you are using straight HTML / JavaScript, the code is basically the same as well.  First, create a client object using the URL to your App Service.  You can get this from the Azure Portal.

var client = new WindowsAzure.MobileServiceClient(azureMobileClientUrl);

Now, we can call getTable with the table name to get the event.  Selecting data from the table is easy, just call .read() to get everything.  It has a promise attached so that you can work with your results and errors accordingly.

var table = client.getTable('event');
return table.read()
.then(function (events) {
	// do something with events
}, function (error) {
	// do somethign with errors
});

If you want to filter your data just add a where clause.  Each item you add to the collection will be treated as an “AND” operator.  Note, that it is only simple equal comparisons though.

var table = client.getTable('event');
return table
	.where({ id: myId})
	.read()
.then(function (events) {
	// do something with events
}, function (error) {
	// do somethign with errors
});

You can also use .orderBy() to control the order of the data as well.  It can be used in conjunction with the where clause if desired.

var table = client.getTable('event');
return table
	.orderBy('start_date')
	.read()
.then(function (events) {
	// do something with events
}, function (error) {
	// do somethign with errors
});

Making any of the above calls is enough to get your table created.  You can then go to Server Explorer –> Azure –> SQL Databases and verify that the table was created.  This is a great way to look and see what data is there as well.

Have a look at the SDK reference for inserting and updating data.  You simply need to create an option with the matching column names and specify the values and call .insert() or .update() accordingly.  Remember you don’t need to specify values for the Id or any of the other fields Easy Table creates.  The response you get back will be the inserted or updated data.

var table = client.getTable('event');
return table.insert({
	title: 'Event Title',
	description: 'This is an event description',
	start_date: new Date(),
	end_date: new Date(),
	event_type: 'Special Event',
	send_notification: true
})
	.then(function (response) {
	}, function (error) {
	});

If you need to delete data, the one thing I ran into is that it only works with the Id field.  If you try to delete based on some other value, you’ll get an error.  If you need to delete based on some other field, you will need to create your own API.  We’ll cover that in another post.

var table = client.getTable('event');
table.del({ id: id })
.then(function () {
	// deleted
}, function (error) {
	// error
});

Mobile Apps in Azure App Service is an evolution of Azure Mobile Services.  I often find there is more documentation on that since it’s been around longer.  For example, take a look at this article on working with mobile data as it has a lot more examples.

Summary

Azure App Service Easy Tables make it super easy to get started creating a back-end for your mobile app.  Give them a try and see what you can create.  If you are looking for some samples, be sure and check out the Azure Mobile Apps repository in GitHub.

Read the original blog entry...

More Stories By Corey Roth

Corey Roth, a SharePoint Server MVP, is a consultant at Hitachi Consulting specializing in SharePoint and Office 365 for clients in the energy sector. He has more than ten years of experience delivering solutions in the energy, travel, advertising and consumer electronics verticals.

Corey specializes in delivering ECM and search solutions to clients using SharePoint. Corey has always focused on rapid adoption of new Microsoft technologies including Visual Studio 2013, Office 365, and SharePoint.

He is a member of the .NET Mafia (www.dotnetmafia.com) where he blogs about the latest technology and SharePoint. He is dedicated to the community and speaks regularly at user groups and SharePoint Saturdays.

Latest Stories
Internet of @ThingsExpo, taking place October 31 - November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA, is co-located with the 21st International Cloud Expo and will feature technical sessions from a rock star conference faculty and the leading industry players in the world. @ThingsExpo Silicon Valley Call for Papers is now open.
SYS-CON Events announced today that Twistlock, the leading provider of cloud container security solutions, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Twistlock is the industry's first enterprise security suite for container security. Twistlock's technology addresses risks on the host and within the application of the container, enabling enterprises to consistently enforce security policies, monitor...
This talk centers around how to automate best practices in a multi-/hybrid-cloud world based on our work with customers like GE, Discovery Communications and Fannie Mae. Today’s enterprises are reaping the benefits of cloud computing, but also discovering many risks and challenges. In the age of DevOps and the decentralization of IT, it’s easy to over-provision resources, forget that instances are running, or unintentionally expose vulnerabilities.
SYS-CON Events announced today that Ocean9will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Ocean9 provides cloud services for Backup, Disaster Recovery (DRaaS) and instant Innovation, and redefines enterprise infrastructure with its cloud native subscription offerings for mission critical SAP workloads.
Five years ago development was seen as a dead-end career, now it’s anything but – with an explosion in mobile and IoT initiatives increasing the demand for skilled engineers. But apart from having a ready supply of great coders, what constitutes true ‘DevOps Royalty’? It’ll be the ability to craft resilient architectures, supportability, security everywhere across the software lifecycle. In his keynote at @DevOpsSummit at 20th Cloud Expo, Jeffrey Scheaffer, GM and SVP, Continuous Delivery Busine...
While some vendors scramble to create and sell you a fancy solution for monitoring your spanking new Amazon Lambdas, hear how you can do it on the cheap using just built-in Java APIs yourself. By exploiting a little-known fact that Lambdas aren’t exactly single threaded, you can effectively identify hot spots in your serverless code. In his session at 20th Cloud Expo, David Martin, Principal Product Owner at CA Technologies, will give a live demonstration and code walkthrough, showing how to ov...
SYS-CON Events announced today that Enzu will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY, and the 21st International Cloud Expo®, which will take place October 31-November 2, 2017, at the Santa Clara Convention Center in Santa Clara, CA. Enzu’s mission is to be the leading provider of enterprise cloud solutions worldwide. Enzu enables online businesses to use its IT infrastructure to their competitive ad...
Everywhere we turn in our industry we can find strong opinions about the direction, type and nature of cloud’s impact on computing and business. Another word that is used in every context in our industry is “hybrid.” In his session at 20th Cloud Expo, Alvaro Gonzalez, Director of Technical, Partner and Field Marketing at Peak 10, will use a combination of a few conceptual props and some research recently commissioned by Peak 10 to offer a real-world consideration of how the various categories of...
DevOps is often described as a combination of technology and culture. Without both, DevOps isn't complete. However, applying the culture to outdated technology is a recipe for disaster; as response times grow and connections between teams are delayed by technology, the culture will die. A Nutanix Enterprise Cloud has many benefits that provide the needed base for a true DevOps paradigm. In his Day 3 Keynote at 20th Cloud Expo, Chris Brown, a Solutions Marketing Manager at Nutanix, will explore t...
SYS-CON Events announced today that SoftLayer, an IBM Company, has been named “Gold Sponsor” of SYS-CON's 18th Cloud Expo, which will take place on June 7-9, 2016, at the Javits Center in New York, New York. SoftLayer, an IBM Company, provides cloud infrastructure as a service from a growing number of data centers and network points of presence around the world. SoftLayer’s customers range from Web startups to global enterprises.
SYS-CON Events announced today that Interoute has been named “Bronze Sponsor” of SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Interoute is the owner operator of Europe's largest network and a global cloud services platform, which encompasses over 70,000 km of lit fiber, 15 data centers, 17 virtual data centers and 33 colocation centers, with connections to 195 additional partner data centers. Our full-service Unifie...
SYS-CON Events announced today that CollabNet, a global leader in enterprise software development, release automation and DevOps solutions, will be a Bronze Sponsor of SYS-CON's 20th International Cloud Expo®, taking place from June 6-8, 2017, at the Javits Center in New York City, NY. CollabNet offers a broad range of solutions with the mission of helping modern organizations deliver quality software at speed. The company’s latest innovation, the DevOps Lifecycle Manager (DLM), supports Value S...
SYS-CON Events announced today that Peak 10, Inc., a national IT infrastructure and cloud services provider, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Peak 10 provides reliable, tailored data center and network services, cloud and managed services. Its solutions are designed to scale and adapt to customers’ changing business needs, enabling them to lower costs, improve performance and focus intern...
Multiple data types are pouring into IoT deployments. Data is coming in small packages as well as enormous files and data streams of many sizes. Widespread use of mobile devices adds to the total. In this power panel at @ThingsExpo, moderated by Conference Chair Roger Strukhoff, panelists will look at the tools and environments that are being put to use in IoT deployments, as well as the team skills a modern enterprise IT shop needs to keep things running, get a handle on all this data, and deli...
SYS-CON Events announced today that Linux Academy, the foremost online Linux and cloud training platform and community, will exhibit at SYS-CON's 20th International Cloud Expo®, which will take place on June 6-8, 2017, at the Javits Center in New York City, NY. Linux Academy was founded on the belief that providing high-quality, in-depth training should be available at an affordable price. Industry leaders in quality training, provided services, and student certification passes, its goal is to c...