Skip to content

Latest commit

 

History

History
434 lines (340 loc) · 9.95 KB

README.zh-CN.md

File metadata and controls

434 lines (340 loc) · 9.95 KB
Raw

kmore

基于 Knex 的 SQL 查询生成器工厂， 提供高级 TypeScript 类型支持， 声明式事务， 集成 OpenTelemetry 链路追踪。

GitHub tag License ci codecov

特性

安装全局依赖

npm i -g c8 lerna rollup tsx zx

安装

npm i kmore && npm i -D kmore-cli
// for Midway.js
npm i @mwcp/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

基础应用

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"
  },
}

创建数据库连接

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 })

建表

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)
  })

插入数据

Snake style

// auto generated accessor 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 accessor 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()

智能连表 （类型提示和自动完成）

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 }) // <-- 'uid' 可自动完成
  // .where('uid', uid)   <-- 'uid' 可自动完成
  // .where('tb_user_ext_uid', uid) <-- '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   // <-- 重复字段名将会自动转换 为 "<表名>_<字段名>" 格式

More examples of join see joint-table

自动分页

  • 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)

// 不可枚举分页属性
const { 
  total,    // 总记录数
  page,     // 当前页号，起始 1
  pageSize, // 每页记录数
} = 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)

// 可枚举分页属性
const { 
  total,    // 总记录数
  page,     // 当前页号，起始 1
  pageSize, // 每页记录数
  rows,     // 查询结果数据
} = users
const [ user ] = users.rows

More examples of auto paging see auto-paing

声明式事务

限制：

  • 不能应用在基类
  • 类方法必须是 AsyncFunction，意味着每个 Query 构造器结果都必须使用 "await"
  • 事务传播当前仅支持
    • PropagationType.REQUIRED
    • PropagationType.SUPPORTS

使用：

  • 类装饰器 Class decorator

    import { Init, Inject, Singleton } from '@midwayjs/core'
import { Transactional } from '@mwcp/kmore'

@Transactional()  // <-- 
@Singleton()
export class UserRepo {
  @Inject() dbManager: DbManager<'master', Db>

  tb_user: Kmore<Db>['camelTables']['tb_user']
  tb_user_ext: Kmore<Db>['camelTables']['tb_user_ext']

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

  async getUsers(): Promise<UserDTO[]> {
    const users = await this.tb_user()
    return users
  }

  // will throw error
  wrongUsage() {
    return this.tb_user()
  }
}

  • 方法装饰器 Method decorator

    @Singleton()
export class UserRepo {
  @Inject() dbManager: DbManager<'master', Db>

  tb_user: Kmore<Db>['camelTables']['tb_user']
  tb_user_ext: Kmore<Db>['camelTables']['tb_user_ext']

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

  @Transactional()  // <--
  async getUsers(): Promise<UserDTO[]> {
    const users = await this.tb_user()
    return users
  }
}

使用 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> = {
  config: {
    client: 'pg',
    connection: {
      host: 'localhost',
      port: 5432,
      database: 'db_test',
      user: 'postgres',
      password: 'password',
    },
  },
  dict: dbDict,
}
export const kmoreConfig: KmoreSourceConfig = {
  dataSource: {
    master,
    // slave,
  },
}

Usage

import { Init, Inject, Singleton } from '@midwayjs/core'

@Provide()
@Singleton()
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.

Package Version
kmore kmore-svg
kmore-types types-svg
kmore-cli cli-svg
@mwcp/kmore mw-svg

License

MIT

Languages