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, that quadrillions of developers have encountered before me and that would be done within a few minutes. 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).
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.
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:
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:
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:
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:
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:migrateI 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.
Helpful Stack Overflow Discussions:
