How to dynamically connect mysql database according to each client?

Question

I am building a SaaS product using MEAN stack here M = mysql

I am using sequelize as ORM for mysql, my connection:

const sequelize = new Sequelize('smallscale_superapp', 'root', 'root1', {
  host: '127.0.0.1',
  port: 3306,
  dialect: 'mysql',
  logging: false,
});

For example i have one admin super app so i create one business for example business code called "001abcd", this business code stores in my main admin db, then i told my all clients to go to one particular url called "example.com".

So if they enter the url, the home page will looks like slack.com there is one input box and after hardcoded value called example.com

So now my client in input box they put the business code which i created them and stored in my main db.

After putting the business code in input box a new sub domain will open called "001abcd.example.com" (I am checking whether the business code matches in my main db if matches i am creating dynamic subdomains)

SO if all good, after creation of subdomain client page will show as LOGIN screen where they needs to signup and the signup details needs to be there db dynamically

NOTE: I am getting the client db, username, host, password in my main db table while creating business.

How to done this using sequelize mysql?

How to get sequelize connection to connect dynamically based on client login?

As i said before i have client dbname..etc in my db I am trying to create connection like this:

exports.dynamicDatabase = (req, res) => {
  const { code } = req.params;
  Business.find({
    where: { code },
    raw: true,
  })
    .then((data) => {
      const sequelizeClient = new Sequelize(data.dbname, data.dbusername, data.dbpassword, {
        host: data.dbhost,
        port: 3306,
        dialect: 'mysql',
        logging: false,
      });
    })
    .catch((err) => { res.status(400).send(err.message); });
};

Now outside my this function i am trying to sync tables, like this:

sequelizeClient.authenticate().then((err) => {
  if (err) {
    console.log('There is connection in ERROR.');
  } else {
    console.log('Dynamic MYSQL Connection has been established successfully');
  }
});
sequelizeClient.sync().then(() => {
  console.log('Dynamic Missing Table Created');
}, (err) => {
  console.log('An error occurred while creating the table:', err.message);
});

The variable sequelizeClient is inside the function, so i cant able to access? globally.

So i make the variable globally, but one problem

I have sequelizeClient variable inside function, this will function execute after only entering my domain in url,

But the sequelizeClient.sync() is not inside function so when i start node server.js it throws me error:

sequelizeClient.authenticate().then((err) => {
[0]                 ^
[0]
[0] TypeError: Cannot read property 'authenticate' of undefined

Answer 1

Yep, After a lot of search, i found there are three databases approach to build a SaaS product,

1. Separate Databases for each tenant
2. Separate Schemas(tables) and common one database for all tenants
3. Shared tables and one database for all tenants

Finally i decided to use option 2, and i moved to Postgresql instead of mysql, because Postgresql has schema based approach!

Now my final code:

const postgresDB = new Sequelize('postgres://localhost:5432/test_enduser');

postgresDB.authenticate().then((err) => {
  if (err) {
    console.log('There is connection in ERROR.');
  } else {
    console.log('Postgres Connection has been established successfully');
  }
});

postgresDB.define('inventory', {
  name: Sequelize.STRING,
});

const createSchema = () => {
  Business.findAll({
    raw: true,
  }).then((data) => {
    data.forEach((client) => {
      postgresDB.createSchema(client.code).then(() => {
        Object.keys(postgresDB.models).forEach((currentItem) => {
          postgresDB.models[currentItem].schema(client.code).sync();
        });
        // new schema is created
        console.log('Postgres schema created');
      }).catch((err) => {
        console.log(err.message);
      });
    });
  });
};
createSchema();


// apis
exports.getAllBusiness = (req, res) => {
  postgresDB.models.inventory.schema(req.user.code).findAll()
    .then((results) => {
      res.send(results);
    });
};

exports.postBusiness = (req, res) => {
  const user = {
    name: req.body.name,
  };
  postgresDB.models.inventory.schema(req.user.code).create(user)
    .then(data => res.status(200).send(data))
    .catch(Sequelize.ValidationError, err => res.status(422).send(err.errors[0].message))
    .catch(err => res.status(400).send(err.message));
};

1. I am setting my postgres connection
2. Simply for test i am creating a table called "inventory"

3. In my application a online came and he signup, while signup i am storing there Business Code into my Business table(The Business table coming from separate DB, which is a super main app DB), so i am finding the business code and looping and make dynamic schemas in postgres

4. Now see my api, I am doing CRUD based on req.user.code(from login session),and i am finally matching showing datas according to particular schemas,

So finally i have clean separate schemas for each client 😁

Thanks!

Answer 2

Finally i tried this and make it work, but i dont know this is good approach or not, so tell me whether this is good approach or not?

I am getting the exact data by user request params:

let sequelizeClient;
let Login;
exports.getDomain = (req, res) => {
  const { code } = req.params;
  Business.find({
    raw: true,
    where: { code },
  }).then((data) => {
    if (data === null) {
      res.status(400).send(data);
    } else {
      sequelizeClient = new Sequelize(data.dbname, data.dbusername, data.dbpassword, {
        host: data.dbhost,
        port: 3306,
        dialect: 'mysql',
        logging: false,
      });

      // end user connections
      sequelizeClient.authenticate().then((err) => {
        if (err) {
          console.log('There is connection in ERROR.');
        } else {
          console.log(`${data.code} MYSQL Connection has been established successfully`);
        }
      });

      sequelizeClient.sync().then(() => {
        console.log(`${data.code} Missing Table Created`);
      }, (err) => {
        console.log('An error occurred while creating the table:', err.message);
      });

      // mysql tables;
      Login = sequelizeClient.define('login', {
        username: Sequelize.STRING,
        firstName: Sequelize.STRING,
        lastName: Sequelize.STRING,
      });
    }
  });
};

I am declaring table schema variable as global and i am using in my code:

// api
exports.signUp = (req, res) => {
  const user = {
    username: req.body.username,
    firstName: req.body.firstName,
    lastName: req.body.lastName,

  };
  Login.create(user)
    .then(data => res.status(200).send(data))
    .catch(Sequelize.ValidationError, err => res.status(422).send(err.errors[0].message))
    .catch(err => res.status(400).send(err.message));
};