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

• 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
  

• 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
  

• 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')
  

• 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'
  

• 设置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);// 相关字段信息（不一定有结果） 
  })
  

• 模型

  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]