CODEWITHSUNDEEP
node.jspostgresqlsequelize.jsmulti-tenantsaas
Riyas TK
Riyas TK

Reputation: 1251

Multi tenant (SAAS) using nodejs sequelize

I am trying to build a multi tenant ( / Software as a service) using nodejs and postgres, sequelize as ORM. I decided to go with separate DBs for each client, rather than having single DB with all table having the extra column, because of security reasons. I achieved the result, but performance was not good, since i have to initialise models for each DB according to sequelize(for almost each request). Is there any better way to do this? Am I missing something in sequelize?

Upvotes: 4

Views: 8546

Answers (2)

westmark
westmark

Reputation: 919

I prefer the schema approach to multi tenancy in Postgres. I'm no authority on security but it should be better than table based multi tenancy. Disaster recovery should also be slightly easier than table based MT, but still worse than with separate DBs.

I managed to implement this in Sequilize using ES6 Proxies. This puts a hard requirment on your Node version, but if you're willing to use at least v6.9.2 you could give my lib a try:

https://www.npmjs.com/package/sequelize-multi-tenant-enhancer

Upvotes: 1

Tom Jardine-McNamara
Tom Jardine-McNamara

Reputation: 2608

A quick implementation of my comment above.

app.js:

const Sequelize = require('sequelize');

const connections = {
  client1: new Sequelize('postgres://user:[email protected]:5432/client1'),
  client2: new Sequelize('postgres://user:[email protected]:5432/client2'),
  client3: new Sequelize('postgres://user:[email protected]:5432/client3'),
};

const User = require('./models/user');
const Post = require('./models/post');
const Comment = require('./models/comment');

Object.keys(connections).forEach(connection => {
  connection.define('User', userColumns);
  connection.define('Post', postColumns);
  connection.define('Comment', commentColumns);
});

models/user.js:

module.exports = {
  id: {
    type: Sequelize.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  username: Sequelize.STRING,
  email: Sequelize.STRING
  // etc, etc
};

Obviously whatever server framework you use you'll need to detect (from the url I imagine) which client connection to use for a given request.

Alternatively, consider writing a single-connection app and deploying multiple instances of it (I'm doing this currently). Might be a simpler choice if you're set on separate DBs.

Upvotes: 4

Related Questions