11436 SSO

From MySQL to NOSQL: A foray into building apps with App Services

Mary Simpson
Aug 28, 2012

Mary Simpson has a Master's Degree in Software Engineering from the University of Florida.  She has been developing Web and PC applications in a variety of languages since 2000, and has operated Bark! Multimedia, a website and software development studio, since 2003. We thank Mary for giving Apigee App Services (formerly Usergrid) a try and for sharing her experiences in this guest blog post.

Find the full source code for Mary's project on github and see the code running live.

Operating Bark! Multimedia, a website and software development studio, I get many requests to build mobile apps.  Customers are often surprised by the development effort required to make great mobile apps.  Although apps are not as difficult to build as they once were, there is still a lot of server-side code that must be written in addition to the app itself.

Recently, I have been reading various tech blogs and other sites (1, 2, 3) that trumpeted the emergence of Backend as a Service (BaaS).  They all touted the features and time savings of these new services, but could they really make app-building faster and easier?  Although I'm married to an Apigee engineer, as owner and operator of my own business, I wanted to find out for myself. Building my own app and making an independent assessment was the best way for me to do that.

My choice for an app to build was a simple to-do list. With loads of samples on the 'net and many commercial versions, this seemed like a perfect place to start.  I decided to forgo user registration and accounts at the start, and instead focus on simply adding and removing items from the list and putting them into categories.  I wanted persistent storage so that items in the to-do list would remain there from browser to browser, and session to session. I decided that two days ought to be plenty of time to get such a simple app off the ground.

PHP/MySQL: My standard choice
I have been using PHP/MySQL technologies for over a decade and normally they would be the logical choice for the back-end data store of my to-do app. Some quick requirements sketching produced my own to-do list of tasks to get the server up and running.  In PHP, I would have to build several endpoints for my "API," including a way to add and remove items from the list, and a way to specify categories.  I would also need to build out some tables (items, categories, etc.).

Looking over my list, I determined that I could build out my PHP files and set up the database in a day or so, but that wouldn't leave me with much time to actually build the app.  What was becoming apparent was that for such a simple app, there was a lot to do!

Enter Backend as a Service
The promise of BaaS is that I would save loads of time because I wouldn't have to build out all of the server-side infrastructure. With my time constraints, I could certainly use some help, so I signed up for a free Apigee App Services account.

What I found was that once I had signed up for an account, I was essentially done with all the server side "stuff."  All that was left was to download the SDK and get to work building the front end.  Using the sample app that came with the SDK, it was apparent how to make API calls to the App Services servers.

Trading MySQL for NOSQL
What was different for me was getting used to the NOSQL approach to databases. Instead of making a bunch of tables, you simply make a collection–in my case, a collection of "items".  This was easily done by doing a single POST operation to the server:

POST http://api.usergrid.com/<myorg>/<myapp>/items

After I built out the basic structure of my app (I went with a Publish Subscribe model), I was ready to begin saving items to the database.  Normally, I would have done an INSERT to the "items" table, like so:

INSERT INTO items (description, category) VALUES('Get milk', 'errands')

With App Services, I simply passed my JavaScript object as a POST to the items collection:

POST http://api.usergrid.com/<myorg>/<myapp>/items {'description':'Get Milk', 'category':'errands'}

Higher level abstraction with the Apigee SDK

The Apigee JavaScript SDK offers an Entity object that makes creating and saving objects in the API a little easier.  All of the nitty-gritty interaction with API is abstracted to make creating entities quite simple:

var item = new Entity('items');
item.setField('description', 'Get Milk');
item.setField('category', 'errands');

What about the ID of the newly inserted item?
With MySQL you would typically get the ID of the item you just inserted into the table by calling:

$id = mysql_insert_id();

App Services uses a UUID as the unique identifier for any entity in a custom collection. Using the Entity object, you need only call one of the built-in functions once the save method is finished:

var uuid = item.getUUID();

How to get items back out of the database?
With items now in the database, getting them back out was the next order of business.  In MySQL, this would have been accomplished by a SELECT statement:

SELECT description, "category" FROM items

To narrow the search to just a single category, you would qualify it with a WHERE clause:

SELECT description, category FROM items WHERE category = "errands"

Fortunately, using NOSQL is not too different.  Same concept, just different syntax:

GET http://api.usergrid.com/items

Likewise, we can narrow the search to just one category:

GET http://api.usergrid.com/<myorg/<myapp>/items?ql=category='errands'

Return formats and the Collection object
The above MySQL calls to get items from the database always gives you back a simple array of table rows.  Calls to App Services return an array of Entity objects:

 "action" : "get",
 "application" : "b954a936-bcae-11e1-bc66-12313d2c5a2e",
 "params" : {
 "path" : "/items",
 "uri" : "https://usergrid-prod-api-v2.elasticbeanstalk.com/b954a936-bcae-11e1-bc66-12313d2c5a2e/items",
 "entities" : [ {
   "uuid" : "b9f75792-df39-11e1-9cf5-12313b122d0e",
   "type" : "item",
   "created" : 1344197482395,
   "modified" : 1344197482395,
   "category" : "errands",
   "description" : "Get Milk",
   "metadata" : {
     "path" : "/items/b9f75792-df39-11e1-9cf5-12313b122d0e"
 }, {
   "uuid" : "c92c21f6-df3c-11e1-9cf5-12313b122d0e",
   "type" : "item",
   "created" : 1344198796397,
   "modified" : 1344198796397,
   "category" : "errands",
   "description" : "Wash Car",
   "metadata" : {
     "path" : "/items/c92c21f6-df3c-11e1-9cf5-12313b122d0e"
 } ],
 "timestamp" : 1344198915207,
 "duration" : 124

The data above looks a bit cryptic, but the App Services SDK provides a Collection object to make dealing with this data more manageable.  You simply make a new Collection object and then call the get() method to populate it:

var items = new Collection('items');

The code above makes a new Collection, then retrieves all the entities in the collection from the API.  You can then iterate across the collection by using the built in getNextEntity() method:

while(items.hasNextEntity()) {
  var item = items.getNextEntity();
   $('#myitemlist').append('<li>'+ item.getName() + '</li>');

In the code above, we iterate across all the items in our to-do list and then push each one into a list in the display.

Putting it all together
The transition from MySQL to NOSQL was easier than I expected.  I found that storing my data as objects in collections matched up well with the needs of my app.  Also, the advanced query options allowed me to easily narrow my result sets just like I would have done in MySQL.  By eliminating the need to build a backend, I was able to spend almost all of my time working on the front end code. 

As I look down the road, adding user registration and login to my new app will be easy because App Services already has this functionality built in. 

Overall I was very impressed with App Services.  There were several new concepts to learn, and some adjusting to do, but the JavaScript SDK made it easy to get up and running, and the time savings was well worth the effort.

This blog post is part of a series about Apigee’s new App Services.
Sign up for App Services today and join our community! We look forward to seeing what you build with Apigee.

API Management Decision-Making Kit

Next Steps


Resources Gallery