0.0.3 • Published 9 years ago
tosql v0.0.3
Introduction
This is a new tool for building sql.
I will try my best to complete it.
If you feel that the tool gives you some help, I hope you can star me ManitoYu.
Examples
var tosql = require('tosql');
var table = tosql('table');
Insert
var sql = table.insert({ name: 'your name', age: 21 });
console.log(sql); // INSERT INTO `table` (`your name`, `age`) VALUES ('yucong', 21)
Field
var sql = table.field(['id', 'name']).select();
console.log(sql); // SELECT `id`, `name` FROM `table`
Where
- ne
- le
- ge
- eq
- gt
- lt
- like
- between
- in
table.where({ id: { ne: 1 } }).select(); // SELECT * FROM `table` WHERE `id` != 1
table.where({ id: { ge: 1, le: 10 } }).select(); // SELECT * FROM `table` WHERE `id` >= 1 AND `id` <= 10
table.where({ name: { like: '%name%' } }).select(); // SELECT * FROM `table` WHERE `name` LIKE '%name%'
table.where([{ id: 1 }, { name: 'name' }]).select(); // SELECT * FROM `table` WHERE (`id` = 1) OR (`name` = 'name')
table.where({ id: 1, name: 'name' }).select(); // SELECT * FROM `table` WHERE `id` = 1 AND `name` = 'name'
table.where({ id: [{ in: [1, 2, 3] }, { between: [10, 20] }] }).select(); // SELECT * FROM `table` WHERE ((`id` IN (1, 2, 3)) OR (`id` BETWEEN 10 AND 20))
Delete
table.where({ id: 1 }).delete(); // DELETE FROM `table` WHERE `id` = 1
If you create a model with primary key, you can do this more easier.
var table = tosql('table', 'id');
table.delete(1); // DELETE FROM `table` WHERE `id` = 1
Update
table.where({ id: 1 }).update({ age: 21 }); // UPDATE `table` SET `age` = 21 WHERE `id` = 1
The same as delete, you can also do it as follow.
var table = tosql('table', 'id');
table.update({ name: 'name' }, 1); // UPDATE `table` SET `name` = 'name' WHERE `id` = 1
Join
// set the map of relations
tosql.relations([
{ table: 'table1_id', table1: 'id' }
]);
// set the alias of table
tosql.config('table', { alias: 't' });
tosql.config('table1', { alias: 't1' });
var table = tosql('table');
table.join('table1').where({ 't.id': 1 }).select(); // SELECT * FROM `table` `t` LEFT JOIN `table1` `t1` ON `t1`.`id` = `t`.`table1_id` WHERE `t`.`id` = 1
Limit
var table = tosql('table');
table.limit(0, 10).select(); // SELECT * FROM `table` LIMIT 0, 1
Order
var table = tosql('table');
table.where({ status: 1 }).order('id').select(); // SELECT * FROM `table` WHERE `status` = 1 ORDER BY `id` DESC
table.order('time', false).select(); // SELECT * FROM `table` ORDER BY `time` ASC
Group
var table = tosql('table');
table.field(['sex', { sum: 'age' }, { avg: 'age' }]).group('sex').select(); // SELECT `sex`, SUM(`age`), AVG(`age`) FROM `table` GROUP BY `sex`
Having
var table = tosql('table');
table.field(['sex', { sum: 'sex' }]).group('sex').having({ sum: 'sex' }, { gt: 1 }).select(); // SELECT `sex`, SUM(`sex`) FROM `table` GROUP BY `sex` HAVING SUM(`sex`) > 1