Getting Started
Developers Deployment Guide Get Help

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

Quickstart Guide

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.

.sqlconnectorrc
[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=db ## localhost or container alias
  user=root
  password=password
  database=testing

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

;; PG CONFIGURATION
; [application.knex]
;   client=pg
;   version=7.2
; [application.knex.connection]
;   host=db ## localhost or container alias
;   user=postgres
;   password=postgres
;   database=postgres
.env
## NoSQL Database
MONGO=mongodb://mongo:27017/formio

## App Server Config
LICENSE_KEY=
PORTAL_ENABLED=1
ADMIN_EMAIL=admin@example.com
ADMIN_PASS=CHANGEME
DB_SECRET=CHANGEME
PORTAL_SECRET=CHANGEME
JWT_SECRET=CHANGEME
ADMIN_KEY=thisIsMyXAdminKey

## PDF Server Config
PDF_SERVER=http://pdf-server:4005
FORMIO_S3_KEY=
FORMIO_S3_SECRET=
FORMIO_S3_BUCKET=fomrio-pdf-server-s3-bucket-01
FORMIO_S3_REGION=us-east-2

Configuration Steps

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 NameSetting Value

SQL Connector Host URL

http://formio-sql:3002

Type

MySQL

SQL Connector Username

foo

SQL Connector Password

bar

Configuring SQL Connector action on Form

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

Create a Custom REST API for your SQL Connector

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.

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.

Example Query Formats

# 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 = ?

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.

Examples

 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.

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.

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.

PreviousDeveloper LicenseNextIntegration Libraries

Last updated