Sequelize

basic

Sequelize 是一个基于 promise 的 Node.js ORM, 目前支持 Postgres, MySQL, SQLite 和 Microsoft SQL Server, 主要用来进行数据库和nodejs后端数据映射。
Docs: Sequelize Docs 中文版(不全) | Official api docs

Here I only mark certain api I use most frequently.

Model

definition

use define to map data model to table in db.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
'use strict';
module.exports = (sequelize, DataTypes) => {
    var Cell = sequelize.define("Cell",
        {
            id: {
                type: DataTypes.INTEGER,
                primaryKey: true,
                autoIncrement: true
            },
            type: {
                type: DataTypes.INTEGER,
                defaultValue:1
            },
            name: {
                type: DataTypes.STRING(255),
                unique: 'compositeIndex'
            },
            ...
        },
        {
            timestamps: false
        });

    Cell.getCells = function () {
      ...
    }

    ...function...

    ...function...
}

usage

  1. find
  • findByPk, search element in db by primaryKey (usually id).
    1
    2
    3
    4
    5
    6
    7
    8
    Cell.findCell = async function (id) {
      let cell = await Cell.findByPk(id);
    }
    // or like
    Project.findByPk(123).then(project => {
    // project 将是 Project的一个实例,并具有在表中存为 id 123 条目的内容.
    // 如果没有定义这样的条目,你将获得null
    })
  • findOne, search element in db by property.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    Cell.findCellByName = async function (name) {
        return await Cell.findOne({where: {name: name}});
    };
    // if multiple properties
    Project.findOne({
      where: {title: 'aProject'},
      attributes: ['id', ['name', 'title']]
    }).then(project => {
      // project 将是 Projects 表中 title 为 'aProject'  的第一个条目 || null
      // project.get('title') 将包含 project 的 name
    })
  • findAndCountAll is a combination of findAll and count.It search db with conditions, return records with a data set (rows) and total count (count). Useful when you want to list data as a table, it provides with limit and offset in pagination.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    Project
      .findAndCountAll({
         where: {
            title: {
              [Op.like]: 'foo%'
            }
         },
         offset: 10,
         limit: 2
      })
      .then(result => {
        console.log(result.count);
        console.log(result.rows);
      });
    Op = Operators. for more available Ops, see https://sequelize.org/master/manual/model-querying-basics.html#operators
    it also support include to set limits of count, or construct left join:
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    // only data with required attribute value equals to true will be counted
    User.findAndCountAll({
        include: [
           { model: Profile, required: true }
        ],
        limit: 3
    });
    // e.g. model Cells left join with model XXX.
    Cell.searchByNameAndPagination = async function (offs, limits, attribute, order, name) {
        ...
        let cells = await Cell.findAndCountAll({
                include: [{
                    model: XXX
                    // right: true  // will turn to right join, only when required is false
                }],
                where: where,
                offset: offs,
                limit: limits,
                order: orderTmp
        });
        cells.rows = cells.rows.map(function (cell) {
            cell.dataValues.xxxname = cell.XXX ? cell.XXX.name : null;
            delete cell.dataValues.XXX;
            delete cell.dataValues.xxxId;
            return cell.dataValues;
        });
        return cells;
    }
  • findAll, similar to above, without counting.

  1. create
    create a new obj in current model

  2. count
    normally use with findAndCountAll

  3. min / max / sum
    return min/max/sum value with given attribute.

  4. upsert
    ref, Insert or update a single row. An update will be executed if a row which matches the supplied values on either the primary key or a unique key is found.

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    return Cell.upsert({
          type: value.type,
          name: value.name,
          ...
      }).then(function () {
          successRecords.push({
              value
          });
      }).catch(function (exc) {
          failedRecords.push({
              gis: value,
              reason: exc.original
          });
      });

    According to the data model as above, this means the upsert will use id as the key to match, cuz id is the only attribute not changed in upsert.