Tony Harris - October 4, 2013

LeeHicksFor the third installment on this blog series on DreamFactory Services Platform (DSP) support for NoSQL, I wanted to include a little more information on how to use MongoDB as a service. As you may or may not know, MongoDB is the leading NoSQL database currently, and while it does come with a REST interface, it is simple and has no support for CRUD operations on the data itself and is generally used for monitoring or administrative tasks. We have taken one of the native drivers available and added a fully REST-based and “blended” (see earlier post) API for collection and document (otherwise known as table and record) operations.

MongoDB as a Service

As I mentioned in the earlier blog, DSPs allow you to “on-board” services that you want to use from your app, and at the same time give you user role-based access control of those services. These services can be local to the server running the DSP itself, or remotely running on a different server altogether. In the case of MongoDB, it can be loaded locally on the same server, remotely on a different server, or hosted on the web by a third party like MongoHQ or MongoLab.

To add your MongoDB as a service on your DSP, select the ‘Services’ menu option on the admin console and select ‘Create New Service’ on the far left if an empty form is not already present. Set the ‘Type’ drop down menu to ‘NoSQL DB’, select your desired service and API names. Select MongoDB for the ‘NoSQL Type’ drop down menu. You should see something like the following…

config

The Connection String can be a fully qualified connection containing the authentication (if required), host name, port (if not default) and database. If that is the case, then nothing is required for the ‘Database’, ‘Username’, and ‘Password’ fields.

Note the following exceptions…

  • If the username or password are required and contain special characters, particularly @, :, or / (see MongoDB documentation) in them, then they must not be in the connection string and must be added in the given form fields below.
  • If you desire for the database name to not be in the connection string, then it is required in the form field below. The service is defined for one database at a time.
  • If you don’t add ‘mongodb://’ to the beginning of the connection string it will be automatically added for you during use.
  • The connection string can be blank if the host name and port are not required because you are using mongo with the local default setup, i.e. localhost:27017. In this case, use the other fields for any required configuration, i.e. database.

Once you save the configuration, you can click the document icon to the right of the service listing and get the Swagger UI interface for documentation and testing or issue commands from our javascript sdk, your hosted or native app, or cURL, etc.

See my notes in the earlier blogs in this series here and here about how the HTTP verbs function and basic operations.

Table (Collection) Administration

To get a list of currently available collections and their details, just send a GET request to the service’s root. Add the URL parameter ‘include_properties’ to see MongoDB-specific details about each collection, including any default or provisioned indexes.

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

The response will look like the following…

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

Use this same data format for creating new tables, or deleting existing tables. 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" } ] }'

Note: Currently, you can’t create or update indexes or cap the size of new collections through this interface. This functionality is planned for release at a later time.

You can also use the ‘names’ url parameter in the retrieve request or for a delete request to delete existing tables.

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

All table operations return, at a minimum, an array of table names affected by the operation. For example, the above two operations return…

{
  "table": [
      {
	    "name": "test"
	  }
    ]
}

Record (Document) Administration

In the previous blog, I described the “blended” REST API operations for record CRUD operations. As mentioned, all of those apply to using MongoDB as a service. However, for those of you who enjoy MongoDB’s json format, particularly for query request, DreamFactory also supports most of MongoDB’s json query document format.

Query Operations

Here are the available options for a GET request on a particular table in the DreamFactory REST API for NoSQL DBs.

get

The table name is sent as part of the url, while the other options are typically sent as url parameters. If you would like to use MongoDB’s json query document format, you can send the ‘filter’ parameter in json format as part of the posted data. For example, if we want to find the first 3 records in the zipcodes table 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 using the “blended” approach with a simple SQL filter.

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

Using MongoDB’s native json query document format, we will post the ‘filter’ parameter as json data. The ‘limit’ and ‘fields’ parameters will continue to be passed as URL parameters. With cURL, we can accomplish this by using the -X GET option to pass the data.

curl -X GET 'https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes?limit=3&fields=city%2Cstate%2CPopulationOver40' 
     -H 'Content-Type: application/json' 
     -d '{"filter": { "pop": { "$gt": 20000 } } }'

Other utilities or clients may require the HTTP method to be specified as POST and include either a URL parameter ‘method=GET’ or an extra header value of ‘X-HTTP-METHOD = GET’ (shown here for clarity).

curl -X POST 'https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes?limit=3&fields=city%2Cstate%2CPopulationOver40' 
     -H 'X-HTTP-METHOD: GET' 
     -H 'Content-Type: application/json' 
     -d '{"filter": { "pop": { "$gt": 20000 } } }'

In all three cases, 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
    }
  ]
}

Passing the filter this way opens up use of the query operators supported natively in MongoDB. Besides record retrieval, these query documents can also be used as filters when updating or deleting records, along with the other methods mentioned in the ‘Updating and Merging Into Records’ section of the previous post.

Updating Records

As mentioned in the previous blog, updates to NoSQL records using the API generically take on one of two forms; either replacing the whole record by posting the new name-value pairs along with any old name-value pairs that you want to keep to that record’s identifier (using PUT requests), or by merging changes to exiting records by posting only the changed or new name-value pairs (using PATCH requests).

For the second scenario, merging changes, MongoDB also supports specialized update operators that facilitate changes to record data in various ways. These can also be passed in the posted data as part of the records themselves when using the json format.

For example, consider a record in the zipcodes table with the following data…

    {
      "_id": "29684",
      "city": "STAR",
      "state": "SC",
      "pop": 2889
    }

Lets say that we need to correct the spelling of the city name and increment the population by 5. We could do this by sending the changed name value pairs in a PATCH request to merge in the changes as follows…

curl -X PATCH 'https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes/29684' 
     -H 'Content-Type: application/json' 
     -d '{ "city": "STARR", "pop": 2894 } } }'

Obviously, for the population increase to be accurate we would have to pull a copy of the value to the client to get the latest before the modification, not ideal. Using the native MongoDB update operators, we can send it as follows…

curl -X PATCH 'https://dsp-mydspname.cloud.dreamfactory.com/rest/mongo/zipcodes/29684' 
     -H 'Content-Type: application/json' 
     -d '{ "$set": { "city": "STARR" }, "$inc": { "pop": 5 } }'

Either way yields the results…

    {
      "_id": "29684",
      "city": "STARR",
      "state": "SC",
      "pop": 2894
    }

What Next?

Stay tuned for more specifics on other NoSQL vendors, like AWS DynamoDB, etc.