Clickhouse ORM for Nodejs. Send query over HTTP interface. Using TimonKK/clickhouse.
npm i clickhouse-orm
Create instance:
const { ClickhouseOrm, DATA_TYPE, setLogService } = require("clickhouse-orm");
const chOrm = ClickhouseOrm({
db: {
name: "orm_test",
},
debug: true,
client: {
url: "localhost",
port: "8123",
basicAuth: {
username: "default",
password: "",
},
debug: false,
isUseGzip: true,
format: "json", // "json" || "csv" || "tsv"
},
});
Define Schema:
const table1Schema = {
// table name
tableName: "table1",
// define column name
schema: {
time: { type: DATA_TYPE.DateTime, default: Date },
status: { type: DATA_TYPE.Int32 },
browser: { type: DATA_TYPE.String },
browser_v: {},
},
// create table sql
createTable: (dbTableName) => {
// dbTableName = db + '.' + tableName = (orm_test.table1)
return `
CREATE TABLE IF NOT EXISTS ${dbTableName}
(
time DateTime,
status Int32,
browser LowCardinality(String),
browser_v String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(time)
ORDER BY time`;
},
};
create / build + save / find:
const doDemo = async () => {
// create database 'orm_test'
// SQL: CREATE DATABASE IF NOT EXISTS orm_test
await chOrm.createDatabase();
// register schema and create [if] table
// createTable() SQL: CREATE TABLE IF NOT EXISTS orm_test.table1...
const Table1Model = await chOrm.model(table1Schema);
// new data model
const data = Table1Model.build({ status: 2 });
// set value
data.time = new Date();
data.browser = "chrome";
data.browser_v = "90.0.1.21";
// do save
const res = await data.save();
// SQL: INSERT INTO orm_test.table1 (time,status,browser,browser_v) [{"time":"2022-02-05T07:51:16.919Z","status":2,"browser":"chrome","browser_v":"90.0.1.21"}]
console.log("save:", res);
// create === build + save
const resCreate = await Table1Model.create({
status: 1,
time: new Date(),
browser: "chrome",
browser_v: "90.0.1.21",
});
console.log("create:", resCreate);
// do find
Table1Model.find({
select: "*",
limit: 3,
}).then((res) => {
// SQL: SELECT * from orm_test.table1 LIMIT 3
console.log("find:", res);
});
};
doDemo();
More in Basic Example.
Note
: '?' is a Optional
db
: object<{name:string, engine?:string, cluster?:string}>
name: database name
engine?: database engine
cluster?: cluster name
debug
: boolean
Default: false
client
: object
Drive configuration. More in TimonKK/clickhouse.
tableName
: string
It is the table name.
schema
: { [column]: { type?, default? } }
The
type
will be verified, Thedefault
is the default value for column.
createTable
: string
It is the SQL for creating tables.When model is executed, this SQL will be executed. It is suggested to add 'IF NOT EXISTS'.
Watch out !!! >>>>> If the table already exists and you want to modify it. You need to execute the modification sql through other clients(Such as Remote terminal) and update the code of the Schema!!!
const table1Schema = {
// table name
tableName: "table1",
// define column
schema: {
time: { type: DATA_TYPE.DateTime, default: Date },
status: { type: DATA_TYPE.Int32 },
browser: { type: DATA_TYPE.String },
browser_v: {},
},
// create table sql
createTable: (dbTableName) => {
// dbTableName = db + '.' + tableName = (orm_test.table1)
return `
CREATE TABLE IF NOT EXISTS ${dbTableName}
(
time DateTime,
status Int32,
browser LowCardinality(String),
browser_v String
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(time)
ORDER BY time`;
},
};
// register schema and create [if] table
const Table1Model = await chOrm.model(table1Schema);
The clickhouse data types. Some types are defined here.
Columns with defined types will be verified. It only checks the basic type of data, not the most standard value. They are number | string | boolean | date
.
export enum DATA_TYPE {
UInt8 = 'number',
UInt16 = 'number',
UInt32 = 'number',
UInt64 = 'number',
UInt128 = 'number',
UInt256 = 'number',
Int8 = 'number',
Int16 = 'number',
Int32 = 'number',
Int64 = 'number',
Int128 = 'number',
Int256 = 'number',
Float32 = 'number',
Float64 = 'number',
Boolean = 'boolean',
String = 'string',
UUID = 'string',
Date = 'date|string|number',
Date32 = 'date|string|number',
DateTime = 'date|string|number',
DateTime64 = 'date|string|number',
}
schema: {
time: { type: DATA_TYPE.DateTime, default: Date },
[column]: { type?, default? }
}
The setLogService is a global configuration method and will affect all instances.
Default: console.log
Custom example: winston
const { setLogService } = require("clickhouse-orm");
const winston = require("winston");
const logger = winston.createLogger();
setLogService(logger.info);
chOrm.client
.query(`select * from orm_test.table1 limit 3`)
.toPromise()
.then((res) => {
console.log("Use sql:", res);
});
The chOrm.client
is the TimonKK/clickhouse instance.
import * as dayjs from "dayjs";
queryExample1({
Model: Table1Model,
status: 1,
beginTime: dayjs().subtract(1, "day").format("YYYY-MM-DD HH:mm:ss"),
endTime: dayjs().format("YYYY-MM-DD HH:mm:ss"),
}).then((res) => {
console.log("queryExample1:", res);
});
const queryExample1 = ({ Model, status, beginTime, endTime }) => {
let wheres = [],
where;
if (status) wheres.push(`status='${status}'`);
if (beginTime) wheres.push(`time>='${beginTime}'`);
if (endTime) wheres.push(`time<='${endTime}'`);
if (wheres.length > 0) where = wheres.join(" and ");
return Model.find({
where,
select: `*`,
orderBy: "time ASC",
limit: 5,
});
};
Final executed SQL:
SELECT * from orm_test.table1 where status='1' and time>='2022-02-04 15:34:22' and time<='2022-02-05 15:34:22' ORDER BY time ASC LIMIT 5
countExample1({
Model: Table1Model,
}).then((res) => {
console.log("countExample1:", res);
});
const countExample1 = ({ Model }) => {
return Model.find({
select: `count(*) AS total`,
});
};
Final executed SQL:
SELECT count(*) AS total from orm_test.table1
Table1Model.find({
select: `status,browser`,
groupBy: "status,browser",
});
Final executed SQL:
SELECT status,browser from orm_test.table1 GROUP BY status,browser
Table1Model.find([
{
select: `browser`,
groupBy: "browser",
},
{
select: `count() as browserTotal`,
},
]);
Final executed SQL:
SELECT count() as browserTotal from (SELECT browser from orm_test.table1 GROUP BY browser )
// new data model
const data = Table1Model.build();
// set value
data.time = new _Date_();
data.status = 1;
data.browser = "chrome";
data.browser_v = "90.0.1.21";
// do save
data.save().then((res) => {
console.log("save:", res);
});
Final executed SQL:
INSERT INTO orm_test.table1 (time,status,browser,browser_v) [{"time":"2022-02-05T07:51:16.919Z","status":1,"browser":"chrome","browser_v":"90.0.1.21"}]\
//do create
await Table1Model.create({
status: 1,
time: new Date(),
browser: "chrome",
browser_v: "90.0.1.21",
});
Final executed SQL:
INSERT INTO orm_test.table1 (time,status,browser,browser_v) [{"time":"2022-02-05T07:51:16.919Z","status":1,"browser":"chrome","browser_v":"90.0.1.21"}]\
const list = [
{ status: 2, browser: "IE", browser_v: "10.0.1.21" },
{ status: 2, browser: "FF", browser_v: "2.0.3" },
{ status: 3, browser: "IE", browser_v: "1.1.1" },
];
Table1Model.insertMany(list);
// or
Table1Model.insertMany(
list.map((item) => {
const data = Table1Model.build();
// set value
data.time = new Date();
data.status = item.status;
data.browser = item.browser;
data.browser_v = item.browser_v;
return data;
})
);
Final executed SQL:
INSERT INTO orm_test.table1 (time,status,browser,browser_v) [{"time":"2022-02-05T07:34:22.226Z","status":2,"browser":"IE","browser_v":"10.0.1.21"},{"time":"2022-02-05T07:34:22.226Z","status":2,"browser":"FF","browser_v":"2.0.3"},{"time":"2022-02-05T07:34:22.226Z","status":3,"browser":"IE","browser_v":"1.1.1"}]
Create a cluster instance:
const { ClickhouseOrm, DATA_TYPE, setLogService } = require("clickhouse-orm");
const chOrm = ClickhouseOrm({
db: {
name: "orm_cluster_test",
cluster: "default_cluster",
},
// ...other
});
const table2Schema = {
// table name
tableName: "table2",
// create table sql
createTable: (dbTableName, db) => {
// dbTableName = db + '.' + tableName = (orm_test.table2)
return `
CREATE TABLE IF NOT EXISTS ${dbTableName} ON CLUSTER ${db.cluster}
...other`;
},
...other,
};
// create database 'orm_cluster_test'
// SQL: CREATE DATABASE IF NOT EXISTS orm_cluster_test ON CLUSTER default_cluster
await chOrm.createDatabase();
// register schema and create [if] table
// createTable() SQL: CREATE TABLE IF NOT EXISTS orm_cluster_test.table2 ON CLUSTER default_cluster...
const Table2Model = await chOrm.model(table2Schema);