@fnet/clickhouse

Introduction

@fnet/clickhouse is a Node.js module designed to provide a streamlined interface for interacting with a ClickHouse database. This project aims to simplify database operations by allowing users to run single or multiple SQL queries efficiently. It offers a convenient method to manage database connections and execute queries dynamically from a specified directory.

How It Works

The primary functionality revolves around establishing a connection to a ClickHouse database and executing SQL queries. Users can initiate a connection with basic parameters such as host, port, and authentication details. Once connected, the module allows you to run queries either defined in your code or stored in SQL files within a designated directory. The module handles query execution and can return results in a JSON format, providing a straightforward way to integrate ClickHouse operations into your application.

Key Features

• Dynamic Query Execution: Execute individual or multiple SQL queries, either programmatically or from stored files.
• Flexible Configuration: Set up connection parameters such as host, port, username, password, and database name, including optional settings like request timeout and compression.
• Caching Mechanism: Optionally cache query results to optimize repeated query execution.
• Session Management: Supports optional session IDs and keeps the connection alive for improved performance.
• Error Handling: Provides error messages to assist with debugging when queries fail.
• Support for SQL Files: Load and execute SQL queries from files stored in a specified directory.

Conclusion

@fnet/clickhouse offers a practical and efficient way to interact with a ClickHouse database by abstracting the technical details of connection management and query execution. With its flexible setup and caching options, this module is a useful tool for developers looking to integrate ClickHouse database operations into their Node.js applications effectively.

Developer Guide for @fnet/clickhouse

Overview

The @fnet/clickhouse library is designed to facilitate interaction with a ClickHouse database using JavaScript. It provides an easy and efficient way to run SQL queries on ClickHouse, manage query files, and handle database connections. The primary use cases include executing single and multiple queries and leveraging local or cached query files.

Installation

To install the library, use npm or yarn:

npm install @fnet/clickhouse

or

yarn add @fnet/clickhouse

Usage

The @fnet/clickhouse library allows you to create a database connection and execute queries stored in files or defined within the code. Here’s how you can set up and use the library:

Setting Up a Connection

To establish a connection to your ClickHouse database, configure the connection parameters:

import clickhouse from '@fnet/clickhouse';

// Define connection parameters
const args = {
  host: 'http://localhost',
  port: 8123,
  username: 'default',
  password: '',
  database: 'default',
  queries_dir: './queries', // Directory where SQL queries are stored
  use_cache: false // Optional: Enable query caching
};

(async () => {
  // Initialize the database connection
  const db = await clickhouse(args);

  // Check the connection
  const tableCount = await db.check();
  console.log(`Number of tables in the database: ${tableCount}`);

  // Don't forget to close the connection when done
  await db.close();
})();

Running Queries

Single Query

To run a single query from a file or in-memory string, use the run method:

(async () => {
  const params = { id: 1 }; // Example parameters
  const result = await db.query.users.run(params);
  console.log(result);
})();

Multiple Queries

If you have multiple queries in a single file separated by semicolons, use the mrun method:

(async () => {
  const params = {}; // Provide parameters as needed
  const results = await db.query.reports.mrun(params);
  console.log(results);
})();

Examples

Example Query Usage

Suppose you have a query stored in ./queries/users.sql. The file might contain:

SELECT * FROM users WHERE id = {id};

You can execute this query as shown below:

await db.query.users.run({ id: 123 }); // Replaces {id} with 123

Example Multi-Query Usage

For multiple queries within a single file:

SELECT name FROM users;
SELECT COUNT(*) FROM orders;

Run the queries together:

const results = await db.query.multiReports.mrun();
console.log(results); // Output will be an array of results for each query

Acknowledgement

The @fnet/clickhouse library is built on top of the @clickhouse/client package, which handles the underlying interactions with the ClickHouse database. Special thanks to the contributors of the @clickhouse/client for their efforts in creating a reliable database client.

Input Schema

$schema: https://json-schema.org/draft/2020-12/schema
type: object
properties:
  host:
    type: string
    description: The host URL for the ClickHouse server
    default: http://localhost
  port:
    type: integer
    description: The port number for the ClickHouse server
    default: 8123
  username:
    type: string
    description: The username for ClickHouse authentication
    default: default
  password:
    type: string
    description: The password for ClickHouse authentication
    default: ""
  database:
    type: string
    description: The name of the database to connect to
    default: default
  request_timeout:
    type: integer
    description: Request timeout in milliseconds
    default: 30000
  compression:
    type: object
    properties:
      response:
        type: boolean
        description: Response compression toggle
        default: false
      request:
        type: boolean
        description: Request compression toggle
        default: false
    description: Request and response compression settings
    default:
      response: false
      request: false
  max_open_connections:
    type: integer
    description: Maximum number of open connections
    default: 10
  keep_alive:
    type: object
    properties:
      enabled:
        type: boolean
        description: Keep-alive toggle
        default: true
    description: Keep-alive configuration
    default:
      enabled: true
  http_headers:
    type: object
    additionalProperties: true
    description: Additional HTTP headers for requests
    default: {}
  session_id:
    type:
      - string
      - "null"
    description: Optional session ID for ClickHouse sessions
    default: null
  log:
    type: object
    properties:
      level:
        type: string
        description: Logging level
        default: OFF
    description: Logging configuration
    default:
      level: OFF
  clickhouse_settings:
    type: object
    additionalProperties: true
    description: Specific ClickHouse settings
    default: {}
  tls:
    type:
      - object
      - "null"
    description: TLS configuration settings (optional)
    default: null
  queries_dir:
    type: string
    description: Path to the directory containing query files
    default: ./queries
  use_cache:
    type: boolean
    description: Toggle to use caching for queries
    default: false
required: []