ClickHouse JS

The entire list of supported ClickHouse formats is available
[here](/docs/sql-reference/data-types).

See also:

- [Working with Dynamic/Variant/JSON examples](https://github.com/ClickHouse/clickhouse-js/blob/main/examples/dynamic_variant_json.ts)
- [Working with Time/Time64 examples](https://github.com/ClickHouse/clickhouse-js/blob/main/examples/time_time64.ts)

### Date/Date32 types caveats[​](#datedate32-types-caveats "Direct link to Date/Date32 types caveats")

Since the client inserts values without additional type conversion, `Date`/`Date32` type columns can only be inserted as
strings.

**Example:** Insert a `Date` type value.
[Source code](https://github.com/ClickHouse/clickhouse-js/blob/ba387d7f4ce375a60982ac2d99cb47391cf76cec/__tests__/integration/date_time.test.ts)

```
await client.insert({
  table: 'my_table',
  values: [ { date: '2022-09-05' } ],
  format: 'JSONEachRow',
})
```

However, if you're using `DateTime` or `DateTime64` columns, you can use both strings and JS Date objects. JS Date objects can be passed to `insert` as-is with `date_time_input_format` set to `best_effort`. See this [example](https://github.com/ClickHouse/clickhouse-js/blob/main/examples/insert_js_dates.ts) for more details.

### Decimal\* types caveats[​](#decimal-types-caveats "Direct link to Decimal* types caveats")

It is possible to insert Decimals using `JSON*` family formats. Assuming we have a table defined as:

```
CREATE TABLE my_table
(
  id     UInt32,
  dec32  Decimal(9, 2),
  dec64  Decimal(18, 3),
  dec128 Decimal(38, 10),
  dec256 Decimal(76, 20)
)
ENGINE MergeTree()
ORDER BY (id)
```

We can insert values without precision loss using the string representation:

```
await client.insert({
  table: 'my_table',
  values: [{
    id: 1,
    dec32:  '1234567.89',
    dec64:  '123456789123456.789',
    dec128: '1234567891234567891234567891.1234567891',
    dec256: '12345678912345678912345678911234567891234567891234567891.12345678911234567891',
  }],
  format: 'JSONEachRow',
})
```

However, when querying the data in `JSON*` formats, ClickHouse will return Decimals as *numbers* by default, which could lead to precision loss. To avoid this, you could cast Decimals to string in the query:

```
await client.query({
  query: `
    SELECT toString(dec32)  AS decimal32,
           toString(dec64)  AS decimal64,
           toString(dec128) AS decimal128,
           toString(dec256) AS decimal256
    FROM my_table
  `,
  format: 'JSONEachRow',
})
```

See [this example](https://github.com/ClickHouse/clickhouse-js/blob/main/examples/insert_decimals.ts) for more details.

### Integral types: Int64, Int128, Int256, UInt64, UInt128, UInt256[​](#integral-types-int64-int128-int256-uint64-uint128-uint256 "Direct link to Integral types: Int64, Int128, Int256, UInt64, UInt128, UInt256")

Though the server can accept it as a number, it is returned as a string in `JSON*` family output formats to avoid
integer overflow as max values for these types are bigger than `Number.MAX_SAFE_INTEGER`.

This behavior, however, can be modified
with [`output_format_json_quote_64bit_integers` setting](/docs/operations/settings/formats#output_format_json_quote_64bit_integers)
.

**Example:** Adjust the JSON output format for 64-bit numbers.

```
const resultSet = await client.query({
  query: 'SELECT * from system.numbers LIMIT 1',
  format: 'JSONEachRow',
})

expect(await resultSet.json()).toEqual([ { number: '0' } ])
```

```
const resultSet = await client.query({
  query: 'SELECT * from system.numbers LIMIT 1',
  format: 'JSONEachRow',
  clickhouse_settings: { output_format_json_quote_64bit_integers: 0 },
})

expect(await resultSet.json()).toEqual([ { number: 0 } ])
```

## ClickHouse settings[​](#clickhouse-settings "Direct link to ClickHouse settings")

The client can adjust ClickHouse behavior via [settings](/docs/operations/settings/settings)
mechanism.
The settings can be set on the client instance level so that they will be applied to every request sent to the
ClickHouse:

```
const client = createClient({
  clickhouse_settings: {}
})
```

Or a setting can be configured on a request-level:

```
client.query({
  clickhouse_settings: {}
})
```

A type declaration file with all the supported ClickHouse settings can be found
[here](https://github.com/ClickHouse/clickhouse-js/blob/main/packages/client-common/src/settings.ts).

References

Make sure that the user on whose behalf the queries are made has sufficient rights to change the settings.

## Advanced topics[​](#advanced-topics "Direct link to Advanced topics")

### Queries with parameters[​](#queries-with-parameters "Direct link to Queries with parameters")

You can create a query with parameters and pass values to them from client application. This allows to avoid formatting
query with specific dynamic values on client side.

Format a query as usual, then place the values that you want to pass from the app parameters to the query in braces in
the following format:

```
{<name>: <data_type>}
```

where:

- `name` — Placeholder identifier.
- `data_type` - [Data type](/docs/sql-reference/data-types) of the app parameter value.

**Example:**: Query with parameters.
[Source code](https://github.com/ClickHouse/clickhouse-js/blob/main/examples/query_with_parameter_binding.ts)
.

```
await client.query({
  query: 'SELECT plus({val1: Int32}, {val2: Int32})',
  format: 'CSV',
  query_params: {
    val1: 10,
    val2: 20,
  },
})
```

Check <https://clickhouse.com/docs/interfaces/cli#cli-queries-with-parameters-syntax> for additional details.

### Compression[​](#compression "Direct link to Compression")

NB: request compression is currently not available in the Web version. Response compression works as normal. Node.js version supports both.

Data applications operating with large datasets over the wire can benefit from enabling compression. Currently, only `GZIP` is supported using [zlib](https://nodejs.org/docs/latest-v14.x/api/zlib.html).

```
createClient({
  compression: {
    response: true,
    request: true
  }
})
```

Configurations parameters are:

- `response: true` instructs ClickHouse server to respond with compressed response body. Default value: `response: false`
- `request: true` enables compression on the client request body. Default value: `request: false`

### Logging (Node.js only)[​](#logging-nodejs-only "Direct link to Logging (Node.js only)")

References

The logging is an experimental feature and is subject to change in the future.

The default logger implementation emits log records into `stdout` via `console.debug/info` methods and `stderr` via `console.warn/error` methods.
You can customize the logging logic via providing a `LoggerClass`, and choose the desired log level via `level` parameter (default is `WARN`):

```
import type { Logger } from '@clickhouse/client'

// All three LogParams types are exported by the client
interface LogParams {
  module: string
  message: string
  args?: Record<string, unknown>
}
type ErrorLogParams = LogParams & { err: Error }
type WarnLogParams = LogParams & { err?: Error }

class MyLogger implements Logger {
  trace({ module, message, args }: LogParams) {
    // ...
  }
  debug({ module, message, args }: LogParams) {
    // ...
  }
  info({ module, message, args }: LogParams) {
    // ...
  }
  warn({ module, message, args }: WarnLogParams) {
    // ...
  }
  error({ module, message, args, err }: ErrorLogParams) {
    // ...
  }
}

const client = createClient({
  log: {
    LoggerClass: MyLogger,
    level: ClickHouseLogLevel.DEBUG,
  }
})
```

Currently, the client will log the following events:

- `TRACE` - low-level information about the Keep-Alive sockets life cycle
- `DEBUG` - response information (without authorization headers and host info)
- `INFO` - mostly unused, will print the current log level when the client is initialized
- `WARN` - non-fatal errors; failed `ping` request is logged as a warning, as the underlying error is included in the returned result
- `ERROR` - fatal errors from `query`/`insert`/`exec`/`command` methods, such as a failed request