CODEWITHSUNDEEP
node.jssequelize.js
Austin
Austin

Reputation: 311

How can I use the same Sequelize model for two separate databases?

I am using Sequelize and have multiple MySQL databases that all have the same tables. There is one database per customer, which is to ensure isolation between each customer's data.

For analytics purposes, I need to be able to query data from multiple customer databases. I don't need to join any tables.

To achieve this, I am creating a Sequelize instance per database, however, once I init() the models using the second Sequelize instance, the first is overwritten.

In the example below, once customerTwoStudent is initialized, all queries to customerOneStudent will run on the customer two database instead of customer one. So in this instance, customerOneJanes and customerTwoJanes will be the same.

I am new to Sequelize and Node.js, so any help is appreciated! I'm sure this is because something is being passed by reference, but I can't figure out where.

index.js

import {Sequelize} from "sequelize";

import Student from "./models/Student";

const dbHost = "";
const dbUsername = "";
const dbPasssword = "";

async function getStudents() {
  const databaseOneSequelize = new Sequelize(
    "customer01",
    dbUsername,
    dbPassword,
    {host: dbHost, dialect: "mysql", dialectOptions: {ssl: "Amazon RDS"}},
  );

  const databaseTwoSequelize = new Sequelize(
    "customer02",
    dbUsername,
    dbPassword,
    {host: dbHost, dialect: "mysql", dialectOptions: {ssl: "Amazon RDS"}},
  );

  const customerOneStudent = Student.init(databaseOneSequelize);
  const customerTwoStudent = Student.init(databaseTwoSequelize);

  const customerOneJanes = await customerOneStudent.findAll({where: {firstName: "Jane"}});
  const customerTwoJanes = await customerTwoStudent.findAll({where: {firstName: "Jane"}});
}

models/Student.js

import {DataTypes, Model} from "sequelize";

export default class Student extends Model {
  static init(sequelize) {
    const attributes = {
      firstName: DataTypes.STRING,
      lastName: DataTypes.STRING,
    };

    const options = {
      underscored: true,
    };

    return super.init(attributes, {sequelize, ...options});
  }
}

Upvotes: 1

Views: 1208

Answers (2)

stefan2718
stefan2718

Reputation: 1800

This is possible without using raw queries. We are doing something similar with different customer databases. The key is that you cannot use the class X extends Model and init() approach to defining your models, since this overwrites the connection with the last one called.

Instead you have to use sequelize.define() for each Sequelize instance, as documented here.

databaseOneSequelize.define('Student', { modelFields });
databaseTwoSequelize.define('Student', { modelFields });

const customerOneJanes = await databaseOneSequelize.models.Student.findAll({where: {firstName: "Jane"}});
const customerTwoJanes = await databaseTwoSequelize.models.Student.findAll({where: {firstName: "Jane"}});

Upvotes: 1

r9119
r9119

Reputation: 606

How I understand sequelize, is that it can only query one database (Last initialised) at a time with its default query methods.

To query different databases you can use raw queries and specify the database with a replacement:

const { QueryTypes } = require('sequelize')

let dbToQuery = "";
let firstName = "Jane";

dbToQuery = "customer01"; // db name
const customerOneJanes = await sequelize.query('SELECT * FROM :db.student WHERE firstname = :firstName', {
  replacements: {db: dbToQuery, firstName: firstName}
  type: QueryTypes.SELECT
});

dbToQuery = "customer02";
const customerOneJanes = await sequelize.query('SELECT * FROM :db.student WHERE firstname = :firstName', {
  replacements: {db: dbToQuery, firstName: firstName}
  type: QueryTypes.SELECT
});
const customerTwoJanes

Upvotes: 0

Related Questions