superdb
使用场景：
- 拒绝拼接SQL语句，长期维护且达到易读效果
- 减少ORM模型定义，表更新频繁
- 支持链式操作，让数据定义更灵活
- 多数据库支持
- 频繁读数据放入缓存
- 性能提升
TODO
- postgresqls
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
// 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 promise : true // using promise async/await ? default true logging : false, // print sql ? default false maxLimit : -1, // sql limit, default no limit redis : { config : undefined, // can use {host: "", port: "", password: "", db: ""} or "redis://:password@host:port/db", cache : 'false' // use cache ? default false ttl : 60 * 60 // if use cache, how long expire? default 60 * 60, ttl can set at every query(); }}复制代码 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, findOptions);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, findOptions);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, findOptions); 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
await conn.count(tbName, findOptions);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'复制代码 create
await conn.create(tbName, createParams);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, findOptions);const result = await conn.update('tb_example', { name: 'qtds' }, { where: { id: 100 }, });// UPDATE tb_example SET name = 'qtds' WHERE id = 100复制代码 delete
await conn.delete(tbName, deleteOptions)const result = await conn.delete('tb_example', { where: { id: 100 }, limit: 1, });// DELETE FROM tb_example WHERE id = 100 LIMIT 1复制代码 findOptions
findOptions = { 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')conn.table(['tb_example'])conn.table('tb_example as exp')conn.table(['tb_example', 'exp']) const result = await conn .find(['tb_example','exp']);// SELECT * FROM tb_example AS exp复制代码 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
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复制代码 limit(params.limit)
conn.limit(10) // limit 10conn.limit([10, 1]) // limit 10 offset 1const result = await conn .limit([10, 1]) .find('tb_example');// SELECT * FROM tb_example LIMIT 10 OFFSET 1复制代码 offset(params.offset)
conn.offset(1) // offset 1const 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 descconst 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 descconst 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 > 4const 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'复制代码 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'复制代码 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'复制代码 const result = await conn.find('tb_example', { where: { name: conn.Op.lt('d'), }, });// SELECT * FROM tb_example WHERE name < 'd'复制代码 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复制代码 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'复制代码