1 ORM

  • Object Relational Mapping，对象关系映射，通过实体对象的语法，完成对关系数据库的操作（几乎不编写SQL语句）
  • ORM将数据库中的术语映射为面向对象编程中的概念：
    • 数据表=>类
    • 数据行=>类实例
    • 数据列=>实例属性

2 Sequelize.js

Sequelize是Node.js操作关系数据库的一个npm模块，基于Promise开发，包含很多特性，数据库模型映射、事务处理、模型属性校验、关系映射、自动创建数据表等，支持MySQL、PostgreSQL等主流SQL数据库。

2.1 安装

1

yarn add sequelize mysql2

2.2 快速开始

 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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

// 引入
const { Sequelize, Model, DataTypes } = require('sequelize')
// 创建sequelize连接
const sequelize = new Sequelize({
    host: 'localhost', // 数据库连接地址，默认localhost
  	port: 3306, // 数据库连接端口
    dialect: 'mysql', // 底层数据库
  	username: 'gsq', // 数据库账号
  	password: '123456', // 数据库密码
  	database: 'test', // 数据库名称
  	timezone: '+08:00', // 时区
  	pool: { 
      max: 10, // 连接池最大连接数，默认为5
      min: 0
    },
  	logging: false, // 是否显示SQL日志，默认true
  	benchmark: true // 是否显示SQL执行时长，默认为false
})

// 创建User模型
class User extends Model { }
// 初始化User表
User.init({
    // 指定字段及字段类型
  	id: { 
    	type: DataTypes.INTEGER({ unsigned: true }), // 无符号整型
      primaryKey: true, // 是否主键 
      autoIncrement: true,
      comment: '用户id' // 字段注释
    },
    username: {
      type: DataTypes.STRING(40),
      unique: true, // 是否唯一索引
      allowNull: false,
      comment: '账号'
    },
  	password: {
      type: DataType.CHAR(64),
      allowNull: false,
      comment: '密码'
    }
  	// 配置模型选项
}, { sequelize, modelName: 'user', tableName: 'user' })// 表名

// 同步到数据库
sequelize.sync()
    // 创建一条数据
    .then(() => User.create({
        username: 'gsq',
  			password: '123456'
    }))
    // 把数据打印出来
    .then(user => {
        console.log(user.toJSON())
    })
		.catch((e) => {
  		  console.error(e)
		})

Sequelize构造方法的常用配置选项详见上文代码注释。

3 模型定义

  • 定义模型需要两步
    1. 继承Sequelize内置的Modal
    2. 使用init方法初始化模型字段和模型选项（分别是init方法的两个参数）

3.1 模型字段

模型字段是一个对象，对象的键为对应数据库的字段名，值为该字段的数据类型和其他属性配置，常用的字段选项如下：

  • type：数据类型
  • unique：是否唯一索引
  • primaryKey：是否主键
  • autoIncrement：是否自增
  • comment：字段注释

3.2 模型选项

常用的模型选项如下

  • omitNull：是否提交null值到数据库，默认为false
  • timestamps：是否自动添加创建、修改和删除时间字段，默认为true
  • paranoid：是否启用软删除，启用后不会真正删除数据，而是设置deletedAt字段的值，默认为false
  • underscored：是否将驼峰命名的JS键转化为下划线，默认false
  • modelName：模型名称，默认值为类名
  • tableName：表名称，默认使用类名
  • engine：储存引擎，默认InnoDB
  • charset：字符集
  • hooks：操作数据库生命周期函数
  • validate：模型验证规则定义

3.2.1 hooks

  • 生命周期顺序：
 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

(1)
  beforeBulkCreate(instances, options)
  beforeBulkDestroy(options)
  beforeBulkUpdate(options)
(2)
  beforeValidate(instance, options)

[... 验证 happens ...]

(3)
  afterValidate(instance, options)
  validationFailed(instance, options, error)
(4)
  beforeCreate(instance, options)
  beforeDestroy(instance, options)
  beforeUpdate(instance, options)
  beforeSave(instance, options)
  beforeUpsert(values, options)

[... 创建/更新/销毁 happens ...]

(5)
  afterCreate(instance, options)
  afterDestroy(instance, options)
  afterUpdate(instance, options)
  afterSave(instance, options)
  afterUpsert(created, options)
(6)
  afterBulkCreate(instances, options)
  afterBulkDestroy(options)
  afterBulkUpdate(options)
  • 以注册账号为例
1
2
3
4
5
6
7
8
9

beforeValidate		// 验证之前，此时要设置验证规则
validate					// 调用验证函数进行验证
afterValidate			// 验证通过
validationFailed	// 验证失败，注册流程结束
beforeCreate			// 创建账号之前
beforeSave				// 保存数据之前，进行密码加密等操作
create						// 写入数据库
afterCreate				// 创建账号之后
afterSave					// 保存数据之后

3.2.2 定义声明周期函数

  1. 模型选项方式
1
2
3
4
5
6
7

User.init(模型字段, {
	hooks: {
		beforeSave(user){
			// 生命周期函数
		}
	}
})
  1. 模型类静态方法
1
2
3
4
5

User.beforeSave((user) => {
	if(user.changed('password')){
		user.password = crypto(user.password)
	}
})

3.2.3 模型验证器

通过模型验证器，可以验证模型每个字段是否符合设置的规则，如果不符合规则，就不允许写入数据库

  1. 定义验证规则

可以在模型字段中定义，即刻使用内置的验证规则，也可使用自定义验证规则：

1
2
3
4
5
6
7
8

User.init({
	email: {
		type: DataType.STRING(40),
		validate: {
			isEmail: true
		}
	}
}, { ...模型选项 })
  1. 内置验证规则
 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
32
33

is: /^[a-z]+$/i,          // matches this RegExp
is: ["^[a-z]+$",'i'],     // same as above, but constructing the RegExp from a string
not: /^[a-z]+$/i,         // does not match this RegExp
not: ["^[a-z]+$",'i'],    // same as above, but constructing the RegExp from a string
isEmail: true,            // checks for email format (foo@bar.com)
isUrl: true,              // checks for url format (http://foo.com)
isIP: true,               // checks for IPv4 (129.89.23.1) or IPv6 format
isIPv4: true,             // checks for IPv4 (129.89.23.1)
isIPv6: true,             // checks for IPv6 format
isAlpha: true,            // will only allow letters
isAlphanumeric: true,     // will only allow alphanumeric characters, so "_abc" will fail
isNumeric: true,          // will only allow numbers
isInt: true,              // checks for valid integers
isFloat: true,            // checks for valid floating point numbers
isDecimal: true,          // checks for any numbers
isLowercase: true,        // checks for lowercase
isUppercase: true,        // checks for uppercase
notNull: true,            // won't allow null
isNull: true,             // only allows null
notEmpty: true,           // don't allow empty strings
equals: 'specific value', // only allow a specific value
contains: 'foo',          // force specific substrings
notIn: [['foo', 'bar']],  // check the value is not one of these
isIn: [['foo', 'bar']],   // check the value is one of these
notContains: 'bar',       // don't allow specific substrings
len: [2,10],              // only allow values with length between 2 and 10
isUUID: 4,                // only allow uuids
isDate: true,             // only allow date strings
isAfter: "2011-11-05",    // only allow date strings after a specific date
isBefore: "2011-11-05",   // only allow date strings before a specific date
max: 23,                  // only allow values <= 23
min: 23,                  // only allow values >= 23
isCreditCard: true,       // check for valid credit card numbers
  1. 自定义验证
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

// Examples of custom validators:
isEven(value) {
  if (parseInt(value) % 2 !== 0) {
    throw new Error('Only even values are allowed!');
  }
}
isGreaterThanOtherField(value) {
  if (parseInt(value) <= parseInt(this.otherField)) {
    throw new Error('Bar must be greater than otherField.');
  }
}
  1. 异步验证

支持async/await异步验证：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

status: {
	allowNull: false,
	validate: {
		async remoteValidate(value) {
			const result = await httpRequest(value)
			if(result.errcode){
				throw new Error('status字段验证失败')
			}
		}
	}
}
  1. 自定义验证消息

可以针对内置验证规则自定义验证返回消息。

  • 对于boolean形式的验证条件：
1
2
3

isEmail: {
	msg: '邮箱格式错误'
}
  • 对于其他形式的验证条件：
1
2
3
4

is: {
	args: /^1[3-9]\d{9}$/,
	msg: '手机号码格式错误'
}
  1. 其他
  • 如果定义模型字段时设置了allowNull为false，在使用时传递了null值，将跳过所有验证器并抛出错误
  • 如果为true，在使用时传递了null值，则只跳过内置验证器

3.3 模型方法

可以直接采用ES6的class语法添加静态方法和实例方法：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

class User extends Model {
	// 实例方法，this表示User实例，也就是一行数据
	getFullName() {
		return this.lastname + this.firstName
	}
	// 静态方法，通过User.checkName(name)调用
	static checkName(name) {
		return true
	}
}

3.4 索引

3.4.1 普通索引

普通索引可以在模型选项中定义：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

User.init({ 字段配置 }, {
	sequelize: sequelize,
	indexes: [
		{
			name: 'idx_username_status',// 索引名称
			fields: ['username', 'status'],// 索引字段，如果是联合索引则添加多个字段名称
			unique: false // 是否唯一索引
		}
	]
})

3.4.2 唯一索引

添加字段属性时可以方便地设置唯一属性（unique: true）

3.5 同步数据库

  • sequelize.sync()：建立数据表，如果数据表存在则跳过
  • sequelize.sync({ force: true })：强制建立数据表，如果数据表已经存在将被覆盖，存在一定风险
  • sequelize.sync({ force:true, match: /_test&/ })：只有_test结尾的数据库才会强制执行
  • sequelize.drop()：删除数据表

4 模型使用

4.1 插入数据

sequelize支持单条插入和批量插入两种方式

  • 单条插入：
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

async function addOne() {
	try {
		await sequelize.sync({ force: true })
		const user = await User.create({
			username: 'gsq',
			password: '123456'
		})
		console.log('创建成功',user.toJSON())
	} catch(e) {
		console.error(e)
	}
}
  • 批量创建
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

async function addSome() {
	try {
		await sequelize.sync({ force: true })
		const users = await User.bulkCreate([
			{ username: 'gsq', password: '123456' },
			{ username: 'zs', password: '111111' },
		], {
			individualHooks: true // 对每个数据分别执行一遍生命周期函数，因为默认只会触发bulk相关的hooks
		})
		console.log('创建成功',users.toJSON())
	} catch(e) {
		console.error(e)
	}
}

4.2 更新数据

  • 批量更新
    • 模型类的update方法接收两个参数，第1个参数为更新数据，第二个参数为更新选项。常用的更新选项：
      • where：查询对象，可选
      • individualHooks：是否对每个数组执行单独的hooks，默认为false
      • limit：更新数量，指定更新的行数
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

async function updateSome() {
	try {
    // 返回更新的条数
		const [count] = await User.update({ password: '123456' }, {
			where: { // 查询账号为gsq的数据
				username: 'gsq'
			},
			individualHooks: true
		})
		console.log('success', count)
	} catch(e) {
		console.log('failed', e)
	}
}
  • 单条更新，先查询，再修改，最后save()
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

async function updateSome() {
	try {
    // 先查询数据
		const user = await User.findOne({
      where: {
        username: 'gsq'
      }
    })
    if(!user){
      throw new Error('用户不存在')
      return
    }
    user.password = '111111'
    await user.save()
    console.log('success', count)
	} catch(e) {
		console.log('failed', e)
	}
}

4.3 删除数据

  • 批量删除（软删除情况下，不会真正删除数据库中的数据，只是更新了delete_at字段）
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

async function deleteSome() {
	try {
		const count = await User.destroy({
			where: {
				username: 'gsq'
			}
		})
		console.log('success', count)
	} catch(e) {
		console.log('failed', e)
	}
}
  • 单条删除，先查询，再destroy()
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

async function deleteSome() {
	try {
		const count = await User.findOne({
			where: {
				username: 'gsq'
			}
		})
    await user.destroy({ force: true }) // 在模型定义了软删除的情况下，可以传入force选项以强制删除数据
		if (!user) {
			throw new Error('用户不存在')
		}
		console.log('success', count)
	} catch(e) {
		console.log('failed', e)
	}
}

4.4 查询数据

4.4.1 查询单条数据

  • Sequelize支持通过主键查询指定记录
1
2
3

async function example() {
	const user = await User.findByPk(1) // 查询主键为1的数据
}
  • 调用模型类的findOne方法并传入where选项
1
2
3
4
5
6
7
8

async function example() {  
	const user = await User.findOne({
		attributes: ['name'], // 指定返回字段
		where: {
			username: 'gsq'
		}
	})
}

4.4.2 查询多条数据

  • findAll用来查询满足条件的所有记录，支持配置选项如下
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

// 查询所有数据
async function getAll() {
	const users = await User.findAll() // 查询所有数据
}
// 查询recommand为1的11-20条数据，先按照status倒序，然后按照ID倒序排列
async function getSome() {
	const users = await User.findAll({
		where: {
			recommand: 1
		},
		// 设置排序
		order: [
			['status', 'DESC'], // DESC为倒序，ASC为正序
			['id', 'DESC']
		],
		// 设置记录集偏移，用来分页
		offset: 10,
		// 限制查询结果的条数
		limit: 10
	})
}

4.4.3 查询或创建单条数据

  • findOrCreate可以查询单条数据，若数据不存在则自动创建（default配置项），返回一个数组，第一个元素为模型实例，第二个元素标记模型实例是查询结果还是创建结果。
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

async function findOrCreate() {
	const [user, isCreated] = await User.findOrCreate({
		where: {
			username: 'gsq'
		},
		default: { // 若为查询到数据，则作为默认值插入数据库
			username: 'gsq',
			password: '123456'
		}
	})
	if (isCreated) {
		console.log('created')
	} else {
	  console.log('searched')
	}
}

4.4.4 同时查询数据列表和数据总数

  • findAndCountAll方法用来查询满足条件的数据列表和数据总数，在分页场景下用的比较多，该方法返回一个包含rows（模型实例列表）和count（数据总数）属性的对象。
1
2
3
4
5
6
7
8

async function example() {
	const { rows, count } = await User.findAndCountAll({
		where: {
			status: 1
		}
	})
	console.log(rows.map(row => row.toJSON()), count)
}

4.4.5 统计数据

  • count方法用来统计所有数据或者满足条件的数据的总数量，用于分页
1
2
3
4
5
6
7
8

async function example() {
	const count = User.count({ // 也可直接User.count()返回全部数据量
		where: {
			status: 1
		}
	})
	console.log('status为1的用户总数为', count)
}

4.5 查询语法

主要是针对where查询的配置项

4.5.1 基本使用

 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
32

const Op = Sequelize.Op

// select * from post where authorId = 2;
Post.findAll({
	where: {
		authorId: 2
	}
})
// select * from post where anthorId = 12 and status = 'active';
Post.findAll({
	where: {
		[Op.and]: [{authorId: 12}, {status: 'active'}]
	}
})
// select * from post where authorId = 12 or authorId = 13;
Post.findAll({
	where: {
		authorId: {
			[Op.or]: [12, 13]
		}
	}
})
// update post set updatedAt = null where deletedAt is not null
Post.update({
	updatedAt: null
}, {
  where: {
    deletedAt: {
      [Op.ne]: null
    }
  }
})

4.5.2 常用运算符

 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
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

const { Op } = require("sequelize");
Post.findAll({
  where: {
    [Op.and]: [{ a: 5 }, { b: 6 }],            // (a = 5) AND (b = 6)
    [Op.or]: [{ a: 5 }, { b: 6 }],             // (a = 5) OR (b = 6)
    someAttribute: {
      // Basics
      [Op.eq]: 3,                              // = 3
      [Op.ne]: 20,                             // != 20
      [Op.is]: null,                           // IS NULL
      [Op.not]: true,                          // IS NOT TRUE
      [Op.or]: [5, 6],                         // (someAttribute = 5) OR (someAttribute = 6)

      // Using dialect specific column identifiers (PG in the following example):
      [Op.col]: 'user.organization_id',        // = "user"."organization_id"

      // Number comparisons
      [Op.gt]: 6,                              // > 6
      [Op.gte]: 6,                             // >= 6
      [Op.lt]: 10,                             // < 10
      [Op.lte]: 10,                            // <= 10
      [Op.between]: [6, 10],                   // BETWEEN 6 AND 10
      [Op.notBetween]: [11, 15],               // NOT BETWEEN 11 AND 15

      // Other operators

      [Op.all]: sequelize.literal('SELECT 1'), // > ALL (SELECT 1)

      [Op.in]: [1, 2],                         // IN [1, 2]
      [Op.notIn]: [1, 2],                      // NOT IN [1, 2]

      [Op.like]: '%hat',                       // LIKE '%hat'
      [Op.notLike]: '%hat',                    // NOT LIKE '%hat'
      [Op.startsWith]: 'hat',                  // LIKE 'hat%'
      [Op.endsWith]: 'hat',                    // LIKE '%hat'
      [Op.substring]: 'hat',                   // LIKE '%hat%'
      [Op.iLike]: '%hat',                      // ILIKE '%hat' (case insensitive) (PG only)
      [Op.notILike]: '%hat',                   // NOT ILIKE '%hat'  (PG only)
      [Op.regexp]: '^[h|a|t]',                 // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
      [Op.notRegexp]: '^[h|a|t]',              // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
      [Op.iRegexp]: '^[h|a|t]',                // ~* '^[h|a|t]' (PG only)
      [Op.notIRegexp]: '^[h|a|t]',             // !~* '^[h|a|t]' (PG only)

      [Op.any]: [2, 3],                        // ANY ARRAY[2, 3]::INTEGER (PG only)
      [Op.match]: Sequelize.fn('to_tsquery', 'fat & rat') // match text search for strings 'fat' and 'rat' (PG only)

      // In Postgres, Op.like/Op.iLike/Op.notLike can be combined to Op.any:
      [Op.like]: { [Op.any]: ['cat', 'hat'] }  // LIKE ANY ARRAY['cat', 'hat']

      // There are more postgres-only range operators, see below
    }
  }
});

4.6 事务

5 关联