Type-safe NodeJS query builder library for Snowflake with smart return type inference, written in Typescript.
Supports the following SQL operations in Snowflake:
SELECTstatements, including all SQL clauses and subqueries.INSERT INTOrows directly, or the result of aSELECTquery.CREATE TABLE
Instantiate a Db instance by passing a Snowflake NodeJS SDK connection and table definitions. Managing the lifecycle of the Snowflake connection (e.g. connecting & destroying) is not handled by snow-builder.
import {
TConfig,
sNumber,
sVarchar,
sBoolean,
DBConfig,
Db,
} from 'snow-builder';
const users = {
tRef: { db: 'foo', schema: 'bar', table: 'users' },
tSchema: {
user_id: sNumber(38, 0).notNull(),
email: sVarchar().notNull(),
is_verified: sBoolean().notNull(),
first_name: sVarchar(),
},
} satisfies TConfig;
const orders = {
tRef: { db: 'foo', schema: 'bar', table: 'orders' },
tSchema: {
order_id: sNumber(38, 0).notNull(),
user_id: sNumber(38, 0).notNull(),
order_date: sDate().notNull(),
total: sNumber(38, 2).notNull(),
},
} satisfies TConfig;
const dbConfig = {
users,
orders,
} satisfies DBConfig;
const db = new Db(conn, dbConfig);const result = await db
.selectFrom('users', 'u')
.innerJoin('users', 'u', 'o.user_id', 'u.user_id')
.select((f) => ['u.user_id', f.sum('o.total').as('user_total')])
.where('u.is_verified', '=', true)
.groupBy('u.user_id')
.orderBy('u.first_name')
.limit(10)
.findMany();From Records
Use the generic type TInsert together with the table's tSchema property to create the corresponding object type. Snowflake column types are mapped to object properties as per the Snowflake NodeJS SDK mapping. Nullable columns are represented by optional properties.
import { TInsert } from 'snow-builder';
type User = TInsert<typeof users.tSchema>;
const newUsers: User[] = [
{
user_id: 1,
email: '[email protected]',
is_verified: true,
// 'first_name' is optional since nullable
},
];
const result = await db.insertInto('users', newUsers);From Select:
The select query's return type must resolve to the same type as the table's corresponding object type (after calling TInsert). Nullable fields in the table may be omitted from the select query.
const query = db
.selectFrom('orders', 'o')
.select((f) => [
'o.user_id',
s<string>(`'[email protected]'`).as('email'),
s<boolean>('true').as('is_verified'),
])
.where('o.user_id', '=', 1)
.limit(1);
const result = await db.insertInto('users', query);
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent