How Node.js Sequelize implements database read-write separation

1. Introduction

When building a highly concurrent web application, in addition to adopting a load balancing solution at the application layer, the database must also support high availability and high concurrency. The most commonly used database optimization solution is to synchronize data through master-slave replication, and then improve the concurrent load capacity of the database through read-write separation (MySQL-Proxy).

1. Replication option and separation of reading and writing

Sequelize supports read/write separation. To achieve read/write separation, you can create a Sequelize instance for reading and writing respectively. The more convenient way to use it is to create an instance. Specify the read/write database separately through the replication option.

To use read/write replication in Sequelize, you can sometimes pass an object to its replication option when initializing Sequelize. This object has two properties: read and write. write is a single object (ie: writes are handled by a single server), while read is an array containing objects (ie: reads are handled by multiple servers). Each read and write server can contain the following attributes:

· host - the host of the database server

· port - the host port of the database server

· username - verification username

· password - verification password

· database - the database to be connected

2. Sequelize read/write separation example

In multiple database clusters using master-slave replication, you can set it in the read attribute of the replication object, which is an array , into which one or more copies of the server connection can be passed in. The read operation is equivalent to operating on the slave node in the database cluster, which handles all SELECT query operations (read operations). The write attribute of the replication object is an object that represents the server connection. The write operation is equivalent to the primary node. It will handle all insert, update, and delete operations (write operations).

var sequelize = new Sequelize('database', null, null, {
 dialect: 'mysql',
 port: 3306
 replication: {
  read: [
   { host: '192.168.1.33', username: 'itbilu.com', password: 'pwd' },
   { host: 'localhost', username: 'root', password: null }
  ],
  write: { host: 'localhost', username: 'root', password: null }
 },
 pool: { // 如果需要重写链接池，请在 pool 选项中修改
  maxConnections: 20,
  maxIdleTime: 30000
 },
})

All global settings will apply to all node copies, so there is no need to specify them separately for each instance. In the above example, the database name and port number will apply to all node replicas, as will the username and password options. If a node replica does not use global settings, it needs to be specified separately in the replication options.

Note: Sequelize does not set up master-slave replication nodes and data synchronization (replication) between nodes. These operations are actually completed by MySQL (or the database you use). Sequelize is only responsible for writing or reading data from the master and slave nodes.

Sequelize will use a connection pool to manage node copies.

The default option is:

{
 maxConnections: 10,
 minConnections: 0,
 maxIdleTime:1000
}