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.
1
[application]
2
; Value from Live Endpoint in portal
3
LIVE_ENDPOINT=http://localhost:3000/sqlconnector-project
4
; API Key from that projecet
5
API_KEY=
6
; Basic Auth info
7
username=foo
8
password=bar
9
10
; DATABASE CONFIGURATION
11
12
;; MYSQL CONFIGURATION
13
;[application.knex]
14
; client=mysql
15
;[application.knex.connection]
16
; host=localhost
17
; user=root
18
; password=password
19
; database=testing
20
21
;; MSSQL CONFIGURATION
22
; [application.knex]
23
; client=mssql
24
; [application.knex.connection]
25
; host=localhost
26
; user=sa
27
; password=yourStrong(!)Password
28
; database=testing
29
30
;; PG CONFIGURATION
31
; [application.knex]
32
; client=pg
33
; version=7.2
34
; [application.knex.connection]
35
; host=localhost
36
; user=postgres
37
; password=postgres
38
; database=postgres
Copied!

Deployments

Docker Compose

1
---
2
version: 2
3
4
network:
5
sqlconnector:
6
7
services:
8
formio-sql:
9
image: formio/sql-connector:latest
10
container_name: formio-sql
11
volumes:
12
# Mount the config file in a spot the app knows to look for
13
- /path/to/config.ini:/app/.sqlconnectorrc
14
ports:
15
# "external:internal"
16
- '3002:3002'
17
restart: unless-stopped
Copied!

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
1
"UPDATE `customers` SET (`firstName` = ?, `lastName` = ?) WHERE `customerId` = ?",11
Copied!
Example subsitutions
1
"body.data.firstName",
2
"body.data.lastName",
3
"params.customerId"
Copied!
  • 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