Example code for data isolation with node.js, express and sequelize

gettasks.js

/* gettasks.js */

app.get('/tasks', async (req, res, next) => {
  const { user } = req
  const { Task, TaskType } = req.models

  try {
    const tasks = await Task.findAll({
      where: { userId: user.id },
      include: {
        model: TaskType,
        as: 'taskType'
      }
    })

    res.json(tasks)
  } catch (err) {
    next(err)
  }
})
/* Full code example at https://github.com/renatoargh/data-isolation-example */

index-1.js

/* index-1.js */

const express = require('express')
const sequelizeMiddleware = require('./middlewares/sequelize')
const authMiddleware = require('./middlewares/auth')

const app = express()
app.use(sequelizeMiddleware()) // 1
app.use(authMiddleware) // 2

app.get('/tasks', async (req, res, next) => {
  // 3 - Code to access database - see below
})

app.listen(9090, () => {
  console.log('data-isolation-example running on port 9090')
})

/* Full code example at https://github.com/renatoargh/data-isolation-example */

index.js

/* index.js */

const express = require('express')
const sequelizeMiddleware = require('./middlewares/sequelize')
const authMiddleware = require('./middlewares/auth')

const app = express()
app.use(sequelizeMiddleware())
app.use(authMiddleware)

app.get('/tasks', async (req, res, next) => {
  const { user } = req
  const { Task, TaskType } = req.models

  try {
    const tasks = await Task.findAll({
      where: { userId: user.id },
      include: {
        model: TaskType,
        as: 'taskType'
      }
    })

    res.json(tasks)
  } catch (err) {
    next(err)
  }
})

app.listen(9090, () => {
  console.log('data-isolation-example running on port 9090')
})

/* Full code example at https://github.com/renatoargh/data-isolation-example */

middlewares-auth.js

/* middlewares-auth.js */

module.exports = async (req, res, next) => {
  const { User } = req.models

  let { authorization = '' } = req.headers
  authorization = authorization.replace('Basic ', '')
  authorization = Buffer.from(authorization, 'base64')
  authorization = authorization.toString('ascii')

  const [ email, password ] = authorization.split(':')

  try {
    const user = await User.findOne({ where: { email } }) // 1

    if (!user || user.password !== password) {
      res.status(403).json({})
    }

    req.user = user
    req.changeSchema(user.dedicatedSchema) // 2

    next()
  } catch (err) {
    next(err)
  }
}

/* Full code example at https://github.com/renatoargh/data-isolation-example */

middlewares-sequelize.js

/* middlewares-sequelize.js */

const path = require('path')
const fs = require('fs')
const Sequelize = require('sequelize')

const { CONNECTION_STRING } = process.env
const modelsFolder = path.join(__dirname, '../models')
const models = {}

module.exports = () => {
  const sequelize = new Sequelize(CONNECTION_STRING, {
    dialect: 'mssql',
    dialectOptions: { encrypt: true }
  })

  fs.readdirSync(modelsFolder).forEach(modelPath => {
    modelPath = path.join(modelsFolder, modelPath)

    let model = require(modelPath)
    model = sequelize.import(modelPath, model)
    model = model.changeSchema('shared')
    models[model.name] = model
  })

  Object.values(models)
        .filter(m => m.associate)
        .forEach(m => m.associate(models))

  return (req, res, next) => {
    req.models = models
    req.changeSchema = schema => {
      Object.keys(req.models)
            .forEach(modelName => {
              const model = req.models[modelName]
              req.models[modelName] = model.changeSchema(schema)
            })
    }

    next()
  }
}

/* Full code example at https://github.com/renatoargh/data-isolation-example */

models-task.js

/* models-task.js */

module.exports = (sequelize, DataTypes) => {
  const Task = sequelize.define('Task', {
    id: {
      type: DataTypes.UUIDV4,
      allowNull: false,
      primaryKey: true,
      field: 'Id'
    },

    description: {
      type: DataTypes.STRING(100),
      allowNull: false,
      field: 'Description'
    },

    done: {
      type: DataTypes.BOOLEAN,
      allowNull: false,
      default: false,
      field: 'Done'
    },

    taskTypeId: {
      type: DataTypes.INTEGER,
      allowNull: true,
      field: 'TaskTypeId'
    },

    userId: {
      type: DataTypes.INTEGER,
      allowNull: true,
      field: 'UserId'
    }
  }, {
    freezeTableName: true,
    tableName: 'Tasks',
    createdAt: false,
    updatedAt: false,
    classMethods: {
      changeSchema: schema => Task.schema(schema),
      associate: models => {
        Task.belongsTo(models.TaskType, {
          as: 'taskType',
          foreignKey: 'taskTypeId'
        })
      }
    }
  })

  return Task
}

/* Full code example at https://github.com/renatoargh/data-isolation-example */

models-tasktype.js

/* models-tasktype.js */

module.exports = (sequelize, DataTypes) => {
  const TaskType = sequelize.define('TaskType', {
    id: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true,
      field: 'Id'
    },

    description: {
      type: DataTypes.STRING(100),
      allowNull: false,
      field: 'Description'
    }
  }, {
    freezeTableName: true,
    tableName: 'TaskTypes',
    createdAt: false,
    updatedAt: false,
    classMethods: {
      changeSchema: () => TaskType.schema('shared')
    }
  })

  return TaskType
}

/* Full code example at https://github.com/renatoargh/data-isolation-example */

models-user.js

/* models-user.js */

module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    id: {
      type: DataTypes.UUIDV4,
      allowNull: false,
      primaryKey: true,
      field: 'Id'
    },

    email: {
      type: DataTypes.STRING(100),
      allowNull: false,
      field: 'Email'
    },

    password: {
      type: DataTypes.STRING(100),
      allowNull: false,
      field: 'Password'
    },

    dedicatedSchema: {
      type: DataTypes.STRING,
      allowNull: true,
      field: 'DedicatedSchema'
    }
  }, {
    freezeTableName: true,
    tableName: 'Users',
    createdAt: false,
    updatedAt: false,
    classMethods: {
      changeSchema: () => User.schema('shared')
    }
  })

  return User
}

/* Full code example at https://github.com/renatoargh/data-isolation-example */

sql-create-shared-schema.sql

/* sql-create-shared-schema.sql */

CREATE SCHEMA "shared";

CREATE TABLE "shared".Users (
  Id uniqueidentifier NOT NULL,
  Email varchar(100) NOT NULL,
  Password varchar(100) NOT NULL,
  DedicatedSchema varchar(100) NOT NULL,
  CONSTRAINT Users_PK PRIMARY KEY (Id)
)

INSERT INTO "shared".Users
VALUES (NEWID(), '[email protected]', 'pass1', 'tenant-1')

INSERT INTO "shared".Users
VALUES (NEWID(), '[email protected]', 'pass2', 'tenant-2')

CREATE TABLE "shared".TaskTypes (
  Id int NOT NULL IDENTITY(1,1),
  Description varchar(100) NULL,
  CONSTRAINT TaskTypes_PK PRIMARY KEY (Id)
)

INSERT INTO "shared".TaskTypes
VALUES
  ('Database Tasks'),
  ('Back-End Tasks'),
  ('Front-End Tasks')

/* Full code example at https://github.com/renatoargh/data-isolation-example */

sql-create-tenant-schema.sql

/* sql-create-tenant-schema.sql */

CREATE SCHEMA "tenant-1";
CREATE TABLE "tenant-1".Tasks (
  Id uniqueidentifier NOT NULL,
  Description varchar(100) NOT NULL,
  Done bit NOT NULL DEFAULT ((0)),
  TaskTypeId int NOT NULL,
  UserId uniqueidentifier NOT NULL,
  CONSTRAINT Tasks_PK PRIMARY KEY (Id)
)

INSERT INTO "tenant-1".Tasks
VALUES (
  NEWID(),
  'Create database schemas',
  0,
  1,
  (SELECT Id FROM "shared".Users WHERE DedicatedSchema='tenant-1')
)

INSERT INTO "tenant-1".Tasks
VALUES (
  NEWID(),
  'Create express app',
  0,
  2,
  (SELECT Id FROM "shared".Users WHERE DedicatedSchema='tenant-1')
)

CREATE SCHEMA "tenant-2";
CREATE TABLE "tenant-2".Tasks (
  Id uniqueidentifier NOT NULL,
  Description varchar(100) NOT NULL,
  Done bit NOT NULL DEFAULT ((0)),
  TaskTypeId int NOT NULL,
  UserId uniqueidentifier NOT NULL,
  CONSTRAINT Tasks_PK PRIMARY KEY (Id)
)

INSERT INTO "tenant-2".Tasks
VALUES (
  NEWID(),
  'Create tables',
  0,
  1,
  (SELECT Id FROM "shared".Users WHERE DedicatedSchema='tenant-2')
)

INSERT INTO "tenant-2".Tasks
VALUES (
  NEWID(),
  'Create the UI',
  0,
  3,
  (SELECT Id FROM "shared".Users WHERE DedicatedSchema='tenant-2')
)
/* Full code example at https://github.com/renatoargh/data-isolation-example */

sql-stored-procedure-migration-example.js

/* sql-stored-procedure-migration-example.js */

const serialQueryExecuter = require('../serialQueryExecuter')

module.exports = {
  description: 'Creates `ListTasks` procedure and `ClosedTasks` view',

  up: (schema, sequelize) => {
    const createProcedure = `
      CREATE PROCEDURE [${schema}].ListTasks AS
      BEGIN
        SELECT * FROM [${schema}].Tasks
      END
    `

    const createView = `
      CREATE VIEW [${schema}].ClosedTasks AS
      BEGIN
        SELECT * FROM [${schema}].Tasks WHERE Done=1
      END
    `

    return serialQueryExecuter({
      queries: [
        createProcedure,
        createView
      ],
      sequelize: sequelize
    })
  }
}

/* Full code example at https://github.com/renatoargh/data-isolation-example */