Sequelize : Using Multiple Databases

With Sequelize, you can set up and manage multiple database instances. Each instance can connect to different database services, including MySQL, PostgreSQL, or SQLite. This setup enables applications to isolate data across different databases or run in multiple database environments.

Step 1: Installing and Configuring Sequelize

First, make sure you have installed Sequelize and the necessary database drivers. For instance, if you are using MySQL and PostgreSQL, install the following npm packages:

bash
npm install --save sequelize
npm install --save mysql2
npm install --save pg pg-hstore

Step 2: Creating Sequelize Instances

Create a separate Sequelize instance for each database. Each instance is configured with the details for connecting to a specific database.

javascript
const { Sequelize } = require('sequelize');

// Create an instance connecting to the MySQL database
const sequelizeMySQL = new Sequelize('mysql_database', 'username', 'password', {
  host: 'localhost',
  dialect: 'mysql'
});

// Create an instance connecting to the PostgreSQL database
const sequelizePostgreSQL = new Sequelize('postgres_database', 'username', 'password', {
  host: 'localhost',
  dialect: 'postgres'
});

Step 3: Using Instances to Operate on Data

Each Sequelize instance can independently define models, run queries, and perform database operations. For instance, consider a User model that can be defined and used separately in both databases.

javascript
// For MySQL
const UserMySQL = sequelizeMySQL.define('User', {
  name: Sequelize.STRING,
  age: Sequelize.INTEGER
});

// For PostgreSQL
const UserPostgreSQL = sequelizePostgreSQL.define('User', {
  name: Sequelize.STRING,
  age: Sequelize.INTEGER
});

// Example: Add a new user to MySQL
UserMySQL.create({ name: 'Alice', age: 25 });

// Example: Add a new user to PostgreSQL
UserPostgreSQL.create({ name: 'Bob', age: 30 });

Step 4: Managing Connections and Transactions

When working with multiple databases, properly manage connections and transactions for each instance. Sequelize offers transaction support to ensure data consistency in case of errors.

javascript
// Start a transaction on the MySQL instance
const t = await sequelizeMySQL.transaction();

try {
  const user = await UserMySQL.create({ name: 'Charlie', age: 35 }, { transaction: t });
  // Additional database operations
  await t.commit();
} catch (error) {
  await t.rollback();
}

Summary

The key to using Sequelize with multiple databases is creating multiple Sequelize instances, each configured with the specific database details. This approach enables applications to flexibly manage data across multiple databases, fulfilling more complex data management requirements. Each instance can independently define models, perform data operations, and handle transactions. This method ensures efficient and stable operation of applications in multi-database environments.