Breadcrumbs

Setting Up SQL Lookups (Resquel)

Resquel lets you publish SQL queries as GlobalForms web addresses (routes). Forms call these addresses to load data, for example to fill a Select list or look up a customer by ID. Each route runs a query you define, and values from the request are passed to the query safely as parameters.

PostgreSQL support and route tokens require GlobalForms version 10.7.1 or greater.

Supported databases

Database

type value

Microsoft SQL Server

mssql

MySQL or MariaDB

mysql2

PostgreSQL

postgres

Configure Resquel

Add a resquel section under settings in config\local.json, then restart the GlobalForms service:

JSON
{
  "settings": {
    "resquel": {
      "type": "mssql",
      "db": {
        "server": "sql-server",
        "port": 1433,
        "database": "CompanyDB",
        "user": "svc_globalforms",
        "password": "<Password>",
        "requestTimeout": 30000
      },
      "routes": [
        {
          "method": "get",
          "endpoint": "/departments",
          "query": "SELECT DeptCode, DeptName FROM Departments ORDER BY DeptName;"
        }
      ]
    }
  }
}

Connection settings (db)

Setting

Description

server

Database server name or address. For a named SQL Server instance, use SERVER\\INSTANCE.

port

1433 (SQL Server), 3306 (MySQL), 5432 (PostgreSQL)

database

The database queries run against

user, password

A database account with only the permissions the routes need

requestTimeout

Milliseconds to wait for a query (default 30000)

options.encrypt, options.trustServerCertificate

SQL Server only. Use encrypted connections; trust a self-signed server certificate.

ssl.rejectUnauthorized

PostgreSQL only. Set to false to trust a self-signed server certificate.

Route settings (routes)

Setting

Description

method

get, post, put or delete. Lookups use get.

endpoint

The route's address, unique across routes, for example /departments or /customers/:id

query

The SQL to run. Test it in your database tool first.

tokens

Optional. A list of access tokens; callers must provide one. See Route tokens.

A route is available at your GlobalForms address plus the endpoint, for example https://forms.example.com/departments. Open it in a browser to check that it returns data. Results are returned in a rows list.

Pass values to a query

Use {{ }} to insert a value from the request:

Placeholder

Value from

Example

{{ params.name }}

A :name part of the endpoint

/customers/:id called as /customers/12345

{{ query.name }}

A ?name= value in the address

/employees/search?dept=HR
JSON
{
  "method": "get",
  "endpoint": "/customers/:id",
  "query": "SELECT CustomerName, CustomerContact FROM Customers WHERE CustomerID = {{ params.id }};"
}

Do not put quotes around {{ }} placeholders, even for text values. Each placeholder is sent to the database as a parameter, which already handles text correctly and protects against SQL injection. Write WHERE Make = {{ params.make }}, not WHERE Make = '{{ params.make }}'.

For a partial text match, join the wildcards in SQL. For SQL Server: WHERE Name LIKE '%' + {{ query.q }} + '%'. Queries written in the older '%{{ query.q }}%' style are converted automatically when GlobalForms starts.

Route tokens

To require a token for a route, add a tokens list:

JSON
{
  "method": "get",
  "endpoint": "/customers/:id",
  "tokens": ["<long random token>"],
  "query": "SELECT CustomerName FROM Customers WHERE CustomerID = {{ params.id }};"
}

Callers must send a matching token in an x-api-token header or a token= value in the address. Requests without a valid token are refused with 403 Forbidden.

A token used in a form's Select URL is visible to anyone who can open that form. Tokens keep out callers who don't have the form, but they are not a substitute for limiting what each query returns. See Securing Your SQL Data Lookups.

Troubleshooting

Symptom

Cause and fix

The route address returns "not found"

Check the endpoint spelling and that you restarted the service. Check logs\service.err.log for Resquel startup errors.

Cannot connect to the database

Check the server, port and firewall. For SQL Server, check that TCP/IP is enabled and, for named instances, that the SQL Server Browser service is running.

Login failed

Check the user and password, and that SQL Server allows SQL Server authentication.

Query errors, or no results when filtering by text

Remove any quotes around {{ }} placeholders. Test the query in your database tool.

403 Forbidden

The route has tokens and the request didn't include a valid one.