Connecting to Multiple Databases with Nodejs and Sequelize

I know why you are here. You have reached a point where you wish to interact with multiple databases using your Sequelize library. However, you recently noticed that the Sequelize documentation mentions that

If you're connecting to the database from a single process, you should create only one Sequelize instance. Sequelize will set up a connection pool on initialization.

Consequently, it implies that we must generate a new instance for each database we intend to establish a connection. Your goal is to circumvent the need for creating numerous instances, which led you to seek assistance. Additionally, you are curious about the scarcity of engineers who have written about this crucial subject. I, too, share your wonderment.

Great news awaits you! You've come to the perfect location. Below, I present a comprehensive, step-by-step guide on how to connect multiple databases without the need to create multiple instances.

I wanted to start with how Sequelize is installed but a former colleague has already done a great job on Getting Started with Sequelize and Postgres.. If you haven't yet created a Node.js and Sequelize project, I encourage you to click the link for detailed instructions. However, if you're already working on one, feel free to disregard the link.

Let us assume you are already working with a Postgres database called Customer and you want to add these 4 more Postgres(any database that is supported by Sequelize) databases to your Nodejs application:

• Wallet
• Auth
• Merchant
• Role

Step 1:

Create a .env file and populate it as seen below:

PORT=
API_VERSION=

DB_USERNAME=
DB_PASSWORD=
DB_NAME=
DB_HOST=
DB_PORT=

DB_CUSTOMER_URL=
DB_CUSTOMER_USERNAME=
DB_CUSTOMER_PASSWORD=
DB_CUSTOMER_NAME=
DB_CUSTOMER_HOST=

DB_WALLET_URL=
DB_WALLET_USERNAME=
DB_WALLET_PASSWORD=
DB_WALLET_NAME=
DB_WALLET_HOST=

DB_ROLE_URL=
DB_ROLE_USERNAME=
DB_ROLE_PASSWORD=
DB_ROLE_NAME=
DB_ROLE_HOST=

DB_MERCHANT_URL=
DB_MERCHANT_USERNAME=
DB_MERCHANT_PASSWORD=
DB_MERCHANT_NAME=
DB_MERCHANT_HOST=

DB_AUTH_URL=
DB_AUTH_USERNAME=
DB_AUTH_PASSWORD=
DB_AUTH_NAME=
DB_AUTH_HOST=

Step 2:

In the config directory, modify your config.ts (config.js if you are not using typescript) to this:

import config from "./index";

const { secrets, dbVariables } = config();

module.exports = {
  development: {
    username: secrets.username,
    password: secrets.password,
    database: secrets.name,
    host: secrets.host,
    port: secrets.port,
    dialect: "postgres",
    dialectOptions: {
      bigNumberStrings: true,
    },
    databases: {
      Customer: {
        username: dbVariables.dbCustomerUsername,
        password: dbVariables.dbCustomerPassword,
        database: dbVariables.dbCustomerName,
        host: dbVariables.dbCustomerHost,
        port: 5432,
        dialect: "postgres",
        dialectOptions: {
          bigNumberStrings: true,
        },
      },
      Auth: {
        username: dbVariables.dbAuthUsername,
        password: dbVariables.dbAuthPassword,
        database: dbVariables.dbAuthName,
        host: dbVariables.dbAuthHost,
        port: 5432,
        dialect: "postgres",
        dialectOptions: {
          bigNumberStrings: true,
        },
      },
      Wallet: {
        username: dbVariables.dbWalletUsername,
        password: dbVariables.dbWalletPassword,
        database: dbVariables.dbWalletName,
        host: dbVariables.dbWalletHost,
        port: 5432,
        dialect: "postgres",
        dialectOptions: {
          bigNumberStrings: true,
        },
      },

      Merchant: {
        username: dbVariables.dbMerchantUsername,
        password: dbVariables.dbMerchantPassword,
        database: dbVariables.dbMerchantName,
        host: dbVariables.dbMerchantHost,
        port: 5432,
        dialect: "postgres",
        dialectOptions: {
          bigNumberStrings: true,
        },
      },
      Role: {
        username: dbVariables.dbRoleUsername,
        password: dbVariables.dbRolePassword,
        database: dbVariables.dbRoleName,
        host: dbVariables.dbRoleHost,
        port: 5432,
        dialect: "postgres",
        dialectOptions: {
          bigNumberStrings: true,
        },
      },
    },
  },
};

Here we have succeeded in declaring 4 more databases with their credentials. Now you must have noticed that I imported an index file at the beginning of this file. It’s not compulsory. I always like to have a separate file for my secret keys. Just hold on, we will create that index file soon. Now speaking of keeping the code clean, let's create a helper file that will read and declare all our db environment variables by looping through them.

Step 3:

In your helpers directory, create a dbConfig.ts file and enter the following lines of codes:

export const fetchAllDbConfig = () => {
  const DBs = {
    CUSTOMER: "Customer",
    WALLET: "Wallet",
    ROLE: "Role",
    MERCHANT: "Merchant",
    AUTH: "Auth",
  };

  const dbVariables = {};
  for (let [key, value] of Object.entries(DBs)) {
    dbVariables[`db${value}URL`] = process.env[`DB_${key}_URL`] || "";
    dbVariables[`db${value}Name`] = process.env[`DB_${key}_NAME`] || "";
    dbVariables[`db${value}Host`] = process.env[`DB_${key}_HOST`] || "";
    dbVariables[`db${value}Password`] = process.env[`DB_${key}_PASSWORD`] || "";
    dbVariables[`db${value}Username`] = process.env[`DB_${key}_USERNAME`] || "";
  }
  return dbVariables;
};

Step 4:

Now let us take a look at this index file we imported earlier. Back to creating the index file we imported in the config.ts file. In that same config directory create an index.ts file and add the following lines to it.

import dotenv from "dotenv";
import path from "path";
import { fetchAllDbConfig } from "../helpers/dbConfig";

// describes a secrets object
type Secrets = Readonly<{
  env: string;
  version: string;
  port: string;
  secrets: {
    name: string;
    host: string;
    port: number;
    username: string;
    password: string;
  };
  dbVariables: {
    dbCustomerURL: string;
    dbCustomerName: string;
    dbCustomerHost: string;
    dbCustomerPassword: string;
    dbCustomerUsername: string;

    dbWalletURL: string;
    dbWalletName: string;
    dbWalletHost: string;
    dbWalletPassword: string;
    dbWalletUsername: string;

    dbRoleURL: string;
    dbRoleName: string;
    dbRoleHost: string;
    dbRolePassword: string;
    dbRoleUsername: string;

    dbAuthURL: string;
    dbAuthName: string;
    dbAuthHost: string;
    dbAuthPassword: string;
    dbAuthUsername: string;

    dbMerchantURL: string;
    dbMerchantName: string;
    dbMerchantHost: string;
    dbMerchantPassword: string;
    dbMerchantUsername: string;
  };
}>;

const env = process.env.NODE_ENV || "development";
let envfile: string;

switch (env) {
  case "production":
    envfile = ".env";
    break;
  case "test":
    envfile = ".env.test";
    break;
  case "development":
  default:
    envfile = ".env.local";
    break;
}

const envpath: string = path.join(__dirname, "../..", envfile);
let cache: Secrets | any;

export default function config() {
  if (!cache) {
    dotenv.config({ path: envpath });
    cache = Object.freeze({
      env,
      version: process.env.API_VERSION || "v1",
      port: process.env.PORT || "3300",
      secrets: {
        name: process.env.DB_NAME || "",
        host: process.env.DB_HOST || "",
        port: +(process.env.DB_PORT || "") || 25,
        username: process.env.DB_USERNAME || "",
        password: process.env.DB_PASSWORD || "",
      },
      dbVariables: fetchAllDbConfig(),
    });
  }
  return cache;
}

Here we have declared our variables in a more clean format. Also, notice how we imported fetchAllDbConfig from our helpers/dbConfig we created earlier.

Step 5:

Carefully edit the models/index.js file as follows:

"use strict";

const fs = require("fs");
const path = require("path");
const Sequelize = require("sequelize");
const basename = path.basename(__filename);
const env = process.env.NODE_ENV || "development";
const config = require(path.resolve("build/config", "config.js"))[env];

const db = {};
const databases = Object.keys(config.databases);

for (let i = 0; i < databases.length; ++i) {
  let database = databases[i];
  let dbPath = config.databases[database];
  if (config.use_env_variable) {
    db[database] = new Sequelize(process.env[config.use_env_variable], config);
  } else {
    db[database] = new Sequelize(
      dbPath.database,
      dbPath.username,
      dbPath.password,
      dbPath
    );
  }
}

for (let i = 0; i < databases.length; ++i) {
  let database = databases[i].toLowerCase();
  fs.readdirSync(`${__dirname}/${database}`)
    .filter((file) => {
      return (
        file.indexOf(".") !== 0 && file !== basename && file.slice(-3) === ".js"
      );
    })
    .forEach((file) => {
      const model = require(path.join(`${__dirname}/${database}`, file))(
        db[databases[i]],
        Sequelize.DataTypes
      );

      db[model.name] = model;
    });
}

Object.keys(db).forEach((modelName) => {
  if (db[modelName].associate) {
    db[modelName].associate(db);
  }
});

module.exports = db;

The "db" object currently holds connection information for all databases specified in our config.js file. However, it lacks details about the models in the databases, which results in incorrect SQL queries. To rectify this, we must read the model information from our model files and incorporate it into the "db" object.

Please be attentive as we proceed. To achieve this, we must modify the folder structure as depicted below:

Step 6:

Restructure your folder tree to look as seen below.

Step 7:

Create new Sequelize options files similar to our .sequelizerc file for every database. So let us create these two files in the directory of our package.json file:

• .sequelize-customer
• .sequelize-auth
• .sequelize-wallet
• .sequelize-role
• .sequelize-merchant

In each file, we can define in which directory our config, models, migrations, and seeders are stored. Do this for all 5 files and remember to change the names. Below is an example of how the .sequelize-customer file will look.

const path = require("path");

module.exports = {
  config: path.resolve("build/config", "config.js"),
  "models-path": path.resolve("src/db/models"),
  "seeders-path": path.resolve("src/db/models/customer/seeders"),
  "migrations-path": path.resolve("src/db/models/customer/migrations"),
};

Step 8:

In your package.json, add these migration scripts as follows:

{
  "customer:migrate": "sequelize --options-path ./.sequelize-customer --env development db:migrate",
  "customer:seed": "sequelize --options-path ./.sequelize-customer --env development db:seed:all",

  "wallet:migrate": "sequelize --options-path ./.sequelize-wallet --env development db:migrate",
  "wallet:seed": "sequelize --options-path ./.sequelize-wallet --env development db:seed:all",

  "merchant:migrate": "sequelize --options-path ./.sequelize-merchant --env development db:migrate",
  "merchant:seed": "sequelize --options-path ./.sequelize-merchant --env development db:seed:all",

  "role:migrate": "sequelize --options-path ./.sequelize-role --env development db:migrate",
  "role:seed": "sequelize --options-path ./.sequelize-role --env development db:seed:all",

  "auth:migrate": "sequelize --options-path ./.sequelize-auth --env development db:migrate",
  "auth:seed": "sequelize --options-path ./.sequelize-auth --env development db:seed:all"
}

Download from Github Repo

You can clone this template from github repo

Step 9:

Congratulations. You did it. You can now access all five different databases from one instance.

Please don’t forget to share.

References:

• Connection Pool by Sequelize
• Getting Started with Sequelize and Postgres by Chinedu Orie
• USING MULTIPLE DATABASES WITH NODEJS AND SEQUELIZE by LUCAS SPREITER
• tutofox image