Full-Stack Engine

How to translate MongoDB to SQL - Building a "try it out" app

In the previous part of this article, we made a tour over the concepts regarding the similarities and differences on using a NoSQL database (especially MongoDB) and a SQL or relational database, how to translate a MongoDB statement to a SQL statement and of course, we saw in what cases we would need to do such a thing.

Let's briefly recap out agenda:

  1. First part: How to translate MongoDB to SQL statements.
  2. (*) Second part: Building a "try it out" MongoDB to SQL translator.

We finished the first part with the demonstration of a "Try it out" app that we can use to translate MongoDB statements into SQL statements, allowing us to 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

We called this app as mongo-sql-tester. You can access it at [http://mongo-sql-tester.herokuapp.com/](http://mongo-sql-tester.herokuapp.com/

Now, we'll continue learning how to build this application in code, step by step using Sails, React/Redux, Bootstrap 4 and of course mongo-sql.

For starters, we can begin using an existing template. In a previous post I wrote about how to create a starter project using Sails, React and webpack. We can use this project as the starting point; you can find the code in this repository.

Let's start by cloning the project,

git clone https://github.com/sepineda/sails-react-webpack.git

The next step is to add the packages we'll need, let's make a list,

  1. codemirror: To let us have a couple of text editors, one for MongoDB queries, styled as JavaScript code syntax, and other for the translated SQL code, styled with SQL syntax as well.
  2. react-codemirror2: As codemirror is a pure JavaScript library, react-codemirror2 simplifies the process of integrating it with React.
  3. bootstrap: We'll use it to provide style and layout to the entire application.
  4. mongo-sql: The library we'll use to translate statements from MongoDB to SQL.
  5. redux and react-redux: Perhaps the use of Redux is not strictly necessary in a small application like this one, but we'll use to make the code cleaner and separate the concerns of UI presentation and statements translation.

Without any more delays, let's start writing our application.

Packages and dependencies

First, install all the packages from the previous list,

npm install --save codemirror react-codemirror2 mongo-sql redux react-redux

Alternatively, if you use yarn (faster on installing packages),

yarn add codemirror react-codemirror2 mongo-sql redux react-redux

Moreover, then, we add Bootstrap 4 library with its dependencies,

npm install --save bootstrap popper.js jquery

Alternatively, using yarn,

yarn add bootstrap popper.js jquery

Actions/Reducers and convertMongoToSql() function

Once we have installed all packages, next step is adding a Redux store in out assets/src/index.js, we do this using createStore from the redux library, and to link this with react, we set a Provider component from react-redux.
In index.js, we also import bootstrap js code and style sheets.

import React from 'react';
import ReactDOM from 'react-dom';
import 'bootstrap';
import 'bootstrap/dist/css/bootstrap.min.css';
import { createStore } from 'redux';
import { Provider } from 'react-redux';
import conversion from './reducers/convert';
import Main from './components/Main';

const store = createStore(conversion);

const App = () => (
    <Provider store={store}>
      <Main />
    </Provider>
);

const element = document.getElementById('root');
ReactDOM.render(<App />, element);

Now, let's add our reducers, by creating a new folder in src/reducers and then adding a new reducer called convert.js,

import { combineReducers } from 'redux';
import { CONVERT_MONGO_SQL } from '../actions/actions';
import convertMongoToSql from '../components/Utils';

const conversion = (state = {}, action) => {
  switch (action.type) {
    case CONVERT_MONGO_SQL:
      return convertMongoToSql(action.value);
    default:
      return state;
  }
};

export default combineReducers({ conversion });

As you can see, we have a simple state object and a single action (we'll see in short). The routine to respond to the conversion action goes in a separate Utils module we'll name convertMongoToSql.js,

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

function looseJsonParse(jsString) {
  return Function(`"use strict";return ( ${jsString} );`)();
}

export default function convertMongoToSql(mongoString) {
  try {
    const enclosingText = mongoString.trim().match(/\{([^\b]+)\}/)[0];
    const jsCode = looseJsonParse(enclosingText);

    const result = builder.sql(jsCode);
    const values =
      result.values.length > 0
        ? `\n--Values:\n${result.values.map((v, i) => `--$${i + 1}= ${v || ''}`).join(',\n')}`
        : '';

    return {
      result: result.toString() + values,
      status: true,
      message: 'Query compiled succesfully',
      values: result.values
    };
  } catch (e) {
    return {
      result: '',
      status: false,
      message: `Error: ${e.message}`,
      values: []
    };
  }
}

The previous block is the core of our application, let's see it in detail.

First, we have a builder object we load from mongo-sql and use its function sql(jsCode), which accepts a JavaScript object. Let's take the example from mongo-sql site,

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

We need to load the code user types in the text editor and somehow translate it from plain text to a JavaScript Object, as usersQuery in this example.

The first step is to detect where in the code typed by the user, there's a code section enclosed in {...} (curly braces). For this, we use a regular expression: /\{([^\b]+)\}/, that matches all code following this pattern.

Once we have the code for the js object, the next step is converting it into an actual object in memory, that's the task of function looseJsonParse(jsString). It uses Function constructor (in an eval fashioned way) with an interpolated string including the previous section code enclosed in curly braces.

That's it for statement conversion. We have the query ready to convert using mongo-sql. If the result is a success, the code returns a state object for redux, representing the actual result as a SQL query, the status (we'll use it in a status bar), a message and the query values. On the other hand, if the conversion failed, the codes return a state object with an empty result and a message indicating what is wrong with the code typed by the user.

UI components and containers

Now, let's move to UI code, but before that, let's briefly add the code for the action we call every time the user types something in the editor,

export const CONVERT_MONGO_SQL = 'CONVERT_MONGO_SQL';

export function convertMongoSql(mongoString) {
  return {
    type: CONVERT_MONGO_SQL,
    value: mongoString
  };
}

This code takes the input from the code editor and passes it to the reducer defined in the previous step.

Create the components folder src/components and inside it, add Main.js,

import React from 'react';
import CodePanel from '../containers/CodePanel';

const Main = props => (
    <div>
      <nav className="navbar navbar-toggleable-md navbar-inverse bg-inverse">
        <button
          className="navbar-toggler navbar-toggler-right"
          type="button"
          data-toggle="collapse"
          data-target="#navbarSupportedContent"
          aria-controls="navbarSupportedContent"
          aria-expanded="false"
          aria-label="Toggle navigation"
        >
          <span className="navbar-toggler-icon" />
        </button>
        <a className="navbar-brand" href="#">
          mongo-sql tester
        </a>
      </nav>
      <CodePanel />
    </div>
);

export default Main;

These components have a simple navigation bar and host the <CodePanel/> container.

Now, let's create a folder src/containers and add CodePanel.js,

import { connect } from 'react-redux';
import { convertMongoSql } from '../actions/actions';
import CodeMirrorPanel from '../components/CodeMirrorPanel';

const mapStateToProps = state => (state.conversion);

const mapDispatchToProps = dispatch => ({
  onWriteDown: (text) => {
    dispatch(convertMongoSql(text));
  }
});

const CodePanel = connect(mapStateToProps, mapDispatchToProps)(CodeMirrorPanel);

export default CodePanel;

Thre previous is a container that maps the state to props for the main component called <CodeMirrorPanel/>

Back to components folder, let's add CodeMirrorPanel.js component,

import React, { Component } from 'react';
import { Controlled as CodeMirror } from 'react-codemirror2';
import 'codemirror/lib/codemirror.css';
import 'codemirror/mode/javascript/javascript';
import 'codemirror/mode/sql/sql';
import 'codemirror/mode/markdown/markdown';
import './_codeMirrorPanel.css';

class CodeMirrorPanel extends Component {
  constructor() {
    super();
    const initialString = `{
  "type": "select",
  "table": "users"
}`;
    this.state = {
      codeLeft: initialString,
      mode: 'javascript'
    };

    this.updateCode = this.updateCode.bind(this);
  }

  componentDidMount() {
    const { onWriteDown } = this.props;
    onWriteDown(this.state.codeLeft);
  }

  updateCode(editor, data, newCode) {
    const { onWriteDown } = this.props;
    this.setState({
      codeLeft: newCode
    });
    onWriteDown(this.state.codeLeft);
  }

  render() {
    const { result, message, status } = this.props;

    const optionsLeft = {
      lineNumbers: true,
      mode: this.state.mode,
      lineWrapping: true
    };

    const optionsRight = {
      lineNumbers: true,
      mode: 'text/x-sql',
      lineWrapping: true
    };

    const taClass = `form-control ${status ? 'text-success' : 'text-danger'}`;

    return (
      <div>
        <div className="row">
          <div className="col">
            <CodeMirror
              value={this.state.codeLeft}
              onBeforeChange={(editor, data, value) => {
                this.setState({ codeLeft: value });
              }}
              onChange={this.updateCode}
              options={optionsLeft}
              autoFocus={true}
            />
          </div>
          <div className="col">
            <CodeMirror options={optionsRight} value={result} />
          </div>
        </div>
        <div className="row bg-faded">
          <br />
        </div>
        <div className="form-group row container-fluid">
          <textarea className={taClass} rows="6" value={message} />
        </div>
      </div>
    );
  }
}

export default CodeMirrorPanel;

This is the main UI component, where the text editors are defined. We have a <CodeMirror/> component in the left column, for MongoDB statements, which has a mode set to javascript and calls updateCode() every time a key is typed, which in turn calls the onWriteDown() function from <CodePanel/> container and finally dispatchs the convertMongoToSql(text) action.

On the other hand, we have another <CodeMirror/> component in the right column to show the resulting translated SQL statement and a textarea that shows the message sent as the status of the conversion (note that taClass also changes the color based on the conversion result).

Finally, we add the styles for <CodeMirrorPanel>,

.ReactCodeMirror {
    border: 1px solid #ccc;
    height: 100%;
}

.CodeMirror {
    min-height: 75vh;
}

textarea {
    height: 100%;
    width: 100%;
}

The final step is modifying assets/scr/webpack.common.js; you can find the source here. (I didn't include it here to keep the amount of code shown to a manageable length for reading).

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

mongo-sql

Thank you so much for reading this blog please stay tuned for more exciting news and full-stack topics. See you next time, take care!.

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