B-sql-runner NPM

b-sql-runner是一款简单的sql query builder，追求以最接近写SQL的方式来完成对数据的操作

b-sql-runner底层使用knex执行SQL，后期可能使用其他库实现。目前没有单元测试，请谨慎考虑在生产环境里使用！ 欢迎提供PR，尤其是单元测试PR，Have Fun！

b-sql-runner参考了以下框架，在次致谢！

Feature

仅支持单表的增删改查
动态SQL
分页查询
数据库事务
支持Typescript

Example

let sqlRunner=getConnection().sqlRunner();

let users=await sqlRunner
    .select("oid,group")
    .field(b.sum('score').as('scores'))
    .from('t_user')
    .where(b.eq('status','enable'))
    .and(b.isnotnull('mobile'))
    .groupBy('group')
    .having(b.gte('scores',100))
    .orderBy(b.asc('gid,creationDate'))
    .limit(10)
    .findMany();

//update `t_user` set `score` = `score` + 10, `age` = `age` - 10, `nickname` = `name`
let UserRepository=getRepository({name:'t_user',pk:'oid'});
let affected=await UserRepository
    .update()
    .incr('score',10)
    .decr('age',10)
    .replace('nickname','name')
    .run();

let users=[{name:'n1'},{name:'n2'}];
let affectedUsers=await UserRepository
    .insert(users)
    .returning("*",b.expr(b.idIn(users,'name')))
    .run();

Donate

如果你喜欢本项目，不妨请我喝杯咖啡

快速指引

Connecton
SqlRunner
Repository
Transaction
Dynamic SQL
Raw Query
Returning Query
API

INSTALL

通过npm安装

npm install b-sql-runner -S

安装你使用的数据库驱动，如mysql

npm install mysql -S

如果你使用Typescript，推荐以下安装

npm install @types/knex -D

Connection

创建数据库连接，建议总是创建默认数据库连接

knex将创建数据库连接池，以下为mysql的配置，其他数据库配置请参考knex

import {Config} from "knex";
import {ConnectionManager} from "b-sql-runner";

//创建mysq数据库连接配置
const dbConfig:Config={
    client: 'mysql',
    debug: false,
    connection: {
        host: DB_HOST,
        port: DB_PORT,
        user: DB_USER,
        password: DB_PASSWORD,
        database: DB_NAME
    }
}

//创建默认数据库连接
ConnectionManager.createDefaultConnection(dbConfig)

//创建多个数据库连接
ConnectionManager.createConnections({
    'default':dbConfig,
    slaveDb:slaveDdConfig
})

获取数据库连接

import {getConnection} from "b-sql-runner";

//获取默认数据库连接
const conn=getConnection();

//获取指定名称的数据库连接
const slaveDbConn=getConnection('slaveDb');

关闭数据库连接池

await getConnection().close();

SqlRunner

SqlRunner负责拼接并执行SQL，以下获取SqlRunner

const sqlRunner=getConnection().sqlRunner();

//可选指定数据库记录的对象类型
interface User{
    oid:number;
    name:string;
}
const sqlRunner=getConnection().sqlRunner<User>();


//获取`SqlRunner`实例后，还需要进一步获取具体的sql runner
let selectSqlRunner=sqlRunner.select();

SELECT

SelectSqlRunner负责执行SELECT SQL

简单查询

//select `oid`, `name` from `t_user` limit 1
let user=await sqlRunner
    .select('oid,name')
    .from('t_user')
    .findFirst();

指定查询字段

//select `oid`, `name`
sqlRunner.select('oid,name');
sqlRunner.select().field('oid,name');
sqlRunner.select().field('oid','name');
sqlRunner.select().field('oid').field('name');

//select `oid` as `id`, `name` as `nickname`
sqlRunner.select('oid as id,name as nickname');

指定聚合函数查询字段

import * as b from "b-sql-runner";

//select count(`oid`)
sqlRunner.select(b.count('oid'));

//select count(`oid`) as `count`
sqlRunner.select(b.count('oid').as('count'));

//select count(distinct `oid`)
sqlRunner.select(b.count().distinct('oid'));

指定简单查询条件

//where `oid` = 1 and `name` != 'b'
sqlRunner.select()
    .where(b.eq('oid',1))
    .and(b.neq('name','bill'))

//where `oid` = 1 or `name` != 'b'
sqlRunner.select()
    .where(b.eq('oid',1))
    .or(b.neq('name','bill'))

// where `oid` = 100 and `name` = 'bill'
sqlRunner.select()
    .where({oid:100,name:'bill'})

// where `oid` = 100 or `name` = 'bill'
sqlRunner.select()
    .where()
    .or({oid:100,name:'bill'})

// where `oid` = 100 and `name` = 'bill'
sqlRunner.select()
    .where(
        b.eq('oid',100),
        b.eq('name','bill')
    )

指定复杂查询条件，使用expr()

//where `oid` < 100 and (`oid` > 100 or `name` = 'bill')
sqlRunner.select()
        .where(b.lt('oid',100))
        .or(
            b.expr()
                .and(b.gt('oid',100))
                .and(b.eq('name','bill'))
        )

使用in()/idIn()查询条件

//where `oid` =1
sqlRunner.select()
    .where(b.in('oid',[1]))

//where `oid` in (1, 2)
sqlRunner.select()
    .where(b.in('oid',[1,2]))

//idIn()会从传入的行数据里取出(pick)指定字段的值拼接in()查询条件
//idIn()默认取表的主键字段作为`idField`
let users=[{oid:1},{oid:2}];

//where `oid` in (1, 2)
sqlRunner.select()
    .from('t_user','oid')               //指定t_user表的主键为oid
    .where(b.idIn(users))

//where `oid` in (1, 2)
sqlRunner.select()
    .from('t_user')                      //未指定表的主键
    .where(b.idIn(users,'oid'))  //指定'idField'为oid

指定子查询条件，使用in()/exists()实现join查询

//select `oid` from `t_user` where `oid` in (select `oid` from `t_user` where `age` < 12)
sqlRunner
    .select('oid')
    .from('t_user','oid')
    .where(b.idIn(
        sqlRunner
            .select('oid')
            .from('t_user')
            .where(b.lt('age',12))
    ))

指定分组，排序条件

//select `area`, sum(`score`) as `scores` from `t_user`
//group by `area` having `scores` > 100
//order by `scores` desc, `area` asc
sqlRunner
    .select('area')
    .field(b.sum('score').as('scores'))
    .from('t_user')
    .groupBy('area')
    .having(b.gt('scores',100))
    .orderBy(b.desc('scores'),b.asc('area'))

使用分页查询，并且返回总记录数

//select `oid` from `t_user` limit 10 offset 20
//select count(*) as `total` from `t_user`
//return: {data:Array<T>,total:number}
sqlRunner
    .select('oid')
    .from('t_user')
    .offset(20)
    .limit(10)
    .findWithTotal()

INSERT

sqlRunner.insert(users).into('t_user').run();
sqlRunner.insert().into('t_user').values(users).run();

//指定要插入的字段，以下`oid`字段将被忽略
//insert into `t_user` (`name`) values ('bill')
let user={oid:1,name:'bill'}
sqlRunner.insert(user).into('t_user').fields('name').run();

UPDATE

//update `t_user` set `name` = 'bill' where `oid` = 1
sqlRunner
    .update()
    .table('t_user')
    .set('name','bill')
    .where(b.eq('oid',1))
    .run();

//update `t_user` set `name` = 'bill', `age` = 10
sqlRunner
    .update()
    .table('t_user')
    .set({name:'bill',age:10})
    .run()

//update `t_user` set `salary` = `salary` + 100, `age` = `age` - 1, `nickname` = `name`
sqlRunner
    .update()
    .table('t_user')
    .incr('salary',100)
    .decr('age')
    .replace('nickname','name')
    .run()

更新记录行，此时必须设置主键，并且传入的记录必须包含主键字段值

let users=[{oid:1,name:'n1'},{oid:2,name:'n2'}];

//每条记录生成1条更新SQL，在同1个事务里完成
//update `t_user` set `name` = 'n1' where `oid` = 1
//update `t_user` set `name` = 'n2' where `oid` = 2
sqlRunner
    .update()
    .table('t_user','oid')    //设置主键为'oid'
    .values(users)
    .run()

保存记录行，必须设置主键，对传入的记录执行如下操作（同1个数据库事务） 1. 如果记录不包含主键字段值，则归到fresh rows 2. 如果记录包含主键字段值： 1. 取所有记录的主键值，查询数据库判断对应的记录是否存在 2. 如果不存在，则归到fresh rows 3. 如果已存在，则归到dirty rows 3. 对fresh rows执行insert，对dirty rows执行update

let users=[{oid:1,name:'n1'},{name:'n2'}];

//select `oid` from `t_user` where `oid` = '1' for update
//insert into `t_user` (`name`) values ('n2')
//update `t_user` set `name` = 'n1' where `oid` = 1
sqlRunner
    .update()
    .table('t_user','oid')
    .values(users)
    .save()

DELETE

let users=[{oid:1,name:'n1'},{oid:2,name:'n2'}];

//delete from `t_user` where `oid` in (1, 2)
sqlRunner.delete(users).from('t_user','oid').run();
sqlRunner.delete().from('t_user','oid').values(users).run();
sqlRunner.delete().from('t_user').where(b.in('oid',[1,2])).run();

删除表的全部记录，为了防止误删除，必须设置查询条件

//delete from `t_user` where 1 = 1
sqlRunner.delete().from('t_user2').clear();
sqlRunner.delete().from('t_user2').where(b.alwaysTrue()).run();

Repository

Repository针对单表操作，简单封装SqlRunner

let UserRepository=getRepository<User>({name:'t_user',pk:'oid'});

let user=await UserRepository.findOne(1);
let affected=await UserRepository.update(user).run();

自定义Repository

class UserRepo extends Repository<User>{
    //可以在构造函数里设置表
    constructor(){
        super();
        this.useTable({name:'t_user',pk:'oid'});
    }

    //自定义查询
    async findDisabledUsers():Promise<Array<User>>{
        return this.select().where(b.eq('status','disabled')).findMany();
    }
}

let UserRepository=getCustomRepository(UserRepo);
let users=await UserRepository.findDisabledUsers();

Transaction

SqlRunner.inTx()用于设置执行SQL时使用的数据库事务

Connection.createTx()创建1个新事务，在执行完SQL后需要自行提交/回滚事务

let tx=await getConnection().createTx();
try{
    let repo=getRepository({name:'t_user',pk:'oid'});
    let user=await repo.select().forUpdate().inTx(tx).findFirst();
    let affected=await repo.update(user,tx).run();

    tx.commit();
}catch(e){
    tx.rollback();
}

Connection.runInTx()创建1个新事务，并传给回调函数。执行完SQL后会自动提交/回滚事务

let affected=await getConnection()
    .runInTx(async tx=>{
        let repo=getRepository({name:'t_user',pk:'oid'});
        let user=await repo.select('cc').forUpdate().inTx(tx).findFirst();
        let affected=await repo.update(user,tx).run();

        return affected;
    });

Dynamic SQL

select(), where()等方法可以传入undefined作为参数值，在创建查询时将被忽略

let isAdmin=true;
let cid=1;//current login user id;
//admin:  select `oid`, `name`, `secret` from `t_user`
//!admin: select `oid`, `name` from `t_user` where `oid`=1
UserRepository
    .select(
        'oid,name',
        isAdmin?'secret':undefined
    )
    .where(
        !isAdmin?b.eq('oid',cid):undefined
    )
    .findMany();

//也可以使用以下方式
let q=UserRepository.select('oid,name');
q.field(isAdmin?'secret':undefined);
q.where(!isAdmin?b.eq('oid',cid):undefined);
q.findMany();

//如果是简单查询条件，可以使用如下方式
//select * from `t_user` where `name` = 'bill'
UserRepository.select()
    .where({name:'bill',gender:undefined})
    .findMany()

Raw Query

raw()支持设置raw表达式，详细用法请参考knex raw

//select lower('name') from `t_user` where `status`='disable' and `age` < 10
UserRepository
    .select(b.raw('lower(?)',['name']))
    .where(b.raw('??=?',['status','disable']))
    .and(b.raw(':field: < :value',{field:'age',value:10}));

//update `t_user` set `name` = `firstName`+`lastName`
UserRepository.update()
    .set('name',b.raw('??+??',['firstName','lastName']))

Returning Query

returning()支持设置1条查询语句，在执行增删改操作前/后会执行此查询语句，以返回受影响的记录

returning()不需要底层数据库支持returning表达式，这点与knex returning不同

//insert into `t_user` (`title`) values (1)
//select * from `t_user` where `oid` = '3'
UserRepository.insert({title:1}).returning('*').run();

//以上语句等价于
getConnection()
    .runInTx(async tx=>{
       let id=await UserRepository.insert({title:1},tx).run();
       let user=await UserRepository.findOne(id as number,tx);

       return user;
    });

如果新增多条记录，并且数据库不支持returning语句(如mysql)。那么returning()必须设置查询条件

//`title`需要为unique key，否则返回的可能不是新增的记录
//insert into `t_user` (`title`) values (1), (2)
//select * from `t_user` where `title` in ('1', '2')
let users=[{title:1},{title:2}];
UserRepository.insert(users)
    .returning('*',b.expr(b.idIn(users,'title'))).run();

记住：returning()只是帮助你在执行增删改前/后执行1条查询语句，这条查询语句的返回结果是由你决定的