SQL Connector

Usage

• Docker Image: formio/sql-connector:latest

• Requires a form.io enterprise deployment version > 7.1.1

Configuration

The application name used for this container is sqlconnector

Below is a minimum configuration. The library providing the underlying connection to the database is knex. The configuration placed inside application.knex is passed through.

;; SQLITE
; [application.knex]
;   client=sqlite3
; [application.knex.connection]
;   filename=./mydb.sqlite
;   useNullAsDefault=true
​
;; PG
; [application.knex]
;   client=pg
;   version=7.2
; [application.knex.connection]
;   host=127.0.0.1
;   user=username
;   password=password
;   database=database
​
;; MYSQL
; [application.knex]
;   client=mysql
; [application.knex.connection]
;   host=127.0.0.1
;   user=root
;   password=password
;   database=test
​
[libs.formiosdk]
  API_KEY=
  ; Live endpoint
  PROJECT_URL=
​
[libs.authentication]
  BASIC_USERNAME=foo
  BASIC_PASSWORD=bar

• How to translate into environment variables​

• Routes can be defined in the application.routes (type: ConnectorRoute[])

  • Note: A JSON format file may be easier to define your routes in. The config loader supports both formats interchangeably

Deployments

Docker Compose

---
version: 2
​
network:
  sqlconnector:
​
services:
  formio-sql:
    image: formio/sql-connector:latest
    container_name: formio-sql
    volumes:
      # Should contain a file named "sqlconnector"
      - /path/to/config/dir:/home/node/.config
    ports:
      # "external:internal"
      - '3002:3002'
    networks:
      - sqlconnector
    restart: unless-stopped

Basic

When launched, the SQL Connector will connect to a single database, and expose rest endpoints that can be used to execute pre-configured sql statements. The connector is capable of executing multiple queries in a row, returning the result from the final query as the return response. When the connector launches, it will communicate with an API server to load data about the project it is connected it. An api key is used for authentication.

Advanced

With some additional configuration, the connector can be set up to execute custom developer defined queries, against a configurable route. These routes can be defined in the config, or loaded directly from your portal by providing additional forms with the correct tags.

Authentication

The connector supports basic auth

Loading routes from portal forms

On startup, the connector will seek out any form in the provided project with both the tags sqlconnector & route. Those forms(s) should container at minimum these fields: (ConnectorRoute)

All the current submissions will be loaded for those forms, merge them with the config and base project data to produce the active set of routes + queries.

Automatic reload

The connector has a refresh route to allow for importing updates to the config (updates to the submissions in the portal forms). This is exposed as a GET /refresh endpoint. The form in the portal can be set up with a webhook action that has this endpoint as the target. With that set up, any time you modify the routes in your portal, your sql connector will automatically update

Query format

Example value

[
  [
    "UPDATE `customers` SET (`firstName` = ?, `lastName` = ?) WHERE `customerId` = ?",
    "body.data.firstName",
    "body.data.lastName",
    "params.customerId"
  ],
  ...
]

The first value must always be the query. All following strings will be used for substitutions in the query. These values are object paths relative to the express request object. The format will follow this general format:

• body.* your submission / postdata

• params.* url params. In the table above, the param was created named customerId

• query.* query parameters

• headers.* headers

The return result from the final query is the value

Portal Setup

Data Connections

Configure your Form.io Project Settings, to have a SQL Connector Data Connection (Integrations > Data Connections, http(s) scheme must be included on Host URL field)

Form Action

Configure a Form to have the SQL Connector action, and configure the mappings