nodejs之knex模块从安装到使用

knex.js是bookshelf框架的基础,其核心是query builder。这篇文章参考自Knex.js官网,翻译并总结了该框架的方法。

0 安装

1 初始化

把数据库类型和连接相关的参数配置好之后,才可以正确的连接到数据库,connection的信息可以写到config文件中。

2 概述

knex框架的方法大致分为几类:

  • 操作table的方法,属于Schema Builder,对应create、drop、alter等
  • 操作column的方法,属于Schema Builder,如设置键的类型,设置主键、外键等
  • 执行SQL请求的方法,属于Query Builder,对应select、 insert、update、delete等
  • 其他方法

3 Schema Builder

3.1 操作table的方法

这一部分的方法主要用于创建数据表、删除数据表或修改数据表的结构,即对应SQL中的create、drop、alter。这些方法都是在schema对象上调用。

3.1.1 withSchema

用于指定schema名

3.1.2 createTable

创建新表

例子:

3.1.3 createTableIfNotExists

先判断表是否存在,若不存在创建新表
类似于:

3.1.4 renameTable

重命名from表为to

例子:

3.1.5 dropTable

删除表

3.1.6 dropTableIfExists

先判断表是否存在,若存在删除表

3.1.7 hasTable

判断表是否存在,返回布尔值

例子:

3.1.8 hasColumn

判断列名是否存在,用法和hasTable类似

3.1.9 table/alterTable

用callback的内容修改tableName对应的表

例子:

3.1.10 raw

用于执行原始的SQL请求

例子:

3.2 操作column的方法

这一部分的方法,多用于添加、删除、修改字段的名称或类型、设置主键外键唯一键等。这些方法都是在table对象上调用。

3.2.1 添加字段

方法 说明
increments(name) 自增列,会被用作主键
integer(name) int类型
bigInteger(name) bigInt类型
text(name,[textType]) 文本类型,MySQL下有text,mediumtext,longtext可选
string(name,[length]) 字符串类型,默认长度255字符
float(name,[precision],[scale]) 浮点类型,默认为8和2
decimal(column,[precision],[scale]) 十进制数类型,默认为8和2
boolean(name) 布尔型
date(name) date类型
dateTime(name) dateTime类型
time(name) time类型
timestamp(name,[standard]) timestamp类型,PostgreSQL下默认为timestampz
timestamps() 添加created_atupdated_at字段
binary(name,[length]) 二进制数类型,MySQL下可选择长度
enu(col,values) 枚举类型,第二个参数需要是数组
json(name) json类型,使用pgSQL内建的JSON格式,需要调用JSON.stringify()方法强制转换成JSON格式,在不支持json的数据库中会变成字符串类型
jsonb(name) jsonb类型,可以使用本地的json格式
uuid(name) uuid类型,使用pgSQL内建的uuid类型,不支持的数据库会变成36位的字符串
specificType(column,value) 添加不支持的类型

3.2.2 删改字段

方法 说明
dropColumn(name) 通过name删除指定的字段
dropColumns(*names) 删除多个字段
dropTimestamps() 删除时间戳字段
renameColumn(from, to) 重命名该字段

3.2.3 添加配置信息

方法 说明
comment(value) 添加注释
engine(val) 设置表的引擎,只对MySQL有效,且只能在创建表时调用
charset(val) 设置表的字符集,只对MySQL有效,且只能在创建表时调用
collate(val) 设置表的简介,只对MySQL有效,且只能在创建表时调用
inherits(val) 设置该表的父表,只对PostgreSQL有效,且只能在创建表时调用

3.2.4 添加特殊键

方法 说明
index(columns, [indexName], [indexType]) 在columns上添加索引,indexName默认为column名,indexType类型在pgsql下可选
unique(columns) 添加唯一键
foreign(columns) 将已存在的键设置为外键,和references搭配使用

例子:

3.2.5 删除特殊键

方法 说明
dropIndex(columns, [indexName]) 删除索引
ropUnique(columns, [indexName]) 删除唯一键
dropForeign(columns, [foreignKeyName]) 删除外键
dropPrimary([constraintName]) 删除主键,默认为tablename_pkey

3.2.6 链式操作

有的方法可以进行链式操作,通常是在定义字段的同时,链在末尾完成功能。这些方法都是在column对象上调用。

方法 说明
index([indexName], [indexType]) 将该column设为索引
primary([constraintName]) 将该column设为主键,如果传入多个参数,则设置为联合主键
unique() 将该column设为唯一键
references(column) 设置外键所引用的表和字段,详见下方的例子
inTable(table) 设置外键所在的表的表名,详见下方的例子
onDelete(command) 设置运行onDelete时的SQL命令
onUpdate(command) 设置运行onUpdate时的SQL命令
defaultTo(value) 在插入时设置该column的默认值
unsigned() 设置该column为无符号整数
notNullable() 在创建column的时候设置该字段不可为空
nullable() 显式设置该column可以为空,默认都是可以为空
first() 将该column作为表的第一列,只在MySQL aliter表的时候有效
after(field) 将该column插入到给定参数之后,只在MySQL aliter表的时候有效
comment(value) 为该column添加注释
collate(collation) 对该column添加校对器,只对MySQL有效

例子:

4 Query Builder

Query Builder需要指明query操作对应的table或直接调用knex对象的方法。
整个构造过程包括,构造sql语句,调用interface方法(接口方法用于执行语句或者转换为字符串打印出来)。

4.1 基础方法

常规增删改查,运算操作都有对应的方法。

4.1.1 查询

select([*columns])
column(columns)
from([tableName])

4.1.2 插入

insert(data, [returning])
returning(column) /returning([column1, column2, ...])

4.1.3 修改

update(data, [returning]) / update(key, value, [returning])

4.1.4 删除

del()

4.1.5 运算

方法 说明
count(column) countDistinct
min(column)
max(column)
sum(column) sumDistinct
avg(column)
increment(column, amount)
decrement(column, amount)

4.2 where方法

和SQL语法一样,where用于设置一些约束条件。如果where的内容不存在,则会抛出错误,所以执行之前要确保where的内容是存在的。

4.2.1 where(~mixed~)

该方法可以接收多种类型的参数:
a 对象型

b 键值对型

c 操作符型
常用于处理不等关系,包含关系

d 回调函数型
处理复杂查询(嵌套查询、并列查询)的时候更简洁

e 链型
常用于处理复杂查询或条件较多的情况

如果orWhere中有多个条件,则这些条件之间是and的关系

4.2.2 whereNot(~mixed~)

可接收的参数种类与where一样,用法也类似。
逻辑上需要注意转换就可以了。

whereNot不能用in或者between类型的子查询,需要用not in或者not between替代。
whereNot('id','in',subquery)这种写法是错误的;
where('id','not in',subquery)这种写法是正确的。

4.2.3 whereIn(column, array|callback|builder)

whereIn('id',subquery)可以用于替代where('id','in',subquery)

4.2.4 whereNotIn(column, array|callback|builder)

whereNotIn('id',subquery)可以用于替代where('id','not in',subquery)

4.2.5 whereNull(column)

4.2.6 whereNotNull(column)

作用和whereNull相反

4.2.7 whereExists(builder | callback)

4.2.8 whereNotExists(builder | callback)

作用和whereExists相反

4.2.9 whereBetween(column, range)

4.2.10 whereNotBetween(column, range)

作用和whereBetween相反

4.2.11 whereRaw(query, [bindings])

执行原始的SQL语句

4.3 having方法

having(column, operator, value)

Adds a having clause to the query.

knex(‘users’)
.groupBy(‘count’)
.orderBy(‘name’, ‘desc’)
.having(‘count’, ‘>’, 100)
Outputs:
select * from “users” group by “count” having “count” > 100 order by “name” desc

havingRaw(column, operator, value)

Adds a havingRaw clause to the query.

groupBy(*names)

Adds a group by clause to the query.

groupBy(sql)

Adds a raw group by clause to the query.

orderBy(column, [direction])

Adds an order by clause to the query.

orderByRaw(sql)

Adds an order by raw clause to the query.

4.4 join方法

Several methods are provided which assist in building joins.

4.4.1 join(table, first, [operator], second)

The join builder can be used to specify joins between tables, with the first argument being the joining table, the next three arguments being the first join column, the join operator and the second join column, respectively.

For grouped joins, specify a function as the second argument for the join query, and use on with orOn or andOn to create joins that are grouped with parentheses.

For nested join statements, specify a function as first argument of on, orOn or andOn

It is also possible to use an object to represent the join syntax.

If you need to use a literal value (string, number, or boolean) in a join instead of a column, use knex.raw.

innerJoin(column, ~mixed~)

leftJoin(column, ~mixed~)

leftOuterJoin(column, ~mixed~)

rightJoin(column, ~mixed~)

rightOuterJoin(column, ~mixed~)

outerJoin(column, ~mixed~)

fullOuterJoin(column, ~mixed~)

crossJoin(column, ~mixed~)

joinRaw(sql, [bindings])

4.5 其他方法

除了以上归类的方法之外,还有一些常用的方法。

4.5.1 timeout(ms, options={cancel: boolean})

该方法只有MySQL可用。
为SQL操作设定一个计时器,单位是毫秒,超时后抛出异常TimeoutError
cancel为true表示超时就取消请求。

4.5.2 as(name)

用于给子查询命名,提高可读性。

4.5.3 with(alias, function|raw)

该方法在PostgreSQL, Oracle, SQLite3和MSSQL上都可用。
也用于给子查询命名。

4.5.4 withSchema([schemaName])

指明数据表属于的schema。

4.5.5

distinct()
offset(value)
limit(value)
union([*queries], [wrap])
unionAll(query)
truncate()
pluck(id)
first([columns])
clone()
modify(fn, *arguments)
columnInfo([columnName])
debug([enabled])
connection(dbConnection)
options()