Neighbour Problem Manager

    kmore
    TypeScript icon, indicating that this package has built-in type declarations

    33.3.0 • Public • Published

    kmore

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

    GitHub tag License ci codecov Conventional Commits lerna

    Features

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

    Installation

    npm install kmore kmore-cli
    
    # 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": {
        "build": "tsc -b && npm run db:gen",
        "db:gen": "kmore gen --path src/ test/",
        "db:gen-cjs": "kmore gen --path src/ test/ --format cjs"
      },
    }

    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

    Snake style

    // auto generated accessort tb_user() and tb_user_detail()
    const { ref_tb_user, ref_tb_user_detail } = km.refTables
    
    await ref_tb_user()
      .insert([
        { user_name: 'user1', ctime: new Date() }, // ms
        { user_name: 'user2', ctime: 'now()' }, // μs
      ])
      .then()
    
    const affectedRows = await ref_tb_user_detail()
      .insert([
        { uid: 1, age: 10, user_address: 'address1' },
        { uid: 2, age: 10, user_address: 'address1' },
      ])
      .returning('*')
      .then()

    Camel style

    import { RecordCamelKeys } from '@waiting/shared-types'
    
    // auto generated accessort tb_user() and tb_user_detail() 
    const { ref_tb_user, ref_tb_user_detail } = km.camelTables
    
    interface UserDO {
      user_name: string
      ctime: date | string
    }
    type UserDTO = RecordCamelKeys<UserDO>
    
    const users: UserDTO[] = await ref_tb_user()
      .insert([
        { userName: 'user1', ctime: new Date() }, // ms
        { userName: 'user2', ctime: 'now()' }, // μs
      ])
      .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 camelTables.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 camelTables.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()

    Midway.js component

    @Provide()
    export class UserRepo {
    
      @Inject() dbManager: DbManager<'master' | 'slave', Db>
    
      async getUser(uid: number): Promise<UserDTO[]> {
        const db = this.dbManager.getDataSource('master')
        assert(db)
    
        const { ref_tb_user } = db.camelTables
        const user = await ref_tb_user()
          .select('*')
          .where({ uid })
        return user
      }
    }

    Demo

    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-svg
    kmore-types types-svg
    kmore-cli cli-svg
    midway-component-kmore mw-svg

    License

    MIT

    Languages

    Install

    npm i kmore

    DownloadsWeekly Downloads

    1,004

    Version

    33.3.0

    License

    MIT

    Unpacked Size

    131 kB

    Total Files

    42

    Last publish

    Collaborators

    • waiting