DEV Community

Cover image for Connecting to Multiple Databases with Nodejs and Sequelize
Julius Ngwu
Julius Ngwu

Posted on • Edited on • Originally published at javascript.plainenglish.io

Connecting to Multiple Databases with Nodejs and Sequelize

#postgres #sequelize #database #node

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=
Enter fullscreen mode Exit fullscreen mode

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,
        },
      },
    },
  },
};
Enter fullscreen mode Exit fullscreen mode

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;
};
Enter fullscreen mode Exit fullscreen mode

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;
}
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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.

multiple databases with Sequelize and nodejs folder structure

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"),

};
Enter fullscreen mode Exit fullscreen mode




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",



</span><span class="nl">"wallet:migrate"</span><span class="p">:</span><span class="w"> </span><span class="s2">"sequelize --options-path ./.sequelize-wallet --env development db:migrate"</span><span class="p">,</span><span class="w">
</span><span class="nl">"wallet:seed"</span><span class="p">:</span><span class="w"> </span><span class="s2">"sequelize --options-path ./.sequelize-wallet --env development db:seed:all"</span><span class="p">,</span><span class="w">

</span><span class="nl">"merchant:migrate"</span><span class="p">:</span><span class="w"> </span><span class="s2">"sequelize --options-path ./.sequelize-merchant --env development db:migrate"</span><span class="p">,</span><span class="w">
</span><span class="nl">"merchant:seed"</span><span class="p">:</span><span class="w"> </span><span class="s2">"sequelize --options-path ./.sequelize-merchant --env development db:seed:all"</span><span class="p">,</span><span class="w">

</span><span class="nl">"role:migrate"</span><span class="p">:</span><span class="w"> </span><span class="s2">"sequelize --options-path ./.sequelize-role --env development db:migrate"</span><span class="p">,</span><span class="w">
</span><span class="nl">"role:seed"</span><span class="p">:</span><span class="w"> </span><span class="s2">"sequelize --options-path ./.sequelize-role --env development db:seed:all"</span><span class="p">,</span><span class="w">

</span><span class="nl">"auth:migrate"</span><span class="p">:</span><span class="w"> </span><span class="s2">"sequelize --options-path ./.sequelize-auth --env development db:migrate"</span><span class="p">,</span><span class="w">
</span><span class="nl">"auth:seed"</span><span class="p">:</span><span class="w"> </span><span class="s2">"sequelize --options-path ./.sequelize-auth --env development db:seed:all"</span><span class="p">,</span><span class="w">






    Enter fullscreen mode
    


    Exit fullscreen mode
    








}
Enter fullscreen mode Exit fullscreen mode




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 leave a like and comment.

References:

Top comments (0)

Read next

joanroucoux profile image

Building a Scalable URL Shortener with Node.js (Part 1/2)

Joan Roucoux -

abeertech01 profile image

Dockerize PERN-TypeScript App Using Prisma ORM With Docker Compose

Abdul Ahad Abeer -

scion01 profile image

Optimizing Pagination in PostgreSQL: OFFSET/LIMIT vs. Keyset

Nishant -

tsudhishnair profile image

Benchmarking Crunchy Data for latency

T Sudhish Nair -