SQL Connector

Integrate Form.io forms with SQL databases (Microsoft SQL, MySQL, PostgreSQL) for efficient data management and direct handling of form submissions. Allows for also configuring a Custom SQL Rest API.

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

Quickstart Guide

27KB

sql-connector-deployment.zip

archive

This zip folder contains the files used in the video demonstration below.

Covers how to run the sql-connector-deployment.zip

Configuration Files

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.

MySQL

.sqlconnectorrc

; PROJECT CONFIGURATION
[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

; MYSQL CONFIGURATION
[application.knex]
  client=mysql
[application.knex.connection]
  host=db ## localhost or container alias
  user=root
  password=password
  database=testing

MSSQL

.sqlconnectorrc

; PROJECT CONFIGURATION
[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

; MSSQL CONFIGURATION
[application.knex]
  client=mssql
[application.knex.connection]
  host=db ## localhost or container alias
  user=sa
  password=!test1234567
  database=testing

Postgres

.sqlconnectorrc

; PROJECT CONFIGURATION
[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

; PG CONFIGURATION
[application.knex]
  client=postgresql
  version=7.2
[application.knex.connection]
  host=db ## localhost or container alias
  user=postgres
  password=postgres
  database=testing

Setup Services

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	http://formio-sql:3002
Type	MySQL
SQL Connector Username	foo
SQL Connector Password	bar

Configure Action

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

Custom Defined API Routes

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.

4KB

sql-connector-rest-client-postman.json

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.

5) Once routes are created perform a Connector Refresh, see below for more details.

Route Parameters

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.* Access URL Query Parameters

• body.* Access Request Body Parameters

• params.* Access URL Path Parameters named id

• headers.* Access Request Headers

MySQL

-- List all customers
SELECT * FROM customers

-- Create customer with firstname and email
INSERT INTO customers (firstname, email, age) VALUES (?, ?, ?)
/* Params:
- body.data.firstname
- body.data.email
- body.data.age
*/

-- Return inserted record
SELECT * FROM customers WHERE id = LAST_INSERT_ID()

-- Find all rows where customer id is equal to
SELECT * FROM customers WHERE id = ?
/* Params:
- params.id
*/

-- Update customer firstname and email by ID
UPDATE customers SET (firstname = ?, email = ?, age = ?) WHERE id = ?
/* Params:
- body.data.firstname
- body.data.email
- body.data.age
- params.id
*/

-- Return updated record
SELECT * FROM customers where id = ?
/* Params:
- params.id
*/

-- Delete customer by ID
DELETE FROM customers WHERE id = ?
/* Params:
- params.id
*/

MSSQL

-- List all customers
SELECT * FROM customers

-- Create customer with firstname and email
INSERT INTO customers (firstname, email, age) OUTPUT INSERTED.* VALUES (?, ?, ?);
/* Params:
- body.data.firstname
- body.data.email
- body.data.age
*/

-- Find all rows where customer id is equal to
SELECT * FROM customers WHERE id = ?
/* Params:
- params.id
*/

-- Update customer firstname and email by ID
UPDATE customers SET firstname = ?, email = ?, age = ? OUTPUT INSERTED.* WHERE id = ?;
/* Params:
- body.data.firstname
- body.data.email
- body.data.age
- params.id
*/

-- Delete customer by ID
DELETE FROM customers WHERE id = ?
/* Params:
- params.id
*/

Postgres

-- List all customers
SELECT * FROM customers

-- Create customer with firstname and email
INSERT INTO customers (firstname, email, age) VALUES (?, ?, ?) RETURNING *
/* Params:
- body.data.firstname
- body.data.email
- body.data.age
*/

-- Find all rows where customer id is equal to
SELECT * FROM customers WHERE id = ?
/* Params:
- params.id
*/

-- Update customer firstname and email by ID
UPDATE customers SET firstname = ?, email = ?, age = ? WHERE id = ? RETURNING *
/* Params:
- body.data.firstname
- body.data.email
- body.data.age
- params.id
*/

-- Delete customer by ID
DELETE FROM customers WHERE id = ?
/* Params:
- params.id
*/

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. 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.

curl -u foo:bar http://localhost:3002/refresh

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.

Configure Automatic Refresh

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.