SQL Connector

SQL Connector

Basic Requirements

If you are not using a Form.io project, check out Resquel​. The following items are required to use the formio-sql server/container.
  1. 1.
    A Form.io Project on the Team Pro or Commercial Plan
  2. 2.
    A Node.js LTS Server, 9.x.x or higher, or Docker
  3. 3.
    A MySQL/Microsoft SQL/PostgreSQL compatible server(s)
  4. 4.
    Instance of formio-server running versions (minimum as of 2021-02-11):
    1. 1.
      7.1.x
    2. 2.
      7.1.0-rc.2

Configuration

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.
[application]
; Value from Live Endpoint in portal
LIVE_ENDPOINT=http://localhost:3000/sqlconnector-project
; API Key from that projecet
API_KEY=
; Basic Auth info
username=foo
password=bar
; DATABASE CONFIGURATION
;; MYSQL CONFIGURATION
;[application.knex]
; client=mysql
;[application.knex.connection]
; host=localhost
; user=root
; password=password
; database=testing
;; MSSQL CONFIGURATION
; [application.knex]
; client=mssql
; [application.knex.connection]
; host=localhost
; user=sa
; password=yourStrong(!)Password
; database=testing
;; PG CONFIGURATION
; [application.knex]
; client=pg
; version=7.2
; [application.knex.connection]
; host=localhost
; user=postgres
; password=postgres
; database=postgres

Deployments

Docker Compose

---
version: 2
network:
sqlconnector:
services:
formio-sql:
image: formio/sql-connector:latest
container_name: formio-sql
volumes:
# Mount the config file in a spot the app knows to look for
- /path/to/config.ini:/app/.sqlconnectorrc
ports:
# "external:internal"
- '3002:3002'
restart: unless-stopped

Deployment Workflow

1) Have API Server + SQL Databases already deployed and configured
2) Copy config file from above, and update configuration to match current settings
3) Deploy SQL Connector container, using the above docker-compose file
4) Return to the portal, set up data connection to sql connector in project settings (see below)
5) If using sql connector action, set those up on the appropriate forms
5.a) To manually set up routes + queries, import the Manual Configuration form below
sqlconnector-project-0.0.0.json
3KB
Code
6) Refresh the connector

Connector Refreshing

Whenever the connector configuration updates inside of the portal, the connector will need to be refreshed in order to pull in the changes. Accessing the refresh endpoint requires basic auth tokens to be provided.
Reasons to refresh the connector may include:
  • Modification to a sql connector action
  • Modification to data contained within the manual configuration form

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 query
"UPDATE `customers` SET (`firstName` = ?, `lastName` = ?) WHERE `customerId` = ?",11
Example subsitutions
"body.data.firstName",
"body.data.lastName",
"params.customerId"
  • body.* your submission / postdata
  • params.* url params. In the table above, the param was created named customerId
  • query.* query parameters
  • headers.* headers

Portal Setup for SQLConnector Actions

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