clickhouse-orm v3.0.0-alpha3

clickhouse-orm

Clickhouse ORM for Nodejs. Send query over HTTP interface. Using TimonKK/clickhouse.

Install:

npm i clickhouse-orm

Usage

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 Model：

• ModelConfig

import { DATA_TYPE, ModelSqlCreateTableConfig } from 'clickhouse-orm';
const xxxSchema: ModelSqlCreateTableConfig = {
  // table name
  tableName: "xxx",
  // define column name
  schema: {
    time: { type: DATA_TYPE.DateTime, default: Date },
    status: { type: DATA_TYPE.Int32 },
    browser: { type: DATA_TYPE.String },
    browser_v: {},
  },
};

• ModelSyncTableConfig

Auto create table and sync table

import { DATA_TYPE, ModelSyncTableConfig } from 'clickhouse-orm';
const oldSchema: ModelSyncTableConfig = {
  tableName: "xxx",
  schema: {
    time: { type: DATA_TYPE.DateTime, default: Date },
    will_typeChanged: { type: DATA_TYPE.Int16 },
    will_deleted: { type: DATA_TYPE.String },
  },
  options: `ENGINE = MergeTree
  PARTITION BY toYYYYMM(time)
  ORDER BY time`,
  autoCreate: true,
  autoSync: true,
};

Sync table structure(just schema column) when Model create

const newSchema = {
  ...oldSchema,
  schema: {
    time: { type: DATA_TYPE.DateTime, default: Date },
    will_typeChanged: { type: DATA_TYPE.Int32 },
    add_column: { type: DATA_TYPE.String },
  }
}
chOrm.model(newSchema)

clickhouse-orm-log: sync table structure: ALTER TABLE orm_test.xxx DROP COLUMN will_deleted

clickhouse-orm-log: sync table structure: ALTER TABLE orm_test.xxx ADD COLUMN add_column String

clickhouse-orm-log: sync table structure: ALTER TABLE orm_test.xxx MODIFY COLUMN will_typeChanged Int32

More in SyncTable Example.

• ModelSqlCreateTableConfig

custom create table sql, auto create table

import { DATA_TYPE, ModelSqlCreateTableConfig } from 'clickhouse-orm';
const xxxSchema: ModelSqlCreateTableConfig = {
  // table name
  tableName: "xxx",
  // 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.

Overview

Note: '?' is a Optional

ClickhouseOrm

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.

ModelConfig

• ModelConfig

• ModelSyncTableConfig

• ModelSqlCreateTableConfig

DATA_TYPE

The clickhouse data types. Some types are defined here.

Some defined data types will be verified. It only checks the basic type of data and not the most standard value. They are number | string | boolean | date.

  UInt8;
  UInt16;
  UInt32;
  UInt64;
  UInt128;
  UInt256;
  Int8;
  Int16;
  Int32;
  Int64;
  Int128;
  Int256;
  Float32;
  Float64;
  Boolean;
  String;
  UUID;
  Date;
  Date32;
  DateTime;
  DateTime64;
  /**
   *
   * @param Number
   * @example DATA_TYPE.FixedString(3)
   */
  FixedString;
  /**
   *
   * @param DATA_TYPE
   * @example DATA_TYPE.LowCardinality(DATA_TYPE.String)
   */
  LowCardinality;
  /**
   *
   * @param string
   * @example DATA_TYPE.Enum8(`'hello' = 1, 'world' = 2`)
   * @desc number [-128, 127]
   */
  Enum8:;
  /**
   *
   * @param string
   * @example DATA_TYPE.Enum16(`'hello' = 3000, 'world' = 3500`)
   * @desc number [-32768, 32767]
   */
  Enum16;
  /**
   *
   * @param columnType
   * Clickhouse dataTypes: Array(T), JSON, Map(key, value), IPv4, Nullable(), more...
   * @example DATA_TYPE.Other('Array(String)') , DATA_TYPE.Other('Int8')
   * @desc No `INSERT` data validation provided
   */
  Other;

More in Datatype Example.

Log

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);

Use SQL directly：

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.

More Examples

Find

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

Count

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

GroupBy

Table1Model.find({
  select: `status,browser`,
  groupBy: "status,browser",
});

Final executed SQL:

SELECT status,browser from orm_test.table1  GROUP BY status,browser

Nested Queries

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  )

save

// 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"}]\

create

//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"}]\

InsertMany

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"}]

delete

Table1Model.delete({
  where: `browser='Chrome'`,
})

Final executed SQL:

ALTER TABLE orm_test.table1  DELETE  WHERE browser='Chrome'

cluster

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",
  ...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);

Wechat Discussion

Click to join