superdb
使用场景:
- 拒绝拼接SQL语句,长期维护且达到易读效果
- 减少ORM模型定义,表更新频繁
- 支持链式操作,让数据定义更灵活
- 多数据库支持
- 频繁读数据放入缓存
- 性能提升
Table of contents
Installation
yarn add https://github.com/yujintang/superdb.git
or
yarn add superdb
or
npm install --save superdb
QuickStart
const Superdb = ;const db = 'mysql://root:password@localhost/example' logging: true ; const main = async { const conn = await db; const result = await conn; console;};; // SELECT id, name FROM tb_example WHERE id = 1 AND name IS null LIMIT 5
Connection
const db = config options;const conn = await db;
config
// 1config = connectionLimit : 10 host : 'localhost' port : '3306' user : 'root' password : 'password' database : 'example' // 2config = 'mysql://user:password@host:post/database'
options
options = dialect : 'mysql' // which db? default: "mysql", pool : true // connection pool ? default true logging : false // print sql ? default false logger : console // log, default console redis : undefined // can use {host: "", port: "", password: "", db: ""} or "redis://:password@host:port/db", cache : false // use cache ? default false beforeHooks: afterHooks:
Conn methods
query
await conn const result = await conn// select * from tb_example
find
await conn; const result = await conn;// SELECT * FROM tb_example WHERE id = 333 AND name = 'superdb'
findOne
await conn; const result = await conn;// SELECT * FROM tb_example WHERE id = 333 AND name = 'superdb' LIMIT 1
findAndCountAll
await conn; const result = await conn;// SELECT * FROM tb_example WHERE id = 333 AND name = 'superdb' // SELECT COUNT(*) AS COUNT FROM tb_example WHERE id = 333 AND name = 'superdb'
count
return [{COUNT: Number},...]
await conn; const result = await conn;// SELECT COUNT(*) AS COUNT FROM tb_example WHERE id = 333 AND name = 'superdb'
exists
return Boolean
await conn; const result = await conn;// SELECT COUNT(*) AS COUNT FROM tb_example WHERE id = 333 AND name = 'superdb'
createOne
createParams must {},
await conn; const result = await conn;// INSERT INTO tb_example (id,name) values (100, 'qt'), (101, 'ds')
bulkCreate
createParams must [],
await conn; const result = await conn;// INSERT INTO tb_example (id,name) values (100, 'qt'), (101, 'ds')
update
await conn; const result = await conn;// UPDATE tb_example SET name = 'qtds' WHERE id = 100
bulkUpdate
await conn; const result = await conn;// UPDATE tb_example SET name = 'qtds' WHERE id = 100
updateOne
await conn; const result = await conn;// UPDATE tb_example SET name = 'qtds' WHERE id = 100 LIMIT 1
delete
await conn const result = await conn;// DELETE FROM tb_example WHERE id = 100
deleteOne
await conn const result = await conn;// DELETE FROM tb_example WHERE id = 100 LIMIT 1
options
options = table: undefined // eg: 'tb_example' select: // eg: ['id', 'name'] join: // eg: [{table: 'tb_user', on: 'tb_user.id = tb_example.id'}] where: {} // eg: {name: 'superdb'} group: // eg: ['name desc'] having: // eg: ['count > 4'] order: // eg: ['id desc', 'name asc'] limit: undefined // eg: 1 offset: undefined // eg: 1 logging: false // eg: true ttl: 0 // eg: if open cache, then this ttl have Higher priority than global ttl; if set <=0, then not cache this find
Chain methods
table(params.table)
conn const result = await conn ;// SELECT * FROM tb_example
select(params.select)
conn conn const result = await conn ;// SELECT id, name FROM tb_example AS exp
updateBody(params.updateBody)
conn const result = await conn ;// UPDATE tb_example SET name = 'superdb' WHERE name = 'oldName' LIMIT 1
insertBody(params.insertBody)
参数为数组,则代表插入多条
connconn const result = await conn ;// INSERT INTO tb_example (id,name) values (100, 'alldb100'), (101, 'alldb101')
where(params.where)
more detail where, please enter op
conn const result = await conn ;// SELECT * FROM tb_example WHERE id = 5
join(params.join)
const result = await conn ; // SELECT * FROM tb_example LEFT JOIN tb_user as User ON User.id = tb_example.id const result = await conn ; // SELECT * FROM tb_example LEFT JOIN tb_user as User ON User.id = tb_example.id const result = await conn ; // SELECT * FROM tb_example LEFT JOIN tb_user as User ON User.id = tb_example.id
limit(params.limit)
conn // limit 10conn // limit 10 offset 1 const result = await conn ;// SELECT * FROM tb_example LIMIT 10 OFFSET 1
offset(params.offset)
conn // offset 1 const result = await conn ;// SELECT * FROM tb_example LIMIT 1 OFFSET 1
order(params.order)
connconn // ORDER BY id desc const result = await conn ;// SELECT * FROM tb_example ORDER BY id desc, name asc
group(params.group)
connconn // GROUP BY name desc const result = await conn ;// SELECT name FROM tb_example GROUP BY name desc
having(params.having)
connconn // HAVING count > 4 const result = await conn ;// SELECT count(*) as count, name FROM tb_example GROUP BY name desc HAVING count > 4
logging(params.logging);
conn // print superdb sql conn // not print superdb sql
ttl(params.ttl)
conn // redis cache ex = 60 * 5
Op
Op = conn.op; 用来提供一系列where查询的方法集
Op.or
const result = await conn;// SELECT * FROM tb_example WHERE (id = 6 OR name = 'superdb')
OP.and
Op.literal
literal is unrelated with where.key ,just depends on where.value
const result = await conn;// SELECT * FROM tb_example WHERE id IS NULL
Op.eq
const result = await conn;// SELECT * FROM tb_example WHERE name = 'superdb'
Op.ne
const result = await conn;// SELECT * FROM tb_example WHERE name != 'superdb'
Op.gte
const result = await conn;// SELECT * FROM tb_example WHERE name >= 'd'
Op.gt
const result = await conn;// SELECT * FROM tb_example WHERE name > 'd'
Op.lte
const result = await conn;// SELECT * FROM tb_example WHERE name <= 'd'
Op.lt
const result = await conn;// SELECT * FROM tb_example WHERE name < 'd'
Op.is
const result = await conn;// SELECT * FROM tb_example WHERE name IS null
Op.not
const result = await conn;// SELECT * FROM tb_example WHERE name IS NOT null
Op.in
const result = await conn;// SELECT * FROM tb_example WHERE name IN ('qtds', 'superdb')
Op.notIn
const result = await conn;// SELECT * FROM tb_example WHERE name NOT IN ('qtds', 'superdb')
Op.like
const result = await conn;// SELECT * FROM tb_example WHERE name LIKE '%d'
Op.notLike
const result = await conn;// SELECT * FROM tb_example WHERE name NOT LIKE '%d'
Op.between
const result = await conn;// SELECT * FROM tb_example WHERE name BETWEEN 'c' AND 'f'
Op.notBetween
const result = await conn;// SELECT * FROM tb_example WHERE name NOT BETWEEN 'c' AND 'f'
beforeHooks
config beforeHooks
select: (params:String)=>{return params}
where: (params:Object)=>{return params}
updateBody: (params:Object)=>{return params}
beforeHooks: { const result = Object; return result; }
insertBody: (params:Array)=>{return params}
beforeHooks: { const result = params; return result; }
limit: (params:Integer)=>{return params}
find 不指定limit, 一次最多查询10*1000条数据,内置该hook
beforeHooks: { if limit === undefined) return 10 * 1000; return limit; }
ttl: (params:Integer)=>{return params}
cache ttl = 60 * 60, 内置该hook
beforeHooks: { if ttl === undefined) return 60 * 60; return ttl; }
afterHooks
find: (result: Array)=>{return result}
删除find结果中的created 与 updated字段
afterHooks: { const result = list; return result; }