Sequelize 教程

最后修改于 2023 年 10 月 18 日

在本文中，我们将展示如何使用 Sequelize ORM 在 JavaScript 中编写数据库程序。

Sequelize

Sequelize 是一个基于 promise 的 Node.js ORM。它适用于 PostgreSQL、MySQL、SQLite 和 MSSQL 方言，并具有强大的事务支持、关系、读取复制等功能。

对象关系映射（ORM）是一种从面向对象的语言访问关系数据库的技术。

在本文中，我们使用 MySQL。

设置 Sequelize

我们初始化一个 Node 应用程序并安装 Sequelize 和 MySQL 适配器。

$ npm init

我们启动一个新的 Node 应用程序。

$ npm i sequelize
$ nmp i mysql2

我们安装 Seqelize 和 MySQL 驱动程序。有两个可用的驱动程序：mysql 和 mysql2；我们选择了后者。

Sequelize 认证

在第一个示例中，我们创建一个与 MySQL 数据库的连接。

authenticate.js

const Sequelize = require('sequelize');

const path = 'mysql://user12:12user@localhost:3306/testdb';
const sequelize = new Sequelize(path, { operatorsAliases: false });

sequelize.authenticate().then(() => {
  console.log('Connection established successfully.');
}).catch(err => {
  console.error('Unable to connect to the database:', err);
}).finally(() => {
  sequelize.close();
});

当连接到 MySQL 数据库时，该示例会打印一条消息。

const Sequelize = require('sequelize');

我们加载 Sequelize 模块。

const path = 'mysql://user12:12user@localhost:3306/testdb';

这是 MySQL 连接路径。它包含用户名、密码、主机名、数据库端口和数据库名称。

const sequelize = new Sequelize(path, { operatorsAliases: false });

我们实例化 Sequelize。

sequelize.authenticate().then(() => {
  console.log('Connection established successfully.');
...

authenticate 方法通过尝试向数据库进行身份验证来测试连接。当连接建立成功时，我们会打印一条消息。

}).catch(err => {
  console.error('Unable to connect to the database:', err);
...

如果发生错误，我们会打印一条错误消息。

}).finally(() => {
  sequelize.close();
});

最后，我们关闭数据库连接。

$ node authenticate.js
Executing (default): SELECT 1+1 AS result
Connection established successfully

输出也包括调试输出。

Sequelize 模型定义

一个 Model 代表数据库中的一个表。这个类的实例代表一个数据库行。 Sequelize 的 define 方法定义一个新模型。

define_model.js

const Sequelize = require('sequelize');

const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false
});

let Dummy = sequelize.define('dummy', {
    description: Sequelize.STRING
});

Dummy.sync().then(() => {
    console.log('New table created');
}).finally(() => {
    sequelize.close();
})

该示例创建一个简单的模型。它将模型保存到数据库表中。

let Dummy = sequelize.define('dummy', {
    description: Sequelize.STRING
});

创建了一个新模型 Dummy。第一个参数是模型名称。第二个参数由属性组成，这些属性是表列。在我们的例子中，我们有一个名为 description 的列，它的类型是字符串。

Dummy.sync().then(() => {
    console.log('New table created');
}).finally(() => {
    sequelize.close();
})

sync 方法将模型同步到数据库。实际上，它创建一个新的 dummies 表。（表名是复数。）

$ node model_define.js
Executing (default): CREATE TABLE IF NOT EXISTS `dummies` (`id` INTEGER 
NOT NULL auto_increment , `description` VARCHAR(255), 
`createdAt` DATETIME NOT NULL, `updatedAt` DATETIME NOT NULL, 
PRIMARY KEY (`id`)) ENGINE=InnoDB;
Executing (default): SHOW INDEX FROM `dummies`
New table created

这是输出。默认情况下，Sequelize 提供日志记录。可以使用 logging 选项关闭它。

mysql> describe dummies;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| description | varchar(255) | YES  |     | NULL    |                |
| createdAt   | datetime     | NO   |     | NULL    |                |
| updatedAt   | datetime     | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

我们在 MySQL 中检查创建的表。 Sequelize 还创建了两个额外的列：createdAt 和 updatedAt。可以使用 timestamps 选项将其关闭。

Sequelize 删除表

使用 drop 方法删除一个表。

drop_table.js

const Sequelize = require('sequelize');

const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Dummy = sequelize.define('dummy', {
    description: Sequelize.STRING
});

Dummy.drop().then(() => {
    console.log('table deleted');
}).finally(() => {
    sequelize.close();
});

该示例删除 dummies 表。

Sequelize 时间戳

Sequelize 会自动将时间戳添加到模型中。我们可以使用 timestamps 来控制此行为。

timestamps.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false,
    define: {
        timestamps: false
    }
});

let Dummy = sequelize.define('dummy', {
    description: Sequelize.STRING
});

sequelize.sync({force: true}).then(() => {

    Dummy.create({ description: 'test 1' }).then(() => {
        console.log('table created');
    }).finally(() => {
        sequelize.close();
    });
});

该示例创建一个没有时间戳的表。

const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false,
    define: {
        timestamps: false
    }
});

在这里，我们关闭时间戳。

mysql> describe dummies;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| description | varchar(255) | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

我们确认表中没有时间戳。

Sequelize bulkCreate

bulkCreate 方法批量创建和插入多个实例。该方法接受一个对象数组。

bulk_create_notes.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

let notes = [
    { description: 'Tai chi in the morning' },
    { description: 'Visited friend' },
    { description: 'Went to cinema' },
    { description: 'Listened to music' },
    { description: 'Watched TV all day' },
    { description: 'Walked for a hour' },
];

sequelize.sync({ force: true }).then(() => {
    Note.bulkCreate(notes, { validate: true }).then(() => {
        console.log('notes created');
    }).catch((err) => {
        console.log('failed to create notes');
        console.log(err);
    }).finally(() => {
        sequelize.close();
    });
});

该示例创建一个带有几行的表 notes。

const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

我们禁用日志记录。

sequelize.sync({ force: true }).then(() => {

sqeuelize.syn 同步所有模型。如果存在，force 选项会在创建之前删除表。

Note.bulkCreate(notes, { validate: true }).then(() => {
    console.log('notes created');
...

bulkCreate 创建一个包含六行的表。

mysql> select * from notes;
+----+------------------------+---------------------+---------------------+
| id | description            | createdAt           | updatedAt           |
+----+------------------------+---------------------+---------------------+
|  1 | Tai chi in the morning | 2018-10-21 14:34:28 | 2018-10-21 14:34:28 |
|  2 | Visited friend         | 2018-10-21 14:34:28 | 2018-10-21 14:34:28 |
|  3 | Went to cinema         | 2018-10-21 14:34:28 | 2018-10-21 14:34:28 |
|  4 | Listened to music      | 2018-10-21 14:34:28 | 2018-10-21 14:34:28 |
|  5 | Watched TV all day     | 2018-10-21 14:34:28 | 2018-10-21 14:34:28 |
|  6 | Walked for a hour      | 2018-10-21 14:34:28 | 2018-10-21 14:34:28 |
+----+------------------------+---------------------+---------------------+
6 rows in set (0.00 sec)

这是数据库中创建的表。

Sequelize build, save

使用 build 和 save 或使用 create 在一个步骤中创建新行。

build_save.js

const Sequelize = require('sequelize');

const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

const note = Note.build({ description: 'Took a cold bath' });
note.save().then(() => {
    console.log('new task saved');
}).finally(() => {
    sequelize.close();
});

该示例使用 build 和 save 创建一个新笔记。

Sequelize findById

使用 findById，我们通过其 Id 查找特定的行。

find_by_id.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';

const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

Note.findById(2).then((note) => {
    console.log(note.get({ plain: true }));
    console.log('********************')
    console.log(`id: ${note.id}, description: ${note.description}`);
}).finally(() => {
    sequelize.close();
});

该示例查找 Id 为 2 的笔记。

console.log(note.get({ plain: true }));

默认情况下，Sequelize 返回大量元数据。要关闭元数据，我们使用 plain: true 选项。

$ node find_by_id.js
{ id: 2,
  description: 'Visited friend',
  createdAt: 2018-10-21T14:34:28.000Z,
  updatedAt: 2018-10-21T14:34:28.000Z }
********************
id: 2, description: Visited friend

我们打印该行两次。在第一种情况下，我们返回所有数据。在第二种情况下，我们只选择两个字段。

Sequelize findOne

findOne 方法搜索单个行。

find_one.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';

const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

Note.findOne({ where: { id: 1 } }).then(note => {
    console.log(note.get({ plain: true }));
}).finally(() => {
    sequelize.close();
});

该示例使用 find_one 从表中返回第一行。 where 选项指定要查找的 Id。

$ node find_one.js
{ id: 1,
  description: 'Tai chi in the morning',
  createdAt: 2018-10-21T14:34:28.000Z,
  updatedAt: 2018-10-21T14:34:28.000Z }

Sequelize 与 async, await

在下一个示例中，我们使用 async 和 await 关键字。

find_one2.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';

const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

async function getOneNote() {

    let user = await Note.findOne();

    console.log(user.get('description'));
    sequelize.close();
}

getOneNote();

我们使用 async 和 await 关键字通过 findOne 返回第一行。

Sequelize count

count 方法计算表中的行数。

count_rows.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

async function countRows() {

    let n = await Note.count();
    console.log(`There are ${n} rows`);
    
    sequelize.close();
}

countRows();

该示例计算 notes 表中的行数。

$ node count_rows.js
There are 7 rows

目前，我们在表中拥有七行。

Sequelize 删除行

使用 destroy 方法删除一行。它返回已删除的行数。

delete_row.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

async function deleteRow() {

    let n = await Note.destroy({ where: { id: 2 } });
    console.log(`number of deleted rows: ${n}`);

    sequelize.close();
}

deleteRow();

该示例删除 Id 为 2 的行。

Sequelize 更新行

使用 update 方法更新一行。

update_row.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

async function updateRow() {

    let id = await Note.update(
        { description: 'Finished reading history book' },
        { where: { id: 1 } });
    sequelize.close();
}

updateRow();

该示例更新第一行的描述。

Sequelize findAll

findAll 方法搜索多个实例。

find_all.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

async function findAllRows() {

    let notes = await Note.findAll({ raw: true });
    console.log(notes);

    sequelize.close();
}

findAllRows();

该示例使用 findAll 从数据库表中检索所有行。

let notes = await Note.findAll({ raw: true });

raw: true 选项关闭元数据。

$ node find_all.js
[ { id: 1,
    description: 'Finished reading history book',
    createdAt: 2018-10-21T14:34:28.000Z,
    updatedAt: 2018-10-21T16:00:22.000Z },
  { id: 2,
    description: 'Visited friend',
    createdAt: 2018-10-21T14:34:28.000Z,
    updatedAt: 2018-10-21T14:34:28.000Z },
  { id: 3,
    description: 'Went to cinema',
    createdAt: 2018-10-21T14:34:28.000Z,
    updatedAt: 2018-10-21T14:34:28.000Z },
  { id: 4,
    description: 'Listened to music',
    createdAt: 2018-10-21T14:34:28.000Z,
    updatedAt: 2018-10-21T14:34:28.000Z },
  { id: 5,
    description: 'Watched TV all day',
    createdAt: 2018-10-21T14:34:28.000Z,
    updatedAt: 2018-10-21T14:34:28.000Z },
  { id: 6,
    description: 'Walked for a hour',
    createdAt: 2018-10-21T14:34:28.000Z,
    updatedAt: 2018-10-21T14:34:28.000Z },
  { id: 7,
    description: 'Took a cold bath',
    createdAt: 2018-10-21T14:49:51.000Z,
    updatedAt: 2018-10-21T14:49:51.000Z } ]

该示例返回了七行。

Sequelize 选择列

使用 attributes 选项，我们可以选择要包含在查询中的列。

columns.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

async function getTwoColumns() {

    let notes = await Note.findAll({ attributes: ['id', 'description'], raw: true });
    console.log(notes);

    sequelize.close();
}

getTwoColumns();

在该示例中，我们选择 id 和 description 列。

$ node columns.js
Executing (default): SELECT `id`, `description` FROM `notes` AS `notes`;
[ { id: 1, description: 'Finished reading history book' },
  { id: 3, description: 'Went to cinema' },
  { id: 4, description: 'Listened to music' },
  { id: 5, description: 'Watched TV all day' },
  { id: 6, description: 'Walked for a hour' } ]

Sequelize offset, limit

使用 offset 和 limit 属性，我们可以定义行的初始跳过和要包含在 findAll 方法中的行数。

offset_limit.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

async function getRows() {

    let notes = await Note.findAll({ offset: 2, limit: 3, 
        attributes: ['id', 'description'], raw: true
    });
    
    console.log(notes);

    sequelize.close();
}

getRows();

该示例返回三行，从第二行开始。

$ node offset_limit.js
[ { id: 3, description: 'Went to cinema' },
  { id: 4, description: 'Listened to music' },
  { id: 5, description: 'Watched TV all day' } ]

Sequelize order by 子句

要在查询中包含 ORDER BY 子句，我们使用 order 选项。

order_by.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

async function getRows() {

    let notes = await Note.findAll({
        order: [['description', 'DESC']],
        attributes: ['id', 'description'], raw: true
    })

    console.log(notes);

    sequelize.close();
}

getRows();

在该示例中，我们从表中选择所有行，并按描述降序排列。

$ node order_by.js
Executing (default): SELECT `id`, `description` FROM `notes` AS `notes` 
    ORDER BY `notes`.`description` DESC;
[ { id: 3, description: 'Went to cinema'}, { id: 5, description: 'Watched TV all day' },
  { id: 6, description: 'Walked for a hour'}, { id: 2, description: 'Visited friend' },
  { id: 1, description: 'Tai chi in the morning' },
  { id: 4, description: 'Listened to music' } ]

从输出中我们可以看到 ORDER BY 子句已添加到查询中。

Sequelize Op.IN 运算符

使用 Op.IN 运算符，我们可以确定指定的值是否与子查询或列表中的任何值匹配。

operator_in.js

const Sequelize = require('sequelize');
const Op = Sequelize.Op;

const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

async function getRows() {
    
    let notes = await Note.findAll({ where: { id: { [Op.in]: [3, 6] } } });

    notes.forEach(note => {
        console.log(`${note.id}: ${note.description}`);
    });

    sequelize.close();
}

getRows();

在该示例中，我们选择与 Id 列表匹配的所有行。

$ node operator_in.js
3: Went to cinema
6: Walked for a hour

输出显示了两行：Id 为 3 和 6。

Sequelize Op.between 运算符

使用 Op.between 运算符，我们可以确定指定的值是否与给定范围内的任何值匹配。

operator_between.js

const Sequelize = require('sequelize');
const Op = Sequelize.Op;

const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Note = sequelize.define('notes', {
    description: Sequelize.STRING
});

async function getRows() {

    let notes = await Note.findAll({ where: { id: { [Op.between]: [3, 6] } }});

    notes.forEach(note => {
        console.log(`${note.id}: ${note.description}`);
    });
    
    sequelize.close();
}

getRows();

该示例使用 Op.between 运算符显示第 3..6 行。

Sequelize belongsTo

Sequelize belongsTo 在源模型和提供的目标模型之间创建一个一对一的关联。外键添加到源上。

belongs_to.js

const Sequelize = require('sequelize');

const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Employee = sequelize.define('employees', {
    name: Sequelize.STRING
});

let Project = sequelize.define('projects', {
    name: Sequelize.STRING
});

Employee.belongsTo(Project);

let employees = [
    { name: 'Jane Brown' }, { name: 'Lucia Benner' }, { name: 'Peter Novak' }
];

sequelize.sync({ force: true }).then(() => {
    return Employee.bulkCreate(employees);
}).then((employees) => {

    let works = [];
    let i = 0;

    employees.forEach(employee => {

        let pname = 'Project ' + String.fromCharCode('A'.charCodeAt() + i);
        i++;
        
        let work = Project.create({ name: pname }).then(project => {

            employee.setProject(project);
        });

        works.push(work);

    });

    Promise.all(works).then(() => sequelize.close());
    console.log('finish');

});

在该示例中，我们有两个模型：Employee 和 Project。我们使用 belongsTo 在两个模型之间创建一对一的关联。我们向模型添加数据。

let Employee = sequelize.define('employees', {
    name: Sequelize.STRING
});

let Project = sequelize.define('projects', {
    name: Sequelize.STRING
});

我们定义了两个模型。

Employee.belongsTo(Project);

我们在 Employee 和 Project 模型之间创建一个一对一的关联。外键是在 Employee 中生成的。

let employees = [
    { name: 'Jane Brown' }, { name: 'Lucia Benner' }, { name: 'Peter Novak' }
];

我们将创建三名员工。

let works = [];

works 数组用于存储生成的 promises。

employees.forEach(employee => {

        let pname = 'Project ' + String.fromCharCode('A'.charCodeAt() + i);
        i++;
        
        let work = Project.create({ name: pname }).then(project => {

            employee.setProject(project);
        });

        works.push(work);

    });

我们遍历员工数组并为他们中的每个人生成一个新项目。使用 setProject 添加一个新项目。 Project.create 生成一个新的 promise，该 promise 被添加到 works 数组中。

Promise.all(works).then(() => sequelize.close());

Promise.all 解析数组中的所有 promises。

接下来，我们检索已连接的数据。当我们生成也从其他表中获取关联数据的查询时，我们有_急切加载_。使用 include 选项启用急切加载。

belongs_to2.js

const Sequelize = require('sequelize');

const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Employee = sequelize.define('employees', {
    name: Sequelize.STRING
});

let Project = sequelize.define('projects', {
    name: Sequelize.STRING
});

Employee.belongsTo(Project);

Employee.findAll({include: [Project]}).then(employees => {

    employees.forEach(employee => {
        console.log(`${employee.name} is in project ${employee.project.name}`);
    });
}).finally(() => {
    sequelize.close();
});

该示例列出了员工及其项目。

Employee.findAll({include: [Project]}).then(employees => {

在查询中，我们添加 include 选项，它包含关联模型。

$ node belongs_to2.js 
Jane Brown is in project Project A
Lucia Benner is in project Project B
Peter Novak is in project Project C

Sequelize 双向一对一关系

双向关系在两个方向上都有效。我们可以从源模型引用到目标模型，反之亦然。要在模型之间创建双向一对一关系，我们使用 belongsTo 和 hasOne 映射它们。

bidi_one2one.js

const Sequelize = require('sequelize');

const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let Employee = sequelize.define('employees', {
    name: Sequelize.STRING
});

let Project = sequelize.define('projects', {
    name: Sequelize.STRING
});

Employee.belongsTo(Project);
Project.hasOne(Employee);

Project.findAll({include: [Employee]}).then(projects => {

    projects.forEach(project => {
        console.log(`${project.name} belongs to user ${project.employee.name}`);
    });
}).finally(() => {
    sequelize.close();
});

在此示例中，我们从每个项目中检索一名员工。

Employee.belongsTo(Project);
Project.hasOne(Employee);

为了实现双向关联，我们还使用 hasOne 映射模型。

$ node bidi_one2one.js
Project A belongs to user Jane Brown
Project B belongs to user Lucia Benner
Project C belongs to user Peter Novak

Sequelize hasMany

Sequelize hasMany 在源和提供的目标之间创建一个多对一的关联。外键添加到目标上。

one_to_many.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let User = sequelize.define('user', {
    name: Sequelize.STRING,
});

let Task = sequelize.define('task', {
    description: Sequelize.STRING,
});

User.hasMany(Task);

async function createTables() {

    await User.sync();
    await Task.sync();

    console.log('done');
    sequelize.close();
}

createTables();

首先，我们创建两个表：users 和 tasks。

在第二步，我们用数据填充表。

one_to_many2.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let User = sequelize.define('user', {
    name: Sequelize.STRING
});

let Task = sequelize.define('task', {
    description: Sequelize.STRING,
});

User.hasMany(Task);

let mytasks1 = [
    { description: 'write memo' }, { description: 'check accounts' }
];

let mytasks2 = [
    { description: 'make two phone calls' },
    { description: 'read new emails' },
    { description: 'arrange meeting' }
];

async function addUsersTasks() {

    let user1 = await User.create({ name: 'John Doe' });
    let tasks1 = await Task.bulkCreate(mytasks1);

    await user1.setTasks(tasks1);

    let user2 = await User.create({ name: 'Debbie Griffin' });
    let tasks2 = await Task.bulkCreate(mytasks2);

    await user2.setTasks(tasks2);

    console.log('done');
    sequelize.close();
}

addUsersTasks();

我们有两个用户，他们有一些任务。

let user1 = await User.create({ name: 'John Doe' });

使用 User.create 创建一个新用户。

let tasks1 = await Task.bulkCreate(mytasks1);

使用 Task.bulkCreate 生成新任务。

await user1.setTasks(tasks1);

使用 setTasks 将任务添加到用户。

最后，我们检索数据。

one_to_many3.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let User = sequelize.define('user', {
    name: Sequelize.STRING
});

let Task = sequelize.define('task', {
    description: Sequelize.STRING,
});

User.hasMany(Task);

async function showUsersTasks() {

    let users = await User.findAll({ include: [Task] });

    users.forEach(user => {

        console.log(`${user.name} has tasks: `);

        let tasks = user.tasks;

        tasks.forEach(task => {
            console.log(`  * ${task.description}`);
        })
    });

    console.log('done');
    sequelize.close();
}

showUsersTasks();

在该示例中，我们显示所有用户及其关联任务。

let users = await User.findAll({ include: [Task] });

要启用急切加载，我们使用 include 选项。急切加载是指在查询中也检索关联数据。

$ node one_to_many3.js
John Doe has tasks:
  * write memo  * check accountsDebbie Griffin has tasks:
  * make two phone calls  * read new emails
  * arrange meeting
done

双向多对一关系

双向多对一关系在两个方向上都有效。要在模型之间创建双向多对一关系，我们使用 hasMany 和 belongsTo 映射它们。

bidi_one2many.js

const Sequelize = require('sequelize');
const path = 'mysql://user12:12user@localhost:3306/mydb';
const sequelize = new Sequelize(path, {
    operatorsAliases: false,
    logging: false
});

let User = sequelize.define('user', {
    name: Sequelize.STRING
});

let Task = sequelize.define('task', {
    description: Sequelize.STRING
});

User.hasMany(Task);
Task.belongsTo(User);

async function showTaskUser() {

    let task = await Task.findOne({ include: [User] });

    console.log(`${task.description} belongs to ${task.user.name}`);

    sequelize.close();
}

showTaskUser();

该示例从检索到的任务中获取一个用户。

User.hasMany(Task);
Task.belongsTo(User);

为了实现双向一对一关系，我们使用 hasMany 和 belongsTo 映射模型。

$ node bidi_one2many.js
write memo belongs to John Doe

来源

Sequlize 文档

在本文中，我们使用了 Seqeulize 库。我们创建了一些与 MySQL 交互的命令行程序。

作者

我叫 Jan Bodnar，是一位充满激情的程序员，拥有丰富的编程经验。自 2007 年以来，我一直在撰写编程文章。迄今为止，我已撰写了 1,400 多篇文章和 8 本电子书。我拥有超过十年的编程教学经验。

查看所有 JavaScript 教程。