npm package discovery and stats viewer.

Discover Tips

  • General search

    [free text search, go nuts!]

  • Package details

    pkg:[package-name]

  • User packages

    @[username]

Sponsor

Optimize Toolset

I’ve always been into building performant and accessible sites, but lately I’ve been taking it extremely seriously. So much so that I’ve been building a tool to help me optimize and monitor the sites that I build to make sure that I’m making an attempt to offer the best experience to those who visit them. If you’re into performant, accessible and SEO friendly sites, you might like it too! You can check it out at Optimize Toolset.

About

Hi, 👋, I’m Ryan Hefner  and I built this site for me, and you! The goal of this site was to provide an easy way for me to check the stats on my npm packages, both for prioritizing issues and updates, and to give me a little kick in the pants to keep up on stuff.

As I was building it, I realized that I was actually using the tool to build the tool, and figured I might as well put this out there and hopefully others will find it to be a fast and useful way to search and browse npm packages as I have.

If you’re interested in other things I’m working on, follow me on Twitter or check out the open source projects I’ve been publishing on GitHub.

I am also working on a Twitter bot for this site to tweet the most popular, newest, random packages from npm. Please follow that account now and it will start sending out packages soon–ish.

Open Software & Tools

This site wouldn’t be possible without the immense generosity and tireless efforts from the people who make contributions to the world and share their work via open source initiatives. Thank you 🙏

© 2024 – Pkg Stats / Ryan Hefner

clickhouse-ts-client

v1.1.0

Published

Clickhouse DB client for Node JS written in Typescript

Downloads

7

Readme

clickhouse-ts-client

Clickhouse DB client for NodeJS written in Typescript.

Key features

  • Provides a simple and clear interface for working with ClickHouse databases
  • Supports streaming for efficient memory utilization
  • Supports parameterized queries
  • Optional type checking for query results and input data
  • Minimal dependencies

Table of Contents

Installation

You can install clickhouse-ts-client using npm/yarn:

npm install clickhouse-ts-client

# or

yarn add clickhouse-ts-client

Usage example

import clickhouse, { ParseMode } from "clickhouse-ts-client";

const { query, input } = clickhouse();

const createTab = query("CREATE TABLE IF NOT EXISTS clicks (time DateTime, ip IPv4) ENGINE = Memory").exec;
const dropTab = query("DROP TABLE IF EXISTS clicks").exec;
const insertData = input("INSERT INTO clicks");
const getDailyStats = query("SELECT toDate(time) as dt, uniq(ip) FROM clicks GROUP BY dt")
        .loader({ mode: ParseMode.Rows });

await createTab();
await insertData([
    { time: "2023-06-16 23:45:00", ip: "192.168.1.0" },
    { time: "2023-06-17 00:01:15", ip: "192.168.1.1" },
    { time: "2023-06-17 14:47:01", ip: "192.168.1.1" },
]);
console.table([["Date", "Uniq clicks"], ...await getDailyStats()]);
await dropTab();

Guide

Setting up a connection

import clickhouse from "clickhouse-ts-client";

// using settings object:
const conn1 = clickhouse({
    proto: "https",
    host: "192.168.1.0",
    port: 18123, // you can pass string here as well
    user: "tester",
    pwd: "secret",
    db: "test"
});

// using DSN string:
const conn2 = clickhouse("https://tester:[email protected]:18123/?database=test");

Note: call to default function will not establish a connection, it will only create a connector object. Connection will be established on first query call.

Executing a query

const { query } = clickhouse();
await query("CREATE TABLE IF NOT EXISTS clicks (time DateTime, ip IPv4) ENGINE = Memory").exec();

Receiving data from DB

Load/parse whole result

const last100ClicksQuery = query("SELECT * FROM clicks ORDER BY time DESC LIMIT 100 FORMAT PrettyCompact");

// Don't parse data, just return raw string as it was received from DB:
const loadRaw = last100ClicksQuery.loader(); // default mode is ParseMode.Raw
console.log(await loadRaw());

// Parse data as array of objects:
const loadClicks = last100ClicksQuery.loader({ mode: ParseMode.Objects });
const uniqIps = new Set((await loadClicks()).map(({ ip }) => ip));

// Parse data as array of rows:
const loadRows = last100ClicksQuery.loader({ mode: ParseMode.Rows });
console.table(await loadRows());

Note: format defined by query will be overridden if you use Rows or Objects parse mode.

Stream query results

import {createWriteStream} from "fs";
import {pipeline} from "stream/promises";

const last100ClicksQuery = query("SELECT * FROM clicks ORDER BY time DESC LIMIT 100 FORMAT CSV");

// Stream response without parsing:
const readRaw = last100ClicksQuery.reader(); // default mode is ParseMode.Raw
await pipeline(readRaw(), createWriteStream("clicks.csv"));

// Create a stream of objects:
const read = last100ClicksQuery.reader({ mode: ParseMode.Objects });
for await (const { time, ip } of read()) {
    console.log(`${time};${ip}`);
}

// Stream as parsed rows:
const readRows = last100ClicksQuery.reader({ mode: ParseMode.Rows });
for await (const [time, ip] of readRows()) {
    console.log(`${time};${ip}`);
}

Notes:

  • format defined by query will be overridden if you use Rows or Objects parse mode
  • stream returned by reader function is a NodeJS ReadableStream, so you can use it with any other NodeJS stream API
  • returned stream set to the object mode if you use Rows or Objects parse mode

Describe types for query results

You can pass generic type to the loader/reader funcs to get type checking for query results:

import { Row } from "clickhouse-ts-client";

interface Click {
    time: string;
    ip: number;
}

type ClickRow = Row<Click, ["time", "ip"]>; // [string, number];

const last100ClicksQuery = query("SELECT time, ip FROM clicks ORDER BY time DESC LIMIT 100");

// we can pass type in both rows and objects mode:
const loadClicks = last100ClicksQuery.loader<Click>({ mode: ParseMode.Objects }); // () => Promise<Click[]>
const loadClickRows = last100ClicksQuery.loader<ClickRow>({ mode: ParseMode.Rows }); // () => Promise<ClickRow[]>

// same for stream:
const readClicks = last100ClicksQuery.reader<Click>({ mode: ParseMode.Objects }); // () => TypedReadable<Click>
const readClickRows = last100ClicksQuery.reader<ClickRow>({ mode: ParseMode.Rows }); // () => TypedReadable<ClickRow>

// TS will emit an error if you try to pass wrong type for the current parse mode
const loadClicksE = last100ClicksQuery.loader<Click>(); // compile error!
const loadClickRowsE = last100ClicksQuery.loader<ClickRow>({ mode: ParseMode.Objects }); // compile error!

Sending data into DB

import { createReadStream } from "fs";
import { Readable } from "stream";
import clickhouse, { createStreamInput } from "clickhouse-ts-client";

const { input } = clickhouse();

const insertData = input("INSERT INTO clicks FORMAT CSV");

// Insert data from CSV string:
await insertData(`2023-06-16 23:45:00,192.168.1.0\n2023-06-17 00:01:15,192.168.1.1`);

// Insert data from array of objects:
await insertData([
    { time: "2023-06-16 23:45:00", ip: "192.168.1.0" },
    { time: "2023-06-17 00:01:15", ip: "192.168.1.1" }
]);

// Insert data from array of rows:
await insertData({
    rows: [
        ["2023-06-17 14:47:01", "192.168.1.0"],
        ["2023-06-17 00:01:15", "192.168.1.1"]
    ]
});

// Insert data from raw stream:
await insertData(createReadStream("clicks.csv"));

// Insert data from stream of objects (we use generator here to create a stream):
async function *generateClicks(n=100) {
    for (let i=0; i<n; i++) {
        yield { time: new Date(), ip: "192.168.1.0" };
        await new Promise(resolve => setTimeout(resolve, 500));
    }
}
await insertData(Readable.from(generateClicks()));

// Insert data from stream of rows 
// (we use util function createStreamInput here to create a temporary stream):
const rows = createStreamInput();
(async (n=100) => {
    for (let i=0; i<n; i++) {
        rows.write([new Date(), "192.168.1.0"]);
        await new Promise(resolve => setTimeout(resolve, 500));
    }
    rows.end();
})().catch(console.error);
await insertData({ rows });

Note: format defined by query will be overridden if you use objects/rows input mode.

Describe types for input data

import { Row } from "clickhouse-ts-client";

interface Click {
  time: string;
  ip: number;
}

type ClickRow = Row<CLick, ["time", "ip"]>; // [string, number];

const { input } = clickhouse();

const insertClicks = input<Click>("INSERT INTO clicks");
await insertData([
  { time: "2023-06-16 23:45:00", ip: 3232235776 /* "192.168.1.0" in a long format */ }
]); // OK
await insertData([
  { time: "2023-06-16 23:45:00", ip: "192.168.1.0" }
]); // compile error!

const insertClickRows = input<ClickRow>("INSERT INTO clicks (time, ip)");
await insertClickRows({
    rows: [
        ["2023-06-16 23:45:00", 3232235776]
    ]
}); // OK
await insertClickRows({
    rows: [
        ["2023-06-16 23:45:00", "192.168.1.0"]
    ]
}); // compile error!
await insertClickRows([
  { time: "2023-06-16 23:45:00", ip: 3232235776 /* "192.168.1.0" in a long format */ }
]); // compile error!

Parameterized queries

Clickhouse supports parameterized queries in a form of {name:type} placeholders. You can use them in your queries and pass values for them as an object:

const { query } = clickhouse();
const loadData = query("select * from clicks where toDate(time) = {date:String}");

const someApiHandler = async (req, res) => {
    const { date } = req.query;
    res.json(await loadData({ date }));
};

Notes:

  • you should use parameterized queries to avoid SQL injection when you pass user input to the query string
  • you may not to pre-process the input values some way, because it's not a part of the query string, and you'll just get an error if your input is not correct for the specified format (or format that was inferred from the type of input data)

Error handling

There are three types of errors that can be thrown: ConnectionError, DataProcessingError and QueryingError. All of them are subclasses of ClickhouseError.

import clickhouse, { ClickhouseError, ConnectionError, DataProcessingError, QueryingError } from "clickhouse-ts-client";

const { query } = clickhouse( { host: "wrong.host" } );

try {
    await query("INSERT INTO clicks VALUES (1, 2, 3)").exec();
} catch (err) {
    if (err instanceof ClickhouseError) {
        switch (true) {
            case err instanceof ConnectionError:
                // handle ConnectionError
            case err instanceof DataProcessingError:
                // handle DataProcessingError
            case err instanceof QueryingError:
                // handle QueryingError
            default:
                // handle other ClickhouseError (normally it should not happen)
        }
    } else {
        throw err;
    }
}

Note: ConnectionError is thrown only when you try to execute a query, but not when you initialize a connection. So you can create a connection with wrong credentials, but you'll get an error only when you try to execute a query.

Contributing

Contributions are welcome! If you find any issues or have suggestions for improvement, please create an issue or submit a pull request on the GitHub repository.

License

clickhouse-ts-client is MIT licensed.