# ClickHouse

## Installation

1. Install the package:

   **If you are developing with binary, the package is already bundled in the binary. You can skip this step.**

   ```bash
   npm i @vulcan-sql/extension-driver-clickhouse
   ```

2. Update your `vulcan.yaml` file to enable the extension:

   ```yaml
   extensions:
     ...
     // highlight-next-line
     ch: '@vulcan-sql/extension-driver-clickhouse' # Add this line
   ```

3. Create a new profile in your `profiles.yaml` file or in the designated profile paths. For example:

   ```yaml
   - name: ch # profile name
     type: clickhouse
     connection:
       host: www.example.com:8123
       request_timeout: 60000
       compression:
         request: true
       max_open_connections: 10
       username: user
       password: pass
       database: hello-clickhouse
     allow: '*'
   ```

## Configuration

For more information, please refer to the [ClickHouse Client documentation](https://clickhouse.com/docs/en/integrations/language-clients/nodejs) to learn about the available arguments for the ClickHouse Client.

| Name                 | Required | Default               | Description                                                                                                                                                                                                                                                            |
| -------------------- | -------- | --------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| host                 | N        | http://localhost:8123 | ClickHouse instance URL.                                                                                                                                                                                                                                               |
| request_timeout      | N        | 30000                 | Request timeout in milliseconds.                                                                                                                                                                                                                                       |
| max_open_connections | N        | Infinity              | Maximum number of sockets to allow per host.                                                                                                                                                                                                                           |
| compression          | N        |                       | Compression settings for data transfer. Currently, only GZIP compression using zlib is supported. See [Compression docs](https://clickhouse.com/docs/en/integrations/language-clients/nodejs#compression) for details.                                                 |
| username             | N        | default               | The name of the user on whose behalf requests are made.                                                                                                                                                                                                                |
| password             | N        |                       | The user's password.                                                                                                                                                                                                                                                   |
| application          | N        | VulcanSQL             | The name of the application using the Node.js client.                                                                                                                                                                                                                  |
| database             | N        | default               | Database name to use.                                                                                                                                                                                                                                                  |
| clickhouse_settings  | N        |                       | ClickHouse settings to apply to all requests. For all available settings, see [Advanced Settings](https://clickhouse.com/docs/en/operations/settings), and For the definition, see [Definition](https://github.com/ClickHouse/clickhouse-js/blob/0.1.1/src/settings.ts) |
| tls                  | N        |                       | Configure TLS certificates. See [TLS docs](https://clickhouse.com/docs/en/integrations/language-clients/nodejs#tls-certificates).                                                                                                                                      |
| session_id           | N        |                       | ClickHouse Session ID to send with every request.                                                                                                                                                                                                                      |
| keep_alive           | N        |                       | HTTP Keep-Alive related settings. See [Keep Alive docs](https://clickhouse.com/docs/en/integrations/language-clients/nodejs#keep-alive)                                                                                                                                |

The `log` option is not included above because it requires defining a Logger class and assigning it. Therefore, it cannot be set through `profiles.yaml`.

## Note

ClickHouse supports parameterized queries to prevent SQL injection using prepared statements. Named placeholders are defined using the `{name:type}` syntax. For more information, refer to the [Query with Parameters](https://clickhouse.com/docs/en/integrations/language-clients/nodejs#queries-with-parameters) section in the ClickHouse documentation.

However, the VulcanSQL API supports JSON format for API query parameters and does not support the full range of types available in ClickHouse. VulcanSQL only supports the conversion of the following types:

- `boolean` to ClickHouse type `Bool`
- `number` to ClickHouse types `Int` or `Float`
- `string` to ClickHouse type `String`

Therefore, if you need to query data with special types in ClickHouse, such as `Array(Unit8)`, `Record<K, V>`, `Date`, `DateTime`, and so on, you can use ClickHouse [Regular Functions](https://clickhouse.com/docs/en/sql-reference/functions) or [Type Conversion Functions](https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions) to handle them.

Example:

```sql
-- If the `val` from the API query parameter is '1990-11-01' and the `born_date` column is of type `Date32`,
-- you can use the toDate function to convert the value. See https://clickhouse.com/docs/en/sql-reference/functions/type-conversion-functions#todate
SELECT * FROM users WHERE born_date = toDate({val:String});
```

## ⚠️ Caution

The ClickHouse driver currently does not support caching datasets. If you use the ClickHouse driver with caching dataset features, it will result in failure.
