NoSQL, No Problem!

While the technology behind, and the interface in front of, each of these services vary, some dramatically, they each support storing data and lots of it. Some already have a REST or somewhat  REST-like HTTP interface to access them, which may mean storing your access keys or authorization information on the client and passing them along with every call. Some do not have an HTTP interface at all and must be accessed through server-side code that you have to go find and install or write yourself. Some allow you to design the primary keys or indexes of your storage tables, some do not. Some support JSON natively, some do not. In fact, the formats supported for data entry and retrieval (think filtering) vary almost as widely as the number of vendors. Get the drift? Different creators, different creations. But you say “I just want a simple way for my app to get my data in and out, quickly and easily.” That's where I think we can help.

NoSQL as a Service

DSP Services allow you to “on-board” web-based or locally running services that you want to use from your app and access from one authenticated REST API. The access to these services can be controlled by app and by user role. There is more on this and how to provision these services in our documentation here.

The same goes for the supported NoSQL services. We securely store any credentials required to access your storage on the server-side, relieving your app from maintaining it. We remove any cross-site hoops your app would have to handle by making it a “native” REST service on the DSP. We even go a step further and provide a common, fully REST-based API to each of the supported NoSQL service types and use a common data format layout, using JSON, for data entry and retrieval. Where we were able, we also provide a common SQL-like filtering language, making it dirt simple to find the data you need.

We like to call this our “blend layer”. The idea is, just like a DAO (data access object), we simplify the process to use your data. In fact, we have made this so “blended” it is nearly identical to our SQL DB REST API, which we think is powerful but simple to use. So how does this help you? Well, you write the app once, and store your data in your favorite storage. If or when your “favorite” storage changes (i.e. if something cheaper or faster comes along), your app and data will only require minor changes (if any) to keep chugging along.

So what does this wonderful interface look like? Well, since you asked so nice and all. Once you get your service setup on your DSP (using the documentation mentioned earlier, or the vendor-specific follow-up blogs to this blog), you can use our Live API documentation interface to see how it works. Of course, you can use cURL or your favorite REST testing browser app, it is up to you. Since I am such the promotionalist, I'll use the Swagger interface in this blog to show you.

As with the other DSP services, we put a mostly standard meaning to the HTTP verbs in our REST API. Here is the how they are used for NoSQL services (using “mongo” as our demo service).

Note that the 'POST' is used for creating new entities, 'PUT' is used for replacing the whole content of an entity and 'PATCH' is used to merge in changes to an entity ('PATCH' and 'MERGE' HTTP verbs are used interchangeably in the REST API). Also, as with the other DSP services, if your transport layer only accepts 'GET' and 'POST' verbs, using a 'POST' command while setting either a url parameter, 'method=PATCH', or a header, X-HTTP-Method = PATCH will “tunnel” the command through properly.

Table Administration

Azure calls them “tables”. So does AWS, that is for DynamoDb, but for SimpleDb, they are called “domains”. For CouchDb, they are called “databases”, not to be confused with MongoDb's “databases” which house what they call “collections”. See what I mean?? While each vendor calls them something different, we will refer to them collectively as “tables”. These are the things that group your sets of data together or partition them, however you see it. You can actually manage most aspects of the table administration through the REST API.

To get a list of currently available tables, just send a GET request to the service's root, which, if you are using one of our free-trial DSPs, looks like this.

https://dsp-mydspname.cloud.dreamfactory.com/rest/my_service_api_name_here

Every DSP service, when queried at its root with no extra url parameters, returns an array of resources available by that service. In the SQL and NoSQL case, these resources are table names.

{

   "resource": [

      {

         "name": "test"

      },

      {

         "name": "zipcodes"

      }

   ]

}

To include vendor-specific table information, if any is available, you can send 'include_properties=true' as a url parameter in the request. You can also limit the returned data by specifying the tables by name, using the 'names=table1,table2,table3' url parameter. The returned data will include a table array, where each object in the array includes at least the 'name' field and value, as well as, any available vendor-specific properties of the table, i.e. keys, etc.

https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo?include_properties=true&names=test%2Cuploads

{

   "table": [

       {

           "name": "test",

           "indexes": [

               {

                   "v": 1,

                    "key": {

                       "_id": 1

                   },

                   "ns": "test.test",

                   "name": "_id_"

               }

           ]

       },

       {

           "name": "uploads",

           "indexes": []

       }

   ]

}

Use this same data format for creating new tables, or updating and deleting existing tables. You can also use the 'names' url parameter in a delete request to delete existing tables.

Record Administration

Again, whether they are called “entities”, “documents”, or “items”, we collectively call them “records”. These are the groups of name-value pairs that make up your app's data. The DSP REST API provides the following ways for your app to retrieve, create, update and delete data from the tables.

Retrieving Records

When it comes to making your NoSQL data available to your app, our REST API provides several different methods to get just the data sets you want, when you want it. Here are the available options for a GET request on a particular table.

The table name is sent as part of the url, while the other options are sent as url parameters. If url parameters are not your cup of tea, you can send most of them as posted data (using POST request with X-HTTP-METHOD = GET as mentioned earlier. Note that the “record” parameter can only be sent in this way.). Most of them are self explanatory, but a few may need clarification.

• “ids” - This is a comma delimited list of unique identifiers (think primary key) values for retrieving multiple records at a time. If passed as POST data, this can also be an array of ids. In the case where they is no static identifier field (DynamoDb), use  of the “id_field” parameter is necessary to indicate which field these values belong to.
• “filter” - This is where you use our simple SQL-like filter string. Comparison operators supported on every platform are ' = ', ' != ', ' > ', ' >= ', ' < ', ' <= '; or as their SQL short form ' eq ', ' ne ', ' gt ', ' ge ', ' lt ', ' le '. Depending on the db vendor, others such as ' contains ', ' like ', and ' begins_with '. Spaces surrounding the operator is required. String values on the right side of the comparison must be within single of double quotes. Depending on the db vendor, logical comparisons (AND, OR, NOT) are also supported. More on the differences in later blogs. The whole filter string must be url-encoded.
• “record” - Used only in POST data, this option allows you to send partial (minimally the identifier fields) or complete records back to the database to be “refreshed” with the latest values. This is also helpful in instances where the table has multiple indexes, i.e. DynamoDb and Azure Tables.

For example, if we want to find the first 3 records in the zipcodes table information that have a population of over 20,000 people, returning only the city, state and percentage over 40 years of age. If you are familiar with SQL, this would look like “SELECT city,state,PopulationOver40 from zipcodes WHERE pop > 20000;”. Here is what it looks like in our REST API.

https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes?filter=pop+%3E+20000&limit=3&fields=city%2Cstate%2CPopulationOver40

And this is what gets returned...

{

 "record": [

   {

     "_id": "28659",

     "city": "NORTH WILKESBORO",

     "state": "NC",

     "PopulationOver40": 9.19

   },

   {

     "_id": "31201",

     "city": "HUBER",

     "state": "GA",

     "PopulationOver40": 99.877

   },

   {

     "_id": "71291",

     "city": "WEST MONROE",

     "state": "LA",

     "PopulationOver40": 80.099

   }

 ]

}

Creating Records

Use this same data format above for creating new records. One could post an array of records, or a single record like this...

POST https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes

   {

     "_id": "29684",

     "city": "STARR",

     "state": "SC",

     "pop": 2889,

     "PopulationOver40": 82.4

   }

If the DB vendor does not automatically create the primary key, it must be included in the POST request. The applicable keys are always returned for successfully created records on a create request.

Updating and Merging Into Records

The same array of records or a single record format with changes is supported for updating records. Using the PUT HTTP verb as mentioned above will replace the whole record with the posted data if found by matching identifiers in the record.

PUT https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes

   {

     "_id": "29684",

     "city": "STARR",

     "state": "SC",

     "pop": 2890,

     "PopulationOver40": 79.4

   }

If you only want to merge changes into a record without having to reset everything, then use the MERGE or PATCH HTTP verb and send only the changes along with the identification fields.

PATCH https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes

   {

     "_id": "29684",

     "pop": 2890,

     "PopulationOver40": 79.4

   }

If you only want to update or merge data for one record, and it can be identified by a single key field, then you could also add the id to the end of the url, and pass only the fields that require change.

PATCH https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes/29684

   {

     "pop": 2890,

     "PopulationOver40": 79.4

   }

Two other methods of merging data are by id list or filter (using “ids” or “filter” url parameters mentioned above for retrieving records). In these cases, if not natively supported, the server will query the table for the filtering results, merge in the record changes and push the changes back in an update request. This is an easy way of updating multiple records with the same field-value changes.

Deleting Records 

Deleting is similar to updating records but no posted data is required.

More to Come...

Stay tuned for a series of blogs on each of the supported NoSQL services and how to use them in your next awesome app.