# SQL databases

## Overview

**Joule** can publish processed events to a configured SQL database using a **SQL Publisher transport**. All databases with JDBC type 4 drivers are supported. This includes Postgres, MySQL, Oracle, Snowflake, Big Query, HyperSQL etc;. 

This is achieved by dynamically generating `INSERT` statements based on the `StreamEvent`, ensuring the event's attributes match the database table's schema.

This feature is ideal for use cases such as offline analytics, business reporting, dashboards and process testing.

{% hint style="info" %}
**Driver details:** The necessary JDBC driver (Type 4) for the SQL database must be placed in the `userlibs` directory to be included in the Joule runtime `classpath`
{% endhint %}

## Example & DSL attributes

This example configures the **SQL Publisher** to publish events to a PostgreSQL database.

It specifies the JDBC driver (`org.postgresql.Driver`), connects to the database `testdb` on `localhost:5740` and inserts events into the `s1mme` table.

Timestamps are included in the insertions. The connection uses SSL with the username and password set to `postgres`.

```yaml
sqlPublisher:
  jdbcDriver: org.postgresql.Driver
  host: jdbc:postgresql://localhost:5740
  database: testdb
  table: s1mme
  includeTimestamps: true
  properties:
    ssl: true
    user: postgres
    password: postgres
```

### Attributes schema

Configuration parameters available for the InfluxDB publisher transport. The parameters are organised by order of importance, ranked from high to low.

<table><thead><tr><th width="204">Attribute</th><th width="217">Description</th><th width="246">Data Type</th><th data-type="checkbox">Required</th></tr></thead><tbody><tr><td>jdbcDriver </td><td>Full jdbc namespace</td><td>String</td><td>true</td></tr><tr><td>host</td><td>JDBC url</td><td>String</td><td>true</td></tr><tr><td>database</td><td>Database name</td><td>String</td><td>true</td></tr><tr><td>table</td><td>Target loading table</td><td>String</td><td>true</td></tr><tr><td>includeTimestamps</td><td>Bucket where event measurements are persisted</td><td><p>Boolean</p><p>Default: False</p></td><td>false</td></tr><tr><td>properties</td><td>Database specific properties</td><td>Properties map</td><td>true</td></tr><tr><td>batchSize</td><td>Insert batch size </td><td><p>Integer</p><p>Default: 1024</p></td><td>false</td></tr><tr><td>timeout</td><td>Timeout to send queued events to be inserted into database</td><td><p>Long</p><p>Default: 5000ms</p></td><td>false</td></tr><tr><td>autocommit</td><td><p>Sets connection's auto-commit mode to the given state.</p><p></p><p>true to enable auto-commit mode; false to disable it</p></td><td>Boolean<br>Default: false</td><td>false</td></tr></tbody></table>