Users Guide
Developer Guide
Deployments
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.xor 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 insideapplication.knexis 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
Last modified 5d ago