clickhouse-schema
v2.0.1
Published
Clickhouse-Schema is a TypeScript library that simplifies working with ClickHouse databases by allowing developers to effortlessly define table schemas in TypeScript. This approach not only ensures robust type safety but also significantly reduces develop
Downloads
78
Maintainers
Readme
ClickHouse-Schema Guide
In ClickHouse, defining and managing table schemas and their associated types can be done either manually or through the ClickHouse-Schema library. This guide compares these two approaches to illustrate the simplicity and efficiency ClickHouse-Schema brings to your projects.
Traditional Manual Query Approach
Traditionally, creating a table in clickhouse requires manually writing the SQL query and the interface in your code. This method is straightforward but prone to errors and inconsistencies, especially when schema changes occur.
Create Table Query
CREATE TABLE IF NOT EXISTS students
(
id UInt32,
name String,
height float32,
age UInt8,
weight Float64,,
isStudent Boolean
)
# Manually defined
interface StudentsTableTypeManuallyDefined {
id: number,
name: string,
age: number,
height: number,
weight: number,
isStudent: boolean
}
Did you notice any errors with the code below? These would not get caught till runtime
Using ClickHouse-Schema
ClickHouse-Schema automates schema creation and ensures type safety with minimal code, providing a more robust and maintainable solution.
Defining Schema
const studentsTableSchema = new ClickhouseSchema({
id: { type: CHUInt32() },
name: { type: CHString() },
age: { type: CHUInt8() },
height: { type: CHFloat32() },
weight: { type: CHFloat64() },
isStudent: { type: CHBoolean() }
}, {
table_name: 'students',
primary_key: 'id'
})
//Automatic type inference. If schema changes type automatically changes too
type StudentsTableType = InferClickhouseSchemaType<typeof studentsTableSchema>
Getting Started
To start using ClickHouse-Schema in your projects, follow these steps:
Installation To install ClickHouse-Schema, run the following command in your terminal:
npm install clickhouse-schema
Create a Schema
Define your table schema and provide options such as the table name and primary key. This will enable automatic type inference, making your code more robust and maintainable.
import { ClickhouseSchema, CHUInt32, CHString, CHUInt8, CHFloat32, CHFloat64, CHBoolean, InferClickhouseSchemaType } from 'clickhouse-schema' // Use types directly or import ClickhouseTypes object to get all the types in one place const studentsTableSchema = new ClickhouseSchema({ id: { type: CHUInt32() }, name: { type: CHString() }, age: { type: CHUInt8() }, height: { type: CHFloat32() }, weight: { type: CHFloat64() }, isStudent: { type: CHBoolean() } }, { table_name: 'students', primary_key: 'id' }) type MyTableType = InferClickhouseSchemaType<typeof studentsTableSchema>
Utilize Schema Methods ClickHouse-Schema provides several methods to streamline working with your database schema:
- Use
<your_schema>.GetCreateTableQuery()
or<your_schema>.toString()
to generate the SQLCREATE TABLE
query. - Use
<your_schema>.GetOptions()
to access the options passed when creating the table schema. - Use
<your_schema>.GetCreateTableQueryAsList()
to get theCREATE TABLE
query as a list of strings, which can be helpful for debugging or logging.
- Use
Supported Types
- Integer (signed and unsigned integers):
UInt8, UInt16, UInt32, UInt64, UInt128, UInt256, Int8, Int16, Int32, Int64, Int128, Int256
types - Floating-point numbers:
Float32
andFloat64
types - Decimal -
Decimal
type - Boolean:
Boolean
type - Strings:
String
andFixedString
types - Dates:
Date
,Date32
,DateTime
andDateTime64
types - JSON:
JSON
type - UUID:
UUID
type - Arrays:
Array
type - Nullable:
Nullable
type - LowCardinality:
LowCardinality
andEnum
types - IP Addresses -
IPv4
andIPv6
And support for more types is coming!