Dynamically switching datastores at runtime

[MaheshkumarSundaram]

Hi @neonexus,

I have a model and it's schema is like below:

Model

module.exports = {
  tableName: 'dicompat',
  datastore: 'Node0',

  attributes: {
      id: {
          type: 'string',
          required: true,
          unique: true,
          maxLength: 64,
          example: 'RTP1234567',
      },

      name: {
          type: 'string',
          maxLength: 64,
      },

      dob: {
          type: 'string',
          maxLength: 8,
          allowNull: true,
      },

      gender: {
          type: 'string',
          maxLength: 16,
          allowNull: true,
      },

      accessTime: {
          type: 'number',
      },
  },
};

I may have upto 10 datastores which would have the same model schema.

I looked it up and found that the Driver can be used to handle this without using the model. But when using this sails.getDataStore().driver, I'm facing this issue. Not sure how to workaround this.

Or do you have any other suggestions on how to proceed with my usecase?

Any help would be aprreciated. Thank you!

[neonexus]

So...............

This is a REALLY complex issue. Let me see if I can break it down.

Sails is a collection of things; but mainly here, uses Waterline as the ORM to handle all datastores. Waterline, in-turn, uses adapters to translate Waterline's standard interface with whatever datastore. Another thing Waterline does, is create multiple connections to each datastore it needs to access (this is called a "pool" of connections). The idea here, is that multiple users can utilize multiple connections to the datastore, speeding things up a bit.

One way to handle multiple datastores here, would be to use multiple models, each pointed to their datastore. You could create a "base model" that has common configuration (stored elsewhere so Waterline doesn't actually use it), use LoDash merge to copy the common config and change as needed. However, now you are talking about having maybe 10 connections to 10 different datastores, just for this 1 model. That's going to likely require a lot of CPU power on production.

If you are trying to build a multi-tenant application, where each team / client has their own datastore, then I would recommend running an app instance for each client, pointed to their respective databases. This isolates applications, so if somehow 1 client manages to break your application (or fill up a database [happened to me once]), your other clients won't notice, as their instance would still be running.

One last option that came to mind; you could run microservices for each datastore, that can read / write as needed, then you would just make an API call in your controller, making dynamics a bit easier.

[MaheshkumarSundaram]

Thank you so much for your detailed explanation @neonexus. The last option seems the best for my usecase.

[neonexus]

Something that recently came to mind, for those that may run across this (random security rant warning):

When using microservices, make sure they are only accessible by your DEV/PROD machines; preferably the microservices NEVER have access to the open internet, only your "local/internal" network, and DEV is restricted to DEV servers, etc. Additionally, it can't be overstated, use a password manager, and NEVER use the same password twice.

Also, NEVER use your password to unlock your password manager for things online, EVER! (Or for any other purpose for that matter.) Use a long sentence that is easy to remember, punctuation included, as your password manager unlock. Something you don't speak out loud, or tell anyone. Make it funny even. Make a unique sentence to you, to prove you are you.

[SundaramMaheshkumar]

Thanks for the valuable tip. Appreciated!

[neonexus]

Re-opening this discussion for ease of viewing.