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.A Form.io Project on the Team Pro or Commercial Plan
2.A Node.js LTS Server, 9.x.x or higher, or Docker
3.A MySQL/Microsoft SQL/PostgreSQL compatible server(s)
4.Instance of formio-server running versions (minimum as of 2021-02-11):
1.7.1.x
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

Developer Guide - Previous

File Storage

Next - Developer Guide

Two-Factor Authentication

Last modified 3mo ago

Copy link

Outline

Portal Setup for SQLConnector Actions

Data Connections

Form Action