NodeJs Multi-Tenant Structure

db.js

initDatabase = () => {
    const sequelize = new Sequelize(
      'database',
      'username',
      'password',
      {
        host: 'host',
        port: 'port',
        dialect: 'mysql',
        pool: {
          max: 5,
          min: 0,
          acquire: 30000,
          idle: 10000
        },
        logging: false
      }
    );
    return sequelize;
  }
  
  const database = initDatabase();
  
  module.exports = database;

dbtenant.md

Tenants table:

id	name	url	created_at	updated_at
1	johndoe	johndoe.example.com	1970-01-01T00:00:00Z	1970-01-01T00:00:00Z
2	jamesdoe	jamesdoe.example.com	1970-01-01T00:00:01Z	1970-01-01T00:00:01Z
3	murielbing	murielbing.example.com	1970-01-01T00:00:02Z	1970-01-01T00:00:02Z

Users table:

id	tenant_id	email	pwd	created_at	updated_at
1	1	johndeo@example.com	$2y$11$hHs1HTCAZUEn3eHtiwNvX.kX4RUaXS25ks.kBZCyJJaEx8Ho9KA86	1970-01-01T00:00:00Z	1970-01-01T00:00:00Z
2	1	johnassistant@example.com	$2y$11$hHs1HTCAZUEn3eHtiwNvX.kX4RUaXS25ks.kBZCyJJaEx8Ho9KA76	1970-01-01T00:00:01Z	1970-01-01T00:00:01Z
3	2	jamesdoe@example.com	$2y$11$hHs1HTCAZUEn3eHtiwNvX.kX4RUaXS25ks.kBZCyJJaEx8Ho9KA66	1970-01-01T00:00:02Z	1970-01-01T00:00:02Z
4	3	murielbing@example.com	$2y$11$hHs1HTCAZUEn3eHtiwNvX.kX4RUaXS25ks.kBZCyJJaEx8Ho9KA56	1970-01-01T00:00:03Z	1970-01-01T00:00:03Z

hooks.model.js

/* Hooks object */
// https://github.com/sequelize/sequelize/blob/v6/lib/hooks.js#L7
const hooks = {
        beforeCreate: [],
        beforeUpdate: [],
        beforeFind(query) {
            if (this.sequelize.options.tenant_id && this.options.multitenant && !this.sequelize.options.tenant_safe) {
                query.where = Object.assign({}, query.where, {
                    tenant_id: this.sequelize.options.tenant_id
                });
            }
        },
        beforeSave: [],
        beforeBulkCreate: [],
        beforeBulkUpdate: [],
        beforeBulkDestroy: [],
        beforeValidate(instance, options) {
            if (this.sequelize.options.tenant_id && this.options.multitenant && !this.sequelize.options.tenant_safe) {
                instance.tenant_id = this.sequelize.options.tenant_id;
            }
        },
        afterCreate: [],
        afterUpdate: [],
        afterFind: [],
        afterFindAll: [],
        afterBulkCreate: [],
        afterBulkUpdate: [],
        afterBulkDestroy: [],
        afterValidate: [],
        beforeDestroy: [],
    };
module.exports = hooks;

query.js

const user = await models.users.findOne({where: {id: req.user.toString()}});

routing.js

const database = require('./db');
const { tenant } = require('./middleware/tenant');

const router = express.Router();
router.use((req, res, next) => tenant(req, res, next, database));
router.use(...);

tenant.js

exports.tenant = (req, res, next, db) => {
    // Get token from header
    const tenant_id = req.header('App-Tenant');

    // Check if tenant is available
    if (!tenant_id) {
        return res
            .status(401)
            .json(new Response(401, 'No App-Origin, authorization denied!').getResponse());
    }

    try {
        // Add sequelize scope pattern to all models of tenant
        // Query teams table for App-Tenant and if valid, attach to request object.
        db.options.tenant_id = tenant_id;
        db.options.tenant_safe = false;
        req.tenant_id = tenant_id;
        next();
    } catch (err) {
        res
            .status(401)
            .json(
                new Response(401, 'Invalid App-Origin, authorization denied!').getResponse()
            );
    }
};

users.model.js

const Sequelize = require('sequelize');
const hooksModels = require('./hooks.models');

module.exports = function(sequelize, DataTypes) {
  return sequelize.define('users', {
    id: {
      autoIncrement: true,
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true
    },
    tenant_id: {
      type: DataTypes.INTEGER,
      allowNull: true,
      defaultValue: 1
    },
    email: {
      type: DataTypes.STRING(100),
      allowNull: true
    },
    password: {
      type: DataTypes.STRING(100),
      allowNull: true
    },
    created_at: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: Sequelize.Sequelize.literal('CURRENT_TIMESTAMP')
    },
    updated_at: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: Sequelize.Sequelize.literal('CURRENT_TIMESTAMP')
    }
  }, {
    sequelize,
    tableName: 'users',
    multitenant: true,
    timestamps: false,
    indexes: [
      {
        name: "PRIMARY",
        unique: true,
        using: "BTREE",
        fields: [
          { name: "id" },
        ]
      },
    ],
    hooks: hooksModels
  });
};