SQL Connector
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
sql-connector-deployment.zip
27KB
Binary
This zip folder contains the files used in the video demonstration below.
Covers how to run the sql-connector-deployment.zip
Below is a minimum configuration. The library providing the underlying connection to the database is knex. The configuration placed insideapplication.knexis passed through.
.sqlconnectorrc
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=db ## localhost or container alias
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=db ## localhost or container alias
26
; user=sa
27
; password=!test1234567
28
; database=testing
29
30
;; PG CONFIGURATION
31
; [application.knex]
32
; client=pg
33
; version=7.2
34
; [application.knex.connection]
35
; host=db ## localhost or container alias
36
; user=postgres
37
; password=postgres
38
; database=postgres
.env
1
## NoSQL Database
2
MONGO=mongodb://mongo:27017/formio
3
4
## App Server Config
5
LICENSE_KEY=
6
PORTAL_ENABLED=1
8
ADMIN_PASS=CHANGEME
9
DB_SECRET=CHANGEME
10
PORTAL_SECRET=CHANGEME
11
JWT_SECRET=CHANGEME
12
ADMIN_KEY=thisIsMyXAdminKey
13
14
## PDF Server Config
15
PDF_SERVER=http://pdf-server:4005
16
FORMIO_S3_KEY=
17
FORMIO_S3_SECRET=
18
FORMIO_S3_BUCKET=fomrio-pdf-server-s3-bucket-01
19
FORMIO_S3_REGION=us-east-2
1) Have API Server + SQL Databases already deployed and configured, or download the sql-connector-deployment.zip at the top of page.
2) Configure the settings in the .sqlconnectorrc and the .env files. Double check if all the settings will work for you configuration in the docker-compose.yml
3) Start the docker network by running the following command.
## Build Formio Application & SQL Connector
docker-compose up --build
4) Create an API key in the project Settings -> Stage Settings -> API Keys, this will be used to set API_KEY in the
.sqlconnectorrc file.5) In
.sqlconnectorrc configure LIVE_ENDPOINT with the current Project Endpoint.6) Next we will need to restart the formio-sql Docker container.
## This can be achieved using this command
docker restart formio-sql
7) Go to your developer portal application, and select the project that you'd like to integrate the SQL Connector with.
8) 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).
8a) Below are the settings used sql-connector-deployment.zip.
Setting Name | Setting Value |
|---|---|
SQL Connector Host URL | |
Type | MySQL |
SQL Connector Username | foo |
SQL Connector Password | bar |
In this section we are going to be discussing how to configure a form to have the SQL Connector action, and configure the mappings to the columns in the specified table.
For our demonstration of the connector we will be creating a Customer resource then attaching that resource to the SQL Connector action.
1) Create a resource form and call it Customer.
2) Then drag 2 Text Fields on to the Form. Label one as First Name, and label the second field as Email.
3) Now that we've created a Customer resource we need to configure the resource to use a SQL Connector action, and configure the mappings to the columns in the specified table.
4) Your SQL Connector should now be able to create, read, update, and delete records in the SQL database using the Form.io portal
This demonstration illustrates how to provide custom routing to your SQL Connector. Any SQL command you can think of you can create an endpoint to handle it.
How to create a CRUD REST API from the interface. Covers automating refresh by webhook and defining routes.
sql-connector-rest-client-postman.json
4KB
Code
This can be used to interact with the routes created in the above demonstration.
1) Navigated to the stage Settings -> Stage Versions -> Import Template
2) Click Choose File, and import the sqlconnector-project.json located the connector directory in the sql-connector-deployment.zip file at the top of this page.
3) Click Import Project Template to current stage.
4) This will create a form called SQLConnector Routes click on this form, this form will be used to create custom API routes for our SQL database.
# List all customers
SELECT * FROM customers
# Create customer with firstname and email
INSERT INTO customers (firstname, email) VALUES (?, ?)
# Find all rows where customer email is equal to
SELECT * FROM customers WHERE email = ?
# Update customer firstname and email by ID
UPDATE customers SET (firstname = ?, email = ?) WHERE id = ?
# Delete customer by ID
DELETE FROM customers WHERE id = ?
- Query: The query object contains the query string parameters of the request URL. This allows you to access the values of the query string parameters in the request.
- Body: The body object contains the data sent in the request body. This allows you to access the data sent in the request body, such as form data or JSON data.
- Params: The params object contains the route parameters of the request URL. This allows you to access the values of the route parameters in the request.
- Headers: The key-value pairs that are sent in the request and response of an HTTP request. They provide additional information about the request or response, such as the content type, language, encoding, and more. Headers can also be used to authenticate requests and control access to resources.
query.email
query.firstname
----------------------
body.data.firstName
body.data.email
----------------------
params.id
- body.* Access Request Body Parameters
- params.* Access URL Path Parameters named
id - query.* Access URL Query Parameters
- headers.* Access Request Headers
5) Once routes are created perform a Connector Refresh, see below for more details.
Whenever the connector configuration updates inside of the portal, the connector will need to be refreshed in order to pull in the changes. This can be done by making a GET /refresh to the SQL Connector. Accessing the refresh endpoint requires basic auth credentials to be provided.
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.
Reasons to refresh the connector may include:
- Modification to a SQL Connector action
- Modification to data contained within the manual configuration form
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.
1) Navigate to the SQLConnector Routes form actions, and select the Webhook Action.
2) See image below to see how to configure the Webhook Action. If using a deployed environment you will need to replace http://localhost:3002 with the hostname of the deployed environment as well as the Basic Auth credentials.
Last modified 8d ago