The query module exports the [SQL] query method to pass queries to the stored dbs_connections available to the XYZ instance.

Requires

Methods

(async, inner) executeQuery(req, res, template, query)

The method send a parameterised query to a database connection.

The dbs for the query is determined primarily by the template. The layer.dbs is used for layer queries if the dbs on the template is not implicit. The locale.dbs is assumed as the layer.dbs if not defined in JSON layer. The workspace.dbs will be used as fallback if no template, layer, or locale dbs can be determined.

Parameters:
NameTypeDescription
reqreq

HTTP request.

resres

HTTP response.

templateObject

Request template.

querystring

SQL query.

Properties
NameTypeAttributesDescription
req.paramsObject<optional>

Request params.

(inner) getQueryFromTemplate(req, template)

In order to prevent SQL injections queries must be build from templates stored in the workspace.templates{}.

A template may have a render method which returns a query string assigned as template.template.

Parameter to be replaced in the SQL query string must be checked to only contain whitelisted character to prevent SQL injection.

Any variables to be replaced on query execution in the database must be replaced with indices in the query string. eg. $1, $2, ...

The substitute values are stored in the ordered params.SQL[] array.

An error will be returned if the substitution fails.

Parameters:
NameTypeDescription
reqreq

HTTP request.

templateObject

Request template.

Properties
NameTypeDescription
req.paramsObject

Request params.

params.filterObject

JSON filter which must be turned into a SQL filter string for substitution.

params.SQLArray

Substitute parameter for SQL query.

template.renderfunction

Method to render template string.

template.templatestring

SQL template string.

(async, inner) layerQuery(req, res)

Queries which reference a layer must be checked against the layer JSON in the workspace.

Layer queries have restricted viewport and filter params. These params can not be substituted in the database but must be replaced in the SQL query string.

Any query which references a layer and locale will be passed through the layer query method. The getLayer method will fail return an error if the locale is not defined as param or the layer is not a member of the locale.

/api/query?template=query&locale=uk&layer=retail
Parameters:
NameTypeDescription
reqreq

HTTP request.

resres

HTTP response.

Properties
NameTypeAttributesDescription
req.paramsObject

Request params.

params.filterObject

JSON filter which must be turned into a SQL filter string for substitution.

params.SQLArray

Substitute parameter for SQL query.

params.userObject<optional>

Requesting user.

user.rolesArray<optional>

User roles.

(async, inner) query(req, res)

The [SQL] query method requests a query template from the getTemplate method and checks whether the requesting user is permitted to execute the query.

The layerQuery() method must be awaited for queries that reference a layer.

A template is turned into a query by the getQueryFromTemplate() method.

The query is executed by the executeQuery() method.

Parameters:
NameTypeDescription
reqreq

HTTP request.

resres

HTTP response.

Properties
NameTypeAttributesDescription
req.paramsObject

Request params.

params.userObject<optional>

Requesting user.

user.rolesArray<optional>

User roles.

(inner) sendRows(req, res, template, rows)

The method formats the rows returned from a SQL query and sends the formated rows through the HTTP response object.

Parameters:
NameTypeDescription
reqreq

HTTP request.

resres

HTTP response.

templateObject

Request template.

rowsarray

The response from a SQL query.