Node.js 的ORM(Sequelize) 的使用
Sequelize是一个Node.js 的ORM。ORM是对象关系映射(Object Relational Mapping),编程语言的中对象与关系型数据库中的关系(表)进行映射,对象的属性和值映射成表中的列和值。有了ORM,就可以使用面向对象的方式(调用对象的方法)来操作数据库,不用再写SQL语句。登录MySQL,CREATE DATABASE airline; 创建 airline 数据库。mkdir airline && cd airline && npm init -y,npm install sequelize mysql2,就可以使用Sequelize来操作数据库了。touch server.mjs,先连接数据库,就是创建Sequelize的实例
import { Sequelize } from 'sequelize'; // new Sequelize(数据库名, 登录数据库的用户名, 登录数据库的密码, {host: 哪台主机上的数据库, dialect: 使用什么数据库}) const sequelize = new Sequelize('airline', 'root', '123', { host: 'localhost', dialect: 'mysql' }); try { await sequelize.authenticate(); console.log('连接成功'); } catch (error) { console.error('连接失败', error); }
node server.js,连接成功,开始操作数据库。Sequelize有一个Model的概念,它代表数据库中的一张表,所以操作数据库,就要先创建Model,提供表的名称,表的列及其数据类型,从而和数据库表进行关联,这样操作Model就相当于操作表。两种方式创建Model,sequelize.define()和继承Model并调用 init()方法。
import { Sequelize, DataTypes } from 'sequelize';
// 参数: model名, 表中的字段及其数据类型, 可选配置项 sequelize.define('FlightSchedule', { originAirport: DataTypes.STRING, destinationAirport: DataTypes.STRING, departureTime: DataTypes.DATE }); // 或者 类名就是model名 class FlightSchedule extends Model { } // int参数:属性(对应表中的字段), 配置项 FlightSchedule.init({ originAirport: DataTypes.STRING, destinationAirport: DataTypes.STRING, departureTime: DataTypes.DATE }, { sequelize, });
创建Model并没有提供表名,默认情况下,Sequelize会把Model名进行复数化,当作表名。FlightSchedule就代表flightschedules表。当然,表名可以在可选配置项中进行配置,也可以禁止复数化,数据库的字段名也可以不用驼峰命名,而是使用 _连接,
{ freezeTableName: true, // 表名是flightschedule tableName: 'a', // 直接定义表名为a underscored: true // 数据库中的对应的字段是origin_airport }
这里就不配置了,使用默认值就好。但此时数据库中并没有flightschedules表,怎么操作它?Sequelize提供了一个sync()方法,如果数据库中没有对应的表,它就会创建表,如果有,什么都不做。在define调用的后面
await sequelize.sync();
node server.js ,airline有了flightschedules表,但多了3个字段,id,createdAt和updatedAt。默认情况下,Sequelize会为Model增加三个属性(id, createdAt和updatedAt)。创建model时写了三个属性,实际上model有六个属性。当然createdAt和updatedAt 是可以配置的(在可选配置项里面),timestamps为false,就不会给model上添加createdAt 和updatedAt属性。也可以添加某一个字段,比如timestamps为true, createdAt 为true,就只为model 增加createdAt属性。
作为演示代码,以上使用没有问题。但真正的应用开发,就有很多问题,首先,数据库的配置信息写到主文件中,不利于配置不同的环境,需要配置文件。其次,项目中有多个model,不可能都写到主文件,需要一个目录来维护model。再就是sync()方法,修改表就无能为力了,需要migiration。Sequlize 提供了sequelize-cli 来初始化项目结构。npm install sequelize-cli -D, 再npx sequelize init ,项目多了4个目录。config保存数据库配置信息,models包含项目中的所有Model。它默认包含index.js文件,从models目录下读取文件(fs.readdirSync(__dirname)),然后循环创建model(require(path.join(__dirname, file))(sequelize, Sequelize.DataTypes);),赋值给db对象(db[model.name] = model;),最后把db对象暴露出去。暂时不用它的,删除它。在models下创建flightSchedule.mjs,
export default (sequelize, DataTypes) => { return sequelize.define('FlightSchedule', { originAirport: DataTypes.STRING, destinationAirport: DataTypes.STRING, departureTime: DataTypes.DATE }); };
然后在index.mjs
import { Sequelize, DataTypes } from 'sequelize'; import { createRequire } from 'module'; // 在esm中使用require,处理JSON import createFlightSchedule from './flightSchedule.mjs' // 引入model const env = process.env.NODE_ENV || 'development'; const require = createRequire(import.meta.url); const config = require('../config/config.json')[env] const sequelize = new Sequelize(config.database, config.username, config.password, config); const FlightSchedule = createFlightSchedule(sequelize, DataTypes) // 创建Model export { FlightSchedule, sequelize }
可以看到,默认情况下,连接的是development数据库,在config.json中配置development
"development": { "username": "root", "password": "123", "database": "airline", "host": "127.0.0.1", "dialect": "mysql" }
migrations目录包含对表Schema的定义和修改。创建了FlightSchedule Model,就要创建flightschedules表。新建一个建表migration,npx sequelize migration:generate --name create-flight-schedule,name参数表明这是什么migration。migrations目录下生成了一个文件,名字带有时间戳。它有两个方法,up(…) 用来定义migration要做的事情,down(…) 回滚这个migration做的事情,所以要在up中创建table,在down中删除table。需要注意的是,Sequelize默认会为Model增加id,createdAt和updatedAt属性,所以创建表时,表也要有对应的列
/** @type {import('sequelize-cli').Migration} */ module.exports = { async up(queryInterface, Sequelize) { await queryInterface.createTable('flightSchedules', { id: { allowNull: false, autoIncrement: true, primaryKey: true, type: Sequelize.INTEGER }, originAirport: { type: Sequelize.STRING }, destinationAirport: { type: Sequelize.STRING }, departureTime: { type: Sequelize.DATE }, createdAt: { allowNull: false, type: Sequelize.DATE }, updatedAt: { allowNull: false, type: Sequelize.DATE } }); }, async down(queryInterface, Sequelize) { await queryInterface.dropTable('flightSchedules'); } };
在MySQL中删除flightschedules表。再npx sequelize db:migrate 执行migrations目录下的迁移操作,查看数据库,有了flightschedules表,但也多了SequelizeMeta表(记录执行过的迁移脚本)。执行migrate的时候,Sequelize会按时间戳的顺序遍历整个migrations目录, 然后跳过SequelizeMeta 表中包含的文件,也就是以前执行过的migration文件,不用再重复执行了。
seeders目录快速向数据库中填充数据,方便测试程序。npx sequelize seed:generate --name initial-flight-schedules. seeders目录创建了一个js文件,up(…) 填充数据,down(…) 回滚数据。
/** @type {import('sequelize-cli').Migration} */ module.exports = { async up(queryInterface, Sequelize) { await queryInterface.bulkInsert('flightSchedules', [{ originAirport: "济南", destinationAirport: "武汉", departureTime: "2022-01-01 08:00:00", createdAt: new Date(), updatedAt: new Date(), }], {}); }, async down(queryInterface, Sequelize) { await queryInterface.bulkDelete('flightSchedules', null, {}); } };
npx sequelize db:seed:all,执行seeders目录下的所有文件。npx sequelize db:seed --seed=fileName,指定执行seeders目录下哪个文件。无论执行哪一个命令,flightschedules表中都有一条记录。需要注意的是, db:migrate and db:seed 命令使用 NODE_ENV 环境变量来决定执行到哪个数据库,默认是development 配置下的数据库
再创建一个Airplane Model,在models下新建airplane.mjs
export default (sequelize, DataTypes) => { return sequelize.define('Airplane', { planeModel: DataTypes.STRING, totalSeats: DataTypes.STRING, }); };
再在index.mjs 引入并创建Airplane Model
import createAirplane from './airplane.mjs'; const Airplane = createAirplane(sequelize, DataTypes) export { Airplane, FlightSchedule, sequelize }
创建migration,npx sequelize migration:generate --name create-airplane,
module.exports = { async up(queryInterface, Sequelize) { await queryInterface.createTable('airplanes', { // id, createdAt和updatedAt都一样,这里就省略,没有写了 planeModel: { type: Sequelize.STRING }, totalSeats: { type: Sequelize.INTEGER }, }); }, async down(queryInterface, Sequelize) { await queryInterface.dropTable('airplanes'); } };
npx sequelize db:migrate 执行migration,再npx sequelize seed:generate --name initial-airplanes 建一个seeder文件,填充数据
module.exports = { async up(queryInterface, Sequelize) { await queryInterface.bulkInsert('airplanes', [{ planeModel: 'Airbus A220-100', totalSeats: 110, createdAt: new Date(), updatedAt: new Date() }, { planeModel: 'Airbus A220-300', totalSeats: 110, createdAt: new Date(), updatedAt: new Date() }, { planeModel: 'Airbus A 318', totalSeats: 115, createdAt: new Date(), updatedAt: new Date() }, { planeModel: 'Boeing 707-100', totalSeats: 100, createdAt: new Date(), updatedAt: new Date(), }, { planeModel: 'Boeing 737-100', totalSeats: 85, createdAt: new Date(), updatedAt: new Date() }], {}); }, async down(queryInterface, Sequelize) { await queryInterface.bulkDelete('airplanes', null, {}); } };
npx sequelize db:seed --seed=20231017074324-initial-airplanes.js(需要改成自己的文件名)。再创建一个customer Model,在models目录下,创建customer.mjs
export default (sequelize, DataTypes) => { return sequelize.define('Customer', { name: DataTypes.STRING, email: DataTypes.STRING, }); }
然后index.mjs中引入
import createCustomer from './customer.mjs'; const Customer = createCustomer(sequelize, DataTypes); export { Airplane, Customer, FlightSchedule, sequelize }
最后生成一个migration文件,npx sequelize migration:generate --name create-customer
module.exports = { async up(queryInterface, Sequelize) { await queryInterface.createTable('customers', { // 还是省略了id,createdAt和updateAt name: { type: Sequelize.STRING }, email: { type: Sequelize.STRING }, }); }, async down(queryInterface, Sequelize) { queryInterface.dropTable('customers'); } };
npx sequelize db:migrate执行migration。现在server.mjs
import { sequelize, Customer, Airplane, FlightSchedule } from "./models/index.mjs"; await sequelize.authenticate()
增删改查
新增一条记录:Model名.create(),参数是一个对象,对象的属性就是创建model时定义的属性,Sequelize自动添加的属性,它自己会处理好。返回值是一个model实例,就是插入的表的一行,它有toJSON() 方法
const record = await Customer.create({name: 'Sam', email: '123@qq.com'}); console.log(record.toJSON())
node server.mjs, 数据库中成功插入一条数据。这时可以npm i nodemon -D, npx nodemon server.mjs启动服务器。model名.bulkCreate()批量插入数据,它接受的是数组,数组的每一个元素是对象,对象和create()接受的对象一样
const record = await Customer.bulkCreate([ { name: '张三', email: '456@qq.com' }, { name: '李四', email: '789@qq.com' }, { name: '王五', email: '1789@qq.com' }
])
console.log(record.map(r => r.toJSON()))
删除:删除一条记录,可以先model名.findOne() 查出这个实例, 然后再在实例上调用destroy()方法
var record = await Customer.findOne({ where: { id: 3 } }); await record.destroy();
删除多条记录,那就要用model名.destroy(),destroy的参数就是筛选条件。
await Customer.destroy({ where: { id: 2 } });
删除还有硬删除和软删除之分。创建model时配置了paranoid: true,就表示软删除,删除只是在model实例上添加deleteAt字段,不会真正的从数据库中删除数据,当然前提是timestamps 设为true,此时要硬删除,就要在调用destroy方法时,添加force: true。如果软删除,在migration的时候,要在表中创建deleteAt字段。
await Customer.destroy({ where: { id: 4 }, force: true });
更新:更新多条记录,使用model名.update(),第一个参数是要更新成什么, 第二个参数是查询条件
await Customer.update( { name: '张三', email: '456@qq.com' }, { where: { id: 1 } } );
如果只想更新一条记录,先调用model名.findOne()获取到实例,然后修改实例的属性或调用increment 和decrement 方法修改属性,最后调用实例上的save()。
var record = await Customer.findOne({ where: { id: 1 } }); record.name = 'Sam' await record.save()
查询:modal名.方法名,比如findAll,findone。
const airplances = await Airplane.findAll(); // findAll返回的是数组。 console.log(airplances.map(ap => ap.toJSON())); const airplance = await Airplane.findOne(); console.log(airplance.toJSON())
查询方法中,带有attributes参数,列出需要的某个或某些字段
const airplance = await Airplane.findOne({ attributes: ['planeModel', 'totalSeats'] }); console.log(airplance.toJSON())
如果要排除某个字段,用exclude
const airplance = await Airplane.findOne({ attributes: {exclude: ['createdAt']} });
如果对字段进行重命名,数组中的元素需要是一个数组,它的第一项是原字段名,第二项是新字段名。
const airplance = await Airplane.findOne({ attributes: ['planeModel', ['totalSeats', 'seats']] // totalSeats 重命名为 seats });
查询方法中,提供where进行条件查询,为此Sequelize还专门提供了Op。
import { Op } from 'sequelize'; const airplance = await Airplane.findOne({ where: { id: { [Op.or]: [1, 2] /** * 或 * [Op.or]: { * [Op.lt]: 2, * [Op.eq]: null * } */ } } });
如果查询中调用函数,要用sequelize.fn,第一个参数是函数名,第二个参数用sequelize.col来指定对哪一个属性来进行函数操作。如果在where条件中使用函数,sequelize.where 来表示相等性。
const airplances = await Airplane.findAll({ where: sequelize.where( // sequelize.where的第二个参数是基本类型,它就进行相等比较 sequelize.fn('char_length', sequelize.col('planeModel')), 5) // 进行其他比较,第二个参数是个对象,定义比较 // sequelize.fn('char_length', sequelize.col('planeModel')), { // [Op.gt]: 12 // }) });
如果在查询字段中调用函数,要定义别名
const airplance = await Airplane.findOne({ attributes: [ [sequelize.fn('char_length', sequelize.col('planeModel')), 'totalLength'] ], });
查询方法参数中带有order,就是排序,它是一个数组,数组的每一项也是一个数组,子数组的第一项指定按哪个字段进行排序,第二项指定按升序还是降序进行排序
const airplances = await Airplane.findAll({ order: [ ['totalSeats', 'DESC'] ] });
查询方法的参数中带有group,就是分组,指定按哪一个字段进行分组。分组之后,通常使用聚合函数,聚合函数的使用和普通函数的用法相同,
const airplances = await Airplane.findAll({ attributes: ['planeModel', [sequelize.fn('SUM', sequelize.col('totalSeats')), 'totalSeatsCount']], group: 'planeModel' });
查询方法中,带有offset和limit,就是用于分页
const airplances = await Airplane.findAll({ offset: 1, limit: 2 });
sequlieze.query 可以执行原生sql。
const airplances = await sequelize.query('SELECT * FROM airplanes', {
type: QueryTypes.SELECT,
});
在model的定义中,除了定义必要的属性,还可以对每一个属性定义setter,getter函数。getter函数对从数据库获取回来的数据进行转化,比如小写转化成大写。setter函数先对数据进行转化,再存储到数据库,比如对密码进行加密。甚至可以设置虚拟字段,该字段不存数据库,sequlize自己做的转化,主要作用是组合不同的属性,它的类型是DataType.VIRTUAL,然后设置get方法返回值。把Customer modal 修改如下
function hash(value) { return value + '@xdsd' } export default (sequelize, DataTypes) => { return sequelize.define('Customer', { name: { type: DataTypes.STRING, get() { // getDataValue 返回存储在数据库中的原始值 const rawValue = this.getDataValue('name'); return rawValue.toUpperCase() } }, email: { type: DataTypes.STRING, set(value) { // setDataValue对用户输入的值进行操作 this.setDataValue('email', hash(value)); }, }, fullName: { type: DataTypes.VIRTUAL, get() { return `${this.name} ${this.email}`; } } }); }
新增一个user 并查询
await Customer.create({name: 'Jason', email: '2345@qq.com'})
const user = await Customer.findAll()
可以看到
{ "name": "JASON", // name 大写 "fullName": "JASON 2345@qq.com@xdsd", // 虚拟字段 "email": "2345@qq.com@xdsd" // email进行了hash }
关系
关系型数据库中,表与表之间存在1对1,1对多,和多对多的关系,那在Sequelize中,怎么用model来表示这些关系?每一个model都有四个方法,hasOne, BelongsTo, hasMany, BelongsToMany。由于关系是相对的,所以每一种关系都用两个方法实现。
实现1对1,用hasOne和BelongsTo。比如飞机(Airplanes)和飞机详情表(AirplaneDetails), 由于表与表之间的关系是通过外键实现的,外键放到哪张表上?哪张表能单独存在,airplanes能单独存在,details则是依附airplane的,所以外键放到details上,airplanes有一个(hasOne)details, details属于(BelongsTo)airplane,
Airplane.hasOne(AirplaneDetail)
AirplaneDetail.BelongsTo(Airplane)
hasOne把外键放到它的参数上,AirplaneDetail model中,多了一个AirplaneId属性,它所引用的model名+Id,相应的,airplaneDetail表多了AirplaneId列,除非AirplaneId列已存在。当有了关系后,调用hasOne和BelongsTo的model 实例多了几个辅助方法,来方便的设置关系。从数据库查到airplane后,airplane有getAirplaneDetail, setAirplaneDetail 和createAirplaneDetail 方法。AirplaneDetail实例setAirplane等方法。
实现1对多,用hasMany和belongsTo。比如 一架飞机可以执行多次航班(hasMany),但一个航班只能属于一架飞机(BelongsTo)。models下的index.mjs
Airplane.hasMany(FlightSchedule)
FlightSchedule.belongsTo(Airplane)
1对多的关系,外键放到多的那一边,所以FlightSchedule多了AirplaneId属性,对应的,flightschedules表多airplaneId列。为了满足关系,需要做migration。给flightschedules表添加外键,npx sequelize migration:generate --name add-flightschedules-references
module.exports = {
async up (queryInterface, Sequelize) {
await queryInterface.addColumn('FlightSchedules', 'AirplaneId', {
type: Sequelize.INTEGER,
});
await queryInterface.addConstraint('FlightSchedules', {
type: 'foreign key',
fields: ['AirplaneId'],
references: {
table: 'Airplanes',
field: 'id'
},
name: 'fkey_flight_schedules_airplane',
onDelete: 'set null',
onUpdate: 'cascade'
});
},
async down (queryInterface, Sequelize) {
await queryInterface.removeConstraint(
'FlightSchedules', 'fkey_flight_schedules_airplane'
);
await queryInterface.removeColumn('FlightSchedules', 'AirplaneId');
}
};
npx sequelize db:migrate 执行迁移。airplane实例有getFlightSchedules和addFlightSchedule(或addflightSchedules),createFlightSchedule, countFlightSchedules 等方法
const airplance = await Airplane.findOne({ where: { id: 1 } }); const flightSchedule = await FlightSchedule.findOne({ where: { id: 1 } }) await airplance.addFlightSchedule(flightSchedule) // flightSchedules表中,id=1的 AirplaneId 变成了 '1' await airplance.createFlightSchedule({ //创建了一条flightSchedule记录,并且它的AirplaneId也是1 originAirport: "深圳", destinationAirport: "武汉", departureTime: "2023-10-01 20:00:00" }) const flightSchedules = await airplance.getFlightSchedules(); const count = await airplance.countFlightSchedules() console.log(flightSchedules.map(r => r.toJSON()))
实现多对多要用BelongsToMany,因为多对多的关系,需要中间表,所以它还要一个through参数来指定使用哪张表,如果参数是字符串,Sequelize默认表中字段名是两个关联model的名字+id。比如Customer 和FlightSchedule,一个乘客可以乘多趟航班,一趟航班有多个客人,它们之间的关联是购买的飞机票。models下的index.mjs
Customer.belongsToMany(FlightSchedule, { through: 'BoardingTickets' }); FlightSchedule.belongsToMany(Customer, { through: 'BoardingTickets' });
为了满足关系,需要做migration,创建BoardingTickets表, npx sequelize migration:generate --name create-board-ticket
module.exports = { async up(queryInterface, Sequelize) { await queryInterface.createTable('BoardingTickets', { id: { allowNull: false, autoIncrement: true, primaryKey: true, type: Sequelize.INTEGER }, createdAt: { allowNull: false, type: Sequelize.DATE }, updatedAt: { allowNull: false, type: Sequelize.DATE }, CustomerId: { type: Sequelize.INTEGER, references: { model: 'Customers', key: 'id' }, onUpdate: 'set null', onDelete: 'cascade' }, FlightScheduleId: { type: Sequelize.INTEGER, references: { model: 'FlightSchedules', field: 'id' }, onDelete: 'set null', onUpdate: 'cascade' } }); }, async down(queryInterface, Sequelize) { await queryInterface.dropTable('BoardingTickets'); } };
npx sequelize db:migrate 执行迁移。model 实例上多了很多方法来建立联系,比如addFlightSchedule
const customer = await Customer.findOne({ where: { id: 1 } }); const flightSchedule = await FlightSchedule.findOne({ where: { id: 1 } }) customer.addFlightSchedule(flightSchedule)
addFlightSchedule执行的Sql语句是INSERT INTO `BoardingTickets` (`createdAt`,`updatedAt`,`CustomerId`,`FlightScheduleId`) VALUES ('2024-08-15 08:52:37','2024-08-15 08:52:37',1,1); boardingtickets表多了一条记录。getFlightSchedules会把中间表查出来,
const customer = await Customer.findOne({ where: { id: 1 } }); const flightSchedules = await customer.getFlightSchedules(); console.log(JSON.stringify(flightSchedules, undefined, 4))
查询结果如下:
[ { "id": 1, "originAirport": "济南", "destinationAirport": "武汉", "departureTime": "2022-01-01T08:00:00.000Z", "createdAt": "2023-10-21T15:05:37.000Z", "updatedAt": "2023-10-23T14:44:16.000Z", "AirplaneId": 1, "BoardingTickets": { "createdAt": "2023-10-23T15:00:55.000Z", "updatedAt": "2023-10-23T15:00:55.000Z", "CustomerId": 1, "FlightScheduleId": 1 } } ]
如果不想返回中间表,或只想返回中间表的某些属性,给getFlightSchedules传递joinTableAttributes参数,
const flightSchedules = await customer.getFlightSchedules({ joinTableAttributes: [] });
以上的查询称为 Lazy load,先用model 查出一个实例,再用实例的方法,查询另外需要的数据,就是先查一张表,再查另外一张表中,执行了两次query请求,那能不能一次性地把所有数据都查询出来,那就是Eager load,查询的时候,使用include,包含关联的表,连表查询。
const customer = await Customer.findOne({ where: { id: 1 }, include: [{ model: FlightSchedule, //连接FlightSchedules 表 through: { attributes: [] } // 不需要中间表的数据 }] });
一次查出了customer和它关联的 FlightSchedules。
事务:分为 unmanaged 事务和managed事务。 unmanaged事务,手动创建事务,提交或回滚事物
const tx = await sequelize.transaction(); // 创建一个事务 try { const plane = await Airplane.findByPk(2); const schedule = await FlightSchedule.create({ originAirport: '上海', destinationAirport: '北京', departureTime: '2023-10-24 10:10:00', }, { transaction: tx }); await schedule.setAirplane(plane, { transaction: tx }); await tx.commit(); // 提交事务 } catch (error) { await tx.rollback(); // 回滚事务 }
managed transactions 就是自动提交和回滚事务。把要做的事性作为回调函数,传递给sequelize.transaction 就是managed transactions
try { const plane = await Airplane.findByPk(3); const flight = await sequelize.transaction(async (tx) => { const schedule = await FlightSchedule.create({ originAirport: '上海', destinationAirport: '深圳', departureTime: '2023-10-24 10:10:00', }, { transaction: tx }); await schedule.setAirplane(plane, { transaction: tx }); return schedule }) // 在这里自动提交事务 } catch (error) { //在这里,事务已经回滚了 }
对已经存在的数据库使用Sequelize,由于数据库已经存在,我们需要创建model来适配它的Schema。比如数据库有一张表是foo_bars, 它的属性是
id INTEGER AUTOINCREMENT first_name VARCHAR(200) last_name VARCHAR(200) email VARCHAR(200) date_created DATETIME date_updated DATETIME
没有createdAt 和UpdatedAt, 并且表名和字段名都使用下划线,所以在定义model的时候,都需要自定义
{ // options sequelize, modelName: 'FooBar', tableName: 'foo_bars', createdAt: 'date_created', updatedAt: 'date_updated', underscore: true, },
第二个要注意的是如果没有表中没有使用id作为主键,需要在创建的model中删除id,
class FooBar extends Model {} FooBar.removeAttribute('id');