实践+源码分析完全理解 Sequelize，详细例子说明

Sequelize 是一款优秀的数据库 ORM
框架，支持 mysql、postgres、sqlite、mariadb、mssql。使用方法非常灵活多变，GitHub star 数目前 20k 左右，其周边工具 sequelize-auto 可自动从数据库生成模型文件，sequelize-cli 可以依据模型文件创建数据库，能力非常强大。

上篇文章讲解了 Sequelize 的基本使用，这篇文章从源码角度的解析 Sequelize，让大家用的明明白白！！

链接

• 官方文档
• sequelize
• sequelize-auto
• sequelize-cli
• 本文章示例代码

以 mysql 为例

连接数据库

npm i -S sequelize mysql2

实例化 Sequelize 进行连接，参数在构造函数中配置

const Sequelize = require('sequelize');

// Option 1: Passing parameters separately
const sequelize = new Sequelize('database', 'username', 'password', {
   
  host: 'localhost',
  dialect: 'mysql',
  // 额外的配置...
});

// Option 2: Passing a connection URI
const sequelize = new Sequelize('postgres://user:pass@example.com:5432/dbname');

Sequelize 构造函数解析

源码中 Sequelize 构造函数，列举常用的参数

• database string 数据库名
• username=null string 用户名
• password=null string 密码
• options={} Object 配置
• options.host='localhost' string host 地址
• options.port= number 数据库 serve 端口
• options.username=null string 用户名，同上面用户名
• options.password=null string 密码，同上面用户名，只需传其一
• options.database=null string 数据库名，同上
• options.dialect string 要使用的数据库类型，目前支持 mysql, postgres, sqlite, mssql.
• timezone='+00:00' string 时区设置，默认中国时区需要变更为"+08:00"，如果有使用 NOW 函数一定要注意。The timezone used when converting a date from the database into a JavaScript date. The timezone is also used to SET TIMEZONE when connecting to the server, to ensure that the result of NOW, CURRENT_TIMESTAMP and other time related functions have in the right timezone. For best cross platform performance use the format +/-HH:MM. Will also accept string versions of timezones used by moment.js (e.g. ‘America/Los_Angeles’); this is useful to capture daylight savings time changes.
• options.logging=console.log Function A function that gets executed every time Sequelize would log something.
• options.benchmark=false boolean Pass query execution time in milliseconds as second argument to logging function (options.logging).
• options.replication=false boolean Use read / write replication. To enable replication, pass an object, with two properties, read and write. Write should be an object (a single server for handling writes), and read an array of object (several servers to handle reads). Each read/write server can have the following properties: host, port, username, password, database
• options.pool Object 连接池配置
• options.pool.max=5 number 连接池最大连接数
• options.pool.min=0 number 连接池最小连接数
• options.pool.idle=10000 number The maximum time, in milliseconds, that a connection can be idle before being released.闲置连接的最大存活时间
• options.pool.acquire=60000 number The maximum time, in milliseconds, that pool will try to get connection before throwing error，出错之后再次连接的最长时间
• options.pool.evict=1000 number The time interval, in milliseconds, after which sequelize-pool will remove idle connections.多长时间之后将会移除闲置连接
• options.operatorsAliases ObjectString based operator alias. Pass object to limit set of aliased operators.设置操作符别名
• options.hooks Object 连接和断开数据库的一些钩子函数。 An object of global hook functions that are called before and after certain lifecycle events. Global hooks will run after any model-specific hooks defined for the same event (See Sequelize.Model.init() for a list). Additionally, beforeConnect(), afterConnect(), beforeDisconnect(), and afterDisconnect() hooks may be defined here.

最简化的连接

// src/db/index.js
const Sequelize = require('sequelize');

const sequelize = new Sequelize('testdb', 'root', 'root', {
   
  host: 'localhost',
  port: 8889, // 默认是 3306，我的电脑设置的 8889
  dialect: 'mysql',
});

module.exports = sequelize;


// App.js
const seq = require('./src/db');

seq
  .authenticate()
  .then(() => {
   
    console.log('Connection has been established successfully.');
  })
  .catch(err => {
   
    console.error('Unable to connect to the database:', err);
  });
  
// node App.js

连接成功则打印如下

数据库模型

Sequelize 需要建立模型，才可以对数据库进行操作，对数据库的每个表建立模型文件太过繁琐。 可以使用 sequelize-auto 从数据库直接导出模型。

先创建数据库 testdb，执行 sql 语句自动建表 testdb。

安装 sequelize-auto

npm i -D sequelize-auto

自动从数据库导出模型

sequelize-auto 使用

[node] sequelize-auto -h <host> -d <database> -u <user> -x [password] -p [port]  --dialect [dialect] -c [/path/to/config] -o [/path/to/models] -t [tableName] -C

Options:
  -h, --host        IP/Hostname for the database.   [required]
  -d, --database    Database name.                  [required]
  -u, --user        Username for database.
  -x, --pass        Password for database.
  -p, --port        Port number for database.
  -c, --config      JSON file for Sequelize's constructor "options" flag object as defined here: https://sequelize.readthedocs.org/en/latest/api/sequelize/
  -o, --output      What directory to place the models.
  -e, --dialect     The dialect/engine that you're using: postgres, mysql, sqlite
  -a, --additional  Path to a json file containing model definitions (for all tables) which are to be defined within a model's configuration parameter. For more info: https://sequelize.readthedocs.org/en/latest/docs/models-definition/#configuration
  -t, --tables      Comma-separated names of tables to import
  -T, --skip-tables Comma-separated names of tables to skip
  -C, --camel       Use camel case to name models and fields
  -n, --no-write    Prevent writing the models to disk.
  -s, --schema      Database schema from which to retrieve tables
  -z, --typescript  Output models as typescript with a definitions file.

简单配置

// package.json
"scripts": {
   
  "sequelize": "sequelize-auto -o ./src/db/model -d testdb -h localhost -u root -p 8889 -x root -e mysql"
},

自动生成的模型如下

// src/db/model/blog.js
/* jshint indent: 2 */

module.exports = function(sequelize, DataTypes) {
   
  return sequelize.define('blog', {
   
    id: {
   
      type: DataTypes.INTEGER(11),
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    school: {
   
      type: DataTypes.STRING(255),
      allowNull: true
    },
    name: {
   
      type: DataTypes.STRING(255),
      allowNull: true
    }
  }, {
   
    tableName: 'blog',
    timestamps: false,
  });
};


// src/db/model/users.js
/* jshint indent: 2 */

module.exports = function(sequelize, DataTypes) {
   
  return sequelize.define('users', {
   
    id: {
   
      type: DataTypes.INTEGER(11),
      allowNull: false,
      primaryKey: true,
      autoIncrement: true
    },
    name: {
   
      type: DataTypes.STRING(30),