> For the complete documentation index, see [llms.txt](https://docs.elimity.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.elimity.com/gateways-sql/v3.2.10/step-by-step-deployment-guide.md).

# Step-by-step deployment guide

{% hint style="info" %}
This version of the gateway is currently compatible with Elimity Insights server versions matching `>3.42.0`.
{% endhint %}

## 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:

{% tabs %}
{% tab title="SQL Server" %}

```json
{
  "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"
}
```

{% endtab %}

{% tab title="MySQL" %}

```json
{
  "connectionStrings": ["my-user:my-password@tcp(my-host:my-port)/my-database"],
  "driver": "mySql",
  "jwtValidationBaseUrl": "https://example.elimity.com",
  "jwtValidationGatewayUrl": "https://gateway.example.com",
  "jwtValidationSourceId": "42"
}
```

{% endtab %}

{% tab title="Oracle DB" %}

```json
{
  "connectionStrings": ["oracle://my-user:my-password@my-host:my-port/my-database"],
  "driver": "oracleDb",
  "jwtValidationBaseUrl": "https://example.elimity.com",
  "jwtValidationGatewayUrl": "https://gateway.example.com",
  "jwtValidationSourceId": "42"
}
```

{% endtab %}
{% endtabs %}

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

<table data-full-width="true"><thead><tr><th>Property</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code>connectionStrings</code></td><td><code>list[string]</code></td><td>JSON array of strings describing how to connect with the databases; refer to <a href="#connection-strings">the dedicated section below</a> for more information</td></tr><tr><td><code>driver</code></td><td><code>string</code></td><td>Driver to use for connecting with your databases; choose between <code>"ibmDb2"</code> (Linux-only), <code>"mySql"</code>, <code>"oracleDb"</code> and <code>"sqlServer"</code></td></tr><tr><td><code>jwtValidationAudiences</code></td><td><code>option[list[string]]</code></td><td>Audiences for JWT validation, defaults to <code>["gateway"]</code></td></tr><tr><td><code>jwtValidationBaseUrl</code></td><td><code>string</code></td><td>Expected Elimity Insights base URL for JWT validation, e.g. <code>"https://example.elimity.com"</code></td></tr><tr><td><code>jwtValidationGatewayUrl</code></td><td><code>string</code></td><td>Expected gateway URL for JWT validation, e.g. <code>"https://gateway.example.com"</code></td></tr><tr><td><code>jwtValidationIssuer</code></td><td><code>option[string]</code></td><td>Issuer for JWT validation, defaults to <code>"https://auth.elimity.com/"</code></td></tr><tr><td><code>jwtValidationExpr</code></td><td><code>option[string]</code></td><td><a href="https://expr-lang.org/">Expr</a> program implementing JWT custom claim validation, defaults to <code>"claims.base_url == baseURL &#x26;&#x26; claims.gateway_url == gatewayURL &#x26;&#x26; claims.source_id == sourceID"</code></td></tr><tr><td><code>jwtValidationOptional</code></td><td><code>option[boolean]</code></td><td>Flag indicating whether JWT validation is optional, defaults to <code>false</code></td></tr><tr><td><code>jwtValidationSourceId</code></td><td><code>string</code></td><td>Expected source id for JWT validation, e.g. <code>"42"</code></td></tr></tbody></table>

### 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` (Linux-only) | [Official documentation of IBM DB2](https://www.ibm.com/docs/en/db2/12.1.0?topic=functions-sqldriverconnect-function-cli-connect-data-source#r0000584__title__7)        |
| `mySql`               | [Official documentation of the de-facto standard MySQL driver for Go](https://github.com/go-sql-driver/mysql?tab=readme-ov-file#dsn-data-source-name)                   |
| `oracleDb`            | [Connection string parser source code of the de-facto standard Oracle Database driver for Go](https://pkg.go.dev/github.com/sijms/go-ora/v2/configurations#ParseConfig) |
| `sqlServer`           | [Official documentation of the de-facto standard SQL Server driver for Go](https://github.com/microsoft/go-mssqldb?tab=readme-ov-file#connection-parameters-and-dsn)    |

### 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](/reference-manual/advanced-topics/gateway-based-imports.md)
* [OAuth2 endpoint parameters for gateway authentication](/reference-manual/server-configuration/oauth2-endpoint-parameters-for-gateway-authentication.md)

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](/technical-guides/gateways-and-import-agents.md) 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. For Elimity Insights server versions matching `>=3.46.0`, set the SDK version to `1`. Also add the following configuration values:

<table data-full-width="true"><thead><tr><th>Key</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code>entityTypes</code></td><td>JSON</td><td>JSON array describing how to import entities from your databases; refer to <a href="#entity-types">the dedicated section below</a> for more information</td></tr><tr><td><code>relationshipTypes</code></td><td>JSON</td><td>JSON array describing how to import relationship types from your databases; refer to <a href="#relationship-types">the dedicated section below</a> for more information</td></tr></tbody></table>

### 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:

<table data-full-width="true"><thead><tr><th>Property</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code>attributes</code></td><td><code>list[object]</code></td><td>Describes how to import attributes of the entity type's entities</td></tr><tr><td><code>attributes[].id</code></td><td><code>string</code></td><td>Unique identifier of the attribute type for which the gateway should import assignments</td></tr><tr><td><code>attributes[].type</code></td><td><code>string</code></td><td>Data type of the attribute type, one of <code>"boolean"</code>, <code>"date"</code>, <code>"dateTime"</code>, <code>"number"</code>, <code>"string"</code> or <code>"time"</code></td></tr><tr><td><code>id</code></td><td><code>string</code></td><td>Unique identifier of the entity type for which the gateway should import entities</td></tr><tr><td><code>query</code></td><td><code>string</code></td><td>Query that the gateway should send to the configured SQL instances, each resulting row corresponds to an entity of this type; refer to <a href="#query-format">the dedicated section on this page</a> for more information</td></tr></tbody></table>

### 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:

<table data-full-width="true"><thead><tr><th>Property</th><th>Type</th><th>Description</th></tr></thead><tbody><tr><td><code>attributes</code></td><td><code>list[object]</code></td><td>Describes how to import attributes of the relationship type's entities</td></tr><tr><td><code>attributes[].id</code></td><td><code>string</code></td><td>Unique identifier of the attribute type for which the gateway should import assignments</td></tr><tr><td><code>attributes[].type</code></td><td><code>string</code></td><td>Data type of the attribute type, one of <code>"boolean"</code>, <code>"date"</code>, <code>"dateTime"</code>, <code>"number"</code>, <code>"string"</code> or <code>"time"</code></td></tr><tr><td><code>fromEntityType</code></td><td><code>string</code></td><td>Unique identifier of the entity type from which the relationships start</td></tr><tr><td><code>query</code></td><td><code>string</code></td><td>Query that the gateway should send to the configured SQL instances, each resulting row corresponds to an entity of this type; refer to <a href="#query-format">the dedicated section on this page</a> for more information</td></tr><tr><td><code>toEntityType</code></td><td><code>string</code></td><td>Unique identifier of the entity type where the relationships end</td></tr></tbody></table>

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


---

# Agent Instructions
This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com.

## Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter:

```
GET https://docs.elimity.com/gateways-sql/v3.2.10/step-by-step-deployment-guide.md?ask=<question>&goal=<endgoal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal.

The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.