js-sql-from-json

Table of Contents

1. Why to use sql-from-json
2. Getting Started
   • Setup
   • Accepted Operators
3. Finally Run it
4. Support Development

Why to use sql-from-json

You should use this library when working with javascript and you are wanting to convert some json into usable sql select queries.

Getting Started

Prerequisites

This library is pretty strict on format. This is for validation reasons. A strict incoming object means a more successful sql query returned.

Install

npm install js-sql-from-json

Setup

• You will need to determine your DB Schema.
• The schema needs to be json formatted like so:

let tablesAndColumns = [
  {
    table: "USERS",
    columns: [
      { name: "partyNo", type: "string", length: "20" },
      { name: "name", type: "string", length: "20" },
      ...
    ],
  },
  {
    table: "Businesses",
    columns: [
      { name: "partyNo", type: "string", length: "20" },
      { name: "name", type: "string", length: "20" },
      ...
    ],
  },
  ...
]

• Once you have your schema created. Go ahead and set the lookup table like so:

const {
  setLookupTable,
} = require("js-sql-from-json");

setLookupTable(tablesAndColumns);

• When your lookup table is set, its time to query your schema.

Accepted Operators

• Currently, we only allow these operators:
  • (Not) Less than - <, !<
  • (Not) Greater than - >, !>
  • (Not) Equals - =, !=
  • Less than or equal - <=
  • Greater than or equal - >=
  • (N)IN - in, nin
  • Starts With - sw
  • Ends With - ew
  • Contains - contains
  • (Not) isNull - isNull (value should be true or false for !isNull),
• These operators will be placed in your json object.

JSON Format

let json =
  {
    type: "USERS",
    fields: [
      {
        $and: [
          {
            field: "name",
            matches: [{ value: "ben", compare: "=" }],
          }
        ],
        $or: [],
      },
    ],
    selectFields: [],
    **paramCount: 1**
  };

• Type: This is your table name in your lookup table declared above.

• Fields: These are the fields you are wanting to query. Must be an array.

• $and: All these fields will be ANDed together. Must be an array.

• $and[index].field: The name of the column of the table.

• $and[index].matches: An array of values with the desired operator (see above for accepted operators).

• $or: same as $and

• selectFields: Query will return Select * From ... if no fields provided. Put fields in this array if you want it to return specific fields. Select Name From Users.

• Param Count is a tricky one. Some SQL libraries allow params. Well our builder add the params in, but if the user is calling it back to back and concatenating the queries together, the params will get confused. So put current param count here to allow it to increment properly.

• You also must pass in a flag to determine your SQL type. We accept these flags:

  • MySQL
  • MariaDB
  • PostgreSQL
  • SQLite
  • Oracle
  • SQLServer

Finally, run it

When you have your schema in place and your json object ready, it is time to call the library.

const {
  getSQLForJSON,
} = require("js-sql-from-json");

let json =
  {
    type: "USERS",
    fields: [
      {
        $and: [
          {
            field: "name",
            matches: [{ value: "ben", compare: "=" }],
          }
        ],
        $or: [],
      },
    ],
    selectFields: [],
  };

let query  = await getSQLForJSON(json, "SQLServer");
console.log(query.sql);
console.log(query.params);

SELECT * FROM USERS WHERE NAME = @param1
{
    param1: 'ben'
}

Support Development

If you liked using this library and want it to remain free and open in the long run, it would be great if you could help support the development of this library and other libraries in the future.

https://www.buymeacoffee.com/jssqlfromjson

Thanks