kmore v59.5.6
kmore
基于 Knex 的 SQL 查询生成器工厂, 提供高级 TypeScript 类型支持。
特性
- 类型安全的表对象操作
- 类型安全的表连接操作
- 类型安全的 IDE 编辑器自动完成
- 一条查询链实现自动分页
安装
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
基础应用
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 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()
智能连表 (类型提示和自动完成)
const uid = 1
// tb_user JOIN tb_user_ext ON tb_user_ext.uid = tb_user.uid
const ret = await km.camelTables.ref_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.ref_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.ref_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
使用 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 { ref_tb_user } = this.db.camelTables
const user = await ref_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-types | |
kmore-cli | |
@mwcp/kmore |
License
Languages
26 days ago
29 days ago
30 days ago
1 month ago
2 months ago
2 months ago
2 months ago
2 months ago
3 months ago
3 months ago
3 months ago
3 months ago
8 months ago
8 months ago
8 months ago
8 months ago
8 months ago
9 months ago
9 months ago
10 months ago
11 months ago
9 months ago
9 months ago
12 months ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
2 years ago
1 year ago
1 year ago
1 year ago
1 year ago
2 years ago
2 years ago
1 year ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
1 year ago
2 years ago
1 year ago
1 year ago
1 year ago
1 year ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
1 year ago
1 year ago
2 years ago
2 years ago
2 years ago
1 year ago
1 year ago
1 year ago
1 year ago
1 year ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
1 year ago
1 year ago
1 year ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
2 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
3 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
4 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago
5 years ago