clickhouse-sql-client

Examples

import { ClickhouseQueryBuilder } from 'clickhouse-sql-client'

const dialect = new ClickhouseQueryBuilder()

const myRawQuery = dialect
	.select(b => b.raw('count(article)'))
	.from((b) => b.table.name('article_stats'))
	.preWhere(b => b
		.raw('sold = 0')
		.and('date', '=', "toDateTime('1970-02-30')")
	).sql()

comeClickhouseClient.query(myRawQuery).toPromise().then(r => process(r))

// SELECT count(article) FROM article_stats PREWHERE sold = 0 AND date = toDateTime('2021-01-01')

select(builder: SelectBuilder): QueryBuilder

  dialect.
    .select(builder => builder
      .raw('name as userName')
      .raw('count(id) as rowsCount')
    )

from(builder: {table: TableBuilder, query: QueryBuilder}): QueryBuilder

  // query
  dialect.from(b => b.table.name('table_name'))

  // subquery
  dialect.from(b => b.query
    .from(subq => subq.table.name('table_name'))
    .select(subq => subq.raw('user_id'))
    .preWhere(subq => smth)
    .where(subq => smth)
    .sql()
  )

where/preWhere(builder: WhereBuilder/PreWhereBuilder): QueryBuilder

  dialect.where(b => b
    .and('user', '=', 'me') // AND user = me
    .in('user', ['me', 'you', 'others']) // user IN ('me', 'you', 'others')
    .andIn('user', ['me', 'you', 'others']) // AND user IN ('me', 'you', 'others')
    .orIn('user', ['me', 'you', 'others']) // OR user IN ('me', 'you', 'others')
    .or('value', '<=', 5) // OR value = '5'
    .raw('for difficult conditions')
  )

  dialect.preWhere() // same as where

other(builder: WhereBuilder/PreWhereBuilder): QueryBuilder

  dialect
    .groupBy('raw')
    .orderBy('raw')
    .limit(5)
    .limit('1,2 BY id')
    .offset('raw')
    .offset(100000)