@synatic/sql-to-mongo

Package Name Updated to @synatic/noql

Please note this package is now deprecated, as it has been renamed to NoQL. You should use @synatic/noql instead. This package will be removed in the future.

NoQL - Not Only SQL

NoQL Converts SQL statements to Mongo find statements or aggregation pipelines. NoQL supports mySQL and Postgres Syntax, and generates Mongo 3.6 or greater compatible queries.

For full docs and a playground to try NoQL out, vist https://noql.synatic.dev/

Installation

Install NoQL using the npm install command:

npm i @synatic/noql

Usage

NoQL outputs an object with the type, either query or aggregate, along with the components of the Mongo query. To use the output object, construct a query with MongoClient from the MongoDB NodeJS Driver:

const SQLParser = require('@synatic/noql');
const {MongoClient} = require('mongodb');

(async () => {
    try {
        client = new MongoClient('mongodb://127.0.0.1:27017');
        await client.connect();
        const db = client.db('sql-to-mongo-test');

        const parsedSQL = SQLParser.parseSQL('select id from `films` limit 10');
        if (parsedSQL.type === 'query') {
            console.log(
                await db
                    .collection(parsedSQL.collection)
                    .find(parsedSQL.query || {}, parsedSQL.projection || {})
                    .limit(parsedSQL.limit || 50)
                    .toArray()
            );
        } else if (parsedSQL.type === 'aggregate') {
            console.log(
                await db
                    .collection(parsedSQL.collections[0])
                    .aggregate(parsedSQL.pipeline)
                    .toArray()
            );
        }
    } catch (exp) {
        console.error(exp);
    }
})();

NoQL Output Examples

NoQL outputs an object with the type, either query or aggregate, along with the components of the Mongo query. Here are some examples of the output:

For a straight query:

SQLMongoParser.parseSQL("select id from `films` where `id` > 10 limit 10")

NoQL will output:

{
    "limit": 10,
    "collection": "films",
    "projection": {
        "id": "$id"
    },
    "query": {
        "id": {
            "$gt": 10
        }
    },
    "type": "query"
}

For an aggregate query:

SQLMongoParser.makeMongoAggregate("select id from `films` where `id` > 10 group by id")

NoQL will output:

{
    "pipeline": [
        {
            "$match": {
                "id": {
                    "$gt": 10
                }
            }
        },
        {
            "$group": {
                "_id": {
                    "id": "$id"
                }
            }
        },
        {
            "$project": {
                "id": "$_id.id",
                "_id": 0
            }
        }
    ],
    "collections": [
        "films"
    ]
}

Currently Unsupported SQL Statements

• Over
• CTE's
• Pivot
• Union

See more in the full docs at https://noql.synatic.dev/