# js的sql语句生成器

**Repository Path**: haua/h-sql

## Basic Information

- **Project Name**: js的sql语句生成器
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: ISC
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 4
- **Forks**: 2
- **Created**: 2020-12-04
- **Last Updated**: 2023-02-03

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# 简介

[![覆盖率][coveralls-image]][coveralls-url]
![typescript][typescript-image]

> [更新速度最快的接口文档地址（gitee）](https://gitee.com/haua/h-sql#%E7%AE%80%E4%BB%8B)

这是一个根据配置生成`sql`的工具，仅生成`sql`语句，不会执行。

生成的 `sql` 中参数均为`?`，并且在 `values` 中返回这些问号对应的值。

该工具返回的值类型：

```ts
export interface HSqlObjectT {
  // 例 SELECT * FROM `test` WHERE `col` = ?
  // ps. 该值可能为空字符串，通常是因为入参不符合要求，或触发安全检查
  sql: string

  // 以上sql中问号依次对应的值
  values: (string | number)[]

  // 用于本工具判断该sql对象是否由本工具生成，建议不要加工或去除，mysql库的query函数会忽略它
  symbol: string[]
}
```

它可以直接用于`mysql`库的`query`函数第一个入参，参考文档： https://www.npmjs.com/package/mysql#performing-queries

## 安装

```shell
npm i -S @haua/sql
# or
yarn add @haua/sql
```

## 使用

```ts
import {select, del, insert, update} from '@haua/sql'
const sql = select('test')
console.log(sql)
// {
//     sql: 'SELECT * FROM `test`',
//     values: []
// }
```

## 对象 key 的写法

key 可以用`#`分割成 3 段

> - 例：`col1#function#foo`
> - 第一段是表的列名，可为空字符串
> - 第二段是预留字，可为空字符串
> - 第三段仅用于让相同 key 可共存在一个 js 对象中，可填任意字符

|  预留字  |               可用区域               | 说明                                          |
| :------: | :----------------------------------: | :-------------------------------------------- |
| function | select 的 where | 它的值为 SQL 函数，值可为字符串，或字符串数组 |
| function | select 的 order |  |
| function | update 的 data | 它的值为 SQL 函数，值可为字符串，或字符串数组 |
|   sql    |                                      |                                               |

## select 用法

```ts
export declare function select(
  // 表名
  table: string,
  // where 内的语句，还有部分特殊语句也是在这写，如`LEFT JOIN`
  where: Record<string, any> | string | [string, (number | string)[]],
  // 查询的列名
  col?: string | string[],
  // ORDER BY
  order?: null | {
    [k: string]: 'DESC' | 'ASC' | string
  },
  // LIMIT 不建议和OFFSET合写
  limit?: number,
  // OFFSET 一般是分页时使用
  offset?: number,
  // 排序
  groupBy?: string
): HSqlObjectT
```

### 返回指定字段

可以给查询出来的字段设置别名

```js
select('test', {}, ['title', 'count(*) as total', 'id as testId'])
// {
//     sql: 'SELECT `title`,count(*) as `total`,`id` as `testId` FROM `test`',
//     values: []
// }

select('test', {}, 'title')
// {
//     sql: 'SELECT `title` FROM `test`',
//     values: []
// }

select('test', {}, 'count(*) as total')
// {
//     sql: 'SELECT count(*) as `total` FROM `test`',
//     values: []
// }

select('test', {}, '*')
// {
//     sql: 'SELECT * FROM `test`',
//     values: []
// }
```

### 排序 & GROUP BY

支持指定字段排序，也支持排序函数。排序字段支持表别名。

```js
select(
  'test',
  {
    AS: 't1',
    a: 'b',
    b: 'a',
  },
  ['count(*) as num', 'id as testId'],
  {
    't1.col1': 'DESC',
    num: 'DESC',
    b: 'ASC',
    '#function': 'RAND()',
    '#function#': 'FIELD(id,1,2,3)',
  },
  10,
  20,
  'col1'
)

// {
//     sql: 'SELECT count(*) as `num`,`id` as `testId` FROM `test` AS t1 WHERE `a` = ? AND `b` = ? GROUP BY col1 ORDER BY `t1`.`col1` DESC,`num` DESC,`b` ASC,RAND(),FIELD(id,1,2,3) LIMIT 10 OFFSET 20',
//     values: [ 'b', 'a' ]
// }
```

### 当 where 是对象（推荐）

简单示例：

```js
select('test', {
  a: 'b',
  b: 1,
  c: undefined,
  d: null,
  e: {
    '>=': new Date(1606472694938),
  },
  f: true,
  g: false,
})

// {
//     sql: 'SELECT * FROM `test` WHERE `a` = ? AND `b` = ? AND `d` IS NULL AND `e` >= "2020-11-27 18:24:54" AND `f` = ? AND `g` = ?',
//     values: ["b",1,1,0]
// }
```

#### 对比符号的使用

`=`与`IN`可省略，其他符号需要显式注明（如 `>`,`LIKE`,`IS NOT`,`NOT IN` ），支持所有 MySQL 的符号

```js
select('test', {
  a: 1, // = 号可简写
  b: {
    // 同一个字段的可合并写
    '<': 10,
    '>': 1,
    '!': [5],
  },
  c: {
    '!': 5,
  },
  d: [1], // IN 号可简写
})

// {
//   sql: 'SELECT * FROM `test` WHERE `a` = ? AND (`b` < ? AND `b` > ? AND `b` NOT IN (?)) AND `c` != ? AND `d` IN (?)',
//   values: [ 1, 10, 1, 5, 5, 1 ],
// }
```

有些符号可缩写：

| 缩写的符号 | 转为 SQL 后的符号 | 备注           |
| :--------: | :---------------: | :------------- |
|     !      |        !=         | 当值不为数组时 |
|     !      |      NOT IN       | 当值为数组时   |
|    NOT     |      IS NOT       |                |

#### 相同 key 可重复出现

js 对象不允许出现多个相同的 key，可增加#号，#号后面的内容可以随便写，但以后可能会增加预留字，尽量使用双#号

```js
select('test', {
  a: {
    '>': 1,
  },
  'a##': {
    '<': 10,
  },
  'a##666': {
    '!': 5,
  },
})

// {
//   sql: 'SELECT * FROM `test` WHERE `a` > ? AND `a` < ? AND `a` != ?',
//   values: [ 1, 10, 5 ],
// }
```

#### OR/AND 的使用

不同条件之间的关系默认是 `AND`，可通过以下方法修改为`OR`

```js
select('test', {
  g: 1,
  AND: {
    a: 2,
    b: 3,
  },
  f: 4,
  OR: {
    a: 5,
    b: 6,
    AND: {
      a: 7,
      b: 8,
      OR: {
        a: 9,
        b: 10,
      },
    },
  },
  e: 11,
  'OR##': {
    a: 12,
    b: 13,
    c: [14],
  },
  d: 15,
})

// {
//   sql: 'SELECT * FROM `test` WHERE `g` = ? AND (`a` = ? AND `b` = ?) AND `f` = ? AND (`a` = ? OR `b` = ? OR (`a` = ? AND `b` = ? AND (`a` = ? OR `b` = ?))) AND `e` = ? AND (`a` = ? OR `b` = ? OR `c` IN (?)) AND `d` = ?',
//   values: [1,2,3,4,5,6,7,8,9,10,11,12,13,14,15],
// }
```

> 根条件之间的关系默认是 AND，如果想要 OR，则在根写一个 OR，所有条件都写在这个 OR 内：

```js
select('test', {
  OR: {
    a: 'b',
    b: 'A',
    c: 'A',
    d: 'A',
  },
})

// {
//   sql: 'SELECT * FROM `test` WHERE (`a` = ? OR `b` = ? OR `c` = ? OR `d` = ?)',
//   values: ["b","A","A","A"],
// }
```

ps. 根部如果有两个 OR，则两个 OR 之间的关系是 AND

#### left join 用法

```js
select(
  'test',
  {
    'LEFT JOIN': 'test2 as t2',
    't2.a': 'text1',
    b: 1,
    c: undefined,
    d: null,
    e: {
      '>=': new Date(1606472694938),
    },
  },
  '*'
)

// {
//   sql: 'SELECT * FROM `test` LEFT JOIN test2 as t2 WHERE `t2`.`a` = ? AND `test`.`b` = ? AND `test`.`d` IS NULL AND `test`.`e` >= "2020-11-27 18:24:54"',
//   values: ["text1",1],
// }
```

#### 设置表别名

常与 left join 一起使用

```js
select(
  'test',
  {
    AS: 't1',
  },
  ['count(*) as num', 'id as testId'],
  {
    't1.col1': 'DESC',
  }
)

// {
//   sql: 'SELECT * FROM `test` LEFT JOIN test2 as t2 WHERE `t2`.`a` = ? AND `test`.`b` = ? AND `test`.`d` IS NULL AND `test`.`e` >= "2020-11-27 18:24:54"',
//   values: ["text1",1],
// }
```

#### where 中含 function

当 key 为 `#function` 或其后是`#+任意字符` 时，该行被认为是函数，且不做任何检查。

> 为防止 sql 注入，推荐 value 为`(string|number)[]`，数组第一个字符串中的`?`将被依次替换为数组后面的参数

```js
select('test', {
  '#function': "find_in_set('4',col1)",
  '#function#2': ['find_in_set(?,col2)', '666'], // 推荐传入此格式的 value，以防止sql注入
  b: 'A',
})

// {
//   sql: 'SELECT * FROM `test` WHERE find_in_set('4',col1) AND find_in_set(?,col2) AND `b` = ?',
//   values: [ '666', 'A' ],
// }
```

#### 子查询

在一条 sql 语句中包含另一条或多条 sql 语句

只需要把 select 的返回值，直接作为 where 中的 value 即可，注意，如果 key 与 value 之间的关系是 IN，则必须显式声明，因为 value 是另一条 sql 语句，无法判断它的返回值只有一个还是多个

ps. select 返回值中有个字段叫 symbol，请勿修改其值，否则 hsql 无法判断该 value 是不是 sql。

```js
select('test', {
  col1: {
    IN: select(
      'test2',
      {
        col2: {
          IN: select(
            'test3',
            {
              type: [1, 2],
              name: {
                like: `%666%`,
              },
            },
            'foo_id'
          ),
        },
      },
      'test_id'
    ),
  },
})

// {
//   sql: 'SELECT * FROM `test` WHERE `col1` IN (SELECT `test_id` FROM `test2` WHERE `col2` IN (SELECT `foo_id` FROM `test3` WHERE `type` IN (?,?) AND `name` like ?))',
//   values: [ '666', 'A' ],
// }
```

### 当 where 是数组

```js
select('test', ['`a` = ? AND `b` = ?', ['b', 'a']])

// {
//     sql: 'SELECT * FROM `test` WHERE `a` = ? AND `b` = ?',
//     values: ["b","a"]
// }
```

### 当 where 是字符串（不推荐）

```js
select('test', '`a` = 1 AND `b` = 2')

// {
//     sql: 'SELECT * FROM `test` WHERE `a` = 1 AND `b` = 2'
// }
```

## insert 用法

```ts
export declare function insert(
  // 表名
  table: string,
  // 要插入的数据，可为复数
  data: ObjectT | ObjectT[]
): HSqlObjectT
```

### 基础用法

```ts
insert('table1', {
  col1: '列1',
  col2: '列2',
  col3: null,
})

// {
//   sql: 'INSERT INTO `table1` (`col1`,`col2`,`col3`) VALUES (?,?,DEFAULT)',
//   values: ['列1', '列2']
// }
```

### 批量插入

```ts
insert('table1', [
  {
    col1: '列1',
    col2: '列2',
  },
  {
    col1: '列3',
    col2: '列4',
  },
])

// {
//   sql: 'INSERT INTO `table1` (`col1`,`col2`) VALUES (?,?),(?,?)',
//   values: ['列1', '列2', '列3', '列4']
// }
```

## del 用法

```ts
export declare function del(
  // 表名
  table: string,
  // 与 select 方法的同名入参完全相同
  where: Record<string, any> | string | [string, (number | string)[]],
  // 可设定最多删除行数，默认为1，设为0则不限制条数
  limit?: number,
  // 安全检查项：是否允许where条件为空，默认为false不允许。如果不允许where条件为空，而where条件又是空，则返回的sql为空字符串
  allowEmpty?: boolean
): HSqlObjectT
```

### 基础用法

```ts
del('table1', {
  col1: '列1',
  col2: '列2',
})

// {
//   sql: 'DELETE FROM `table1` WHERE `col1` = ? AND `col2` = ? LIMIT 1'
//   values: ['列1', '列2']
// }
```

### 无效用法

```ts
del('table1', {}, 0)

// {
//   sql: ''
//   values: []
// }
```

## update 用法

```ts
export declare function update(
  // 表名
  table: string,
  // 与 select 方法的同名入参完全相同
  where: Record<string, any> | string | [string, (number | string)[]],
  // 要修改的字段
  data: any,
  // 可设定最多删除行数，默认为1，设为0则不限制条数
  limit?: number,
  // 安全检查项：是否允许where条件为空，默认为false不允许。如果不允许where条件为空，而where条件又是空，则返回的sql为空字符串
  allowEmpty?: boolean
): HSqlObjectT
```

### 基础用法

```ts
update(
  'test',
  {
    a: '1',
  },
  {
    b: '666',
    c: {
      '+': 6,
    },
    'expire_time#function': 'DATE_ADD(expire_time, INTERVAL 1 MONTH)',
    '#sql#666': ['expire_time = DATE_ADD(expire_time, INTERVAL ? MONTH)', [1]],
  }
)

// {
//   sql: 'UPDATE `test` SET `b` = ?,`c` = `c` + ?,`expire_time` = DATE_ADD(expire_time, INTERVAL 1 MONTH),expire_time = DATE_ADD(expire_time, INTERVAL ? MONTH) where `a` = ? LIMIT 1',
//   values: ["666",6,1,"1"]
// }
```

# 下一版本

- `add` where 的对比符号可以直接在 key 中第二段声明
- `add` update data 的操作符号可以直接在 key 中第二段声明
- `add` sort 的 function 可以为数组，提升 sql 注入防御力
- `add` update data 的 function 可以为数组，提升 sql 注入防御力
- `add` ts 中支持设置表模型的 type/interface

[typescript-image]: https://img.shields.io/badge/TypeScript-v3.9.7-blue
[coveralls-image]: https://img.shields.io/badge/coverage-90%25-green
[coveralls-url]: https://haua.gitee.io/h-sql/