38

Do I need to create multiple instances of Sequelize if I want to use two databases? That is, two databases on the same machine.

If not, what's the proper way to do this? It seems like overkill to have to connect twice to use two databases, to me.

So for example, I have different databases for different functions, for example, let's say I have customer data in one database, and statistical data in another.

So in MySQL:

MySQL [customers]> show databases;
+--------------------+
| Database           |
+--------------------+
| customers          |
| stats              |
+--------------------+

And I have this to connect with sequelize

// Create a connection....
var Sequelize = require('sequelize');
var sequelize = new Sequelize('customers', 'my_user', 'some_password', {
    host: 'localhost',
    dialect: 'mysql',

    pool: {
        max: 5,
        min: 0,
        idle: 10000
    },
    logging: function(output) {
        if (opts.log_queries) {
            log.it("sequelize_log",{log: output});
        }
    }

});

// Authenticate it.
sequelize.authenticate().nodeify(function(err) {

    // Do stuff....

});

I tried to "trick" it by in a definition of a model using dot notation

var InterestingStatistics = sequelize.define('stats.interesting_statistics', { /* ... */ });

But that creates the table customers.stats.interesting_statistics. I need to use an existing table in the stats database.

What's the proper way to achieve this? Thanks.

Improve this question
1
  • I am stuck with this. Can you share your implementation on this? Commented Oct 27, 2022 at 9:04

6 Answers 6

Reset to default
58

You need to create different instances of sequelize for each DB connection you want to create:

const { Sequelize } = require('sequelize');
const userDb = new Sequelize(/* ... */);
const contentDb = new Sequelize(/* ... */);

Each instance created from sequelize has its own DB info (db host, url, user, pass, etc...), and these values are not meant to be changed, so there is no "correct" way to create multiple connections with one instance of sequelize.

From their docs:

Observe that, in the examples above, Sequelize refers to the library itself while sequelize refers to an instance of Sequelize, which represents a connection to one database. This is the recommended convention and it will be followed throughout the documentation.

A "common" approach to do this, is having your databases in a config.json file and loop over it to create connections dinamically, something like this maybe:

config.json

{
    /*...*/
    databases: {
        user: {
            path: 'xxxxxxxx'
        },
        content: {
            path: 'xxxxxxxx'
        }
    }
}

Your app

const Sequelize = require('sequelize');
const config = require('./config.json');

// Loop through
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];
    db[database] = new Sequelize( dbPath );
}

// Sequelize instances:
// db.user
// db.content

You will need to do a little bit more coding to get it up and running but its a general idea.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

Excellent answer, and thank you. Confirmed, works flawlessly. I've gone ahead and created multiple instances, and then I reference those when creating my models, and it works a charm. Appreciate the sanity check to make sure I'm not doing it in a hack-ish fashion.
I have the similar issue on multiple databases access. In my app, there are a single db connection pool, which handles all the db connections onto multiple databases with the same schema. So, I haven't found a perfect solution yet.
@dougBTV Can you share snippet to represent relations between them?
Is this scalable ? Could this cause any problem if I have over 100 database to handle ? 1000 ? 10 000 ?
How do you specify which database to use when running an update migration?
|
2

if you are trying to associate objects in the same RDS across multiple databases, you can use schema.

http://docs.sequelizejs.com/class/lib/model.js~Model.html#static-method-schema

this will prepend the db name to the table name so, presumably, your queries would come out like: SELECT A.ColA, B.ColB FROM SchemaA.ATable A INNER JOIN SchemaB.BTable B ON B.BId = A.BId

Improve this answer

3 Comments

This is interesting because PG uses schemas somewhat differently than MSSQL. I am currently working on a multi db MSSQL and I guess I will see if the schema dot notation can be applied to databases instead.
Yes.. I was able to go into different dbs and schemas with the dot notation as long as the models are loaded for each database
By this way ,can we set db name on each api call?if yes how?
0

Why don't you use raw query? With this you can connect to one database and query the other. See sample code below.

const sequelize = require('db_config');
function test(req, res){
  const qry = `SELECT * FROM db1.affiliates_order co
LEFT JOIN db2.affiliates m ON m.id = co.campaign_id`;
  sequelize.query(qry, null, {  raw: true}).then(result=>{
    console.log(result);
  })
}
Improve this answer

5 Comments

Can we put query to get data from two databases in Postgres?
The OP asked about querying different DBs not different tables!
@alfasin this answer includes dbs the syntax is DB.Table where DB is your DB name.
There is no such syntax for db: this is schema.table not db.table!
Tried cross db join... no love. Table does not exist.
0

Adding to the chosen answer as I wanted to be able to run migrations on multiple databases, this is how I do it although its rather laborious to setup, and there may be a simpler way (I hope), once its setup its easy to add migrations:

Install sequelize-cli npm i sequelize-cli

  1. Create a migrations directory, with a sub directory named for each db (eg. /server/migrations/accounting, /server/migrations/users)

  2. Create a .sequelize-mydb file for each db in the root directory (eg .sequelize-accounting .sequelize-users etc) and in each file add the path and db relative info:

    const path = require("path");

module.exports = {
   config: path.resolve("config", "config.json"),
   "models-path": path.resolve("models"),
   "migrations-path": path.resolve("migrations", "accounting"),
};

  3. Inside the sequelize config.json, add a copy of each db config into the top level section, ie. out of the developmont / production section eg:

       {
     "development": {
         "baseUrl": "https://example.com/api/v2/",
         "databases": {
         "accounting": {
             "username": "root",
             "password": "",
             "database": "accounting_development",
             "host": "localhost",
             "dialect": "mysql",
             "dialectOptions": {
             "decimalNumbers": true
             },
         "users": {} etc
         }
     },

     // Copy the above config for each db to the top level ---->>

     "accounting": {
         "username": "root",
         "password": "",
         "database": "accounting_development",
         "host": "localhost",
         "dialect": "mysql",
         "dialectOptions": {
         "decimalNumbers": true
         }
     },
     "users": {}
 }

  4. Create or add to the scripts section to package.json:

     "scripts": {
   "sequelize:accounting:migrate": "sequelize --options-path ./.sequelize-accounting --env accounting db:migrate",
   "sequelize:users:migrate": "sequelize --options-path ./.sequelize-users --env users db:migrate",
 }

  5. Now anytime you create a migration, copy the auto generated migration file to the relative migration directory created above, eg create a migration for a table in the accounting db, and then copy that file to the migrations/accounting directory.

  6. Finally to run the migrations:

     npm run sequelize:accounting:migrate
 npm run sequelize:users:migrate

The trickiest part is getting the relative paths correct, (particularly if building for Docker) once you have that in place, then the only extra work each time you create a new migration is having to copy that file to the appropriate directory.

Improve this answer

1 Comment

Have you considered using Umzug for migrations?
0

I needed to copy data from a table named solar in a mysql database to a postgres database.

I think this nodejs javascript code allows the reader to understand the required concepts that the code above, as extracting database config parameters from the config file abstracts the database connections into an object, which I think makes it harder to read.

This code looks up the most recent value in the destination db, then queries the source db for newer records. It then queries 100 records and copies them across to the target.

// save in file named index.js
// you can then execute the code from your console using `node index.js`
const { Sequelize, Op, DataTypes } = require("sequelize");
const fieldNames = [// These fields will be different for your database
  `battSoc`,
  `panelV`,
  `panelI`,
  `loadV`,
  `loadI`,
  `battV`,
  `battI`,
  `battTemp`,
  `panelW`,
  `loadW`,
  `tempEquip`,
  `ambientTemp`,
];

const SolarFields = {
  id: {
    type: DataTypes.INTEGER,
    autoIncrement: true,
    primaryKey: true,
  },
};

SolarFieldNames.map((fieldName) => {
  SolarFields[fieldName] = { type: DataTypes.FLOAT };
});

async function copyDataMariaToPostgres() {
  const postgres = new Sequelize("postgressdbname", "username", "password", {
    host: "localhost",
    dialect: "postgres",
  });

  const mariaDb = new Sequelize("mariadbname", "username", "password", {
    host: "192.168.1.110",
    dialect: "mariadb",
  });
  let solarSource;
  let solarDest;
  postgres
    .authenticate()
    .then(function (result) {
      return mariaDb.authenticate();
    })
    .then(async function (result) {
      solarSource = mariaDb.define("solar", SolarFields, {
        tableName: "solar",
        timestamps: false,
      });
      solarDest = postgres.define("solar", SolarFields, {
        tableName: "solar",
        timestamps: false,
      });

      await solarSource.sync({ logging: false });
      await solarDest.sync({ logging: false });
      // get last row in dest:
      let lastRow = await solarDest.findOne({ order: [["id", "desc"]] });
      let rows;
      do {
        findAllParams = { logging: false, limit: 100, order: [["id", "asc"]] };
        // lastRow is null if the destination is empty
        if (lastRow) findAllParams.where = { id: { [Op.gt]: lastRow.id } };
        const promises = [];
        rows = await solarSource.findAll(findAllParams);
        rows.forEach(async function (row, i) {
          promises.push(solarDest.create(row.dataValues, { logging: false }));
        });
        const newRows = await Promise.all(promises);
        newRows.forEach((row, i) => {
          console.log(`#${i} ${row.dataValues.id}`);
          lastRow = row;
        });
      } while (rows.length > 0);
    })
    .catch((e) => {
      console.error(e);
    });
}
copyDataMariaToPostgres();
Improve this answer

Comments

0

using transaction we can make copy to another database without creating new models, https://stackoverflow.com/a/77571298/14576258

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.