Run SQL Queries on MongoLab

This is a guest post by John A. De Goes, CTO of SlamData. SlamData is the commercial company behind the open source project of the same name. John is the original author and an active contributor to the SlamData project.

SlamData is a relatively new open source project that lets you write and execute SQL queries against a MongoDB instance. We just launched 1.0 of the product, after many engineering years of effort.

In this post, I'll talk a little bit about what SlamData is useful for, and how you can begin using SlamData with your MongoLab account.

What SlamData's Good For

SlamData is targeted at two distinct audiences:

  1. Developers with a SQL background who are looking to build reporting into their workflows or applications. Using SQL makes it much easier to run the BI-style queries that typically show up in reports, and doesn't require you master the combination of query facilities exposed by MongoDB (find, aggregate, and map/reduce).
  2. Analysts, DB admins, QA personnel, and others who don't know how to code, but would still like to easily query or build reports based on data stored in MongoDB. A lot more people can write SQL than can write Javascript (which you may need  to do in MongoDB for complex queries).

Many developers are currently using MoSQL for reporting and BI on MongoDB. MoSQL helps you copy data from MongoDB into PostgreSQL, where you can use SQL to query the data or hook it up to a BI tool for others to query.

MoSQL is still useful, but there are a few advantages that SlamData has over MoSQL:

  • Zero Data Relocation. Every query executes 100% inside MongoDB (optionally in a replica set). This is unique to SlamData, as MoSQL and other solutions require streaming data from MongoDB or bulk relocating it to another data store.
  • Native NoSQL Data Model. Arrays and nested objects are given first-class status. You can dig into them using SQL and do all the sorts of things you'd expect. You can also query heterogeneous data. MoSQL can only handle simple nested documents, and doesn't play well with arrays or non-uniform schemas.
  • Point and Query. You can point SlamData to your MongoDB cluster and begin querying right away. No need to define any data models, perform any kind of mapping, or setup other software and hardware infrastructure (all of which you have to do with MoSQL).

As an open source project released under the same license as MongoDB, SlamData is 100% free for commercial or non-commercial use. You can download pre-built installers from the SlamData website, or build the project yourself from the code available on Github.

Next, I'm going to walk through connecting SlamData to your MongoLab account, and then we'll take a look at how to query your MongoLab account through the SlamData interface.

Using SlamData with MongoLab

Using SlamData with MongoLab is very easy. In fact, for testing, we use MongoLab internally, so you could say that compatibility with MongoLab is baked in to SlamData.

The first step after logging into your MongoLab account is choosing the MongoDB deployment you want to use with SlamData:MongoLab account home page

After you've selected the right deployment, you'll be presented with a screen similar to the following:

MongoLab collections

On this screen, you'll see a MongoDB Connection URI, with placeholders for the username and password. You'll need to copy this connection URI, modify it to include the database username and password, and then proceed to the next step.

Next, download the official installers from the SlamData website. After you run the installer and choose where you want to install SlamData, you can launch SlamData by double-clicking the icon.

You'll then see a configuration screen similar to the one shown below:

SlamData configuration

In this configuration screen, there are a few settings you can control:

  1. The root path to mount the database within the SlamData file system. I suggest just leaving this as / unless you want to mount multiple databases right away.
  2. The port you want to run the SlamData server process (called SlamEngine) which communicates with MongoDB. You can probably just use the default.
  3. The name of the MongoDB database you want to mount at the specified path.
  4. The full MongoDB Connection URI (including username and password) you copied from your MongoLab deployment page.

Once you enter these configuration details, SlamData will launch the SlamEngine server that talks to MongoDB, and you can begin writing queries. Note that if you want to use the REPL or the API, you can checkout the SlamEngine project on Github.

Querying Data on MongoLab

Once you launch SlamData, you'll see the interface shown in the following video:

You can enter SQL queries in the editor, and hit Ctrl + Enter (Windows / Linux) or Cmd + Enter (Mac) to run the queries. The results appear in a table below, and from there you can choose to put them in a chart.

Currently, a large subset of SQL is supported, including SELECT, WHERE, ORDER BY, LIMIT, OFFSET, GROUP BY, JOIN, and much more.

You can find more information on supported functions on the SlamData doc page.

Note: SlamData allows you to mount different databases to different paths in the file system. So when you write a query, you must reference the full path to the data. If you mount a database at the root (/), then you must reference the collections in the database as "/[collection-name]", including the quotes. For example, if you have a collection named zips, you can query from it using SELECT * FROM "/zips".

Contributing to SlamData

SlamData's an open source project, so if you like what you see, please consider supporting the project in various ways:

  1. Watching, forking, and starring the repositories.
  2. Submitting pull requests, bug reports, and feature requests.
  3. Telling your family, friends, and household pets about SlamData (Twitter, Reddit, etc.).

We also have a newsletter you can sign up for if you want to be notified when the production release goes live. Version 1.0 is a big step for us, but we still have a long way to go.