Forms database setup / Resquel

Resquel SQL Database Integration

Resquel is a lightweight SQL database routing layer that allows you to expose RESTful API endpoints backed by SQL queries. It provides a secure, declarative way to create database-backed endpoints without writing custom route handlers.

Overview

The Resquel integration automatically creates REST endpoints from configured SQL queries, supporting parameterized queries with request parameters and query strings. This allows you to quickly expose database resources through the GlobalForms API.

Supported Database Types

Resquel supports the following SQL database engines:

• PostgreSQL (postgres)

• Microsoft SQL Server (mssql)

• MySQL (mysql or mysql2)

• MariaDB (mysql2)

Configuration

Configure Resquel in your config/*.json files under the settings.resquel block. The configuration requires database connection details and route definitions.

Basic Structure

{
  "settings": {
    "resquel": {
      "type": "postgres",
      "db": {
        "user": "dbuser",
        "password": "dbpassword",
        "server": "localhost",
        "port": "5432",
        "database": "mydb",
        "requestTimeout": "30000"
      },
      "routes": [
        {
          "method": "get",
          "endpoint": "/employees",
          "query": "SELECT * FROM Employees;"
        }
      ]
    }
  }
}

Configuration Options

Database Configuration (db)

• user (string): Database username

• password (string): Database password

• server (string): Database host/server address

• port (string): Database port number

• database (string): Database name

• requestTimeout (string): Query timeout in milliseconds (default: “30000”)

For MSSQL, additional options may include:

• options.encrypt (boolean): Use TLS encryption

• options.trustServerCertificate (boolean): Trust self-signed certificates

For PostgreSQL, additional options may include:

• ssl.rejectUnauthorized (boolean): Trust self-signed certificates

Route Configuration

Each route in the routes array defines an API endpoint:

• method (string): HTTP method (get, post, put, delete)

• endpoint (string): URL path (supports route parameters with :param)

• tokens (array, optional): Allowed API tokens for this route

• query (string): SQL query to execute (supports template variables)

Route Tokens (Optional)

You can optionally restrict a route with a tokens array of allowed API tokens.

{
  "method": "get",
  "endpoint": "/customer/:id",
  "tokens": ["3fa85f64-5717-4562-b3fc-2c963f66afa6"],
  "query": "SELECT * FROM customers WHERE id={{ params.id }}"
}

If tokens is defined and non-empty, each request must provide a matching token using one of the following:

• x-api-token request header

• token query string parameter

If no token is provided, or the provided token is not in the route’s tokens array, the request is rejected with HTTP 403 Forbidden.

Minimal Examples

PostgreSQL Example

{
  "settings": {
    "resquel": {
      "type": "postgres",
      "db": {
        "user": "root",
        "password": "example",
        "server": "localhost",
        "port": "5432",
        "database": "exampledb",
        "requestTimeout": "30000"
      },
      "routes": [
        {
          "method": "get",
          "endpoint": "/users",
          "query": "SELECT id, name, email FROM users;"
        }
      ]
    }
  }
}

Microsoft SQL Server Example

{
  "settings": {
    "resquel": {
      "type": "mssql",
      "db": {
        "user": "sa",
        "password": "P@ssw0rd",
        "server": "localhost",
        "port": "1433",
        "database": "CompanyDB",
        "requestTimeout": "30000"
      },
      "routes": [
        {
          "method": "get",
          "endpoint": "/employees",
          "query": "SELECT * FROM [Employees];"
        }
      ]
    }
  }
}

MySQL Example

{
  "settings": {
    "resquel": {
      "type": "mysql2",
      "db": {
        "user": "root",
        "password": "example",
        "server": "localhost",
        "port": "3306",
        "database": "exampledb",
        "requestTimeout": "30000"
      },
      "routes": [
        {
          "method": "get",
          "endpoint": "/products",
          "query": "SELECT * FROM products WHERE active = 1;"
        }
      ]
    }
  }
}

Parameterized Queries

Resquel supports dynamic queries using template variables from route parameters and query strings.

Route Parameters

Use :param in the endpoint and {{ params.param }} in the query:

{
  "method": "get",
  "endpoint": "/employees/:id",
  "query": "SELECT * FROM Employees WHERE id = {{ params.id }};"
}

Usage: GET /employees/123 → Query uses id = 123

Query String Parameters

Use {{ query.param }} to access query string values:

{
  "method": "get",
  "endpoint": "/employees/search",
  "query": "SELECT * FROM Employees WHERE age >= {{ query.age }};"
}

Usage: GET /employees/search?age=30 → Query uses age >= 30

Multiple Parameters

Combine both types of parameters:

{
  "method": "get",
  "endpoint": "/departments/:dept_id/employees",
  "query": "SELECT * FROM Employees WHERE dept_id = {{ params.dept_id }} AND status = {{ query.status }};"
}

Usage: GET /departments/5/employees?status=active

Security Considerations

• Input Validation: While Resquel provides parameterization, always validate and sanitize input data

• SQL Injection: Use parameterized queries; never concatenate user input directly into SQL

• Database Permissions: Use a database user with minimal required permissions

• Authentication: Ensure endpoints are protected by token authentication to restrict access as needed

• Connection Security: Use encrypted connections (TLS/SSL) for production databases

Accessing Resquel Endpoints

Once configured, Resquel endpoints are available under your GlobalForms server’s base URL. Authentication depends on your GlobalForms configuration:

# With route token authentication in query string
curl -X GET http://localhost:3001/employees?token=TOKEN_HERE

# With route token in a header
curl -X GET http://localhost:3001/employees -H "x-api-token: TOKEN_HERE"

# Without authentication (if endpoint is public)
curl -X GET http://localhost:3001/employees

Troubleshooting

For console reflection of errors, before starting GlobalForms in a console mode, configure filtered debug logging with:

set DEBUG=resquel*

Connection Issues

• Verify database server is running and accessible

• Check firewall rules and network connectivity

• Confirm credentials and database name are correct

• Ensure the database type matches your server (e.g., mysql2 for MySQL)

Query Errors

• Test queries directly in your database client first

• Check for proper table/column name quoting ([] for MSSQL, backticks for MySQL)

• Verify template variable syntax matches parameter names

• Review request timeout settings for slow queries

Initialization

Resquel initializes when the GlobalForms server starts. If configuration is invalid or the database is unreachable, check server logs for error messages during startup.