Complex SQL query builder

Build complex query and exec statements based on external parameters

Query builder

Create file ./queries/sample.json file

{
  "type": "sql-builder",
  "id": "sample-query",
  "connection": "sample-database-connection",
  "file": "sample.js",
  "permissions": [
    "admin",
    "user"
  ],
  "public": true,
  "params": [
    {
      "name": "name",
      "required": true
    },
    {
      "name": "code",
      "required": true
    }
  ]
}

Create file ./js/sample.js and fill it with any of the three options below

If you would like to select data without pagination

let query = "SELECT * FROM estates where `code` = :code limit 3"

builder.setQuery(query)
builder.setParams({ code: "EN", name: request.params.name})

If you would like to select data with pagination

let query = "SELECT * FROM estates where `code` = :code limit 3"
let queryCount = "SELECT count(*) as c FROM estate_type"

builder.setQuery(query)
builder.setCountQuery(queryCount)
builder.setParams({ code: "EN", name: request.params.name})

If you would like to run exec statement like update, delete, insert or any other

let queryExec = "Insert into `estate` (`player_id`, `deviceId`, `created_at`) VALUES (1, :name, NOW())"
builder.setParams({ code: "EN", name: request.params.name})
builder.setQueryExec(queryExec)