Step-by-step deployment guide

This version of the gateway is currently compatible with Elimity Insights server versions matching >3.42.0.

1. Configuring the gateway

The first step in setting up automatic imports via an SQL gateway is configuring the gateway itself. As usual, please make sure your instance of Elimity Insights can reach the gateway and that the gateway can reach your SQL databases.

To configure your gateway, mount a JSON configuration file at /app/config/config.json with the properties listed below. The following snippets provide good starting points:

SQL Server

{
  "connectionStrings": ["sqlserver://my-user:my-password@my-host:my-port?database=my-database"],
  "driver": "sqlServer",
  "jwtValidationBaseUrl": "https://example.elimity.com",
  "jwtValidationGatewayUrl": "https://gateway.example.com",
  "jwtValidationSourceId": "42"
}

MySQL

Oracle DB

Edit the following properties in this file to configure the gateway to your needs:

Property	Type	Description
connectionStrings	list[string]	JSON array of strings describing how to connect with the databases; refer to the dedicated section below for more information
driver	string	Driver to use for connecting with your databases; choose between "ibmDb2", "mySql", "oracleDb" and "sqlServer"
jwtValidationAudiences	option[list[string]]	Audiences for JWT validation, defaults to ["gateway"]
jwtValidationBaseUrl	string	Expected Elimity Insights base URL for JWT validation, e.g. "https://example.elimity.com"
jwtValidationGatewayUrl	string	Expected gateway URL for JWT validation, e.g. "https://gateway.example.com"
jwtValidationIssuer	option[string]	Issuer for JWT validation, defaults to "https://auth.elimity.com/"
jwtValidationExpr	option[string]	Expr program implementing JWT custom claim validation, defaults to "claims.base_url == baseURL && claims.gateway_url == gatewayURL && claims.source_id == sourceID"
jwtValidationOptional	option[boolean]	Flag indicating whether JWT validation is optional, defaults to false
jwtValidationSourceId	string	Expected source id for JWT validation, e.g. "42"

Connection strings

The connectionStrings configuration option determines which database connections the gateway should make. It should be an array of strings, the exact format depends on which driver you configured. The table below contains additional references for each supported value of the driver configuration option:

Driver	Reference
ibmDb2	Official documentation of IBM DB2
mySql	Official documentation of the de-facto standard MySQL driver for Go
oracleDb	Connection string parser source code of the de-facto standard Oracle Database driver for Go
sqlServer	Official documentation of the de-facto standard SQL Server driver for Go

JWT validation

We highly recommend requiring JWT validation to secure your gateway. Please read our official documentation about the following topics to understand how Elimity Insights authenticates to gateways via OAuth2:

• Gateway-based imports

• OAuth2 endpoint parameters for gateway authentication

Our SaaS customers can simply set the jwtValidationBaseUrl, jwtValidationGatewayUrl and jwtValidationSourceId configuration options, which provides the following security guarantees:

• Only requests coming from the configured Elimity Insights are allowed

• Only requests targeting the configured gateway URL are allowed

• Only requests for importing the configured source are allowed

On-premise customers should additionally set the jwtValidationAudiences, jwtValidationIssuer and jwtValidationExpr configuration options. Alternatively you can also set jwtValidationOptional to true and perform authentication in a proxy instead.

2. Deploying the gateway

Since we distribute the gateway as a Docker image, our recommendation for deployment is to use a CaaS solution like Google Cloud Run or Azure Container Apps. If that's not an option, you can also manually deploy the image on e.g. Windows Server. Refer to our documentation about gateways and import agents for additional details.

3. Creating a custom source in Elimity Insights

To set up automatic imports via the SQL gateway we need to create a custom source in Elimity Insights itself first. If you're unsure about the data model then we recommend to start with just a single entity type. You can simply extend the data model later on.

4. Enabling automatic imports

Having created the new custom source, you can now navigate to its detail page in Elimity Insights and open the 'CONFIG' tab. Click the 'EDIT' button and enter your gateway's URL and the desired CRON schedule. Also add the following configuration values:

Key	Type	Description
entityTypes	JSON	JSON array describing how to import entities from your databases; refer to the dedicated section below for more information
relationshipTypes	JSON	JSON array describing how to import relationship types from your databases; refer to the dedicated section below for more information

Entity types

For each item in the entityTypes configuration value, the gateway will perform an SQL query, convert the results into entities and send those to Elimity Insights. More specifically, the entityTypes configuration value should be a JSON array of objects, where each object should have the following properties:

Property	Type	Description
attributes	list[object]	Describes how to import attributes of the entity type's entities
attributes[].id	string	Unique identifier of the attribute type for which the gateway should import assignments
attributes[].type	string	Data type of the attribute type, one of "boolean", "date", "dateTime", "number", "string" or "time"
id	string	Unique identifier of the entity type for which the gateway should import entities
query	string	Query that the gateway should send to the configured SQL instances, each resulting row corresponds to an entity of this type; refer to the dedicated section on this page for more information

Relationship types

For each item in the relationshipTypes configuration value, the gateway will perform an SQL query, convert the results into relationships and send those to Elimity Insights. More specifically, the relationshipTypes configuration value should be a JSON array of objects, where each object should have the following properties:

Property	Type	Description
attributes	list[object]	Describes how to import attributes of the relationship type's entities
attributes[].id	string	Unique identifier of the attribute type for which the gateway should import assignments
attributes[].type	string	Data type of the attribute type, one of "boolean", "date", "dateTime", "number", "string" or "time"
fromEntityType	string	Unique identifier of the entity type from which the relationships start
query	string	Query that the gateway should send to the configured SQL instances, each resulting row corresponds to an entity of this type; refer to the dedicated section on this page for more information
toEntityType	string	Unique identifier of the entity type where the relationships end

Query format

When writing queries to configure the gateway, you should make sure they adhere to the following rules:

• for entity queries:

  • the number of output columns should equal the number of configured attributes plus two

  • the first output column should have a textual type and represents the entity's id

  • the second output column should have a textual type and represents the entity's name

  • the following output columns should type-match with the entity type's configured attributes (so order of configured attributes is important)

• for relationship queries:

  • the number of output columns should equal the number of configured attributes plus two

  • the first output column should have a textual type and represents the source entity's id

  • the second output column should have a textual type and represents the target entity's id

  • the following output columns should type-match with the relationship type's configured attributes (so order of configured attributes is important)

• the gateway will not generate attribute assignments for output columns that have a NULL value