como-sql-builder

como-sql-builder

支持mysql、sqlite3 原生typescript 实现,无任何依赖库,方便实用

实现此库的主要目的是在mysql和sqlite3数据库切换时类型转换不兼容的情况 此库的数据类型验证比较宽松,实现了 Sequelize 的70%的功能

手工构建SQL语句并不有趣，尤其是在一种对多行字符串支持笨拙的语言中

让我们用JavaScript来构建它

安装

$ npm install como-sql-builder

使用示例

import { FindOptions, WhereOptions } from "../types/application";
import { SQLBuilderException, ColumnProperty } from "./core/BaseBuilder";
import { Op } from "./core/operators";
import { SqlBuilder } from "./main";
//自定义回调场景转换或验证函数
//这是只展示了转换函数
//如果需要验证 可以在函数内部验证
const md5 = function(value:string,data:object,scene:string) {
    if(value != "1234") throw new SQLBuilderException("数据验证未通过,请检查数据是否正确");
    if(scene == "insert") {
        return `${value}helloworld`;
    }
    return value;
}

class AdminUserPipe {
    
    @ColumnProperty()
    //ColumnProperty({convert:Fn}) 
    //此处的Number是内定的转换函数 也是可以自定义转换函数
    //会回调三个参数(value,data,scene)
    // value 当前字段的值
    // data 整个对象的值
    // scene 使用场景 当前只支持insert和update

    //如果没有装饰器 则在插入数据 查询数据 更新数据中忽略该字段
    //但前题是必须要使用这个使用 没有使用此类无效
    admin_id?: number;

    @ColumnProperty()
    username?: string;

    // @ColumnProperty({convert:Fn})
    @ColumnProperty()
    password?: string;

    @ColumnProperty()
    admin_status?:number

    @ColumnProperty()
    role_id?:number

    @ColumnProperty({
        insertDefaultValue:"192.168.18.99"
    })
    login_ip?:number
}

// class RolesPipe {
//     role_id?:number
//     role_desc?:string
//     role_name?:string
//     role_flag?:string | number
//     admin_id?:number
//     create_time?:string
// }

const builder = SqlBuilder.Ins({prefix:"szj_"});
//查询语句
/**
 * 返回示例:
 * SELECT `SzjAdminUser`.`admin_id`,`SzjAdminUser`.`username`,`SzjAdminUser`.`password`,`SzjAdminUser`.`admin_status`,`SzjAdminUser`.`role_id`,`role`.`role_name` AS `role_name` FROM `szj_admin_user` AS `SzjAdminUser` LEFT JOIN `szj_admin_user` AS `au` ON `SzjAdminUser`.`action_user` = `au`.`admin_id` LEFT JOIN `szj_roles` AS `role` ON `SzjAdminUser`.`role_id` = `role`.`role_id` ORDER BY `SzjAdminUser`.`admin_id` DESC
 */
const selectSQL = builder.Builder({cls:AdminUserPipe}).Table("admin_user").FindAll({
    include:[
        {table:'admin_user',as:'au',attributes:[],condition:{
            foreignKey:"action_user",targetKey:'admin_id'
        }},
        {table:"roles",as:'role',attributes:["role_name"],condition:{
            foreignKey:'role_id',targetKey:'role_id'
        }}
    ],
    attributes:{
        include:[["au.username","action_username"]],
        exclude:["login_time","login_count"]
    },order:[["admin_id","desc"]]
} as FindOptions<object>);

//插入语句
/**
 * 返回示例:
 * INSERT INTO `szj_admin_user` (`username`,`password`,`role_id`) VALUES ("como","123456",1)
 */
const insertSQL = builder.Builder({cls:AdminUserPipe}).Table("admin_user").Insert<AdminUserPipe>({
    username:"como",
    password:"123456",
    role_id:"1",
});

//占位符插入
/**
 * 返回示例:
 *  [
        'INSERT INTO `szj_admin_user` (`username`,`password`,`role_id`) VALUES ($0,$1,$2)',
        [ 'como', '123456', 1 ]
    ]
 */
const insertValSql = builder.Builder({cls:AdminUserPipe}).Table("admin_user").InsertVal({
    username:"como",
    password:"123456",
    role_id:1,
});
//更新语句
/**
 * 返回示例:
 * UPDATE `szj_admin_user` SET `password` = "12345678",`admin_status` = 1 WHERE ( `admin_id` = 38 )
 */
const updateSQL = builder.Builder({cls:AdminUserPipe}).Table("admin_user").Update<AdminUserPipe>({
    password:"12345678",admin_status:1
},{where:{admin_id:38}});

//更新语句
/**
 * 返回示例:
 * [
        'UPDATE `szj_admin_user` SET `password` = $0,`admin_status` = $1 WHERE ( `admin_id` = 38 )',
        [ '12345678', 1 ]
   ]
 */
const updateValSQL = builder.Builder({cls:AdminUserPipe}).Table("admin_user").UpdateVal({
    password:"12345678",admin_status:1,action_user:1,login_count:10
},{where:{admin_id:38}});

//删除语句
/**
 * 返回示例:
 * DELETE FROM `szj_admin_user` WHERE ( `admin_id` = 38 )
 */
const deleteSql = builder.Builder({cls:AdminUserPipe}).Table("admin_user").Delete<AdminUserPipe>({
    where:{admin_id:38}
});

//统计语句查询
/**
 * 返回示例:
 * SELECT count(`SzjArticles`.`admin_id`) AS `count` FROM `szj_articles` AS `SzjArticles`
 */
const countSql = builder.Builder().Table("articles").Count({col:"admin_id"});

//查询并统计所有数据
/**
 * 返回示例:
 * SELECT count(*) AS `count` FROM `szj_admin_user` AS `SzjAdminUser` LEFT JOIN `szj_admin_user` AS `au` ON `SzjAdminUser`.`action_user` = `au`.`admin_id`
 * SELECT `SzjAdminUser`.`admin_id`,`SzjAdminUser`.`username`,`SzjAdminUser`.`password`,`SzjAdminUser`.`admin_status`,`SzjAdminUser`.`role_id` FROM `szj_admin_user` AS `SzjAdminUser` LEFT JOIN `szj_admin_user` AS `au` ON `SzjAdminUser`.`action_user` = `au`.`admin_id`
 */
const [AllCountSql,AllDataSql] = builder.Builder({cls:AdminUserPipe}).Table("admin_user").FindAndCountAll({
    attributes:{
        exclude:['create_time']
    },
    include:[
        {table:"admin_user",as:'au',attributes:[],condition:{
            foreignKey:"action_user",targetKey:'admin_id'
        }}
    ]
});
//使用函数
/**
 * 返回示例:
 * SELECT `SzjArticles`.`article_id`,`SzjArticles`.`article_title`,`SzjArticles`.`create_time`,FROM_UNIXTIME(UNIX_TIMESTAMP(`create_time`)) AS `create_at_time` FROM `szj_articles` AS `SzjArticles`
 */
const fnSql = builder.Builder().Table("articles").FindAll({
    attributes:[
        "article_id","article_title","create_time",
        [builder.Fn("FROM_UNIXTIME","UNIX_TIMESTAMP","create_time"),"create_at_time"],
    ]
});
//无参数查询
/**
 * 返回示例:
 *  [
        'SELECT count(*) AS `count` FROM `szj_admin_user` AS `SzjAdminUser` LEFT JOIN `szj_admin_user` AS `au` ON `SzjAdminUser`.`action_user` = `au`.`admin_id`',
        'SELECT `SzjAdminUser`.`admin_id`,`SzjAdminUser`.`username`,`SzjAdminUser`.`password`,`SzjAdminUser`.`admin_status`,`SzjAdminUser`.`role_id` FROM `szj_admin_user` AS `SzjAdminUser` LEFT JOIN `szj_admin_user` AS `au` ON `SzjAdminUser`.`action_user` = `au`.`admin_id`'
    ]
 */
const notParamsSql = builder.Builder({cls:AdminUserPipe}).Table("admin_user").FindAndCountAll({
    include:[
        {table:"admin_user",as:'au',attributes:[],condition:{
            foreignKey:'action_user',targetKey:'admin_id'
        }}
    ]
});
//单条查询
/**
 * 返回示例:
 * SELECT `SzjAdminUser`.`admin_id`,`SzjAdminUser`.`username`,`SzjAdminUser`.`password`,`SzjAdminUser`.`admin_status`,`SzjAdminUser`.`role_id` FROM `szj_admin_user` AS `SzjAdminUser` WHERE ( `SzjAdminUser`.`admin_id` > 5 AND `SzjAdminUser`.`admin_id` < 30 ) LIMIT 1
 */
const FindOneSql = builder.Builder({cls:AdminUserPipe}).Table("admin_user").FindOne<AdminUserPipe>({
    where:{
        [Op.and]:[
            {admin_id:{[Op.gt]:5}} as WhereOptions<AdminUserPipe>,
            {admin_id:{[Op.lt]:30}} as WhereOptions<AdminUserPipe>
        ],
        // [Op.or]:[
        //     {admin_id:{[Op.gt]:5}} as WhereOptions<AdminUserPipe>,
        //     {admin_id:{[Op.lt]:30}} as WhereOptions<AdminUserPipe>
        // ]
    }
});

const insertAllSql = builder.Builder({cls:AdminUserPipe}).Table("admin_user").InsertAll([
    {username:"szjcomo",role_id:1,password:"szjcomo"},
    {username:"szjcomo123",role_id:3,password:"szjcomo123"},
]);

console.log('selectSQL',selectSQL);
console.log('insertSQL',insertSQL);
console.log('insertValSql',insertValSql);
console.log('updateSQL',updateSQL);
console.log('updateValSQL',updateValSQL);
console.log('deleteSql',deleteSql);
console.log('countSql',countSql);
console.log('AllCountSql',AllCountSql);
console.log('AllDataSql',AllDataSql);
console.log('fnSql',fnSql);
console.log('notParamsSql',notParamsSql);
console.log('FindOneSql',FindOneSql);
console.log('insertAllSql',insertAllSql);