clickhouse-ts-client
v1.1.0
Published
Clickhouse DB client for Node JS written in Typescript
Downloads
7
Maintainers
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
orObjects
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
orObjects
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.