Sequelize.js: How to Connect to Multiple Databases

Introduction

Sequelize is a promise-based Node.js ORM that supports various dialects of SQL databases. Not only can it connect to a single database, but it also provides the facility to connect to multiple databases, which is a common requirement for complex applications. This tutorial will guide you through the steps required to establish connections to multiple databases using Sequelize.js. We will start with basic setup and gradually move to more advanced scenarios.

Prerequisites:

• Basic understanding of Node.js and SQL databases.
• Node.js installed on your machine.
• Knowledge of JavaScript and Promises.
• Sequelize and database drivers installed for the SQL databases you are using (e.g., PostgreSQL, MySQL).

Basic Setup

Firstly, we need to install Sequelize and the necessary database drivers. For example, to connect to MySQL and PostgreSQL, you would:

npm install sequelize
npm install mysql2 pg pg-hstore

Creating Sequelize Instances

Each database connection in Sequelize is represented by an instance of the Sequelize class. To connect to multiple databases, we will create multiple instances:

const Sequelize = require('sequelize');

// For Database 1 (e.g., MySQL)
const db1 = new Sequelize('database1', 'username', 'password', {
  host: 'host1',
  dialect: 'mysql',
  logging: false
});

// For Database 2 (e.g., PostgreSQL)
const db2 = new Sequelize('database2', 'username', 'password', {
  host: 'host2',
  dialect: 'postgres',
  logging: false
});

Defining Models for Each Database

With connections established, you can define models for each database:

// Defining models for Database 1
const Model1 = db1.define('model1', {
  // ... attributes
});

// Defining models for Database 2
const Model2 = db2.define('model2', {
  // ... attributes
});

Handling Connections

Manage your Sequelize instances by testing connections and handling errors accordingly:

db1.authenticate()
  .then(() => console.log('Connection to database1 has been established successfully.'))
  .catch(err => console.error('Unable to connect to database1:', err));

db2.authenticate()
  .then(() => console.log('Connection to database2 has been established successfully.'))
  .catch(err => console.error('Unable to connect to database2:', err));

Advanced Operations

Once you have your databases set up, you might find yourself needing to coordinate transactions or perform database-agnostic operations:

// Starting transaction in database1
return db1.transaction(t1 => {
  // Transactional operations for db1 here
  return Model1.create({ /* ... */ }, { transaction: t1 }).then(result => {
    // You can also start a transaction on db2
    return db2.transaction(t2 => {
      // Transactional operations for db2 here
      return Model2.create({ /* ... */ }, { transaction: t2 });
    });
  });
});

Conclusion

Throughout this tutorial, you’ve learned how to set up multiple database connections using Sequelize in a Node.js application. We started by installing the necessary software, moved onto creating Sequelize instances for each database, and demonstrated how to define models and authenticate connections. Finally, we discussed coordinating operations across databases. With these steps, you can scale your application to work with complex data across different SQL databases with ease.

Remember, when working with multiple databases, keep an eye on managing connections efficiently and always close connections when they’re no longer needed to avoid any potential leaks or issues.