分享一个非orm模型的sql查询库
发布于 6 年前 作者 yujintang 3953 次浏览 来自 分享

**github 地址： superdb， 欢迎大家前来使用与提供建议 **

superdb

使用场景：

  1. 拒绝拼接SQL语句，长期维护且达到易读效果
  2. 减少ORM模型定义，表更新频繁
  3. 支持链式操作，让数据定义更灵活
  4. 多数据库支持
  5. 频繁读数据放入缓存
  6. 性能提升

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

// 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
    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 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

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'
8 回复

https://github.com/zswDev/mysql_mongoose，我也写了一个类似的，模仿的是mongoose，不完善

来自酷炫的 CNodeMD

很好，试用看看。

@zswnew 代码质量堪忧啊…

@zhulinwei 里面core 代码都已经完成了test

@sonong 欢迎rp

@zswnew 棒棒哒

@yujintang superdb写得挺好的，我发出感慨是因为看了上面那个mysql_mongoose的代码…

加入beforeHooks and afterHooks

回到顶部