Requires a form.io enterprise deployment version >
The application name used for this container is
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.
;; SQLITE; [application.knex]; client=sqlite3; [application.knex.connection]; filename=./mydb.sqlite; useNullAsDefault=true;; PG; [application.knex]; client=pg; version=7.2; [application.knex.connection]; host=127.0.0.1; user=username; password=password; database=database;; MYSQL; [application.knex]; client=mysql; [application.knex.connection]; host=127.0.0.1; user=root; password=password; database=test[libs.formiosdk]API_KEY=; Live endpointPROJECT_URL=[libs.authentication]BASIC_USERNAME=fooBASIC_PASSWORD=bar
How to translate into environment variables
Routes can be defined in the
Note: A JSON format file may be easier to define your routes in. The config loader supports both formats interchangeably
---version: 2network:sqlconnector:services:formio-sql:image: formio/sql-connector:latestcontainer_name: formio-sqlvolumes:# Should contain a file named "sqlconnector"- /path/to/config/dir:/home/node/.configports:# "external:internal"- '3002:3002'networks:- sqlconnectorrestart: unless-stopped
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.
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.
The connector supports basic auth
On startup, the connector will seek out any form in the provided project with both the tags
route. Those forms(s) should container at minimum these fields: (ConnectorRoute)
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.
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
[["UPDATE `customers` SET (`firstName` = ?, `lastName` = ?) WHERE `customerId` = ?","body.data.firstName","body.data.lastName","params.customerId"],...]
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
query.* query parameters
The return result from the final query is the value
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)
Configure a Form to have the SQL Connector action, and configure the mappings