kmore

kmore

A Knex little more factory of SQL query builder, with auto-generated type-safe tables accessor via TypeScript compiler API for Node.js.

Installation

npm install kmore knex

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

Usage

Create connection

import { Config } from 'kmore'

// connection config
export const config: Config = {
  client: 'pg',
  // connection: process.env.PG_CONNECTION_STRING,
  connection: {
    host: 'localhost',
    user: 'postgres',
    password: 'foo',
    database: 'db_ci_test',
  },
}

// Define Types of tables
export interface TbListModel {
  tb_user: User
  tb_user_detail: UserDetail
}

export interface User {
  uid: number
  name: string
  ctime: Date | 'now()'
}
export interface UserDetail {
  uid: number
  age: number
  address: string
}  

/**
 *  Initialize db connection and generate type-safe tables accessor (name and builder)
 *  will get
 *    - db.dbh : the connection instance of knex
 *      eg. db.dbh<OtherType>('tb_other').select()
 *    - db.tables : tables name accessor containing table key/value paris
 *    - db.rb : tables builder accessor,   
 *      eg. db.rb.user() =>  Knex.QueryBuilder<{id: number, name: string}>
 */
const db = kmore<TbListModel>(config)

Create tables with instance of knex

await db.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 db.rb
const { tb_user, tb_user_detail } = db.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, rb } = db

await rb.tb_user()
  .select(`${t.tb_user}.uid`, `${t.tb_user}.name`)
  .innerJoin(
    t.tb_user_detail,
    `${t.tb_user}.uid`,
    `${t.tb_user_detail}.uid`,
  )
  .where(`${t.tb_user}.uid`, 1)
  .then((rows) => {
    assert(rows && rows.length === 1 && rows[0].uid === 1)
    return rows
  })
  .catch((err: Error) => {
    assert(false, err.message)
  })

Use instance of knex

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

// disconnect
await db.dbh.destroy()

Demo

• see test

License

MIT

Languages

• English
• 中文

Changelog

10.4.0 (2021-06-29)

Features

• midway-component-kmore: add KmoreComponentConfig['timeoutWhenDestroy'] 1f95766