sql easy builder

install

npm i sql-easy-builder

example

1const { Builder, raw } = require('sql-easy-builder');
2new Builder().select().from('user').where({ id: 1 }).build();
3// SELECT * FROM `user` WHERE `id` = ?
4// [ 1 ]

select

1select()
2// SELECT *

1select('id', 'name')
2// SELECT `id`, `name`

1select('id', { name: 'realname', age: 'AGE' })
2// SELECT `id`, `name` AS `realname`, `age` AS `AGE`

1select('user.age', { user: ['id', 'name'], profile: ['edu', 'work'] })
2/*
3SELECT
4	`user`.`age`,
5	`user`.`id`,
6	`user`.`name`,
7	`profile`.`edu`,
8  `profile`.`work`
9*/

1select('user.age', { user: ['id', 'name'], profile: { edu: 'p.edu', work: 'p.work' } })
2/*
3SELECT
4	`user`.`age`,
5	`user`.`id`,
6	`user`.`name`,
7	`profile`.`edu` AS `p`.`edu`,
8  `profile`.`work` AS `p`.`work`
9*/

1let b = new Builder();
2b.select(b.func('MAX','id'))
3// SELECT MAX(`id`)

1b = new Builder();
2b.select(b.func('MAX','id','max_id'))
3// SELECT MAX(`id`) AS `max_id`

1b = new Builder();
2b.select(b.raw(`DISTINCT ${b.q('id')}`))
3// SELECT DISTINCT `id`

from

1from('user')
2// FROM `user`

1from('user', 'u')
2// FROM `user` AS `u`

update

1update('user', { name: 'yf', age: 30 })
2// UPDATE `user` SET `name` = ?, `age` = ?
3// [ 'yf', 30 ]

1b = new Builder();
2b.update('user', { name: 'yf', age: b.q('new_age') })
3// UPDATE `user` SET `name` = ?, `age` = `new_age`
4// [ 'yf' ]

1b = new Builder();
2b.update(['user', 'profile'], { 'user.name': 'yf', 'user.age': b.q('profile.age') })
3// UPDATE `user`, `profile` SET `user`.`name` = ?, `user`.`age` = `profile`.`age`
4// [ 'yf' ]

1b = new Builder();
2b.update('user', { updated_at: b.func('NOW') })
3// UPDATE `user` SET `updated_at` = NOW()

1b = new Builder();
2b.update('user', { balance: b.op('balance').op('+', 100) });
3// UPDATE `user` SET `balance` = `balance` + ?
4// [ 100 ]

1b = new Builder();
2b.update('user', { balance: b.op('balance', '+', 100).op('*', b.op('balance', '%', 10)) });
3// UPDATE `user` SET `balance` = `balance` + ? * ( `balance` % ? )
4// [ 100, 10 ]

insert

1insert('user', { name: 'yf', age: 30 })
2// INSERT INTO `user` ( `name`, `age` ) VALUES ( ?, ? )
3// [ 'yf', 30 ]

1b = new Builder();
2b.insert('user', { name: 'yf', age: 30, created_at: b.func('NOW') })
3// INSERT INTO `user` ( `name`, `age`, `created_at` ) VALUES ( ?, ?, NOW() )
4// [ 'yf', 30 ]

delete

1delete('user')
2// DELETE FROM `user`

join

1b = new Builder();
2b.join('user', { 'user.id': b.q('other.id') })
3// INNER JOIN `user` ON (`user`.`id` = `other`.`id`)

1b = new Builder();
2b.join('user', { 'user.id': b.q('other.id'), 'user.status': 1 })
3// INNER JOIN `user` ON (`user`.`id` = `other`.`id` AND `user`.`status` = ?)
4// [ 1 ]

1b = new Builder();
2b.leftJoin('user', { 'user.id': b.q('other.id') })
3// LEFT JOIN `user` ON (`user`.`id` = `other`.`id`)

1b = new Builder();
2b.rightJoin('user', { 'user.id': b.q('other.id') })
3// RIGHT JOIN `user` ON (`user`.`id` = `other`.`id`)

1b = new Builder();
2b.join('user', 'u', { 'u.id': b.q('other.id') })
3// INNER JOIN `user` AS `u` ON (`u`.`id` = `other`.`id`)

count

1count()
2// SELECT COUNT(*)

1count('id')
2// SELECT COUNT(`id`)

1count('id', 'user_count')
2// SELECT COUNT(`id`) AS `user_count`

limit

1limit(100)
2// LIMIT ?
3// [ 100 ]

1limit(100, 200)
2// LIMIT ? OFFSET ?
3// [ 100, 200 ]

1one()
2// LIMIT ?
3// [ 1 ]

1one(2)
2// LIMIT ? OFFSET ?
3// [ 1, 2 ]

1isOne() // => true

order

1order('id')
2// ORDER BY `id` ASC

1order('updated_at', 'id')
2// ORDER BY `updated_at` ASC, `id` ASC

1order('-updated_at', 'id')
2// ORDER BY `updated_at` DESC, `id` ASC

template

1SQL`SELECT * FROM {user} WHERE {user.age} > ${100}`
2// SELECT * FROM `user` WHERE `user`.`age` > ?
3// [ 100 ]

---

json where

operator:

1{
2  eq: '=',
3  ne: '!=',
4  gte: '>=',
5  gt: '>',
6  lte: '<=',
7  lt: '<',
8  not: 'IS NOT',
9  is: 'IS',
10  like: 'LIKE',
11  notlike: 'NOT LIKE',
12  ilike: 'ILIKE',
13  notilike: 'NOT ILIKE',
14  regexp: 'REGEXP',
15  notregexp: 'NOT REGEXP',
16  in: 'IN', // $in: [1,2,3]
17  notin: 'NOT IN', // $notin: [1,2,3]
18  between: 'BETWEEN', // $between: [1,2]
19  notbetween: 'NOT BETWEEN', // $notbetween: [1,2]
20}

demo:

js:

1select().from('user').where({
2  f1: 'f1',
3  f2: { $gt: 'f2-gt', $lt: 'f2-lt', $in: ['f2-in-1', 'f2-in-2'], $eq: raw('f2-raw') },
4  f3: ['f3-1', 'f3-2'],
5  f4: ['f4'],
6  f5: [],
7  f6: raw('f6'),
8  f7: { $between: ['f7-1', 'f7-2'] },
9  $or: { f8: 'f8', f9: 'f9' },
10  // $or: { f8: 'f8' },
11  // $or: [
12  //   { f8: 'f8' },
13  //   { f9: 'f9', f10: 'f10', $or: { f12: 'f12', f13: 'f13' } },
14  // ],
15  f14: null,
16  f15: { $or: { $eq: 'f15-1', $gt: 'f15-2', $or: { $eq: 16, $gt: 18 } } },
17  f16: new Date(),
18  f17: { f18: 'f17.f18', f19: { f20: { $gt: 'f20' } } },
19  f21: { $quote: 'f22', $raw: 'f21-raw' },
20  f23: { $gt: { $quote: 'f24' } },
21  $and: [
22    { f36: { $ne: 1 } },
23    { f36: { $ne: 2 } },
24  ],
25  f37: {
26    $and: { f38: 38, f39: 39 },
27  },
28  f40: {
29    $and: [
30      { f41: { $ne: 1 } },
31      { f41: { $ne: 2 } },
32    ]
33  },
34})

sql:

1SELECT
2	* 
3FROM
4	`user` 
5WHERE
6	`f1` = ?
7  AND `f2` > ?
8  AND `f2` < ?
9  AND `f2` IN (?, ?)
10  AND `f2` = f2-raw
11  AND `f3` IN (?, ?)
12  AND `f4` = ?
13  AND `f6` = f6
14  AND `f7` BETWEEN ? AND ?
15  AND ( `f8` = ? OR `f9` = ? )
16  AND `f14` IS NULL
17  AND (
18    `f15` = ?
19    OR `f15` > ?
20    OR (
21      `f15` = ?
22      OR `f15` > ?
23    )
24  )
25  AND `f16` = ?
26  AND `f17`.`f18` = ?
27  AND `f17`.`f19`.`f20` > ?
28  AND `f21` = `f22`
29  AND `f21` = f21-raw
30  AND `f23` > `f24`
31  AND ( `f36` != ? AND `f36` != ? ) AND ( `f37`.`f38` = ? AND `f37`.`f39` = ? ) AND ( `f40`.`f41` != ? AND `f40`.`f41` != ? )

params:

1[
2  "f1",
3  "f2-gt",
4  "f2-lt",
5  "f2-in-1",
6  "f2-in-2",
7  "f3-1",
8  "f3-2",
9  "f4",
10  "f7-1",
11  "f7-2",
12  "f8",
13  "f9",
14  "f15-1",
15  "f15-2",
16  16,
17  18,
18  "2021-01-29T05:29:09.629Z",
19  "f17.f18",
20  "f20",
21  1, 2, 38, 39, 1, 2
22]