Socket
Socket
Sign inDemoInstall

kmore

Package Overview
Dependencies
1
Maintainers
0
Versions
255
Alerts
File Explorer

Advanced tools

Install Socket

Detect and block malicious and high-risk dependencies

Install

kmore

A SQL query builder based on knex with powerful TypeScript type support


Version published
Weekly downloads
1.1K
increased by133.26%
Maintainers
0
Created
Weekly downloads
 

Readme

Source

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
  • Auto Paging with one query

Installation

npm i kmore && npm i -D kmore-cli
// for Midway.js
npm i @mw-components/kmore && npm i -D kmore-cli

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

pg-native-installation

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 accessors tb_user() and tb_user_detail()
const { tb_user, tb_user_detail } = km.refTables

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

const affectedRows = await 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 accessors tb_user() and tb_user_detail() 
const { tb_user, tb_user_detail } = km.camelTables

interface UserDO {
  user_name: string
  ctime: date | string
}
type UserDTO = RecordCamelKeys<UserDO>

const users: UserDTO[] = await tb_user()
  .insert([
    { userName: 'user1', ctime: new Date() }, // ms
    { userName: 'user2', ctime: 'now()' }, // μs
  ])
  .returning('*')
  .then()

Smart Join tables with types hint and auto complete

const uid = 1

// tb_user JOIN tb_user_ext ON tb_user_ext.uid = tb_user.uid
const ret = await km.camelTables.tb_user()
  .smartJoin(
    'tb_user_ext.uid',
    'tb_user.uid',
  )
  .select('*')
  .where({ uid }) // <-- has auto-complete with 'uid'
  // .where('uid', uid)   <-- has auto-complete with 'uid'
  // .where('tb_user_ext_uid', uid) <-- has auto-complete with 'tb_user_ext_uid'
  // .where(km.dict.scoped.tb_user.uid, 1)
  .then(rows => rows[0])

assert(ret)
ret.uid
ret.tb_user_ext_uid   // <-- duplicate uid will be converted with table prefix like "<tb_name>_<column>"

More examples of join see joint-table

Auto Paging

  • RawType:

    const options: Partial<PagingOptions> = {
      page: 2,      // default 1
      pageSize: 20, // default 10
    }
    const users = await tables.tb_user().autoPaging(options)
    assert(Array.isArray(users))
    assert(users.length)
    
    // not enumerable properties of pager
    const { 
      total,    // total records
      page,     // current page number, start from 1
      pageSize, // size of items each page
    } = users
    const [ user ] = users
    
  • WrapType:

    const options: Partial<PagingOptions> = {
      page: 2,      // default 1
      pageSize: 20, // default 10
    }
    const users = await tables.tb_user().autoPaging(options, true)
    assert(! Array.isArray(users))
    assert(Array.isArray(users.rows))
    assert(users.rows.length)
    
    // enumerable properties of pager
    const { 
      total,    // total records
      page,     // current page number, start from 1
      pageSize, // size of items each page
      rows,     // response records
    } = users
    const [ user ] = users.rows
    

More examples of auto paging see auto-paing

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

Config

// file: src/config/config.{prod | local | unittest}.ts

import { genDbDict } from 'kmore-types'
import { KmoreSourceConfig } from '@mwcp/kmore'
import { TbAppDO, TbMemberDO } from '../do/database.do.js'

export interface Db {
  tb_app: TbAppDO
  tb_user: TbMemberDO
}

export const dbDict = genDbDict<Db>()

const master: DbConfig<Db, Context> = {
  config: {
    client: 'pg',
    connection: {
      host: 'localhost',
      port: 5432,
      database: 'db_test',
      user: 'postgres',
      password: 'password',
    },
  },
  dict: dbDict,
  sampleThrottleMs: 500,
  enableTracing: true, // jaeger tracer
}
export const kmoreConfig: KmoreSourceConfig = {
  dataSource: {
    master,
    // slave,
  },
}  

Usage

import { Init, Inject } from '@midwayjs/decorator'

@Provide()
export class UserRepo {

  @Inject() dbManager: DbManager<'master' | 'slave', Db>

  protected db: Kmore<Db>

  @Init()
  async init(): Promise<void> {
    this.db = this.dbManager.getDataSource('master')
  }

  async getUser(uid: number): Promise<UserDTO | undefined> {
    const { tb_user } = this.db.camelTables
    const user = await tb_user()
      .where({ uid })
      .then(rows => rows[0])
    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.

PackageVersion
kmorekmore-svg
kmore-typestypes-svg
kmore-clicli-svg
@mwcp/kmoremw-svg

License

MIT

Languages


Keywords

FAQs

Last updated on 16 Jul 2024

Did you know?

Socket

Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.

Install

Related posts

SocketSocket SOC 2 Logo

Product

  • Package Alerts
  • Integrations
  • Docs
  • Pricing
  • FAQ
  • Roadmap

Packages

Stay in touch

Get open source security insights delivered straight into your inbox.


  • Terms
  • Privacy
  • Security

Made with ⚡️ by Socket Inc