superdb
v1.1.11
Published
more db support, redis cache, chain Op, Reject splicing SQL
Downloads
8
Maintainers
Readme
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 = require('superdb');
const db = new Superdb('mysql://root:password@localhost/example', { logging: true });
const main = async () => {
const conn = await db.createConn();
const result = await conn.find('tb_example', {
select: ['id', 'name'],
where: {
id: 1,
name: conn.Op.is(null),
},
limit: 5,
});
console.log(result);
};
main();
// SELECT id, name FROM tb_example WHERE id = 1 AND name IS null LIMIT 5
Connection
const db = new Superdb(config, options);
const conn = await db.createConn();
config
// 1
config = {
connectionLimit : 10,
host : 'localhost',
port : '3306',
user : 'root',
password : 'password',
database : 'example'
}
// 2
config = '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.query(sql)
const result = await conn.query('select * from tb_example')
// select * from tb_example
find
await conn.find(tbName, options);
const result = await conn.find('tb_example', {
where: {
id: 333,
name: 'superdb',
},
});
// SELECT * FROM tb_example WHERE id = 333 AND name = 'superdb'
findOne
await conn.findOne(tbName, options);
const result = await conn.find('tb_example', {
where: {
id: 333,
name: 'superdb',
},
});
// SELECT * FROM tb_example WHERE id = 333 AND name = 'superdb' LIMIT 1
findAndCountAll
await conn.findAndCountAll(tbName, options);
const result = await conn.findAndCountAll('tb_example', {
where: {
id: 333,
name: 'superdb',
},
});
// 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.count(tbName, options);
const result = await conn.count('tb_example', {
where: {
id: 333,
name: 'superdb',
},
});
// SELECT COUNT(*) AS COUNT FROM tb_example WHERE id = 333 AND name = 'superdb'
exists
return Boolean
await conn.exists(tbName, options);
const result = await conn.count('tb_example', {
where: {
id: 333,
name: 'superdb',
}
});
// SELECT COUNT(*) AS COUNT FROM tb_example WHERE id = 333 AND name = 'superdb'
createOne
createParams must {},
await conn.createOne(tbName, createParams, options);
const result = await conn.create('tb_example', [{ id: 100, name: 'qt' }, { id: 101, name: 'ds' }]);
// INSERT INTO tb_example (id,name) values (100, 'qt'), (101, 'ds')
bulkCreate
createParams must [],
await conn.bulkCreate(tbName, createParams, options);
const result = await conn.create('tb_example', [{ id: 100, name: 'qt' }, { id: 101, name: 'ds' }]);
// INSERT INTO tb_example (id,name) values (100, 'qt'), (101, 'ds')
update
await conn.update(tbName, updateOptions, options);
const result = await conn.update('tb_example', { name: 'qtds' }, {
where: { id: 100 },
});
// UPDATE tb_example SET name = 'qtds' WHERE id = 100
bulkUpdate
await conn.bulkUpdate(tbName, updateOptions, options);
const result = await conn.bulkUpdate('tb_example', { name: 'qtds' }, {
where: { id: 100 },
});
// UPDATE tb_example SET name = 'qtds' WHERE id = 100
updateOne
await conn.updateOne(tbName, updateOptions, options);
const result = await conn.update('tb_example', { name: 'qtds' }, {
where: { id: 100 },
});
// UPDATE tb_example SET name = 'qtds' WHERE id = 100 LIMIT 1
delete
await conn.delete(tbName, options)
const result = await conn.delete('tb_example', {
where: { id: 100 },
});
// DELETE FROM tb_example WHERE id = 100
deleteOne
await conn.delete(tbName, options)
const result = await conn.delete('tb_example', {
where: { id: 100 },
});
// 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.table('tb_example')
const result = await conn
.find('tb_example');
// SELECT * FROM tb_example
select(params.select)
conn.select('id, name')
conn.select(['id', 'name'])
const result = await conn
.select(['id', 'name'])
.find(['tb_example','exp']);
// SELECT id, name FROM tb_example AS exp
updateBody(params.updateBody)
conn.updateBody({name:'superdb'})
const result = await conn
.updateBody({ name: 'superdb' })
.where({ name: 'oldName' })
.limit(1)
.update('tb_example');
// UPDATE tb_example SET name = 'superdb' WHERE name = 'oldName' LIMIT 1
insertBody(params.insertBody)
参数为数组,则代表插入多条
conn.insertBody({id: 100, name: 'alldb'})
conn.insertBody([{id: 100, name: 'alldb'}])
const result = await conn
.insertBody([{ id: 100, name: 'alldb100' }, { id: 101, name: 'alldb101' }])
.create('tb_example');
// INSERT INTO tb_example (id,name) values (100, 'alldb100'), (101, 'alldb101')
where(params.where)
more detail where, please enter op
conn.where({id: 5})
const result = await conn
.where({ id: 5 })
.find('tb_example');
// SELECT * FROM tb_example WHERE id = 5
join(params.join)
const result = await conn
.join([{
table: 'tb_user as User',
on: 'User.id = tb_example.id',
direction: 'left',
}])
.find('tb_example');
// SELECT * FROM tb_example LEFT JOIN tb_user as User ON User.id = tb_example.id
const result = await conn
.join([
'LEFT JOIN tb_user as User ON User.id = tb_example.id'
])
.find('tb_example');
// SELECT * FROM tb_example LEFT JOIN tb_user as User ON User.id = tb_example.id
const result = await conn
.join('LEFT JOIN tb_user as User ON User.id = tb_example.id')
.find('tb_example');
// SELECT * FROM tb_example LEFT JOIN tb_user as User ON User.id = tb_example.id
limit(params.limit)
conn.limit(10) // limit 10
conn.limit([10, 1]) // limit 10 offset 1
const result = await conn
.limit([10, 1])
.find('tb_example');
// SELECT * FROM tb_example LIMIT 10 OFFSET 1
offset(params.offset)
conn.offset(1) // offset 1
const result = await conn
.limit(1)
.offset(1)
.find('tb_example');
// SELECT * FROM tb_example LIMIT 1 OFFSET 1
order(params.order)
conn.order('id desc')
conn.order(['id desc']) // ORDER BY id desc
const result = await conn
.order(['id desc', 'name asc'])
.find('tb_example');
// SELECT * FROM tb_example ORDER BY id desc, name asc
group(params.group)
conn.group('name desc')
conn.group(['name desc']) // GROUP BY name desc
const result = await conn
.select('name')
.group(['name desc'])
.find('tb_example');
// SELECT name FROM tb_example GROUP BY name desc
having(params.having)
conn.having('count > 4')
conn.having(['count > 4']) // HAVING count > 4
const result = await conn
.select(['count(*) as count', 'name'])
.group(['name desc'])
.having(['count > 4'])
.find('tb_example');
// SELECT count(*) as count, name FROM tb_example GROUP BY name desc HAVING count > 4
logging(params.logging);
conn.logging(true) // print superdb sql
conn.logging(false) // not print superdb sql
ttl(params.ttl)
conn.ttl(60 * 5) // redis cache ex = 60 * 5
Op
Op = conn.op; 用来提供一系列where查询的方法集
Op.or
const result = await conn.find('tb_example', {
where: {
[conn.Op.or]: {
id: 6,
name: 'superdb',
},
},
});
// 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.find('tb_example', {
where: {
'random': conn.Op.literal('id IS NULL'),
},
});
// SELECT * FROM tb_example WHERE id IS NULL
Op.eq
const result = await conn.find('tb_example', {
where: {
name: conn.Op.eq('superdb'),
},
});
// SELECT * FROM tb_example WHERE name = 'superdb'
Op.ne
const result = await conn.find('tb_example', {
where: {
name: conn.Op.ne('superdb'),
},
});
// SELECT * FROM tb_example WHERE name != 'superdb'
Op.gte
const result = await conn.find('tb_example', {
where: {
name: conn.Op.gte('d'),
},
});
// SELECT * FROM tb_example WHERE name >= 'd'
Op.gt
const result = await conn.find('tb_example', {
where: {
name: conn.Op.gt('d')
},
});
// SELECT * FROM tb_example WHERE name > 'd'
Op.lte
const result = await conn.find('tb_example', {
where: {
name: conn.Op.lte('d'),
},
});
// SELECT * FROM tb_example WHERE name <= 'd'
Op.lt
const result = await conn.find('tb_example', {
where: {
name: conn.Op.lt('d'),
},
});
// SELECT * FROM tb_example WHERE name < 'd'
Op.is
const result = await conn.find('tb_example', {
where: {
name: conn.Op.is(null),
},
});
// SELECT * FROM tb_example WHERE name IS null
Op.not
const result = await conn.find('tb_example', {
where: {
name: conn.Op.not(null)
},
});
// SELECT * FROM tb_example WHERE name IS NOT null
Op.in
const result = await conn.find('tb_example', {
where: {
name: conn.Op.in(['qtds', 'superdb'])
},
});
// SELECT * FROM tb_example WHERE name IN ('qtds', 'superdb')
Op.notIn
const result = await conn.find('tb_example', {
where: {
name: conn.Op.notIn(['qtds', 'superdb'])
},
});
// SELECT * FROM tb_example WHERE name NOT IN ('qtds', 'superdb')
Op.like
const result = await conn.find('tb_example', {
where: {
name: conn.Op.like('%d'),
},
});
// SELECT * FROM tb_example WHERE name LIKE '%d'
Op.notLike
const result = await conn.find('tb_example', {
where: {
name: conn.Op.notLike('%d'),
},
});
// SELECT * FROM tb_example WHERE name NOT LIKE '%d'
Op.between
const result = await conn.find('tb_example', {
where: {
name: conn.Op.between(['c', 'f'])
},
});
// SELECT * FROM tb_example WHERE name BETWEEN 'c' AND 'f'
Op.notBetween
const result = await conn.find('tb_example', {
where: {
name: conn.Op.notBetween(['c', 'f']),
},
});
// 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:{
updateBody: (params) => {
const result = Object.assign({}, params, {
updated: Date.parse(new Date()) / 1000,
});
return result;
},
}
insertBody: (params:Array)=>{return params}
beforeHooks:{
insertBody: (params) => {
const result = params.map(v => Object.assign({}, v, {
created: Date.parse(new Date()),
}));
return result;
},
}
limit: (params:Integer)=>{return params}
find 不指定limit, 一次最多查询10*1000条数据,内置该hook
beforeHooks: {
limit: () => (limit) => {
if (limit === undefined)) {
return 10 * 1000;
}
return limit;
},,
}
ttl: (params:Integer)=>{return params}
cache ttl = 60 * 60, 内置该hook
beforeHooks: {
ttl: (ttl) => {
if (ttl === undefined)) {
return 60 * 60;
}
return ttl;
},
}
afterHooks
find: (result: Array)=>{return result}
删除find结果中的created 与 updated字段
afterHooks: {
find: (list) => {
const result = list.map((v) => {
const tempV = v;
delete tempV.created;
delete tempV.updated;
return tempV;
});
return result;
},
},