sequelize（一）

  Sequelize.js 提供对 MySQL，MariaDB，SQLite 和 PostgreSQL 数据库的简单访问，通过映射数据库条目到对象，或者对象到数据库条目。是一个实用 ORM（Object-Relational-Mapper），适用于 Node.js 的环境。

1、node项目中安装Sequelize

npm install --save sequelize

2、引用 Sequelize

<span style="font-size:18px;">import Sequelize from 'sequelize'</span>

3、建立数据库连接

<span style="font-size:18px;">const sequelize = new Sequelize(
  config.get('db:database'),//数据库名
  config.get('db:username'),//数据库用户名
  config.get('db:password'),//密码
  config.get('db:options')
)
其中的options的具体配置仅供参考
options: {
      "host": "localhost",
      "port": 3306,
      "dialect": "mysql",
      "timezone": "+08:00",
      "pool": {
        "max": 15,
        "min": 0,
        "idle": 10000
      }
</span>

可以查看相关文档： http://www.nodeclass.com/api/sequelize.html#new-sequelizedatabase-usernamenull-passwordnull-options

4、根据 Sequelize定义 model

sequelize.define('Order', {
    id: {type: DataTypes.UUID, defaultValue: DataTypes.UUIDV1, field: 'f_id', primaryKey: true},
    created_time: {type: DataTypes.DATE, field: 'f_created_time'},
    code: {type: DataTypes.STRING, field: 'f_code'},
    trade_no: {type: DataTypes.STRING, field: 'f_trade_no'},
    status: {type: DataTypes.STRING, field: 'f_status'},
    pay_status: {type: DataTypes.STRING, field: 'f_pay_status'},
    total_price: {type: DataTypes.FLOAT, field: 'f_total_value'},
    remark: {type: DataTypes.STRING, field: 'f_remark'},
    name: {type: DataTypes.STRING, field: 'f_name'},
    address: {type: DataTypes.STRING, field: 'f_address'},
    mobile: {type: DataTypes.STRING, field: 'f_mobile'},
    telphone: {type: DataTypes.STRING, field: 'f_tel_phone'},
    accounts_id: {type: DataTypes.STRING, field: 'f_account_id'}
  }, {
    timestamps: false,
    tableName: 'kingnoshop_order',
    classMethods: {
      associate: ({Order, User, OrderDetail}) => {
        Order.belongsTo(User, {foreignKey: 'F_ACCOUNT_ID'}),
        Order.hasMany(OrderDetail, {foreignKey: 'F_ORDER_ID'})
      }
    },
    hooks: {
      beforeCreate: (order, options) => {
        order.created_time = new Date()
        order.status = '1'
        order.pay_status = '0'
      }
    }
  })

5、根据model ，可以进行数据的新增，删除，修改等操作

 新增

<span style="font-size:18px;">  let order = {}

  order['field'] = vaule
  order['field'] = (value || 1)
  Order.create(order)</span>

修改

<span style="font-size:18px;"><pre name="code" class="javascript">  let order = {}, where = {}

  where['field'] = value //条件
  order['field'] = value //修改的值
  Order.update(order, {where})</span>

删除

<span style="font-size:18px;">  let where = {}

  where['field'] = value //条件
  Order.destroy({where})</span>

查询

<span style="font-size:18px;"> //根据条件查询单条数据
 let where ={}

 Order.findOne({
    where,
    attributes: ['id',...], //查询的字段
    include: [{model: User, as: 'user'}] //一对多或者多对一的包含复合查询
  })</span>

6、Sequelize中一些常用的函数

Model.find({
  where: {
    attr1: {
      gt: 50
    },
    attr2: {
      lte: 45
    },
    attr3: {
      in: [1,2,3]
    },
    attr4: {
      ne: 5
    }
  }
})

// where条件等价：
WHERE attr1 > 50 AND attr2 <= 45 AND attr3 IN (1,2,3) AND attr4 != 5

Model.find({
  where: Sequelize.and(
    { name: 'a project' },
    Sequelize.or(
      { id: [1,2,3] },
      { id: { gt: 10 } }
    )
  )
})
// where条件等价： 
WHERE name = 'a project' AND (id` IN (1,2,3) OR id > 10)