Full-Stack Engine

How to translate MongoDB to SQL

In recent years there's been an explosion in Big Data in enterprise and research applications, and with it, the need of using another kind of databases apart from the traditional SQL-based from the last 20-30 years. I'm talking of course of what's known as NoSQL databases, as you might know, the term "No-SQL" it's a broad category that includes all DBMS which doesn't use SQL as its query language.

One of the most popular No-SQL databases is MongoDB, a database in the document-based sub-category of NoSQL databases (take a look at the different categories here). In particular, MongoDB uses documents based on JSON to structure the data among with schemas, for what a query in MongoDB is similar as defining an Object in JavaScript Object Notation (or simply JSON).

Nevertheless, NoSQL is handy in applications related to Big-Data, in many cases is not a replacement for traditional SQL databases, but another tool we can use to work along with SQL.

There could be several reasons for why we still need a SQL database, one of the most common directly that many existing databases uses SQL, and a new project should make use of it. Even with a whole new project, when we can choose a NoSQL database from the beginning, the right choice is not always NoSQL, there are many cases when we still need relational tables in our application, yet in a Big-Data context. For such cases, a NoSQL database might fit in the client or middleware side, as caching the millions of records instead of querying every single time from the SQL DB.

MongoDB - SQL relationship

MongoDB developers know the importance of providing the means to interact with SQL, so they give us a comparison chart of the query expressions equivalences between both. However, how can use all of these in a project where we need to integrate MongoDB and SQL. The answer is not to reinvent the wheel and use an existing library that makes the work for us. A good library I've found for this endeavor is mongo-sql. It's easy to use and integrate into our project, as we can put it in the back-end with the help of Node.js or in the front-end with any JS framework of our preference (React, Angular, Vue, Ember, and others). We can also go Full-Stack implementing an architecture that separates the concerns of creating the MongoDB statement in client side and the actual conversion on the server side. The choice is yours, and you have great flexibility with mongo-sql library.

An extract from mongo-sql shows how to perform a simple conversion,

var builder = require('mongo-sql');

var usersQuery = {
  type: 'select'
, table: 'users'
, where: { $or: { id: 5, name: 'Bob' } }
};

var result = builder.sql(usersQuery);

result.values     // Array of values
result.toString() // Sql string value

Which is translated into the following,

select "users".* from "users" where "users.id" = $1 or "users"."name" = $2

On the other hand, MongoDB project also provides some examples of how queries translate from Mongo statement to SQL statements. Here are some examples,

MongoDB find() Statements SQL SELECT Statements
db.people.find( { status: "A", age: 50 } ) SELECT * FROM people WHERE status = "A" AND age = 50
db.people.insertOne( { user_id: "abc123", age: 55, status: "A" } ) CREATE TABLE people ( id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id Varchar(30), age Number, status char(1), PRIMARY KEY (id) )
db.people.updateMany( { }, { $set: { join_date: new Date() } } ) ALTER TABLE people ADD join_date DATETIME
db.people.find().limit(5).skip(10) SELECT * FROM people LIMIT 1
db.people.deleteMany( { status: "D" } ) DELETE FROM people WHERE status = "D"

As you can notice, there are several resources to make use of, when it comes to translating MongoDB to SQL. This probably to the long life MongoDB has been on the market that has given it a high level of adoption and maturity. For this reason is a good idea to use MongoDB statements to do a translation to SQL, even if you are not using MongoDB as your NoSQL database or if you are not using a NoSQL database at all.

MongoDB statements are pretty simple to write as it uses JSON, you can write a query as you write any other statement in JavaScript, which makes it ideal for another kind of applications, think for example a search engine that allows the user to query a database using simple conditions, much more human-like in contrast to using SQL directly. Many potential applications are using this approach.

A mongo-sql "try it out" application

Now, let's see a simple "try it" web application in action, that let us do the following:

  • Input a MongoDB query as JavaScript Object Notation JSON.
  • Input a MongoDB query as plain JavaScript code.
  • Translate the query in real-time as we type.
  • Show the status of the translation in a status panel

I gathered these "desired features" to build and deploy this app at http://mongo-sql-tester.herokuapp.com/. You can access at any time and use it. As it is a free instance in Heroku, it goes to sleep after 30 minutes of inactivity, so it takes around 30 seconds to start up again (please be patient :)).

Note: You can find the complete sources of the mongo-sql "try it out" in this repository.

The following animation shows this "try it out" app in action, first translating a query from JavaScript code and then from a JSON expression, let's see,

mongo-sql

We'll continue building this application from scratch, step by step and analyzing implementation details, using React/Redux, Bootstrap 4 and of course mongo-sql in the next part of this article.

I hope you found this useful. Thank so much for reading this post. Please stay tuned for more exciting news and full-stack topics. Take care!.

Author image
Costa Rica
Passionate Software Developer with full-stack development experience.