kqb

Type-safe query builder for Kintone

Why?

• Type-safe for your app schema
• Less typing with IDE completion
• Human readable and Prettier friendly

Install

$ npm install kqb

Requires TypeScript >= 4.1 (for Template Literal Types)

Usage

import { createBuilder } from "kqb";

const fields = {
  name: "SINGLE_LINE_TEXT",
  age: "NUMBER",
  created: "CREATED_TIME",
} as const;

const { builder, field } = createBuilder(fields);
const query = builder
  .where(field("name").eq("foo"))
  .and(field("age").gt(20))
  .and(field("created").eq().THIS_MONTH())
  .orderBy("age", "desc")
  .limit(100)
  .offset(200)
  .build();
console.log(query);
// name = "foo" and age > "20" and created = THIS_MONTH() order by age desc limit 100 offset 200

API

createBuilder(fields?): { builder, field }

Returns a builder function and a field function.

The fields param is field definition JSON of your target kintone app. The key is a field code and the value is a field type like:

const fields = {
  name: "SINGLE_LINE_TEXT",
  age: "NUMBER",
} as const;
// In TypeScript, you MUST use `as const`

According to this, kqb performs static type checking and runtime validation.

• Correct field codes
• Correct type of value to compare for the field type
• Condition operators available for the field type
• Only sortable fields can be used in order by

If fields are omitted, the checking is mostly disabled. See tips below.

All field types are supported:

• CALC
• CHECK_BOX
• CREATED_TIME
• CREATOR
• DATE
• DATETIME
• DROP_DOWN
• FILE
• GROUP_SELECT
• LINK
• MODIFIER
• MULTI_LINE_TEXT
• MULTI_SELECT
• NUMBER
• ORGANIZATION_SELECT
• RADIO_BUTTON
• RECORD_NUMBER
• RICH_TEXT
• SINGLE_LINE_TEXT
• STATUS
• STATUS_ASSIGNEE
• TIME
• UPDATED_TIME
• USER_SELECT
• SUBTABLE: see below
• REFERENCE_TABLE: see below

Subtables and Reference tables

Specify subtables and reference tables as follows:

const defs = {
  id: "NUMBER"
  name: "SINGLE_LINE_TEXT",
  history: {
    $type: "SUBTABLE",
    $fields: {
      date: "DATE",
      title: "SINGLE_LINE_TEXT",
    },
  },
  items: {
    $type: "REFERENCE_TABLE",
    $fields: {
      price: "NUMBER",
      count: "NUMBER",
    },
  },
} as const;

Note the following spec of Kintone.

• Fields in subtables and reference tables are not sortable.
• Use in / not in instead of = / != for fields in subtables and reference tables.
• Use "tableCode.fieldCode" as field code for fields in reference tables like field("items.price") in the example above.

builder

builder has the following methods.

• .where(...condition[]): builder
  • Same as .and()
• .and(...condition[]): builder
• .or(...condition[]): builder
• .orderBy(fieldCode, direction): builder
• .orderBy(...[fieldCode, direction][]): builder
  • fieldCode: a field code of sortable field types
  • direction: "asc" or "desc"
• .offset(num): builder
• .limit(num): builder
• .build(): string

condition is returned by operator methods. You can get it from field(fieldCode) with method chain like:

builder.where(field("name").eq("Bob"), field("age").gt(20)).build();
// name = "Bob" and age > "20"

field(fieldCode): operator

Returns a operator for the field with the fieldCode. The operator has only those of the following methods that are available for the field type.

• .eq(value): "= value"
• .notEq(value): "!= value"
• .gt(value): "> value"
• .gtOrEq(value): ">= value"
• .lt(value): "< value"
• .ltOrEq(value): "<= value"
• .like(value): "like value"
• .notLike(value): "not like value"
• .in(...value[]): "in (value1, value2, ...)"
• .notIn(...value[]): "not in (value1, value2, ...)"

and(...condition[]), or(...condition[])

If you want to use nested conditions, use and() or or().

import { createBuilder, and, or } from "kqb";

const fields = {
  foo: "NUMBER",
  bar: "NUMBER",
} as const;

const { builder, field } = createBuilder(fields);
const query = builder
  .where(or(field("foo").eq(1), field("bar").eq(2)))
  .and(or(field("foo").eq(3), field("bar").eq(4)))
  .or(and(field("foo").eq(5), field("bar").eq(6)))
  .build();
console.log(query);
// (foo = "1" or bar = "2") and (foo = "3" or bar = "4") or (foo = "5" and bar = "6")

Query Functions

Kintone provides query functions like TODAY() and LOGINUSER()

created_time = TODAY() and creator in (LOGINUSER())

You can use query functions with type-safety fluent method chaining.

import { createBuilder } from "kqb";

const fields = {
  created_time: "CREATED_TIME",
  creator: "CREATOR",
} as const;

const { builder, field } = createBuilder(fields);
const query = builder
  .where(field("created_time").eq().TODAY())
  .and(field("creator").in().LOGINUSER())
  .build();
console.log(query);
// created_time = TODAY() and creator in (LOGINUSER())

Also you can import each query function and specify it in a query operator.

import { createBuilder, LOGINUSER, TODAY } from "kqb";

const fields = {
  created_time: "CREATED_TIME",
  creator: "CREATOR",
} as const;

const { builder, field } = createBuilder(fields);
const query = builder
  .where(field("created_time").eq(TODAY()))
  .and(field("creator").in(LOGINUSER()))
  .build();
console.log(query);
// created_time = TODAY() and creator in (LOGINUSER())

All query functions are supported:

• LOGINUSER
• PRIMARY_ORGANIZATION
• NOW
• TODAY
• YESTERDAY
• TOMORROW
• FROM_TODAY
• THIS_WEEK
• LAST_WEEK
• NEXT_WEEK
• THIS_MONTH
• LAST_MONTH
• NEXT_MONTH
• THIS_YEAR
• LAST_YEAR
• NEXT_YEAR

Tips

Non type-safe query building

If you just want to build a query and do not want type-safety, you can omit the field definition argument of creatBuilder(fields?). You can get the query string easily, but note that type checking will not raise an error if the query is logically wrong. The operators has all methods and orderBy receives all fields, but potentially, it may not work.

const { builder, field } = createBuilder(); // omit the first argument
const query = builder
  .where(field("non_existent_field").gt(20))
  .and(
    field("number_field").like("can_not_actually_use_like_operator")
  )
  .orderBy("non_sortable_field", "asc")
  .build();

Multiple fields in order by

There are two ways to do this.

const query = builder
  .orderBy("foo", "asc")
  .orderBy("bar", "desc")
  .build();
console.log(query);
// order by foo asc, bar desc

const query = builder
  .orderBy(["foo", "asc"], ["bar", "desc"])
  .build();
console.log(query);
// order by foo asc, bar desc

License

MIT License: Teppei Sato <[email protected]>