How to using multiple database in nodejs

How to using multiple database in nodejs

Using multiple databases with NodeJS and Sequelize. Recently I had to connect a second database to my server running Express and Sequelize. Initially I thought, this would be an easy task

Unfortunately, the Sequelize documentation did not cover this specific topic and multiple stackoverflow posts had to save the day. To save you time, I decided to write a post, summarizing my approach and some helpful tips and tricks.

The Sequelize documentation states:

Sequelize will setup a connection pool on initialization so you should ideally only ever create one instance per database if you’re connecting to the DB from a single process.

This means, that we will need to run a new Sequelize instance for every database we want to connect to our server. The easiest and most flexible way to do this is to declare multiple databases in our config.js and then loop over these databases in the file handling our database connections (in my case this is the index.js of my models folder).

module.exports = {
  
  /**Declaration of databases for my development environment**/
    "development": {
        "databases": {
            "Database1": {
                "database": process.env.RDS_DATABASE1, //you should always save these values in environment variables
                "username": process.env.RDS_USERNAME1,  //only for testing purposes you can also define the values here
                "password":  process.env.RDS_PASSWORD1,
                "host": process.env.RDS_HOSTNAME1,
                "port": process.env.RDS_PORT1,
                "dialect": "postgres"  //here you need to define the dialect of your databse, in my case it is Postgres
            },
            "Database2": {
                "database": process.env.RDS_DATABASE2, 
                "username": process.env.RDS_USERNAME2,  
                "password":  process.env.RDS_PASSWORD2,
                "host": process.env.RDS_HOSTNAME2,
                "port": process.env.RDS_PORT2,
                "dialect": "mssql"  //second database can have a different dialect
            },
        },
    }
}

config.js

Here we declared two databases — The Postgres Database “Database1” and the MSSQL Database “Database2” — and their credentials for the connection. Now let’s have a look how to read in these declarations and connect to the databases.

const Sequelize = require('sequelize');
const env = process.env.NODE_ENV || 'development';

//Load the configuration from the config.js
const config = require(`${__dirname}/../config/config.js`)[env];

//Create an empty object which can store our databases
const db = {};

//Extract the database information into an array
const databases = Object.keys(config.databases);

//Loop over the array and create a new Sequelize instance for every database from config.js
for(let i = 0; i < databases.length; ++i) {
    let database = databases[i];
    let dbPath = config.databases[database];
    //Store the database connection in our db object
    db[database] = new Sequelize( dbPath.database, dbPath.username, dbPath.password, dbPath );
}

/**Load Sequelize Models**/

index.js

The “db” object now contains information on how to connect to all databases configured in our config.js, but does not contain information about the models in the database, consequently not being able to make correct SQL queries. To give the “db” object the necessary model information, we need to read the information from our model files and add it to the object. To understand the next few lines of code, we first need to have a look at my folder structure. The index.js is located in the models folder, which has two subdirectories for models of both databases from my config.js:

Now lets add the models from both directories to our “db” object in the index.js:

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const basename = path.basename(module.filename);
const env = process.env.NODE_ENV || 'development';
const config = require(`${__dirname}/../config/config.js`)[env];
const db = {};
const databases = Object.keys(config.databases);

/** Add Databases**/
for(let i = 0; i < databases.length; ++i) {
    let database = databases[i];
    let dbPath = config.databases[database];
    db[database] = new Sequelize( dbPath.database, dbPath.username, dbPath.password, dbPath );
}

/**Add the Database Models**/
//Add models from database1 folder
fs
    .readdirSync(__dirname + '/database1')
    .filter(file =>
        (file.indexOf('.') !== 0) &&
        (file !== basename) &&
        (file.slice(-3) === '.js'))
    .forEach(file => {
        const model = db.ebdb.import(path.join(__dirname + '/database1', file));
        db[model.name] = model;
    });


// Add models from database2 folder

fs
    .readdirSync(__dirname + '/database2')
    .filter(file =>
        (file.indexOf('.') !== 0) &&
        (file !== basename) &&
        (file.slice(-3) === '.js'))
    .forEach(file => {
        const model = db.easyconnect.import(path.join(__dirname + '/database2', file));
        db[model.name] = model;
    });

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


module.exports = db;

index.js

Now we can require our models in our controllers as usual:

const model1= require('../models').model1;


Congratulations! You added multiple databases to your NodeJS server and made them accessible. Now let’s talk about the tricky part: migrations and seeders

Sequelize is currently not supporting multiple migration folders and is not able to distinguish, which migrations should be ran in each database by itself, if all migration files are kept in one folder. While there are multiple ways to deal with this problem — e.g. manually setting migration filepaths in the sequelize cli — I found the approach using separate options files and npm scripts the most productive:

First lets expand our config.js with environments for migrating and seeding our databases. Every environment then only contains data for one specific database, so we can explicitly define to which database we want to connect:

module.exports = {
    "development": {
        "databases": { /** our database declarations from before**/}
    },

    // Special environment only for Database1
    "Database1": {
                "database": process.env.RDS_DATABASE1, //you should always save these values in environment variables
                "username": process.env.RDS_USERNAME1,  //only for testing purposes you can also define the values here
                "password":  process.env.RDS_PASSWORD1,
                "host": process.env.RDS_HOSTNAME1,
                "port": process.env.RDS_PORT1,
                "dialect": "postgres"  //here you need to define the dialect of your databse, in my case it is Postgres
            },

    // Special environment only for Database2
    "Database2": {
                "database": process.env.RDS_DATABASE2, 
                "username": process.env.RDS_USERNAME2,  
                "password":  process.env.RDS_PASSWORD2,
                "host": process.env.RDS_HOSTNAME2,
                "port": process.env.RDS_PORT2,
                "dialect": "mssql"  //second database can have a different dialect
            },

config.js

In the next steps we create new Sequelize options files — similar to our .sequelizerc file — for every database. So lets create these two files in the directory of our package.json file:

  • .sequelize-database1
  • .sequelize-database2

In these files, we can define in which directory our config, models, migrations and seeders are stored:

const path = require('path');

module.exports = {
    "config": path.resolve('./server/config', 'config.js'),
    "models-path": path.resolve('./server/models'),
    "seeders-path": path.resolve('./server/seeders/database1'),
    "migrations-path": path.resolve('./server/migrations/database1')
 };

.sequelize-database1

const path = require('path');

module.exports = {
    "config": path.resolve('./server/config', 'config.js'),
    "models-path": path.resolve('./server/models'),
    "seeders-path": path.resolve('./server/seeders/database2'),
    "migrations-path": path.resolve('./server/migrations/database2')
 };

.sequelize-database2

Finally we can use these options files in npm scripts, that run our migrations oder seeders. Therefore we need to add some lines to the scripts section of package.json file:

{  

  "scripts": {

      "sequelize:database1:migrate": "sequelize --options-path ./.sequelize-database1 --env database1 db:migrate",
      "sequelize:database1:migrate:undo": "sequelize --options-path ./.sequelize-database1 --env database1 db:migrate:undo",
      "sequelize:database1:seed:all": "sequelize --options-path ./.sequelize-database1 --env database1 db:seed:all",

      "sequelize:database2:migrate": "sequelize --options-path ./.sequelize-database2 --env database2 db:migrate",
      "sequelize:database2:migrate:undo": "sequelize --options-path ./.sequelize-database2 --env database2 db:migrate:undo",
      "sequelize:database2:seed:all": "sequelize --options-path ./.sequelize-database2 --env database2 db:seed:all",
    },
}

package.json

The scripts use the two .sequelize-database1, .sequelize-database2 options files to determine the location of the configuration, models, migrations and seeder directories and set the correct environment for reading configurations from the config.js file.

You can run the scripts with an npm run command from the command line:

npm run sequelize:database1:migrate


I hope this article could help you setting up multiple database connections to a single NodeJS server with Sequelize. If you have any questions, feel free to ask them in the comment section.

30s ad

Learn Node.js API’s Fast and Simple

Rapid Node.js Training

A Simple Node.js/Mongo/Restify API in Less Than 3 Hours

MERN Stack Front To Back: Full Stack React, Redux & Node.js

Code with Node: Learn by Doing

Understanding NPM - Node.js Package Manager

Unfortunately, the Sequelize documentation did not cover this specific topic and multiple stackoverflow posts had to save the day. To save you time, I decided to write a post, summarizing my approach and some helpful tips and tricks.

The Sequelize documentation states:

Sequelize will setup a connection pool on initialization so you should ideally only ever create one instance per database if you’re connecting to the DB from a single process.

This means, that we will need to run a new Sequelize instance for every database we want to connect to our server. The easiest and most flexible way to do this is to declare multiple databases in our config.js and then loop over these databases in the file handling our database connections (in my case this is the index.js of my models folder).

module.exports = {
  
  /**Declaration of databases for my development environment**/
    "development": {
        "databases": {
            "Database1": {
                "database": process.env.RDS_DATABASE1, //you should always save these values in environment variables
                "username": process.env.RDS_USERNAME1,  //only for testing purposes you can also define the values here
                "password":  process.env.RDS_PASSWORD1,
                "host": process.env.RDS_HOSTNAME1,
                "port": process.env.RDS_PORT1,
                "dialect": "postgres"  //here you need to define the dialect of your databse, in my case it is Postgres
            },
            "Database2": {
                "database": process.env.RDS_DATABASE2, 
                "username": process.env.RDS_USERNAME2,  
                "password":  process.env.RDS_PASSWORD2,
                "host": process.env.RDS_HOSTNAME2,
                "port": process.env.RDS_PORT2,
                "dialect": "mssql"  //second database can have a different dialect
            },
        },
    }
}

config.js

Here we declared two databases — The Postgres Database “Database1” and the MSSQL Database “Database2” — and their credentials for the connection. Now let’s have a look how to read in these declarations and connect to the databases.

const Sequelize = require('sequelize');
const env = process.env.NODE_ENV || 'development';

//Load the configuration from the config.js
const config = require(`${__dirname}/../config/config.js`)[env];

//Create an empty object which can store our databases
const db = {};

//Extract the database information into an array
const databases = Object.keys(config.databases);

//Loop over the array and create a new Sequelize instance for every database from config.js
for(let i = 0; i < databases.length; ++i) {
    let database = databases[i];
    let dbPath = config.databases[database];
    //Store the database connection in our db object
    db[database] = new Sequelize( dbPath.database, dbPath.username, dbPath.password, dbPath );
}

/**Load Sequelize Models**/

index.js

The “db” object now contains information on how to connect to all databases configured in our config.js, but does not contain information about the models in the database, consequently not being able to make correct SQL queries. To give the “db” object the necessary model information, we need to read the information from our model files and add it to the object. To understand the next few lines of code, we first need to have a look at my folder structure. The index.js is located in the models folder, which has two subdirectories for models of both databases from my config.js:

Now lets add the models from both directories to our “db” object in the index.js:

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');
const basename = path.basename(module.filename);
const env = process.env.NODE_ENV || 'development';
const config = require(`${__dirname}/../config/config.js`)[env];
const db = {};
const databases = Object.keys(config.databases);

/** Add Databases**/
for(let i = 0; i < databases.length; ++i) {
    let database = databases[i];
    let dbPath = config.databases[database];
    db[database] = new Sequelize( dbPath.database, dbPath.username, dbPath.password, dbPath );
}

/**Add the Database Models**/
//Add models from database1 folder
fs
    .readdirSync(__dirname + '/database1')
    .filter(file =>
        (file.indexOf('.') !== 0) &&
        (file !== basename) &&
        (file.slice(-3) === '.js'))
    .forEach(file => {
        const model = db.ebdb.import(path.join(__dirname + '/database1', file));
        db[model.name] = model;
    });


// Add models from database2 folder

fs
    .readdirSync(__dirname + '/database2')
    .filter(file =>
        (file.indexOf('.') !== 0) &&
        (file !== basename) &&
        (file.slice(-3) === '.js'))
    .forEach(file => {
        const model = db.easyconnect.import(path.join(__dirname + '/database2', file));
        db[model.name] = model;
    });

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


module.exports = db;

index.js

Now we can require our models in our controllers as usual:

const model1= require('../models').model1;


Congratulations! You added multiple databases to your NodeJS server and made them accessible. Now let’s talk about the tricky part: migrations and seeders

Sequelize is currently not supporting multiple migration folders and is not able to distinguish, which migrations should be ran in each database by itself, if all migration files are kept in one folder. While there are multiple ways to deal with this problem — e.g. manually setting migration filepaths in the sequelize cli — I found the approach using separate options files and npm scripts the most productive:

First lets expand our config.js with environments for migrating and seeding our databases. Every environment then only contains data for one specific database, so we can explicitly define to which database we want to connect:

module.exports = {
    "development": {
        "databases": { /** our database declarations from before**/}
    },

    // Special environment only for Database1
    "Database1": {
                "database": process.env.RDS_DATABASE1, //you should always save these values in environment variables
                "username": process.env.RDS_USERNAME1,  //only for testing purposes you can also define the values here
                "password":  process.env.RDS_PASSWORD1,
                "host": process.env.RDS_HOSTNAME1,
                "port": process.env.RDS_PORT1,
                "dialect": "postgres"  //here you need to define the dialect of your databse, in my case it is Postgres
            },

    // Special environment only for Database2
    "Database2": {
                "database": process.env.RDS_DATABASE2, 
                "username": process.env.RDS_USERNAME2,  
                "password":  process.env.RDS_PASSWORD2,
                "host": process.env.RDS_HOSTNAME2,
                "port": process.env.RDS_PORT2,
                "dialect": "mssql"  //second database can have a different dialect
            },

config.js

In the next steps we create new Sequelize options files — similar to our .sequelizerc file — for every database. So lets create these two files in the directory of our package.json file:

  • .sequelize-database1
  • .sequelize-database2

In these files, we can define in which directory our config, models, migrations and seeders are stored:

const path = require('path');

module.exports = {
    "config": path.resolve('./server/config', 'config.js'),
    "models-path": path.resolve('./server/models'),
    "seeders-path": path.resolve('./server/seeders/database1'),
    "migrations-path": path.resolve('./server/migrations/database1')
 };

.sequelize-database1

const path = require('path');

module.exports = {
    "config": path.resolve('./server/config', 'config.js'),
    "models-path": path.resolve('./server/models'),
    "seeders-path": path.resolve('./server/seeders/database2'),
    "migrations-path": path.resolve('./server/migrations/database2')
 };

.sequelize-database2

Finally we can use these options files in npm scripts, that run our migrations oder seeders. Therefore we need to add some lines to the scripts section of package.json file:

{  

  "scripts": {

      "sequelize:database1:migrate": "sequelize --options-path ./.sequelize-database1 --env database1 db:migrate",
      "sequelize:database1:migrate:undo": "sequelize --options-path ./.sequelize-database1 --env database1 db:migrate:undo",
      "sequelize:database1:seed:all": "sequelize --options-path ./.sequelize-database1 --env database1 db:seed:all",

      "sequelize:database2:migrate": "sequelize --options-path ./.sequelize-database2 --env database2 db:migrate",
      "sequelize:database2:migrate:undo": "sequelize --options-path ./.sequelize-database2 --env database2 db:migrate:undo",
      "sequelize:database2:seed:all": "sequelize --options-path ./.sequelize-database2 --env database2 db:seed:all",
    },
}

package.json

The scripts use the two .sequelize-database1, .sequelize-database2 options files to determine the location of the configuration, models, migrations and seeder directories and set the correct environment for reading configurations from the config.js file.

You can run the scripts with an npm run command from the command line:

npm run sequelize:database1:migrate


I hope this article could help you setting up multiple database connections to a single NodeJS server with Sequelize. If you have any questions, feel free to ask them in the comment section.

30s ad

Learn Node.js API’s Fast and Simple

Rapid Node.js Training

A Simple Node.js/Mongo/Restify API in Less Than 3 Hours

MERN Stack Front To Back: Full Stack React, Redux & Node.js

Code with Node: Learn by Doing

Understanding NPM - Node.js Package Manager

Top 7 Most Popular Node.js Frameworks You Should Know

Top 7 Most Popular Node.js Frameworks You Should Know

Node.js is an open-source, cross-platform, runtime environment that allows developers to run JavaScript outside of a browser. In this post, you'll see top 7 of the most popular Node frameworks at this point in time (ranked from high to low by GitHub stars).

Node.js is an open-source, cross-platform, runtime environment that allows developers to run JavaScript outside of a browser.

One of the main advantages of Node is that it enables developers to use JavaScript on both the front-end and the back-end of an application. This not only makes the source code of any app cleaner and more consistent, but it significantly speeds up app development too, as developers only need to use one language.

Node is fast, scalable, and easy to get started with. Its default package manager is npm, which means it also sports the largest ecosystem of open-source libraries. Node is used by companies such as NASA, Uber, Netflix, and Walmart.

But Node doesn't come alone. It comes with a plethora of frameworks. A Node framework can be pictured as the external scaffolding that you can build your app in. These frameworks are built on top of Node and extend the technology's functionality, mostly by making apps easier to prototype and develop, while also making them faster and more scalable.

Below are 7of the most popular Node frameworks at this point in time (ranked from high to low by GitHub stars).

Express

With over 43,000 GitHub stars, Express is the most popular Node framework. It brands itself as a fast, unopinionated, and minimalist framework. Express acts as middleware: it helps set up and configure routes to send and receive requests between the front-end and the database of an app.

Express provides lightweight, powerful tools for HTTP servers. It's a great framework for single-page apps, websites, hybrids, or public HTTP APIs. It supports over fourteen different template engines, so developers aren't forced into any specific ORM.

Meteor

Meteor is a full-stack JavaScript platform. It allows developers to build real-time web apps, i.e. apps where code changes are pushed to all browsers and devices in real-time. Additionally, servers send data over the wire, instead of HTML. The client renders the data.

The project has over 41,000 GitHub stars and is built to power large projects. Meteor is used by companies such as Mazda, Honeywell, Qualcomm, and IKEA. It has excellent documentation and a strong community behind it.

Koa

Koa is built by the same team that built Express. It uses ES6 methods that allow developers to work without callbacks. Developers also have more control over error-handling. Koa has no middleware within its core, which means that developers have more control over configuration, but which means that traditional Node middleware (e.g. req, res, next) won't work with Koa.

Koa already has over 26,000 GitHub stars. The Express developers built Koa because they wanted a lighter framework that was more expressive and more robust than Express. You can find out more about the differences between Koa and Express here.

Sails

Sails is a real-time, MVC framework for Node that's built on Express. It supports auto-generated REST APIs and comes with an easy WebSocket integration.

The project has over 20,000 stars on GitHub and is compatible with almost all databases (MySQL, MongoDB, PostgreSQL, Redis). It's also compatible with most front-end technologies (Angular, iOS, Android, React, and even Windows Phone).

Nest

Nest has over 15,000 GitHub stars. It uses progressive JavaScript and is built with TypeScript, which means it comes with strong typing. It combines elements of object-oriented programming, functional programming, and functional reactive programming.

Nest is packaged in such a way it serves as a complete development kit for writing enterprise-level apps. The framework uses Express, but is compatible with a wide range of other libraries.

LoopBack

LoopBack is a framework that allows developers to quickly create REST APIs. It has an easy-to-use CLI wizard and allows developers to create models either on their schema or dynamically. It also has a built-in API explorer.

LoopBack has over 12,000 GitHub stars and is used by companies such as GoDaddy, Symantec, and the Bank of America. It's compatible with many REST services and a wide variety of databases (MongoDB, Oracle, MySQL, PostgreSQL).

Hapi

Similar to Express, hapi serves data by intermediating between server-side and client-side. As such, it's can serve as a substitute for Express. Hapi allows developers to focus on writing reusable app logic in a modular and prescriptive fashion.

The project has over 11,000 GitHub stars. It has built-in support for input validation, caching, authentication, and more. Hapi was originally developed to handle all of Walmart's mobile traffic during Black Friday.

Difference between AngularJS, React, Ember, Backbone, and Node.js.

The most common thing between all of them is that they are Single Page Apps. The SPA is a single page where much of the information remains the same and only some piece of data gets modified when you click on other categories/option.

Node.js Tutorial for Beginners | Node.js Crash Course | Node.js Certification Training

This courseis designed for professionals who aspire to be application developers and gain expertise in building real-time, highly-scalable applications in Node.js. The following professionals can go for this course :

Why learn Node.js?

Node.js uses JavaScript - a language known to millions of developers worldwide - thus giving it a much lower learning curve even for complete beginners. Using Node.js you can build simple Command Line programs or complex enterprise level web applications with equal ease. Node.js is an event-driven, server-side, asynchronous development platform with lightning speed execution. Node.js helps you to code the most complex functionalities in just a few lines of code...

Thanks for reading :heart: If you liked this post, share it with all of your programming buddies! Follow me on Facebook | Twitter

Learn More

The Complete Node.js Developer Course (3rd Edition)

Angular & NodeJS - The MEAN Stack Guide

NodeJS - The Complete Guide (incl. MVC, REST APIs, GraphQL)

Docker for Node.js Projects From a Docker Captain

Intro To MySQL With Node.js - Learn To Use MySQL with Node!

Node.js Absolute Beginners Guide - Learn Node From Scratch

React Node FullStack - Social Network from Scratch to Deploy

Selenium WebDriver - JavaScript nodeJS webdriver IO & more!

Complete Next.js with React & Node - Beautiful Portfolio App

Build a Blockchain & Cryptocurrency | Full-Stack Edition