README

kmore

A SQL query builder based on Knex with powerful TypeScript type support.

Features

Type-safe property of tables accessor
Type-safe join table easily
Type-safe auto-completion in IDE

Installation

npm install kmore kmore-cli knex

# Then add one of the following:
npm install pg
npm install mssql
npm install oracle
npm install sqlite3

Basic usage

Build configuration:

Ensure sourceMap or inlineSourceMap is true in the tsconfig.json

{
  "compilerOptions": {
    "sourceMap": true
  },
}

Edit the package.json

{
  "script": {
    "db:gen": "kmore gen --path src/ test/"
  },
}

Create connection

import { KnexConfig, DbModel } from 'kmore'

// connection config
export const config: KnexConfig = {
  client: 'pg',
  connection: {
    host: 'localhost',
    user: 'postgres',
    password: 'foo',
    database: 'db_ci_test',
  },
}

// Define database model
export interface Db extends DbModel {
  tb_user: User
  tb_user_detail: UserDetail
}

export interface User {
  uid: number
  name: string
  ctime: string
}
export interface UserDetail {
  uid: number
  age: number
  address: string
}  

export const km = kmore<Db>({ config })
// or
const dict = genDbDictFromType<Db>()
export const km = kmore<Db>({ config }, dict)

Create tables with instance of knex

await km.dbh.schema
  .createTable('tb_user', (tb) => {
    tb.increments('uid')
    tb.string('name', 30)
    tb.timestamp('ctime', { useTz: false })
  })
  .createTable('tb_user_detail', (tb) => {
    tb.integer('uid')
    tb.foreign('uid')
      .references('tb_user.uid')
      .onDelete('CASCADE')
      .onUpdate('CASCADE')
    tb.integer('age')
    tb.string('address', 255)
  })
  .catch((err: Error) => {
    assert(false, err.message)
  })

Inert rows via auto generated table accessor

// auto generated accessort tb_user() and tb_user_detail() on km.rb
const { tb_user, tb_user_detail } = km.rb

await tb_user()
  .insert([
    { name: 'user1', ctime: new Date() }, // ms
    { name: 'user2', ctime: 'now()' }, // μs
  ])
  .then()

await tb_user_detail()
  .insert([
    { uid: 1, age: 10, address: 'address1' },
    { uid: 2, age: 10, address: 'address1' },
  ])
  .returning('*')
  .then()

Join tables

const { tables: t, scopedColumns: sc, rb } = km

await rb.tb_user<UserDetail>()
  .select()
  .innerJoin(
    t.tb_user_detail,
    sc.tb_user.uid,
    sc.tb_user_detail.uid,
  )
  .where(sc.tb_user.uid, 1)
  .then((rows) => {
    const [row] = rows
    assert(row && row.uid)
    assert(row && row.name)
    assert(row && row.age)
    return rows
  })

Use instance of knex

// drop table
await km.dbh.raw(`DROP TABLE IF EXISTS "${tb}" CASCADE;`).then()

// disconnect
await km.dbh.destroy()

Advanced usage

Build DictType

npm run db:gen

Create connection

import { KnexConfig, DbModel } from 'kmore'
// this file contains type of the dbDict, created after `npm run db:gen`
import { DbDict } from './.kmore'

// pass `DbDict` as 2nd generics parameter
export const km = kmore<Db, DbDict>({ config })

Join tables

type Db = typeof km.DbModel
type DblAlias = typeof km.DbModelAlias

type User = Db['tb_user']
type UserAlias = DbAlias['tb_user']
type UserDetailAlias = DbAlias['tb_user_detail']

const {
  rb,
  tables: t,
  aliasColumns: ac,
  scopedColumns: sc,
} = km

const cols = [
  ac.tb_user.uid,
  ac.tb_user_detail.uid,
]

const ret = await rb.tb_user()
  .select('name')
  .innerJoin<UserDetailAlias & UserAlias>(
    t.tb_user_detail,
    sc.tb_user.uid,
    sc.tb_user_detail.uid,
  )
  .columns(cols)
  .then(rows => rows[0])

assert(Object.keys(ret).length === 3)
assert(typeof ret.name === 'string')
assert(typeof ret.tbUserUid === 'number')
assert(typeof ret.tbUserDetailUid === 'number')

// typeof the result equals to the following type:
interface RetType {
  name: User['name']
  tbUserUid: UserAlias['tbUserUid']
  tbUserDetailUid: UserDetailAlias['tbUserDetailUid']
}

More examples of join see joint-table