Links

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
services:
formio-sql:
image: formio/sql-connector:2.0.10
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

SQL Connector Container Setup Guide
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

How to create a SQL Connector REST API Demo

Outlines how to create CRUD REST API from the interface. Also covers automating route refresh by webhook and defining routes.
Use postman json file for a copy of the client-side routes used in the demonstration above.
sql-connector-rest-client-postman.json
4KB
Code

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