sqlie

Sqlie

简单的SQL语句构建工具。 A simple SQL query builder.

内置简单模型，支持MySQL的联合主键。

Install

$ npm install sqlie --save

API

• ModelBuilder 构建模型
• createModel 构建模型
• setQueryHandler 设置SQL语句执行函数
• execute 执行SQL语句
• DeleteBuilder 构建删除语句
• InsertBuilder 构建插入数据语句
• SelectBuilder 构建查询语句
• JoinBuilder 构建JOIN表语句
• JoinSelectBuilder 构建JOIN查询语句
• UpdateBuilder 构建更新语句

Examples

SELECT

1. A base SELECT:

   const {SelectBuilder} = require('sqlite');
   
   const builder = new SelectBuilder()
     .from('users')
     .select('name,age,address')
     .where('name', '=', 'Jon Snow')
     .where('age', '=', 22);
   
   builder.build();
   // => SELECT `name`, `age`, `address` FROM `users` WHERE `name` = 'Swat' AND `age` = 22
   

2. SELECT with a simple JOIN:

   const {SelectBuilder} = require('sqlie');
   
   const builder = new SelectBuilder()
     .from('users', 'u') // alias 'u' for table 'users'
     .select('*') // optional with select all
     .where('name', '=', 'Jon Snow')
     .where('age', '=', 22)
     .join('hobbies', function(hobbyBuilder) {
       hobbyBuilder
         .setAlias('h')
         .select('hobby') // select 'hobby' from table 'hobbies'
         .onColumn('h.id', 'u.id');
     })
     .join('colors', function(colorBuilder) {
       colorBuilder
         .setAlias('c')
         .select('favorite') // select 'favorite' from table 'colors'
         .onColumn('c.user_id', 'u.id');
     });
   
   builder.build();
   // => SELECT `u`.*, `h`.`hobby`, `c`.favorite FROM `users`
   //    JOIN `hobbies` ON `h`.`id` = `u`.`id`
   //    JOIN `colors` ON `c`.`user_id` = `u`.`id`
   //    WHERE `u`.`name` = 'Jon Snow' AND `u`.`age` = 22
   

3. INSERT

   const {InsertBuilder} = require('./dist');
   
   const builder = new InsertBuilder()
     .into('users')
     .set('name', 'Super Girl')
     .setSome({
       age: 18,
       gender: 'female'
     });
   
   builder.build();
   // => INSERT INTO `users` (`name`, `age`, `gender`) VALUES ('Super Girl', 18, 'female')
   

4. UPDATE

   const {UpdateBuilder} = require('./dist');
   
   const builder = new UpdateBuilder()
     .from('users')
     .set('age', 22)
     .setSome({})
     .where('name', '=', 'Super Girl');
   
   builder.build();
   // => UPDATE `users` SET `age` = 22 WHERE `name` = 'Super Girl'
   

5. 设置SQL执行函数

   const {createConnection} = require('mysql')
   const {setQueryHandler, execute} = require('sqlie')
   const connection =  createConnection({/*db config*/});
   // 设置 MySQL 语句执行函数
   setQueryHandler(connection.query.bind(connection));
   // 下面可以调用 execute 函数执行 SQL 语句了
   execute('SELECT * FROM tableName').then(function ({rows, fields}) {
       console.log(rows); // 查询结果
       console.log(fields);// 相关字段信息（不一定有结果） 
   })
   

6. 模型

   const {createModel} = require('sqlie');
   const user = createModel('my_users', 'uid');
   
   // 联合主键 'gid'、'uid'
   const msg = createModel('im_group_msgs', ['gid', 'uid']);
   

TODO

• 完善测试用例
• 编写开发文档

Licence

MIT © Frge [e-mail]