SQL Connector

SQL Connector

Usage

  • Docker Image: formio/sql-connector:latest
  • Requires a form.io enterprise deployment version > 7.1.1

Configuration

The application name used for this container is sqlconnector
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.
1
;; SQLITE
2
; [application.knex]
3
; client=sqlite3
4
; [application.knex.connection]
5
; filename=./mydb.sqlite
6
; useNullAsDefault=true
7
8
;; PG
9
; [application.knex]
10
; client=pg
11
; version=7.2
12
; [application.knex.connection]
13
; host=127.0.0.1
14
; user=username
15
; password=password
16
; database=database
17
18
;; MYSQL
19
; [application.knex]
20
; client=mysql
21
; [application.knex.connection]
22
; host=127.0.0.1
23
; user=root
24
; password=password
25
; database=test
26
27
[libs.formiosdk]
28
API_KEY=
29
; Live endpoint
30
PROJECT_URL=
31
32
[libs.authentication]
33
BASIC_USERNAME=foo
34
BASIC_PASSWORD=bar
Copied!
  • How to translate into environment variables
  • Routes can be defined in the application.routes (type: ConnectorRoute[])
    • Note: A JSON format file may be easier to define your routes in. The config loader supports both formats interchangeably

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
# Should contain a file named "sqlconnector"
13
- /path/to/config/dir:/home/node/.config
14
ports:
15
# "external:internal"
16
- '3002:3002'
17
networks:
18
- sqlconnector
19
restart: unless-stopped
Copied!

Basic

When launched, the SQL Connector will connect to a single database, and expose rest endpoints that can be used to execute pre-configured sql statements. The connector is capable of executing multiple queries in a row, returning the result from the final query as the return response. When the connector launches, it will communicate with an API server to load data about the project it is connected it. An api key is used for authentication.

Advanced

With some additional configuration, the connector can be set up to execute custom developer defined queries, against a configurable route. These routes can be defined in the config, or loaded directly from your portal by providing additional forms with the correct tags.

Authentication

The connector supports basic auth

Loading routes from portal forms

On startup, the connector will seek out any form in the provided project with both the tags sqlconnector & route. Those forms(s) should container at minimum these fields: (ConnectorRoute)
key
type
example
endpoint
string
/customers/:customerId
method
enum
post
query
string[][]
See section below
All the current submissions will be loaded for those forms, merge them with the config and base project data to produce the active set of routes + queries.

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 value
1
[
2
[
3
"UPDATE `customers` SET (`firstName` = ?, `lastName` = ?) WHERE `customerId` = ?",
4
"body.data.firstName",
5
"body.data.lastName",
6
"params.customerId"
7
],
8
...
9
]
Copied!
The first value must always be the query. All following strings will be used for substitutions in the query. These values are object paths relative to the express request object. The format will follow this general format:
  • body.* your submission / postdata
  • params.* url params. In the table above, the param was created named customerId
  • query.* query parameters
  • headers.* headers
The return result from the final query is the value

Portal Setup

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 4mo ago