node sequelize 快速开始

-1. 前言

Bob: 会不会SQL?
Alice: 不会
Bob: 什么是ORM?
Alice: 不知道
Bob: 这样你还做个屁的后端?
Alice: 还真能. 现在, 我关心的不是原理, 程序能跑就行了

以简单用户登录流程了解使用Sequelize

1. 如何用Sequelize建立实体 (本例中的User)
2. 如何使用增删查改 (CRUD)
3. 简单批量查询实例

0. 环境引入

详情参考sequelize手册
如果你不喜欢阅读, 看下面的懒人版
安装(Installing)
npm install --save sequelize
封装

// MyConf.js
"use strict";

let app = '/root/test/jujubeServer';

let MyConf = {}
...
MyConf.db = {
  host: "127.0.0.1",
  username: "root",
  password: "123456",
  database: "jujube",
}

module.exports = MyConf;

// dbTool.js
let myConf = require('../config/MyConf');
let dbConf = myConf.db;

const Sequelize = require('sequelize');

const sequelize = new Sequelize(dbConf.database, dbConf.username, dbConf.password, {
  host: dbConf.host,
  dialect: 'mysql',
  pool: {
    max: 5,
    min: 0,
    acquire: 30000,
    idle: 10000,
  },
});

module.exports = {
  Sequelize: Sequelize,
  sequelize: sequelize,
};

1. 用Sequelize建立实体模型User

详情参考sequelize模型
如果你想多快好省一些(也就是懒), 下面有提供简陋版本

// User.js
var myConf = require('../../config/MyConf');
let dbTool = require(myConf.requires.dbTool);

const Sequelize = dbTool.Sequelize;
const sequelize = dbTool.sequelize;

const User = sequelize.define('user', {
  // attributes
  uid: {
    type: Sequelize.INTEGER.UNSIGNED,
    primaryKey: true,
  },
  uname: {
    type: Sequelize.STRING(20),
    unique: true,
    allowNull: false,
  },
  password: {
    type: Sequelize.STRING(60),
    allowNull: false
  },
  email: {
    type: Sequelize.STRING(30),
    allowNull: false,
  },
}, {
  // options
  timestamps: false,
  freezeTableName: true,
  tableName: 'user',
});

在mysql workbench或navicat创建用户表
导出的时候对应的sql语句类似下边

// jujube.sql
CREATE TABLE `user` (
  `uid` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `uname` VARCHAR(20) NOT NULL,
  `password` VARCHAR(60) NOT NULL,
  `email` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`uid`),
  UNIQUE INDEX `uid_UNIQUE` (`uid` ASC),
  UNIQUE INDEX `uname_UNIQUE` (`uname` ASC));

2. 如何使用增删查改

详情参考官网, 简单的一些, 我把它写成多行注释存在代码里了
查询实例: 检查用户名是否存在

// 查询模板
/*
User.findAll().then(users => {
});

User.findOne({
  where: {uname: toFindName},
  attributes: ['uid', ['uname', 'uname']],
}).then((someUser) => {
});
*/

// 查询实例
User.isNameExist = function(toFindName, callback) {
  // Find a user by name
  User.findOne({
    where: {uname: toFindName},
    attributes: ['uid', ['uname', 'uname']],
  }).then((someUser) => {
    let isRegistered = someUser ? true : false;
    callback && callback(isRegistered);
  });
}

增加实例: 新增注册用户

// 新增(插入)模板
/*
User.create().then(newUser => {
});
*/

// 新增实例
User.createNew = function(registerInfo, callback) {
  User.create({
    uname: registerInfo.name,
    password: registerInfo.password,
    email: registerInfo.email,
  }).then((user) => {
    let userInfo = user ? user.dataValues : null;
    callback && callback(userInfo);
  });
}

修改实例: 修改用户名

// 修改模板
/*
User.update({ uname: "Doe" }, {
  where: {
    uname: "Jane"
  }
}).then(() => {
});
*/

User.changeName = function(toFindName, callback) {
  // change some user name
  User.update({ uname: "GeBiLaoWang" }, {
  where: {
    uname: "XiaoZhengTai"
  }
}).then(() => {
  callback && callback(true)
});
}

删除实例: 删除特定用户

// 删除模板
/*
User.destroy({
  where: {
    uname: "Jane"
  }
}).then(() => {
});
*/

// 删除实例
User.deleteByName = function(toDelName, callback) {
  // delete some user(s) by name
  User.destroy({
    where: {
      uname: toDelName
    }
  }).then(() => {
    callback && callback(true);
  });
}

3. 简单批量查询实例

Bob: 什么是批量查询?
Alice: 不知道
Bob: 你怎么写出来的?
Alice: 官网依葫芦画瓢. 当务之急, 能用就行了

来, 传送门: 查询大全
大家都是懒人(如果不是, 请配合下吧), 就贴个代码了

/**
 * @func batchGetUserinfo
 * @param data: a js object
 * @param data.uidList: a js array, user id list.
 * @param data.fields: a js array, attributes names to fetch.
 */
UserBase.batchGetUserinfo = function(data, callback) {
  let uidList = data.uidList || [];
  let fields = data.fields;
  
  // WARNNING: if length of uidList <= 0, MUST return in time.
  // otherwise, ALL user info in database will be returned !!!
  if (uidList.length <= 0) { callback && callback (null); return; }

  UserBase.findAll({
    where: {
      uid: {
        [Op.or]: uidList
      }
    },
    attributes: fields,
  }).then((batchUserinfo) => {
    if (!batchUserinfo) { callback && callback (null); return }

    let resultUserinfos = [];
    batchUserinfo.forEach(element => {
      if (element && element.dataValues) { resultUserinfos.push(element.dataValues); }
    });
    callback && callback(resultUserinfos);
  });
}

4. 小结

基本操作ok就可以上手了

参考

1. Sequelize官网