Sequelize 多数据库操作

 2016年12月10日    2567     声明


Sequelize 基于数据库实例定义模型，可以方便的进行数据的增、删、改、查操作，并可以基于模型关系实现连接查询、事务等操作。但这些操作都是基于一个数据库实例，即：一个sequelize实例，除读写分离外，Sequelize也并不支持多数据库操作。如果你的项目中需要对多个数据库进行操作，就不那么方便。本文将针对Sequelize操作多数据库的情况做一些整理。

  1. 模型定义与导出
  2. 基于多数据库的模型操作

1. 模型定义与导出

本文假设有以下两张表，分别存储在数据库db1和db2中：

  • users - 用户信息表，存储db1中
  • orders - 用户订单表，存储db2中，并通过userId字段与users建立关联关系

1.1 模型定义

Users模型定义如下models/users.js：

module.exports = function (sequelize, DataTypes) {
  return sequelize.define('Users', {
    id:{type:DataTypes.BIGINT(11), autoIncrement:true, primaryKey : true, unique : true},
    name: { type: DataTypes.STRING, comment:'姓名' },
    sex: { type: DataTypes.INTEGER, allowNull: false, defaultValue: 0, comment:'性别' }
  },
  {
    timestamps: false,
    underscore: false,
    freezeTableName: true,
    tableName: 'users',
    charset: 'utf8',
    collate: 'utf8_general_ci'
  });
}

Orders模型定义如下models/orders.js：

module.exports = function (sequelize, DataTypes) {
  return sequelize.define('Orders', {
    id: { type: DataTypes.BIGINT(11), autoIncrement: true, primaryKey: true, unique: true, comment:'主键' },
    userId: {type: DataTypes.BIGINT(11), allowNull: false, comment:'用户Id' },
    total: { type: DataTypes.DECIMAL(10, 2), allowNull: false, comment:'订单总额' }
  },
  {
    underscore: false,
    timestamps: false,
    freezeTableName: true,
    tableName: 'orders',
    comment: '订单表',
    charset: 'utf8',
    collate: 'utf8_general_ci'
  });
}

注意：本文假设基于同服务器的两个不同数据库操作。


1.2 模型导出

创建数据库实例工厂models/_dbs.js：

'use strict'

var Sequelize=require('sequelize');

// 数据库1 实例
exports.db1 = function () {
  var sequelize=new Sequelize(
    'db1', 'root', '111111', 
    {
      logging: console.log, 
      timezone: '+08:00' 
    }
  );

  return sequelize;
}

// 数据库2 实例
exports.db2 = function () {
  var sequelize=new Sequelize(
    'db2', 'root', '111111', 
    {
      logging: console.log, 
      timezone: '+08:00' 
    }
  );

  return sequelize;
}

导出模型models/index.js：

'use strict'

var db1 = require('./_dbs').db1();
var db2 = require('./_dbs').db2();

module.exports = {
  Users: db1.import('./users'),
  Orders: db2.import('./orders')
}

Model层封装后并导出后，在外部调用的只是单个模型，在没有跨数据库事务、跨数据库关系模型查询的情况下，所有操作都和单数据库操作无异。


2. 基于多数据库的模型操作

经过以上封装，当操作单个模型时，多数据库与单数据库操作并没有任何区别。而操作关系模型时，会有一些差别，以下是几种操作场景。

在同一数据库实例（同一sequelize实例）中，我们会像下面这样定义模型关系：

// 定义模型关系
Users.hasMany(Orders, {foreignKey: 'userId'});
Orders.belongsTo(Users, {foreignKey: 'userId'});  // 本例中未使用belongsTo关系

注意：定义模型关系后，使用sequelize.sync()同步时，该方法会根据关系创建外键约束。对于不一个数据库中的两张表，则不能使用外键约束，但仍然可以定义模型关系，并手工指定关系键，通过定义模型可以来方便的进行一些关系模模型的查询、创建等操作。


2.1 多数据库数据插入

单模型数据插入

在不使用事务或模型关系的情况下，多数据库数据插入与单数据库并无区别：

// 多数据库数据插入
Users.create({name:'张小三', sex:1}).then(function (user) {
  return Orders.create({userId:user.id, total:199})
}).then(function (order) {
  console.log(order);
})

以上会生成以下两条SQL语名：

INSERT INTO `users` (`id`,`name`,`sex`) VALUES (DEFAULT,'张小三',1);
INSERT INTO `orders` (`id`,`userId`,`total`) VALUES (DEFAULT,1,199);

基于事务的关系模型数据插入

错误方式

在单数据库实例中，我们可以像下面这样基于事务插入数据：

Users.sequelize.transaction(function(t) {
  return Users.create({name:'王小五', sex:1}, {transaction:t}).then(function (user) {
    return Orders.create({userId:user.id, total:199}, {transaction:t})
  })
}).then(function (results) {
  console.log(results)
})

其执行过程如下：

START TRANSACTION;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET autocommit = 1;
INSERT INTO `users` (`id`,`name`,`sex`) VALUES (DEFAULT,'李小四',1);
 INSERT INTO `orders` (`id`,`userId`,`total`) VALUES (DEFAULT,4,199);
ROLLBACK;

可见sequelize.transaction是基于一个数据库实例创建的事务，而Users和Orders分别们于不同的数据库，所以会抛出类型以下表不存在的错误：

Unhandled rejection SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table 'db1.orders' doesn't exist

改良方式

当在同一服务器的不同数据库进行事务操作时，我们会通过数据库前缀来添加数据库及Schema的引用，类似如下：

INSERT INTO `db`.`schema.talbe` (…) VALUES (…);

这样，可以使用sequelize.query()，通过直接执行SQL的方式实现非同数据库实例的数据插入：

Users.sequelize.transaction(function(t) {
  return Users.create({name:'李小四', sex:1}, {transaction:t}).then(function (user) {
    return Users.sequelize.query('INSERT INTO `db2`.`orders` (`userid`, `total`) VALUES($1, $2)', {bind:[user.id, 199], transaction:t})
  })
}).then(function (results) {
  console.log(results)
})

SQL执行过程如下：

START TRANSACTION;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET autocommit = 1;
INSERT INTO `users` (`id`,`name`,`sex`) VALUES (DEFAULT,'李小四',1);
INSERT INTO `db2`.`orders` (`userid`, `total`) VALUES(6, 199)
COMMIT;


多数据库数据查询

使用多个数据库时，依然可以基于模型的关联进行关系模型的查询。

hasMany关系查询成功：

Users.findOne({name:'张小三'}).then(function (user) {
  return user.getOrders({raw:true});
}).then(function (userOrders) {
  console.log(userOrders.dataValues)
})

查询结果如下：

[ { id: 1, userId: 1, total: 199 } ]

连接查询

include连接查询失败

在同一数据库实例中，可以通过include参数方便的进行连接查询。类似如下：

let include = [{
  model: Users, 
  required: false,
  attributes: ['id', 'name']
}];
Orders.findAll({include:include}).then(function (orders) {
  console.log(orders);
})

在本例中，执行的SQL语句如下：

SELECT `Order`.`id`, `Order`.`userId`, `Order`.`total`, `User`.`id` AS `User.id`, `User`.`name` AS `User.name` FROM `orders` AS `Order` LEFT OUTER JOIN `users` AS `User` ON `Order`.`userId` = `User`.`id`;

由于不在同一实例中，当然会执行失败：

Unhandled rejection SequelizeDatabaseError: ER_NO_SUCH_TABLE: Table 'db2.users' doesn't exist

改良方式

同样的，也可以通过sequelize.query()方法来实现跨数据库的连接查询：

Orders.sequelize.query('SELECT `Order`.`id`, `Order`.`userId`, `Order`.`total`, `User`.`id` AS `User.id`, `User`.`name` AS `User.name` FROM `orders` AS `Order` LEFT OUTER JOIN `db1`.`users` AS `User` ON `Order`.`userId` = `User`.`id`', {raw:true}).then((results) => {
  console.log(results)
})

通过视图实现连接查询

除使用sequelize.query()进行连接查询外，还可以通过视图实现连接查询。视图可以理解为只用于查询操作的一种特殊的表。

首先，在数据库db2中创建视图：

CREATE VIEW v_orders AS SELECT `Order`.`id`, `Order`.`userId`, `Order`.`total`, `User`.`name` AS `name` FROM `orders` AS `Order` LEFT OUTER JOIN `db1`.`users` AS `User` ON `Order`.`userId` = `User`.`id`;

定义这个视图的模型：

module.exports = function (sequelize, DataTypes) {
  return sequelize.define('V_Order', {
    id: { type: DataTypes.BIGINT(11), autoIncrement: true, primaryKey: true, unique: true, comment:'主键' },
    userId: {type: DataTypes.BIGINT(11), allowNull: false, comment:'用户Id' },
    total: { type: DataTypes.DECIMAL(10, 2), allowNull: false, comment:'订单总额' },
    name: { type: DataTypes.STRING, comment:'姓名' },
  },
  {
    underscore: false,
    timestamps: false,
    freezeTableName: true,
    tableName: 'v_orders',
    comment: '订单视图',
    charset: 'utf8',
    collate: 'utf8_general_ci'
  });
}

通过视图实现连接查询：

V_Orders.findAll({raw:true}).then((results) => {
  console.log(results);
});

执行过程及查询结果：

Executing (default): SELECT `id`, `userId`, `total`, `name` FROM `v_orders` AS `V_Order`;
[ { id: 1, userId: 1, total: 199, name: '张小三' },
  { id: 2, userId: 6, total: 199, name: '李小四' } ]


本文基于MySQL进行操作演示。除数据“插入”、“查询”操作外，数据“更新”和“删除”也类似，用法请诸君自行思考测试。

本文示例：sequelizeMultiDB