五、解读Sails之Waterline源代码

sql调试

代码跟踪

因为工作需要调用存储过程来实现对数据库的操作。为此我们需要更加深入的了解Sails是怎么发送Sql指令以及发送了什么样的Sql指令。在Sails里面执行数据库操作的是Waterline(ORM)，前面我们使用Sails的config/datastores做数据库配置的时候，需要先安装一个叫sails-mysql数据库适配器。这个数据库适配器也是开源项目https://github.com/balderdashy/sails-mysql，进入该项目的github可以看到它的依赖库里面是这样的：

"dependencies": {
    "@sailshq/lodash": "^3.10.2",
    "async": "2.0.1",
    "machine": "^15.0.0-21",
    "machinepack-mysql": "^5.0.0",
    "waterline-utils": "^1.3.10"
  },

也就是它操作mysql是通过machinepack-mysql这个库实现的。继续追踪发现这个库也是该公司的，它的依赖库是mysql，是一个nodejs的数据库驱动程序。这一路跟踪，可以捋顺Sails的数据库操作是这样的：

waterline->machinepack-mysql->mysql

跟踪machinepack源代码，有个很有价值的发现：它有一个依赖库是debug，是一个可以通过设置参数来控制 console 输出信息的，在machinepack的lib/send-native-query.js (这个是对waterline 发送原生sql语句的再次封装)发现它的调试信息是通过query参数设置的，代码类似这样：

var debug = require('debug')('query');
.....
debug('Running SQL Query:');
debug('SQL: ' + sql);
debug('Bindings: ' + bindings);
debug('Connection Id: ' + inputs.connection.id);
....

debug库的readme里面是这样说的：The DEBUG environment variable is then used to enable these based on space or comma-delimited names. 结合上述源代码，我们知道只要在环境参数里面设置DEBUG=query就可以在waterline执行sql语句的时候，把相关的sql打印到控制台上。这对我们了解和调试waterline实在是太方便了，很奇怪这么重要的信息为什么帮助文档里面没有记录。

package.json

要开启DEBUG环境变量，需要了解package.json。package.json是用来识别项目并且处理项目的依赖关系的，package.json可以让npm启动项目、运行脚本、安装依赖项，一个项目里面必须要有package.json文件才能用npm安装依赖包。
package.json文件里面的scripts字段中定义的就是npm脚本，它是一个对象实例，里边指定了项目的生命周期各个环节需要执行的命令。key是生命周期中的事件，value是要执行的命令。

{
  // ...
  "scripts": {
    "start": "node app.js"
  }
}

上面代码是package.json文件的一个片段，scripts的每一个属性，对应一段脚本。比如，start命令对应的脚本是node app.js。有了这个定义，在vscode的终端，就可以执行：

npm run start
# 等同于执行
node app.js

npm 脚本的原理非常简单。每当执行npm run，就会自动新建一个 Shell，在这个 Shell 里面执行指定的脚本命令。因此只要是 Shell可以运行的命令，就可以写在 npm 脚本里面。比较特别的是：npm run新建的这个 Shell，会将当前目录的node_modules/.bin子目录加入系统的PATH变量，执行结束后，再将系统的PATH变量恢复原样。这意味着，当前目录的node_modules/.bin子目录里面的所有脚本，都可以直接用脚本名调用，而不必加上路径。比如，当前项目的依赖里面有 Mocha，只要直接写"test": “mocha test"就可以了。而不用写成"test”: “./node_modules/.bin/mocha test”。并且npm脚本还支持通配符：

"lint": "jshint *.js"
"lint": "jshint **/*.js"

上面代码中，*表示任意文件名，**表示任意一层子目录。

script还允许执行多个任务，如果 npm 脚本里面需要执行多个任务，那么需要明确它们的执行顺序：
如果是并行执行（即同时的平行执行），可以使用&符号。

npm run script1.js & npm run script2.js

如果是继发执行（即只有前一个任务成功，才执行下一个任务），可以使用&&符号。

 npm run script1.js && npm run script2.js

综上，我们需要在项目原先的script里面，添加一个新的启动项目dev:“set DEBUG=query && node app.js”，添加后script类似这样：

"scripts": {
    "start": "set NODE_ENV=production && node app.js",//设置开发环境然后执行启动
    "dev": "set DEBUG=query && node app.js",//设置调试参数然后执行启动
    "test": "jest",  //jest测试指令 
  },

package.json 里面不支持用 // 符号写注释，上述代码中的注释是为了表达方便而添加，实际package.json里请删除。

启动调试

之前我们都是用node app.js来启动项目，修改完scripts之后，在终端里面执行npm run dev，启动过程可以看到许多sql语句，诸如：

  query Running SQL Query: +0ms
  query SQL: select * from `user` order by `id` ASC limit ? +1ms //执行的Sql
  query Bindings: 9007199254740991 +0ms//绑定的值就是上面sql语句中的？,这个是mysql库支持的sql参数的写法
  query Connection Id: undefined +0ms
  query Compiled (final) SQL: select * from `user` order by `id` ASC limit ? +0ms
  //Compiled 是对上面sql代码再转义的结果，是machinepack-mysql库的动作，它使得send-native-query.js可以支持用$1,$2做参数定义
  query Running SQL Query: +20ms
  ...

我们还什么都没有做，为什么会有这些sql代码？因为auto-migrating 。开发阶段，我们推荐的迁移配置是alert，这个配置的意思是sails每次启动要检查数据库的表结构和model定义的字段属性是否相同，如果不一样要改成一样。它是如何实现的？

Auto-Migrating

sails的自动迁移操作简单粗暴，分三个步骤：

备份原始数据

针对model上面的所有数据模型（还有一个archive是强制添加的，用于软删除）执行select * from …把所有表的数据备份下来。备份内容保存到一个内存变量中。

删除所有表再重建

执行 DROP TABLE IF EXISTS … 把原有的表删除掉，然后在依据model里面的字段属性设置，重新创建表：CREATE TABLE IF NOT EXISTS…

回写备份数据

前面两个步骤可以确保alert的迁移模式下，model表和数据库的表一致，model有变动也会在sails重新启动之后反映到数据库中。最后一步是把备份在内存变量中的数据回写到数据库中，执行insert… 语句。

这个环节还有一定的纠错功能。比如一些加密字段在数据库里面是以加密后的信息保存的，这个环节会对所有数据进行解密，回写的时候再次加密。我们采用自己的代码加密，加密的结果如果有问题就会导致回写错误而终止执行（数据库中已有数据会因为回写失败而丢失）。
这倒逼我们debug自己的加密算法。我在研究sails的加密算法之后，使用自己的代码加密，然后调用nativequery的时候采用了$1,$2形式的参数，结果waterline对$1,$2进行replace的时候把密文里面的$1符号替换掉了(导致1000条数据里面有30%-40%的加密字段无法auto-migrating 。花费了3个小时才发现这个bug(；′⌒`)，但也因此拯救了我的代码。
这是个非常棒的设计，所以这个迁移模型是我一直推荐开发阶段使用的原因。

sails 采用aes-256-gcm算法加密，加密后的字符串里面用三个$ 把字符串分成4个部分，$符号少一个就会导致解密的时候split出来的数组少了一项，从而导致解密失败，抛出TypeError [ERR_INVALID_ARG_TYPE] 错误消息。

另外如果配置文件里面的迁移模式设置为"drop"，最后一步的数据回写会省略，每次启动sails就会把数据清空，只保留数据结构。

加密库 encrypted-attr

waterline的model都是自带加解密功能的，如果使用model的find，create等功能操作数据库，加解密可以不用关心，直接调用就可以。比如User.findOne({ email: req.body.email }).decrypt(); 现在我们要在存储过程中操作数据，比如在存储过程中insert，那么遇到需要加密的字段，就需要用自己的代码加密。并且这种加密最好是和sails的算法一致，这样确保在其他地方使用sails的时候不会出错（比如自动迁移）。

跟踪waterline源代码的package.json，可以发现它的依赖库里面有一个encrypted-attr ，容易猜测这是一个加密库，waterline的加解密功能就是通过这个库实现的。

"dependencies": {
    "@sailshq/lodash": "^3.10.2",
    "anchor": "^1.2.0",
    "async": "2.6.4",
    "encrypted-attr": "1.0.6",
    "flaverr": "^1.9.2",
    "lodash.issafeinteger": "4.0.4",
    "parley": "^3.3.2",
    "rttc": "^10.0.0-1",
    "waterline-schema": "^1.0.0-20",
    "waterline-utils": "^1.3.7"
  },

aes-256-gcm算法

解读encrypted-attr 加密库源代码可见其采用的加密算法是aes-256-gcm，这是一个标准对称加密算法，其原理并不复杂

这个算法简单来说就是用待加密的字符串和add,iv,tag三个内容组合加密成一个新的字符串。其中

• iv：是一个随机串，它的作用和MD5的“加盐”有些类似，目的是防止同样的明文块，始终加密成同样的密文块
• add：通常由密钥对应Id混合嵌入，所以解密的时候不需要指定密钥id，程序会还原keyId并找到对应密钥
• tag：通常指编码的标记表示
  这样加密后的密文由三个$符合组合了add,iv,payload,tag四部分内容，类似这样：

YWVzLTI1Ni1nY20kJGRlZmF1bHQ=$HDl2IeGoGsmLVP1m$b0fFPehdvxM=$Z3a0hSckioL0bdBVKkaNBg

encrypted-att 的使用

encrypted-att使用分两个步骤，一是传入密钥组成options，一是执行加密/解密

• 加密：

const EncryptedAttributes = require('encrypted-attr');
var EA = EncryptedAttributes(undefined, {
    keys: this.sa.config.models.dataEncryptionKeys,//保存在config/models里面的密钥
    keyId: 'default'//密钥对应的key，加密的时候必须传，解密可以不传因为这个keyId是嵌入在密文里面的
})
let res=EA.encryptAttribute(undefined, tobeEncrypt);//tobeEncrypt是指待加密字符串

• 解密：

const EncryptedAttributes = require('encrypted-attr');
var EA = EncryptedAttributes(undefined, {
    keys: this.sa.config.models.dataEncryptionKeys,//保存在config/models里面的密钥
    //解密不需要传keyId //keyId: 'default'//密钥对应的key，加密的时候必须传，解密可以不传因为这个keyId是嵌入在密文里面的
})
let res=EA.decryptAttribute(undefined, tobeDncrypt);//tobeDncrypt是指待解密的密文

其中出入密钥的这个初始化过程的函数原型如下：
EncryptedAttributes(attributes, options) attributes是可选项，是针对使用整个实例对象加密的用法的，如果只是要进行字符串加密，可以设置为undefined或null,options就是需要传递的密钥，keys是密钥集，keyId是要使用的密钥对应的key，举例如下：

//config/models.js 里面保存的密钥传入encrypted-att之后的option就成了这样：
{
  keys: {
    default: '4lB56JWieHhJISbwG0r+Y9MlPLEyTREu+y+V4ViPasA='
  },
  keyId: 'default'
})

加密的时候必须指定keyId,算法根据指定id到keys集合里面找对应的密钥，这样设计的目的是为了一个系统可以使用多个密钥，也可以在代码里面实现自动定期更换密钥来提高系统安全性。比如，我们可以在config/models.js里面这样设置

dataEncryptionKeys: {
    default: 'aGOSbyFccycxy9q0cj3ARc34rffcvX05V9isCClMy/U=',
    k20224: 'dmppTBxXSlZ3JiyoR63KMOfGk8ngdUQQdc9frnx/gxI=',
    k20231: 'MOHUweqYfOLpx5wNtj5hNTcLpOETbslHZ2mUNfg5yk4=',
    k20232: 'F6NZaIQhoaCgltJ+C3qoXmQgOgFzu0kVQQ/SiA34LVc=',
    k20233: 'JTucnT+/VmThhi98kuD8MyKWma6aOdV5a0DpKAZtWgE=',
    k20234: 'aJE3jvXZf1eC9sPaGlOMZ14mRrst6ybCu0K0qSyE9Sg=',
  },

这里有6个密钥，2022表示年后面的数字表示季度，这种组合可以帮助在代码里面实现根据当前时间自动选择不同密钥来实现密钥的定期更换。（具体可见github上的源代码）

密钥

密钥是可以自己设置的，AES密钥大小可以分别为128位,192位或256位或16字节,24字节或32字节，通常使用32字节来设置密钥。可以利用nodejs的crypto库来产生一个随机的32字节Buffer，这个操作可以直接在终端执行，做法是单击右边“+”号，新建一个shell，然后输入node -p “require(‘crypto’).randomBytes(32)” 指令。每次输入都可以随机产生32个字节的16进制数，操作如下：

encrypted-att要求使用的key要转base64 ,修改上述代码如下，实现 对产生的Buffer 里面的32个字节随机数进行base64编码：

node -p "require('crypto').randomBytes(32).toString('base64')"

执行后可以生成类似这样的key串：

yydFV/wL/+0UMDG9pr+K1ezGSDWK6h0D6z+M9UkVHdY=

多次运行产生多个key，并保存到config/models.js的密钥组里面备用。保存的keyId需要和自己代码里面的算法相呼应。

sql转义 sqlstring

不管是哪种数据库，只要支持Sql查询语法的，都需要一些特殊的符号来保护诸如table,field等名称，确保它们不会和Sql语法里面的关键词起冲突。比如我们不能这样创建表：

CREATE TABLE TABLE  (  ...) ...

这种试图创建一个名称为TABLE的table一定会引发Sql语法错误。但是如果这样写：

CREATE TABLE `TABLE`  (  ...) ...

在my-sql是可以通过的（在ms-sql 采用 [ ] 来替代` `）。那么在代码里面，我们传递给my-sql或mariaDB的时候，也需要把表名称，字段名称，以及？，boolean类型等进行转义。
my-sql库采用 sqlstring库来完成这个工作（这个库也是同一个组织创建和维护），我们需要自己写代码执行原生Sql查询或是调用Sql存储过程，那就需要采用这个库来对我们传入的参数进行转义，waterline有封装这样的功能：

var rawResult = await datastore.sendNativeQuery(sql, valuesToEscape);

其中sql是模版，支持用$1, $2 替代变量的语法。valuesToEscape是需要转义的变量，数组变量。waterline会对valuesToEscape应用转义处理。
但是这个还不够灵活，有时候我们需要自己调用转义功能。sails依赖my-sql，而my-sql依赖sqlstring，这就意味着我们的系统里面是有sqlstring的，我们可以直接使用。

const SqlString = require('sqlstring');

SqlString.escapeId(tableName);//这个功能把表名称，字段名称加上` `进行转义
SqlString.escape(value);//这个功能对变量转义，不同类型变量不一样，比如boolean会转bit Date类型会自动转string

更多信息可以查询：https://github.com/mysqljs/sqlstring

日期处理

三种方式比较

日期类型是一个避不开的话题，在数据库里面怎么表示一个日期类型的数据？number，datetime，string 三种方式都有人使用。它们各有自己的优点又各有问题，因此日期类型处理一直是一个比较纠结的点。只有最适合的选择没有最好的选择。正因为这样，我们必须捋清楚这三种做法，唯此我们才能做出最适合我们的选择。

• number 对 bigint
  程序代码里面的实例采用number类型，把日期时间数据转换成数值（时间戳）。这个转换之后的数值因为精确到毫秒，所以需要13位，数据库方面int类型和c++语言一样通常是4个字节的，能表示最大数是$2^{31}$-1=2147483647，这个不够位，需要采用bigint类型。
  • 优点：这个做法的好处是显而易见的，也是waterline默认采用的做法。代码方面采用number可以方便的对日期进行运算和转换（my-sql或mariaDB底层保存日期类型也是数值类型）运算快，数据库上面也不算浪费存储空间（bigint也就8个字节）。
  • 缺点：直接查询不直观，对于需要进入数据库管理系统进行查询的运营人员不友好。
• string 对 datetime
  程序代码里面的实例采用string类型，把日期时间数据转换成当地时间。数据库方面采用datetime类型。
  • 优点：直观，利于运营维护，数据库方面占用空间小（4-5个字节），日期运算方便。
  • 缺点：程序代码方面需要转换。由于my-sql或mariaDB对日期时间字段类型的定义也比较混乱，需要对数据库了解更多一些。转换之后的插入语句容易出错，数据库适配器查询出来的日期类型通常是零时区的，还需要转当地时间。
• string 对 string
  程序代码里面的实例采用string类型，数据库方面也采用string类型。
  • 优点：直观，利于运营维护，程序代码和数据库之间无需转换，可以直接插入和查询。
  • 缺点：数据库方面要进行日期运算比较麻烦，数据格式要求统一才能进行查询比较。日期时间加减不方便。数据库方面占用空间比较大（需要25个字节左右）

mariaDB（或my-sql）中的日期时间

mariaDB数据库里面可以用来表示日期时间的类型有两种，一是TIMESTAMP 需要 4 个字节，一是DATETIME 需要 5 个字节。默认情况下它们都是精确到秒的，如果需要精确到毫秒，都需要额外字节存储。比如TIMESTAMP(3)则可以保存类似这样的数据：2022-12-03 01:16:48.908 最大可以设置精确到小数点后面6位数。
TIMESTAMP和DATETIME不一样的地方在于

• TIMESTAMP 值范围从 1970-01-01 00:00:01 UTC 到 2038-01-19 03:14:07 UTC
• DATETIME 值范围从1000-01-01 00:00:00 到 9999-12-31 23:59:59
• TIMESTAMP 以 UTC 值存储。DATETIME 值按原样存储，没有时区。如果设置环境变量里面的time_zone值，对TIMESTAMP有影响，对DATETIME没有影响。
  需要更多信息，请参考https://dev.mysql.com/doc/refman/8.0/en/datetime.html

string 对 datetime

综合以上信息，我们采取比较折中的解决方案，也就是string 对 datetime，在sails方面我们采用string类型（sails基本数据类型只有这几个：string，number，boolean，json，ref），在数据库方面采用DATETIME(3) 类型。这样我们可以修改config/models里面的设计如下：（我们在这个地方设置每个表自动添加的字段）

attributes: {    
    createdAt: { type: 'string', autoCreatedAt: true, columnType: 'DATETIME(3) COMMENT "创建时间"' },
    updatedAt: { type: 'number', autoUpdatedAt: true },
    id: { type: 'number', autoIncrement: true, },    
  },

以上代码中columnType用于指定数据库字段，如果没有指定，自动根据type类型转换成默认数据库类型，需要设置数据库字段的长度，可以写在括号里面比如varchar(300)，也可以通过comment指定字段备注，方便数据库运维人员进行管理。
设置完之后，我们可以尝试运行插入代码，在api/controllers/UserController.ts里面create函数代码类似这样：

....
export async function retrieve(req: Api.SailsRequest, res: Api.Response, next: Function): Promise<any> {
let User = <UserModel.UserInstance>req._sails.models.user;
  try {
    let rows = await User.create(req.body).fetch();
    res.status(200).send(rows);
  } catch (error) {
    res.serverError(error);
  }
}
....

重启sails ：在终端执行 npm run dev (根据我们上一个节点讲述的对script的改进，用这个方式添加Sql调试）
使用postman 提交测试数据之后，sails爆出错误如下：

Compiled (final) SQL: insert into `user` (`createdAt`, `email`, `nickname`, `password`, `updatedAt`) values (?, ?, ?, ?, ?) +0ms   
     
error: Sending 500 ("Server Error") response: 
 AdapterError: Unexpected error from database adapter: ER_TRUNCATED_WRONG_VALUE: Incorrect datetime value: '2022-12-03T07:43:26.121Z' for column `admindata`.`user`.`createdAt` at row 1

由于我们开启query的调试模式，我们可以看到当前执行的sql语句是这样的：

insert into `user` (`createdAt`, `email`, `nickname`, `password`, `updatedAt`) values (?, ?, ?, ?, ?) +1ms
query Bindings: 2022-12-03T07:43:26.121Z,1670053406031@gmail.com,wQezb,YWVzLTI1Ni1nY20kJGRlZmF1bHQ=$gncgLcjt9m7PlAEU$wTHRaSXn0JE=$c9Gf43tPJZbagJbGs5H0wA,1670053406121 +0ms

这里我们观察到绑定的数据是正确的，是我们从postman提交过来的数据，代码加密也成功了，报告的错误是插入datetime数据“‘2022-12-03T07:43:26.121Z’”这个有问题。
mysql官方文档有说datetime数据类型可以接受的格式是’YYYY-MM-DD’ （原文：retrieves and displays DATE values in ‘YYYY-MM-DD’ format. ）我们的waterline自动添加的日期是这样：

'2022-12-03T07:43:26.121Z'

这个字符串里面包含T和Z，T表示时间，Z表示这个数据是零时区数据，转换成本地时间可以自己加上时区。但是这种格式my-sql（mariaDB）并不支持。
去修改waterline是不明智的，这样我们就不能享受它后面的升级了，我们只能修改数据库或是自己处理插入代码。

自己处理 Insert 语句

如果我们需要自己解决插入语句的，推荐的做法是通过存储过程来解决。这个我们保留在下一篇博客里面详细解说。
在开发阶段，我们需要用到auto-migrating 自动迁移功能，哪怕我们自己插入数据，每次重启Sails 自动迁移都是需要回写数据的，如果不回写（设置drop模式）每次重启我们的模拟数据就会丢失。所以我们需要修改数据库，来确保每次重启Sails（开发阶段一天要重启太多次了）的时候自动迁移可以成功回写数据。
（正式运营的时候不需要修改） 因为正式运营的时候肯定是要关闭自动迁移的。

修改数据库 sql_mode

观察到错误提示里面有一个ER_TRUNCATED_WRONG_VALUE，这表明错误来源是非法字段值，而对非法字段值的判断和sql的运行模式有关系，比如有些字段没有设置默认值，传入null也会导致错误。sql_model是一个全局变量，它的默认运行模式是严格模式的STRICT_TRANS_TABLES，开发阶段可以关闭此模式，实现带Z的日期类型和无默认值的null类型写入。在sql中执行以下代码：

set @@global.sql_mode ='ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

这个指令如果使用HeidiSQL可以直接在一个新建查询里面运行就可以。这个操作允许my-sql重启之前关闭严格模式（重启后会恢复原来的模式），如果需要永久保存这个模式，可以通过修改my.ini实现，否则重启之后还是严格模式。在mariaDB中如果需要重启后还是能够保留非严格模式，需要在my.ini的[mysqld] 中添加sql_mode，添加完的代码大致是这样的：

[mysqld]
datadir=C:/Program Files/MariaDB 10.10/data
port=3306
innodb_buffer_pool_size=3069M
character-set-server=utf8mb4
sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[client]
port=3306
plugin-dir=C:\Program Files\MariaDB 10.10/lib/plugin

mariaDB 的my.ini默认位置为：C:\Program Files\MariaDB 10.10\data

设置完，重启sails，再执行postman测试，数据可以添加了。

{
        "createdAt": "2022-12-03T00:21:29.264Z",
        "updatedAt": 1670055689264,
        "id": 1,
        "email": "1670055689163@gmail.com",
        "nickname": "kbJGQ"
    }

自动迁移的零时区问题

通过修改sql_mode已经可以正常插入数据了。由于waterline在每次启动的自动迁移会把旧数据select出来，通过控制台的sql调试，我们可以看到auto-migrating select 语句会把数据库里面的数据返回成一个带Z的字符串，比如数据库里面是这样的2022-12-03 16:21:29.264，select返回的数据就变成这样：2022-12-03T08:21:29.264Z 这个是因为Select 出来的数据转成零时区的日期时间（和我们所处东八区+8:00相差8小时），auto-migrating回写的时候，数据库本来是不支持这种格式的，我们强制开启之后，它并不能识别“Z”这种零时区写法，只是强制转换成DateTime了（DateTime类型没有时区数据）于是就变成了2022-12-03 08:21:29.264 这样迁移一次，日期时间就减少8个小时。
这是sails的一个bug，它把日期转换成带Z的字符串的做法也是出于善意，但是反而造成了困扰。我们可以提交这个issue给他们，但是要修改估计很久。们也可以考虑采用TIMESTAMP 但是这个的数据范围太小了，如果我们的数据需要保存超过2038年，那就麻烦了。
好在并不影响我们，因为我们只是在开发期间使用auto-migrating。
同时它也提醒我们，在我们自己设计存储过程处理相关代码的时候，要把select语句从数据库的datetime字段返回的string中的零时区日期时间转换成本地日期时间。