Chemmy's Blog

nodejs-ORM 操作数据库中间件waterline的使用 - 晓晨Master - 博客园

Excerpt

waterline和Sails.js同一团队开发，支持几乎所有的主流数据库,是nodejs下一款非常强大的orm，可以显著提升开发效率 一.waterline支持的数据库 二.waterline的配置 Waterline 之所以可以使用一样的代码来操作多种数据库，奥妙在于其适配器。在进行配置的时候，

---

waterline和Sails.js同一团队开发，支持几乎所有的主流数据库,是nodejs下一款非常强大的orm，可以显著提升开发效率#

一.waterline支持的数据库

二.waterline的配置

Waterline 之所以可以使用一样的代码来操作多种数据库，奥妙在于其适配器。在进行配置的时候，需要设置两方面的内容，一是具体使用哪些适配器，二是建立数据库连接的时候，使用哪个适配器。下面是使用 MongoDB/Mysql 的适配器创建一个数据库连接的配置

MongoDB:

<span> 1</span> <span>var</span> mongoAdapter = require('sails-mongo'<span>);
</span><span> 2</span> <span>var</span> wlconfig =<span> {
</span><span> 3</span> <span>  adapters: {
</span><span> 4</span>     'default'<span>: mongoAdapter,
</span><span> 5</span>     'mongo'<span>: mongoAdapter
</span><span> 6</span> <span>  },
</span><span> 7</span> <span>  connections: {
</span><span> 8</span>     'mongo'<span>: {
</span><span> 9</span>       <span>//</span><span> adapters 中的适配器代码</span>
<span>10</span>       adapter: 'mongo'<span>,
</span><span>11</span>       url: 'mongodb://localhost/waterline-sample'
<span>12</span> <span>    }
</span><span>13</span> <span>  }
</span><span>14</span> };

sails-mongo 为mongo的适配器，执行命令安装：

Mysql:

<span> 1</span> <span>var</span> mysqlAdapter = require('sails-mysql'<span>);
</span><span> 2</span> <span>var</span> Waterline = require('waterline'<span>);
</span><span> 3</span> 
<span> 4</span> <span>var</span> icbcgold = require('../models/IcbcGold'<span>)
</span><span> 5</span> 
<span> 6</span> <span>//</span><span>适配器与连接设置</span>
<span> 7</span> <span>var</span> wlconfig =<span> {
</span><span> 8</span> <span>    adapters: {
</span><span> 9</span>         mysql: mysqlAdapter, <span>//</span><span>mysql适配器</span>
<span>10</span>         <span>default</span>: 'mysql' <span>//</span><span>默认的适配器</span>
<span>11</span> <span>    },
</span><span>12</span> <span>    connections: {
</span><span>13</span>         <span>//</span><span>mysql连接</span>
<span>14</span> <span>        mysql: {
</span><span>15</span>             adapter: 'mysql',<span>//</span><span>指定适配器为mysql</span>
<span>16</span>             url: 'mysql://root:@localhost/IcbcGold' <span>//</span><span>连接字符串</span>
<span>17</span> <span>        }
</span><span>18</span> <span>    }
</span><span>19</span> }

sails-mysql 为mysql适配器，执行命令安装：

<span>1</span> npm i sails-mysql --save

 连接字符串说明：数据库类型://用户名:密码@数据库地址/数据库名

三.waterline的Model的定义

执行命令安装waterline：

<span> 1</span> <span>var</span> Waterline = require('waterline'<span>);
</span><span> 2</span> 
<span> 3</span> module.exports =<span> Waterline.Collection.extend({
</span><span> 4</span>     identity: 'icbcgold',  <span>//</span><span>模型名，如果没有设置 tableName 属性，那么waterline默认将模型名设置为表名</span>
<span> 5</span>     tableName: 'tb_IcbcGold',<span>//</span><span>指定表名</span>
<span> 6</span>     connection: 'mysql',<span>//</span><span>指定数据库连接</span>
<span> 7</span>     <span>//</span><span> 是否强制模式</span>
<span> 8</span>     schema: <span>false</span><span>,
</span><span> 9</span> <span>    attributes: {
</span><span>10</span> <span>        Id: {
</span><span>11</span>             type: 'string'<span>,
</span><span>12</span>             primaryKey: <span>true</span>
<span>13</span> <span>        },
</span><span>14</span> <span>        DataTime: {
</span><span>15</span>             type: 'datetime'
<span>16</span> <span>        },
</span><span>17</span> <span>        DataNumber: {
</span><span>18</span>             type: 'float'
<span>19</span> <span>        }
</span><span>20</span> <span>    }
</span><span>21</span> });

配置相当简单方便，类似于 Mongoose 中的 Schema。但要注意，指定属性的字段时，使用的是一个字符串值，而不是 JavaScript 中的具体类型，目前支持的数据类型有 string / text / integer / float / date /time / datetime / boolean / binary / array / json，这个范围要比 JavaScript 的类型范围大。

除了这四个基本配置，还可以配置校验器，添加自定义的方法，设置生命周期回调方法等。

注意：

　　1.如果没有定义主键，那么waterline会为你默认创建名为id的主键，类型是整型自增长

　　2.waterline自动创建表时会为你添加 createdAt、updatedAt两个字段，类型为datetime，分别在insert和update操作更新字段代表的是记录的创建时间和更新时间

　　3.如果不想自动创建列createdAt、updatedAt，那么请设置autoCreatedAt，autoUpdatedAt的值为false，如下图

　　

　　4.waterline会自动根据定义的model创建表，但是如果你已经创建好了表，但是与model定义有所区别，比如字段名不一样，那么一定要注意了，请按照下图设置，否则waterline将会删除已经存在表且根据model重新创建，以前的数据就完蛋了

　　

四.校验器

校验器是在创建数据集合的时候指定给具体的属性的

更多设置请查看：https://www.npmjs.com/package/waterline

<span> 1</span> <span>attributes: {
</span><span> 2</span> <span>        title: {
</span><span> 3</span>             type: 'string'<span>,
</span><span> 4</span>             required: <span>true</span>,<span>//</span><span>必须的字段</span>
<span> 5</span>             maxLength: 100,<span>//</span><span>最大长度100</span>
<span> 6</span>             minLength: 5 <span>//</span><span>最小长度5</span>
<span> 7</span> <span>        },
</span><span> 8</span> <span>        views: {
</span><span> 9</span>             type: 'integer'<span>,
</span><span>10</span>             min: 0
<span>11</span> <span>        },
</span><span>12</span> <span>        createTime: {
</span><span>13</span>             type: 'date'<span>,
</span><span>14</span>             <span>//</span><span> 在某个时间点之前</span>
<span>15</span>             before: '2017-12-31'<span>, 
</span><span>16</span>             <span>//</span><span> 在某个时间点之后</span>
<span>17</span>             after: <span>function</span><span> () {
</span><span>18</span>                 <span>return</span> <span>new</span><span> Date();
</span><span>19</span> <span>            }
</span><span>20</span> <span>        }
</span><span>21</span>     }

五.生命周期回调

 可以通过waterline，来实现在进行特定操作的时候，调用自定义的方法。，在 create / update / destory时，均有多种回调。直接提供对应的方法名，分别是：

• 创建时：beforeValidate / afterValidate / beforeCreate / afterCreate
• 更新时：beforeValidate / afterValidate / beforeUpdate / afterUpdate
• 删除时：beforeDestroy / afterDestroy

这些方法，需要在初始化数据集合的时候进行定义。

<span> 1</span> <span>//</span><span>values值 cb回调方法</span>
<span> 2</span> beforeCreate: <span>function</span><span>(values, cb) {
</span><span> 3</span>  
<span> 4</span>     <span>//</span><span> 加密password字段的值</span>
<span> 5</span>     encrypt(values.password, <span>function</span><span>(err, password) {
</span><span> 6</span>       <span>if</span>(err) <span>return</span><span> cb(err);
</span><span> 7</span>  
<span> 8</span>       values.password =<span> password;
</span><span> 9</span> <span>      cb();
</span><span>10</span> <span>    });
</span><span>11</span>   },

六.查询方法

waterline有以下查询方法

1.查询 name 等于 foo 的记录

Model.find({ name: 'foo' })

 2.多条件查询 查询 name 等于 water 并且 state 等于new mexico 的记录

<span>1</span> Model.find({ name: 'walter', state: 'new mexico' })

<span>User.find()
.where({ id: { </span>'&gt;': 100<span> }})
.where({ age: </span>21<span> })
.limit(</span>100<span>)
.sort(</span>'name'<span>)
.exec(</span><span>function</span><span>(err, users) {
  </span><span>//</span><span> Do stuff here </span>
});

下面的修饰符可用于构建查询时使用。

• '<' / '小于'
• '<=' / '小于等于'
• '>' / '大于'
• '>=' / '大于等于'
• '!' / '非'
• 'like'/'模糊匹配'
• 'contains'/'包含'
• 'startsWith'/'以某字符开头'
• 'endsWith'/'以某字符结尾'

 3.分页查询

<span>1</span> User.find().skip(10).limit(10); <span>//</span><span>跳过10条记录 取10条记录</span>

<span>1</span> Model.find({ where: { name: 'foo' }, skip: 20, limit: 10, sort: 'name DESC' });<span>//</span><span>带条件分页查询</span>

User.find().paginate({page: 2, limit: 10});<span>//</span><span>根据页数分页查询</span>

 4.新增记录

User.create({Id:'xxx',Name:'xxx'});

5.更新记录

User.update({ name: 'Walter Jr' }, { name: 'Flynn' })

6.删除记录

User.destroy({ name: 'Flynn' })

Promises

<span> 1</span> <span>User.findOne()
</span><span> 2</span> .where({ id: 2<span> })
</span><span> 3</span> .then(<span>function</span><span>(user){
</span><span> 4</span>     <span>var</span> comments = Comment.find({userId: user.id}).then(<span>function</span><span>(comments){
</span><span> 5</span>         <span>return</span><span> comments;
</span><span> 6</span> <span>    });
</span><span> 7</span>     <span>return</span><span> [user.id, user.friendsList, comments];
</span><span> 8</span> }).spread(<span>function</span><span>(userId, friendsList, comments){
</span><span> 9</span>     <span>//</span><span> Promises are awesome! </span>
<span>10</span> }).<span>catch</span>(<span>function</span><span>(err){
</span><span>11</span>     <span>//</span><span> An error occurred </span>
<span>12</span> })

七.使用示例

这里使用的数据库是mysql创建一个数据库名为：IcbcGold

1.新建一个js文件：waterline.js，代码如下：

<span> 1</span> <span>var</span> mysqlAdapter = require('sails-mysql'<span>);
</span><span> 2</span> <span>var</span> Waterline = require('waterline'<span>);
</span><span> 3</span> 
<span> 4</span> <span>var</span> icbcgold = require('../models/IcbcGold'<span>)
</span><span> 5</span> 
<span> 6</span> <span>//</span><span>适配器与连接设置</span>
<span> 7</span> <span>var</span> wlconfig =<span> {
</span><span> 8</span> <span>    adapters: {
</span><span> 9</span>         mysql: mysqlAdapter, <span>//</span><span>mysql适配器</span>
<span>10</span>         <span>default</span>: 'mysql' <span>//</span><span>默认的适配器</span>
<span>11</span> <span>    },
</span><span>12</span> <span>    connections: {
</span><span>13</span>         <span>//</span><span>mysql连接</span>
<span>14</span> <span>        mysql: {
</span><span>15</span>             adapter: 'mysql',<span>//</span><span>指定适配器为mysql</span>
<span>16</span>             url: 'mysql://root:@localhost/IcbcGold' <span>//</span><span>连接字符串</span>
<span>17</span> <span>        }
</span><span>18</span> <span>    }
</span><span>19</span> <span>}
</span><span>20</span> 
<span>21</span> <span>var</span> orm = <span>new</span><span> Waterline();
</span><span>22</span> 
<span>23</span> <span>//</span><span>加载model集合</span>
<span>24</span> <span>orm.loadCollection(icbcgold);
</span><span>25</span> 
<span>26</span> exports.orm =<span> orm;
</span><span>27</span> exports.wlconfig = wlconfig;

2.新建文件 index.js

<span>var</span> waterline = require('./app/config/waterline'<span>);
</span><span>var</span> uuid = require('uuid'<span>);


</span><span>var</span> ormmodels = <span>null</span><span>;

</span><span>//</span><span>初始化waterline</span>
waterline.orm.initialize(waterline.wlconfig, <span>function</span><span> (err, models) {
    </span><span>if</span><span> (err) {
        </span><span>return</span><span>;
    }
    ormmodels </span>=<span> models.collections;
})</span>

<span>//</span><span>执行查询</span>
ormmodels.icbcgold.count({ DataTime: dataTime }).exec(<span>function</span><span> (err, found) {
        </span><span>if</span><span> (err) {
            </span><span>return</span><span>;
        }
        </span><span>if</span> (found === 0<span>) {
            ormmodels.icbcgold.create({ Id: uuid.v1(), DataTime: dataTime, DataNumber: dataNumber }, </span><span>function</span><span> (err, models) {
                </span><span>if</span><span> (err) {
                    </span><span>return</span><span>;
                }
            });
        }
    });</span>

 呼~~~~终于写完了，下面帖几个网址，如果你有什么问题可以先查看

http://sailsjs.com/documentation/reference/waterline-orm/models

https://www.npmjs.com/package/waterline

waterline使用是非常简单的，目前用nodejs写了一个爬虫，使用waterline存储数据到mysql，已经部署到服务器上，使用pm2运行，抓取数据用的是superagent，后面我会继续写如果用nodejs写爬虫，欢迎关注！