Tony Harris - August 28, 2013

LeeHicksAs a continuation to my initial blog on NoSQL support on the DreamFactory Services Platform (DSP), I would like to give you a little more information on how to use the NoSQL service operations. Designed to be flexible and powerful, yet still adhere to simple REST principles, there are several options for CRUD operations available in the DSP REST API for NoSQL services.

Once you have configured your NoSQL service, you can click the document icon to the right of the service listing on the admin console and get the Swagger UI interface for documentation and testing as seen below.

DreamFactory MongoDB Rest API

Notes about this blog:

  1. I am using MongoDB as our example service but all supported NoSQL types behave similarly, with some exceptions.
  2. Also, I am using JSON format everywhere here, but XML is also supported.
  3. In the cURL request, not all headers are included in request, i.e. authentication, app name, etc. You can use the Swagger UI to perform many of these same calls much easier.
  4. See my notes in the earlier blog about how the HTTP verbs function.

Table Administration

As you see from the list above, all table administration operations utilize the root of the service for the URL. All tables are identified by a ‘name’ field in the operations. All posted or returned data is transmitted as a single object representing a table containing a ‘name’ field at a minimum, or an array of table objects returned as a ‘table’ field value, except where noted.

Retrieving Tables

Every DSP service, when queried at its root with no extra parameters, returns an array of ‘resources’ available by that service. In the SQL and NoSQL case, these resources are table names. 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 hosted DSPs, looks like this.

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

The response looks like…

{
  "resource": [
    {
      "name": "test"
    },
    {
      "name": "zipcodes"
    }
  ]
}

To add vendor-specific details to the output, just add the query parameter ‘include_properties’ set to true. In this case, we get MongoDB-specific details about each collection.

curl https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo?include_properties=true

The response looks like…

{
    "table": [
        {
            "name": "test",
            "indexes": [
                {
                    "v": 1,
                    "key": {
                        "_id": 1
                    },
                    "ns": "test.test",
                    "name": "_id_"
                }
            ]
        },
        {
            "name": "zipcodes",
            "indexes": []
        }
    ]
}

Additionally, there is a ‘names’ query parameter accepting a comma-delimited list of table names that allows the client to selectively retrieve information about specific tables

curl https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo?include_properties=true&names=test,zipcodes

Creating Tables

Use the POST operation to create one table or multiple tables at once. At a minimum, a value for the ‘name’ field is required to create a table. Additional vendor-specific fields may also be sent. Some vendors may require additional fields. Again, a single object or an array of objects is permitted in the posted data. The output will reflect the input, i.e. an array posted will result in an array received. To create a new table, use the following…

curl -X POST https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo 
     -H 'Content-Type: application/json' 
     -d '{ "table": [ { "name": "test", … } ] }'

The response looks like…

{
    "table": [
        {
            "name": "test",
            "indexes": [
                {
                    "v": 1,
                    "key": {
                        "_id": 1
                    },
                    "ns": "test.test",
                    "name": "_id_"
                }
            ]
        }
    ]
}

Updating Tables

Most of the NoSQL vendors accept little to no updates on the meta-data part of the tables. Some do however, and those would be accessed as follows…

curl -X PATCH https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo 
     -H 'Content-Type: application/json' 
     -d '{ "table": [ { "name": "test", “indexes”: {…} } ] }'

Deleting Tables

Obviously this should be approached with caution. You can use the ‘names’ query parameter in a delete request to delete existing tables.

curl -X DELETE https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo?names=test

An additional option for delete would be to post the same data format as the other operations.

curl -X DELETE https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo 
     -H 'Content-Type: application/json' 
     -d '{ "table": [ { "name": "test" } ] }'

Record Administration

The DSP REST API provides the following ways for your app to retrieve, create, update and delete data from the tables. The table name is sent as part of the URL, while the other options are sent as query parameters or as part of the posted data. Many of the query parameters can also be sent via the posted data.

One special query parameter available on all requests is the ‘fields‘ parameter. This dictates which fields will be returned for the affected records of the operations. It accepts a comma-delimited string of field names when passed as a query parameter, or an array of field names when passed in posted data. For GET request, this parameter defaults to returning all fields, i.e. a ‘*’ value, while all other request types return only the record identifying fields by default. This saves the client from having to do an additional round-trip call to get things like updated or auto-filled field values, or to get a list of records changed when updating by a filter.

Again, where applicable, a single object or an array of objects is permitted in the posted data. The output will reflect the input, i.e. an array posted will result in an array received.

Creating Records

To create a single record …

curl -X POST https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes 
     -d '{ "_id": "95008", "state": "CA", "pop": 39968 }'

To create multiple records…

curl -X POST https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes 
     -d '{ “record” [ { "_id": "95008", "state": "CA" }, { "_id": "30022", "state": "GA"} ] }'

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

Retrieving Records

There are many ways in which an app can retrieve data through our API. Depending on your app you may want to use one or all of these. Note that the first three also make use of the optional query parameter ‘id_field’ which allows the client to state which field is used as the identifying field for the records in that table. Here is a quick look at the retrieval options.

  • By a single record identifier – In this case, the identifying field (think primary key) value is passed as part of the URL after the table name. This will return a single record or a 404 – Not Found error.
curl https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes/95008

Returns this…

{
    "_id": "95008",
    "city": "CAMPBELL",
    "state": "CA",
    "pop": 39968
}
  • By a list of record identifiers – This case uses the ‘ids’ query parameter sent as a comma-delimited string of id values, or ‘ids’ field sent as comma-delimited string or an array of id values in posted data. If a large number or ids, or the id values are long or have special characters in them, it would be better to pass them as posted data.
curl https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes/?ids=95008,30022

Returns this…

{
    "record": [
        {
            "_id": "95008",
            …
        },
        {
            "_id": "30022",
            …
        }
    ]
}
  • By record – This case allows you to post a single record or an array of records that at a minimum include the identifying field(s) and values. This is useful in retrieving records with more than one key, or for updating a stash of records that you have old or partial data for already. (Note: The below cURL call works for *nix and mac, for Windows, use -X POST -H “X-HTTP-Method: GET” instead of -X GET.)
curl -X GET https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes 
     -d '{ “record” [ { "_id": "95008", … }, { "_id": "30022", … } ] }'
  • By filter – This is the most powerful option for data retrieval. The NoSQL service allows the client to use a simple SQL-like filter string passed as a query parameter (url-encoded), or in some vendor cases, native filters in various formats (like MongoDB’s json format, more on this in later vendor-specific blogs).

For our simple SQL-like filter string, comparison operators supported for every vendor are =, !=, >, >=, <, <=.; Or given as their SQL short form ‘ eq ‘, ‘ ne ‘ (or ‘ <> ‘), ‘ gt ‘, ‘ ge ‘, ‘ lt ‘, ‘ le ‘. Depending on the db vendor, others such as contains, like, and begins-with may also be supported. Spaces surrounding the operator is required. String values on the right side of the comparison must be within single or double quotes. Depending on the db vendor, logical comparisons (AND, OR, NOT) are also supported in order to build out more complicated queries.

Other ‘filter helper’ query parameters are also available.

  • limit – Defaults to return all, accepts an integer greater than 0 to limit the number of returned records.
  • order – Accepts a field name followed by space and then ASC or DESC to order the returned records.
  • offset – Accepts an integer greater than 0 to skip that many records in the response. This is useful for setting up paging through records, but may not be supported by all vendors.

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 and state. If you are familiar with SQL, this would look like “SELECT _id,city,state from zipcodes WHERE pop > 20000;”. Here is what it looks like in cURL.

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

And this is what gets returned…

{
    "record": [
        {
            "_id": "28659",
            "city": "NORTH WILKESBORO",
            "state": "NC"
        },
        {
            "_id": "31201",
            "city": "HUBER",
            "state": "GA"
        },
        {
            "_id": "71291",
            "city": "WEST MONROE",
            "state": "LA"
        }
    ]
}

Updating and Merging Into Records

The same options available for retrieving data also apply to updating records. As mentioned in the earlier blog, the PUT HTTP verb is used when the whole record is to be replaced with the posted data. The PATCH HTTP verb is used when the client only wants to send the changing fields to the server.

The same array of records or a single record format with changes is supported for updating records. To replace the whole record at id of 95008, send the request like…

curl -X PUT https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes 
     -d '{ "_id": "95008", "city": "Campbell", "state": "CA", "pop": 40102 }'

If you only want to merge changes into that record without having to resend everything else, then send the request like…

curl -X PATCH https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes 
     -d '{ "_id": "95008", "pop": 40102 }'

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.

curl -X PATCH https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes/29684
     -d '{ "pop": 40102 }'

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 supports the same options as updating. The only difference is, when using the id, ids or filter options, no posted data is required. Obvious, right? Using the ‘fields’ query parameter, the client can request the full or partial records before they are deleted from the system.

What Next?

Stay tuned for more specifics on each NoSQL vendor, next up more on MongoDB.