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

Developer Guide - Previous

File Storage

Next - Deployments

Deployment Guide

Last modified 4mo ago

Copy link

Contents

Loading routes from portal forms

Portal Setup

Data Connections

Form Action