> ## Documentation Index
> Fetch the complete documentation index at: https://docs.togai.com/llms.txt
> Use this file to discover all available pages before exploring further.

# JDBC

Java Database Connectivity (JDBC) is an application programming interface which defines how a client may access a database. It provides methods to query and update data in a database, and is oriented toward relational databases.

The integration between **JDBC** and **Togai** requires only a one-time setup. Follow the steps below:

## Prerequisites

### Create a read-only user

* Create a read-only user in your database
* Grant the user access to the tables that you want to sync with Togai
* Make sure that the user has access to the tables that you want to sync with Togai

### Connection URI

#### Maria DB

The connection URI for Maria DB is as follows:

```bash theme={null}
jdbc:mariadb://<host>:<port>/<database_name>?user=<username>&password=<password>
```

#### MS SQL Server

The connection URI for MS SQL Server is as follows:

```bash theme={null}
jdbc:sqlserver://<host>:<port>;databaseName=<database_name>;user=<username>;password=<password>
```

#### MySQL DB

The connection URI for MySQL DB is as follows:

```bash theme={null}
jdbc:mysql://<host>:<port>/<database_name>?user=<username>&password=<password>
```

#### Oracle DB

The connection URI for Oracle DB is as follows:

```bash theme={null}
jdbc:oracle:thin:<username>/<password>@<host>:<port>:<database_name>
```

### SQL Query

All the flows in this integration require a SQL query to fetch the data from the database. The query should be written in such a way that it returns the data in the format that is expected by the Togai API. The query should also be written in such a way that it returns only the data that you want to sync with Togai.

## Flows

### 1. Sync customer from Database to Togai

* Provide the SQL query to fetch the customers from Database:
  ```sql theme={null}
  SELECT 
      <customer_id> AS "id", 
      <customer_name> AS "name", 
      <email> AS "primaryEmail", 
      <address_line_1> AS "addressLine1",
      <address_line_2> AS "addressLine2",
      <city> AS "address_city",
      <state> AS "address_state",
      <country> AS "address_country",
      <currency> AS "currency",
      <phone> AS "phone"
  FROM <table_name> 
  WHERE created_at > '${startTime}' 
  AND created_at < '${endTime}'
  ```
* The flow will be available to be triggered on demand from the Togai dashboard
* It will execute a SQL query to fetch all the customers from Database
* It will then create the customers in Togai using the `Customers` API

### 2. Sync events from Database to Togai

* Provide the SQL query to fetch the events from Database:
  ```sql theme={null}
  SELECT 
      <event_id> AS "id", 
      <account_id> AS "accountId", 
      <eventName> AS "schemaName",
      <attributes> AS "attributes",
      <dimensions> AS "dimensions",
      <created_at> AS "timestamp" 
  FROM <table_name> 
  WHERE created_at > '${startTime}' 
  AND created_at < '${endTime}'
  ```
* Make sure that the `attributes` are `JSON` array and `dimensions` columns are of type `JSON`
* The flow will be available to be triggered on demand from the Togai dashboard
* It will execute a SQL query to fetch all the events from Database
* It will then create the events in Togai using the `Events Ingestion` API

### 3. Sync usages from Database to Togai

* Provide the SQL query to fetch the usages from Database:
  ```sql theme={null}
  SELECT 
      <event_id> AS "id", 
      <account_id> AS "accountId", 
      <usage_count> AS "usage",
      <created_at> AS "timestamp" 
  FROM <table_name> 
  WHERE created_at > '${startTime}' 
  AND created_at < '${endTime}'
  ```
* The flow will be available to be triggered on schedule from the Togai dashboard
* It will execute a SQL query to fetch all the usages from Database
* It will then create the usages in Togai using the `Events Ingestion` API