kmore

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:

Edit the package.json

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

Create connection

import { KnexConfig, kmoreFactory, genDbDict } 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 {
  tb_user: UserDo
  tb_user_ext: UserExtDo
}

export interface UserDo {
  uid: number
  name: string
  ctime: Date
}
export interface UserExtDo {
  uid: number
  age: number
  address: string
}  

const dict = genDbDict<Db>()
export const km = kmoreFactory({ 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_ext', (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 { ref_tb_user, ref_tb_user_detail } = km.refTables

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

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

Join tables

const { refTables } = km
const { tables, scoped } = km.dict

const ret = await refTables.ref_tb_user()
  .innerJoin<UserExtDo>(
  tables.tb_user_ext,
  scoped.tb_user.uid,
  scoped.tb_user_ext.uid,
)
  .select('*')
  .where(scoped.tb_user.uid, 1)

const cols = [
  alias.tb_user.uid, // { tbUser: 'tb_user.uid' }
  alias.tb_user_ext.uid, // { tbUserExt: 'tb_user_ext.uid' }
]

// --------------

type CT = DbDictType<Db>
type CT_USER = CT['tb_user']
type CT_USER_EXT = CT['tb_user_ext']

const ret = await refTables.ref_tb_user()
  .innerJoin<CT_USER & CT_USER_EXT>(
  tables.tb_user_ext,
  scoped.tb_user.uid,
  scoped.tb_user_ext.uid,
)
  .columns(cols)
  .then(rows => rows[0])


const cols = {
  uid: scoped.tb_user.uid,
  foo: scoped.tb_user_ext.uid,
}

const ret = await refTables.ref_tb_user()
  .innerJoin<CT_USER & CT_USER_EXT>(
  tables.tb_user_ext,
  scoped.tb_user.uid,
  scoped.tb_user_ext.uid,
)
  .columns(cols)
  .then(rows => rows[0])

More examples of join see joint-table

Use instance of knex

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

// disconnect
await km.dbh.destroy()

Demo

• see test

Packages

kmore is comprised of many specialized packages. This repository contains all these packages. Below you will find a summary of each package.

Package	Version
kmore
kmore-types
kmore-cli

License

MIT

Languages

• English
• 中文

Changelog

28.2.1 (2022-07-13)

Bug Fixes

• boilerplate: define enum without const 37ffd10
• boilerplate: devDeps version 2e29b42