Node.js ORM 框架 Sequelize.js 教程

一,简介

ORM 是"对象-关系映射"(Object/Relational Mapping) 的缩写,就是通过实例对象的语法,完成关系型数据库的操作的技术。面向对象编程把所有实体看成对象(object),关系型数据库则是采用实体之间的关系(relation)连接数据。很早就有人提出,关系也可以用对象表达,这样的话,就能使用面向对象编程,来操作关系型数据库。

ORM 把数据库映射成对象:

数据库的表(table) --> 类(class)
记录(record,行数据)--> 对象(object)
字段(field)--> 对象的属性(attribute)

 

二,举例

举例来说,下面是一行 SQL 语句:

SELECT 
  id, first_name, last_name, phone, birth_date, sex
FROM persons 
WHERE id = 10


程序直接运行 SQL,操作数据库的写法如下:

res = db.execSql(sql);
name = res[0]["FIRST_NAME"];


改成 ORM 的写法如下:

p = Person.findOne({ where: { title: 'My Title' } });
p = await Person.findByPk(123); // 根据主键获取一条数据
name = p.first_name;

一比较就可以发现,ORM 使用对象,封装了数据库操作,因此可以不碰 SQL 语言。开发者只使用面向对象编程,与数据对象直接交互,不用关心底层数据库。

 

三,ORM的优缺点

ORM 的优点:

1.数据模型都在一个地方定义,更容易更新和维护,也利于重用代码。

2.ORM 有现成的工具,很多功能都可以自动完成,比如数据消毒、预处理、事务等等。

3.它迫使你使用 MVC 架构,ORM 就是天然的 Model,最终使代码更清晰。

4基于 ORM 的业务代码比较简单,代码量少,语义性好,容易理解。

5.你不必编写性能不佳的 SQL。


ORM 的缺点:

1.ORM 库不是轻量级工具,需要花很多精力学习和设置。

2.对于复杂的查询,ORM 要么是无法表达,要么是性能不如原生的 SQL。

3.ORM 抽象掉了数据库层,开发者无法了解底层的数据库操作,也无法定制一些特殊的 SQL。

 

四,Sequelize 教程

1,创建一个sequelize对象实例:

const Sequelize = require('sequelize');
const config = require('./config');

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


2,定义模型Pet,告诉Sequelize如何映射数据库表:

var Pet = sequelize.define('pet', {
    id: { type: Sequelize.STRING(50), primaryKey: true},
    name: Sequelize.STRING(100),
    gender: Sequelize.BOOLEAN,
    birth: Sequelize.STRING(10),
    createdAt: Sequelize.BIGINT,
    updatedAt: Sequelize.BIGINT,
    version: Sequelize.BIGINT
}, {
        timestamps: false,
        indexes: [{ unique: true, fields: ['someUnique'] }] //添加索引
});


用sequelize.define()定义Model时,传入名称pet,默认的表名就是pets。第二个参数指定列名和数据类型,如果是主键,需要更详细地指定。第三个参数是额外的配置,我们传入{ timestamps: false }是为了关闭Sequelize的自动添加timestamp的功能。所有的ORM框架都有一种很不好的风气,总是自作聪明地加上所谓“自动化”的功能,但是会让人感到完全摸不着头脑。

我们把通过sequelize.define()返回的Pet称为Model,它表示一个数据模型。

我们把通过Pet.findAll()返回的一个或一组对象称为Model实例,每个实例都可以直接通过JSON.stringify序列化为JSON字符串。但是它们和普通JSON对象相比,多了一些由Sequelize添加的方法,比如save()和destroy()。调用这些方法我们可以执行更新或者删除操作。

可以自定义表名,在第三个参数中,加入属性 freezeTableName: true。

 

3,数据操作

3.1 添加数据,我们可以用Promise的方式写:

var now = Date.now();
Pet.create({
    id: 'g-' + now,
    name: 'Gaffey',
    gender: false,
    birth: '2007-07-07',
    createdAt: now,
    updatedAt: now,
    version: 0
}).then(function (p) {
    console.log('created.' + JSON.stringify(p));
}).catch(function (err) {
    console.log('failed: ' + err);
});

也可以用await写:

(async () => {
    var dog = await Pet.create({
        id: 'd-' + now,
        name: 'Odie',
        gender: false,
        birth: '2008-08-08',
        createdAt: now,
        updatedAt: now,
        version: 0
    });
    console.log('created: ' + JSON.stringify(dog));
})();


3.2 查询数据时,用await写法如下:

var pets = await Pet.findAll({
   where: {name: 'Gaffey'}
});
console.log(`find ${pets.length} pets:`);

for (let p of pets) {
    console.log(JSON.stringify(p));
}


3.3 更新数据

// 可以对查询到的实例调用save()方法
var p = await queryFromSomewhere();
p.gender = true;
p.updatedAt = Date.now();
p.version ++;
await p.save();


// 更新id是86的记录,把is_show设置为“1”
await Article.update({ is_show: "1" }, {
  where: {id: 86}
});

update 换回一个数组,包含一个或者两个元素,第一个元素始终是表中被影响的行数。如果,更新的内容和表中原数据一致,被影响条数会是0。


3.4 删除数据

// 对查询到的实例调用destroy()方法
var p = await queryFromSomewhere();
await p.destroy();


// 使用 option.where 过滤需要删除的记录,返回被删除的行数
var rows = await articleModel.destory({
  where: {id: 86}
});


3.5 Where 查询条件基本用法

const Op = Sequelize.Op;

Post.findAll({
  where: {
    authorId: 2
  }
});
// SELECT * FROM post WHERE authorId = 2

Post.findAll({
  where: {
    authorId: 12,
    status: 'active'
  }
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';

Post.findAll({
  where: {
    [Op.or]: [{authorId: 12}, {authorId: 13}]
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Post.findAll({
  where: {
    authorId: {
      [Op.or]: [12, 13]
    }
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Post.destroy({
  where: {
    status: 'inactive'
  }
});
// DELETE FROM post WHERE status = 'inactive';

Post.update({
  updatedAt: null,
}, {
  where: {
    deletedAt: {
      [Op.ne]: null
    }
  }
});
// UPDATE post SET updatedAt = null WHERE deletedAt NOT NULL;

Post.findAll({
  where: sequelize.where(sequelize.fn('char_length', sequelize.col('status')), 6)
});
// SELECT * FROM post WHERE char_length(status) = 6;


3.6 Where 查询条件运算符

const Op = Sequelize.Op

[Op.and]: {a: 5}           // AND (a = 5)
[Op.or]: [{a: 5}, {a: 6}]  // (a = 5 OR a = 6)
[Op.gt]: 6,                // > 6
[Op.gte]: 6,               // >= 6
[Op.lt]: 10,               // < 10
[Op.lte]: 10,              // <= 10
[Op.ne]: 20,               // != 20
[Op.eq]: 3,                // = 3
[Op.not]: true,            // IS NOT TRUE
[Op.between]: [6, 10],     // BETWEEN 6 AND 10
[Op.notBetween]: [11, 15], // NOT BETWEEN 11 AND 15
[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.iLike]: '%hat'         // ILIKE '%hat' (case insensitive) (PG only)
[Op.notILike]: '%hat'      // NOT ILIKE '%hat'  (PG only)
[Op.startsWith]: 'hat'     // LIKE 'hat%'
[Op.endsWith]: 'hat'       // LIKE '%hat'
[Op.substring]: 'hat'      // LIKE '%hat%'
[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.like]: { [Op.any]: ['cat', 'hat']}
                       // LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
[Op.overlap]: [1, 2]       // && [1, 2] (PG array overlap operator)
[Op.contains]: [1, 2]      // @> [1, 2] (PG array contains operator)
[Op.contained]: [1, 2]     // <@ [1, 2] (PG array contained by operator)
[Op.any]: [2,3]            // ANY ARRAY[2, 3]::INTEGER (PG only)

[Op.col]: 'user.organization_id' // = "user"."organization_id", with dialect specific column identifiers, PG in this example


3.7 查询组合

const Op = Sequelize.Op;

{
  rank: {
    [Op.or]: {
      [Op.lt]: 1000,
      [Op.eq]: null
    }
  }
}
// rank < 1000 OR rank IS NULL

{
  createdAt: {
    [Op.lt]: new Date(),
    [Op.gt]: new Date(new Date() - 24 * 60 * 60 * 1000)
  }
}
// createdAt < [timestamp] AND createdAt > [timestamp]

{
  [Op.or]: [
    {
      title: {
        [Op.like]: 'Boat%'
      }
    },
    {
      description: {
        [Op.like]: '%boat%'
      }
    }
  ]
}
// title LIKE 'Boat%' OR description LIKE '%boat%'


3.8 Pagination / Limiting 分页和限数

// Fetch 10 instances/rows
Project.findAll({ limit: 10 })

// Skip 8 instances/rows
Project.findAll({ offset: 8 })

// Skip 5 instances and fetch the 5 after that
Project.findAll({ offset: 5, limit: 5 })


3.9 Order 排序

Subtask.findAll({
  order: [
    // Will escape title and validate DESC against a list of valid direction parameters
    ['title', 'DESC'],

    // Will order by max(age)
    sequelize.fn('max', sequelize.col('age')),

    // Will order by max(age) DESC
    [sequelize.fn('max', sequelize.col('age')), 'DESC'],

    // Will order by  otherfunction(`col1`, 12, 'lalala') DESC
    [sequelize.fn('otherfunction', sequelize.col('col1'), 12, 'lalala'), 'DESC'],

    // Will order an associated model's created_at using the model name as the association's name.
    [Task, 'createdAt', 'DESC'],

    // Will order through an associated model's created_at using the model names as the associations' names.
    [Task, Project, 'createdAt', 'DESC'],

    // Will order by an associated model's created_at using the name of the association.
    ['Task', 'createdAt', 'DESC'],

    // Will order by a nested associated model's created_at using the names of the associations.
    ['Task', 'Project', 'createdAt', 'DESC'],

    // Will order by an associated model's created_at using an association object. (preferred method)
    [Subtask.associations.Task, 'createdAt', 'DESC'],

    // Will order by a nested associated model's created_at using association objects. (preferred method)
    [Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],

    // Will order by an associated model's created_at using a simple association object.
    [{model: Task, as: 'Task'}, 'createdAt', 'DESC'],

    // Will order by a nested associated model's created_at simple association objects.
    [{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
  ]

  // Will order by max age descending
  order: sequelize.literal('max(age) DESC')

  // Will order by max age ascending assuming ascending is the default order when direction is omitted
  order: sequelize.fn('max', sequelize.col('age'))

  // Will order by age ascending assuming ascending is the default order when direction is omitted
  order: sequelize.col('age')

  // Will order randomly based on the dialect (instead of fn('RAND') or fn('RANDOM'))
  order: sequelize.random()
})


3.10 筛选字段

Model.findAll({
  attributes: ['foo', 'bar']
});
//等同于 SELECT foo, bar ...

Model.findAll({
  attributes: ['foo', ['bar', 'baz']]
});
//等同于SELECT foo, bar AS baz


3.11 实用方法

// 返回ID大于25的数量 
await Project.count({
  where: {id: {[Op.gt]: 25}}
});

await User.max('age'); // 最大年龄
await User.min('age'); // 最小年龄
await User.sum('age'); // 年龄总和



四,执行原生SQL语句

1. 查询结构

sequelize.query('your query', [, options])

// 例子
sequelize.query("SELECT * FROM myTable").then(myTableRows => {
  console.log(myTableRows)
})

 

2. 防注入参数绑定

当SQL语句中的占位符是“?”时,需要传入数组:

sequelize
  .query(
    'SELECT * FROM projects WHERE status = ?',
    { raw: true, replacements: ['active'] }
  )
  .then(projects => {
    console.log(projects)
  })


当SQL语句中的占位符是“:keyword”时,需要传入对象:

sequelize
  .query(
    'SELECT * FROM projects WHERE status = :status ',
    { raw: true, replacements: { status: 'active' } }
  )
  .then(projects => {
    console.log(projects)
  })

默认情况下,函数将返回两个参数 - 一个结果数组,以及一个包含元数据(受影响的行等)的对象。 请注意,由于这是一个原始查询,所以元数据(属性名称等)是具体的方言。 某些方言返回元数据 "within" 结果对象(作为数组上的属性)。 但是,将永远返回两个参数,但对于MSSQL和MySQL,它将是对同一对象的两个引用。

sequelize.query("UPDATE users SET y = 42 WHERE x = 12").spread((results, metadata) => {
  // 结果将是一个空数组,元数据将包含受影响的行数。
})

在不需要访问元数据的情况下,您可以传递一个查询类型来告诉后续如何格式化结果。 例如,对于一个简单的选择查询你可以做:

sequelize.query("SELECT * FROM `users`", { type: sequelize.QueryTypes.SELECT})
  .then(users => {
    // 我们不需要在这里延伸,因为只有结果将返回给选择查询
  })

参考: https://segmentfault.com/a/1190000011583833

另外一种原生查询是使用node-mysql ,参考 https://javascript.net.cn/article?id=343

 

 

五,数据类型

Sequelize支持的数据类型:

Sequelize.STRING                      // VARCHAR(255)
Sequelize.STRING(1234)                // VARCHAR(1234)
Sequelize.STRING.BINARY               // VARCHAR BINARY
Sequelize.TEXT                        // TEXT
Sequelize.TEXT('tiny')                // TINYTEXT
Sequelize.CITEXT                      // CITEXT      PostgreSQL and SQLite only.

Sequelize.INTEGER                     // INTEGER
Sequelize.BIGINT                      // BIGINT
Sequelize.BIGINT(11)                  // BIGINT(11)

Sequelize.FLOAT                       // FLOAT
Sequelize.FLOAT(11)                   // FLOAT(11)
Sequelize.FLOAT(11, 10)               // FLOAT(11,10)

Sequelize.REAL                        // REAL        PostgreSQL only.
Sequelize.REAL(11)                    // REAL(11)    PostgreSQL only.
Sequelize.REAL(11, 12)                // REAL(11,12) PostgreSQL only.

Sequelize.DOUBLE                      // DOUBLE
Sequelize.DOUBLE(11)                  // DOUBLE(11)
Sequelize.DOUBLE(11, 10)              // DOUBLE(11,10)

Sequelize.DECIMAL                     // DECIMAL
Sequelize.DECIMAL(10, 2)              // DECIMAL(10,2)

Sequelize.DATE                        // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres
Sequelize.DATE(6)                     // DATETIME(6) for mysql 5.6.4+. Fractional seconds support with up to 6 digits of precision
Sequelize.DATEONLY                    // DATE without time.
Sequelize.BOOLEAN                     // TINYINT(1)

Sequelize.ENUM('value 1', 'value 2')  // An ENUM with allowed values 'value 1' and 'value 2'
Sequelize.ARRAY(Sequelize.TEXT)       // Defines an array. PostgreSQL only.
Sequelize.ARRAY(Sequelize.ENUM)       // Defines an array of ENUM. PostgreSQL only.

Sequelize.JSON                        // JSON column. PostgreSQL, SQLite and MySQL only.
Sequelize.JSONB                       // JSONB column. PostgreSQL only.

Sequelize.BLOB                        // BLOB (bytea for PostgreSQL)
Sequelize.BLOB('tiny')                // TINYBLOB (bytea for PostgreSQL. Other options are medium and long)

Sequelize.UUID                        // UUID datatype for PostgreSQL and SQLite, CHAR(36) BINARY for MySQL (use defaultValue: Sequelize.UUIDV1 or Sequelize.UUIDV4 to make sequelize generate the ids automatically)

Sequelize.CIDR                        // CIDR datatype for PostgreSQL
Sequelize.INET                        // INET datatype for PostgreSQL
Sequelize.MACADDR                     // MACADDR datatype for PostgreSQL

Sequelize.RANGE(Sequelize.INTEGER)    // Defines int4range range. PostgreSQL only.
Sequelize.RANGE(Sequelize.BIGINT)     // Defined int8range range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DATE)       // Defines tstzrange range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DATEONLY)   // Defines daterange range. PostgreSQL only.
Sequelize.RANGE(Sequelize.DECIMAL)    // Defines numrange range. PostgreSQL only.

Sequelize.ARRAY(Sequelize.RANGE(Sequelize.DATE)) // Defines array of tstzrange ranges. PostgreSQL only.

Sequelize.GEOMETRY                    // Spatial column.  PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT')           // Spatial column with geometry type. PostgreSQL (with PostGIS) or MySQL only.
Sequelize.GEOMETRY('POINT', 4326)     // Spatial column with geometry type and SRID.  PostgreSQL (with PostGIS) or MySQL only.

更多数据类型:http://docs.sequelizejs.com/manual/data-types.html


六,使用 association 连接表

连接查询时,如果要连接查询的两个模型间事先没有定义连接关系,或者要使用定义之外的连接关系。这时,可以通过association来定义或重新定义模型关系。

var assets = await assetModel.findAll({
  include: [{
    association: assetModel.belongsTo(userModel, { foreignKey: 'uuid', targetKey: 'uuid' }),
    attributes: [['nickname', 'nickname'], 'uuid', 'avatar']
  }],
  where,
  offset: start,
  limit: perPage,
  order: [['id', 'desc']]
});

或者查询 Company 模型中的任意一个公司,并查询该公司的管理员:

var include = [{
	association: Company.hasOne(User, {foreignKey:'companyId', as:'manager'}),
	where: {isManager:true}
}]
Company.findOne({include:include})


七,模型同步

定义模型时,你要告诉 Sequelize 有关数据库中表的一些信息. 但是,如果该表实际上不存在于数据库中怎么办? 如果存在,但具有不同的列,较少的列或任何其他差异,该怎么办?

可以通过调用一个异步函数(返回一个Promise)model.sync(options). 通过此调用, Sequelize 将自动对数据库执行 SQL 查询. 请注意,这仅更改数据库中的表,而不更改 JavaScript 端的模型.


User.sync() - 如果表不存在,则创建该表(如果已经存在,则不执行任何操作)

User.sync({ force: true }) - 将创建表,如果表已经存在,则将其首先删除

User.sync({ alter: true }) - 这将检查数据库中表的当前状态,并修改表的结构

sequelize.sync() 自动同步所有模型

User.drop() 删除与模型相关的表

sequelize.drop() 删除所有表


参考:

http://www.ruanyifeng.com/blog/2019/02/orm-tutorial.html

https://www.liaoxuefeng.com

http://docs.sequelizejs.com/manual/querying.html

http://docs.sequelizejs.com/manual/usage.html#executing-raw-sql-queries

https://github.com/demopark/sequelize-docs-Zh-CN/blob/master/core-concepts/model-basics.md

https://itbilu.com/nodejs/npm/EkWJSmmFf.html

修改时间 2021-05-14

真诚赞赏,手留余香
赞赏
随机推荐
Fatal error:Call-time pass-by-reference has been removed
Mac OS原来自带了apache,基本用法总结
gps纠偏及大陆地图偏移原因
PDO预处理语句和存储过程
Express 开发与部署最佳实践
学之者生,用之者死——ACE历史与简评
三点运算符
在网页中调用VLC插件
ThinkPHP v3.1.2版本独立分组功能的解决思路
PS笔记