Install

Via package manager

$ npm install --save @exoshtw/sequelize-views-support

Or Yarn

$ yarn add @exoshtw/sequelize-views-support

Via Git

$ git clone https://github.com/exoshtw/sequelize-views-support.git
$ cd sequelize-views-support
$ npm install

Use in your project

This package provide a decorator for Sequelize class, you need to use it decorated version of the class instead the original:

import { Sequelize } from 'sequelize';
import { decorateSequelize } from '@exoshtw/sequelize-views-support';

const ViewSupporterSequelize = decorateSequelize(Sequelize);

const sequelize = new ViewSupporterSequelize(...);

Basics

The views support try to be the more transparent and squelize-styled as possible.

Define new View

To create a new View, you need to use a familiar syntax, as a normal Model, with the difference that you need to define the select query the view must to create.

There are two ways for it; Using an option called viewQueryOptions or override a static method called getQueryOptions.

Using config:

The easy way is pass the query options by init options, ex:

class EnabledUser extends View {}

EnabledUser.init({
  username: DataTypes.STRING,
  enabled: DataTypes.BOOLEAN,
}, {
  sequelize,
  timestamps: false,
  viewQueryOptions: {
    model: User,
    attributes: ['id', 'username'],
    where: {
      enabled: true,
    },
  },
});

Using it we are creating a view using info from model User, using a similar syntax to do a findAll.

Overriding getQueryOptions

The other way is overriding the static method getQueryOptions, this gets as first param the options of the View (as a model):

class EnabledUser extends View {
  static getQueryOptions(options) {
    return {
      model: User,
      attributes: ['id', 'username'],
      where: {
        enabled: true,
      },
    };
  }
}

EnabledUser.init({
  username: DataTypes.STRING,
  enabled: DataTypes.BOOLEAN,
}, {
  sequelize,
  timestamps: false,
});

Using both

You can to override the method using the previous viewQueryOptions option, ex:

class EnabledUser extends View {
  static getQueryOptions(options) {
    return {
      model: User,
      attributes: ['id', 'username'],
      ...options.viewQueryOptions,
    };
  }
}

EnabledUser.init({
  username: DataTypes.STRING,
  enabled: DataTypes.BOOLEAN,
}, {
  sequelize,
  timestamps: false,
  viewQueryOptions: {
    where: { enabled: true },
  },
});

Usage

The usage of a defined view is totally similar to use a standar model, using the View defined below, we can do:

const result = await EnabledUser.findAll();
console.log(result.map((user) => user.toJSON()));

The query options are applicable over the View, for example:

const result = await EnabledUser.findAll({
  attributes: ['username'],
  order: [
    ['username', 'DESC'],
  ],
});

All the supported methods by Sequelize.Model are supported, like findOne, count, etc.

Using with associations

The viewQueryOptions options support the most features of a sequelize query options, including include, the only extra-step we need to do, is indicate to the view, where are the information of the included fields.

For this proppose, there are an option param called fieldsMap, this should to be a dictionary like object using the view field as key, and the query field as value.

Example:

// Create User Model
class User extends Model {};

User.init({
  username: DataTypes.STRING,
  birthday: DataTypes.DATE,
}, { sequelize, modelName: 'user' });

// Create Post Model
class Post extends Model {};

Post.init({
  title: DataTypes.STRING,
  content: DataTypes.TEXT,
}, { sequelize, modelName: 'post' });

// Create Post -> User sssociation
Post.belongsTo(User, {allowNull: false});

// Create View
class PublicPost extends View {}; 

PublicPost.init({
  title: DataTypes.STRING,
  author: DataTypes.STRING,
  createdAt: DataTypes.DATE,
}, {
    sequelize,
    modelName: 'public_posts',
    timestamps: false,
    viewQueryOptions: {
        model: Post, 
        attributes: ['id', 'title', 'createdAt'],
        include: [{
            model: User,
            required: true,
            attributes: ['username'],
        }],
        // Map user.username to author
        fieldsMap: {
            author: 'user.username',
        },
    },
});

(async () => {
  await sequelize.sync();

  const jane = await User.create({
    username: 'janedoe',
    birthday: new Date(1980, 6, 20),
  });

  const robert = await User.create({
    username: 'robert',
    birthday: new Date(1987, 1, 25),
  });

  const post1 = await Post.create({
    title: 'Post 1',
    content: 'This is the first post',
    userId: jane.id,
  });

  const post2 = await Post.create({
    title: 'Post 2',
    content: 'This is the second post',
    userId: robert.id,
  });

  const result = await PublicPost.findAll();

  console.log(result.map((post) => post.toJSON()));
})();

this example should to return:

[
  {
    id: 1,
    title: 'Post 1',
    author: 'janedoe',
    createdAt: 2022-03-01T01:05:53.411Z
  },
  {
    id: 2,
    title: 'Post 2',
    author: 'robert',
    createdAt: 2022-03-01T01:05:53.414Z
  }
]

Using with aggregations fields

Views support make easy to work with aggregations fields, beig abble to use it as standar fields because this are going to be proccesed by the db engine.

Using the option param fieldsMap, you can to map a View field to an aggregation function.

Example using count

// Create User Model
class User extends Model {};

User.init({
  username: DataTypes.STRING,
  birthday: DataTypes.DATE,
}, { sequelize, modelName: 'user' });

// Create Post Model

class Post extends Model {};

Post.init({
  title: DataTypes.STRING,
  content: DataTypes.TEXT,
}, { sequelize, modelName: 'post' });

// Create Post -> User sssociation
Post.belongsTo(User, {allowNull: false});
User.hasMany(Post);

// Create View
class UserPosts extends View {}; 

UserPosts.init({
    username: DataTypes.STRING,
    posts: DataTypes.INTEGER,
}, {
    sequelize,
    modelName: 'user_posts',
    timestamps: false,
    viewQueryOptions: {
        model: User, 
        attributes: [
            'id',
            'username',
            [fn('count', 'post.id'), 'posts']
        ],
        group: ['user.id'],
        include: [{
            model: Post,
            attributes: [],
        }],
    },
});

(async () => {
  await sequelize.sync();

  const jane = await User.create({
    username: 'janedoe',
    birthday: new Date(1980, 6, 20),
  });

  const robert = await User.create({
    username: 'robert',
    birthday: new Date(1987, 1, 25),
  });

  const post1 = await Post.create({
    title: 'Post 1',
    content: 'This is the first post',
    userId: jane.id,
  });

  const post2 = await Post.create({
    title: 'Post 2',
    content: 'This is the second post',
    userId: robert.id,
  });

  const post3 = await Post.create({
    title: 'Post 3',
    content: 'This is the third post',
    userId: robert.id,
  });

  const result = await UserPosts.findAll();

  console.log(result.map((user) => user.toJSON()));
})();

This should to write out:

[
  { id: 1, username: 'janedoe', posts: 1 },
  { id: 2, username: 'robert', posts: 2 }
]

Using where/group/order with aggregated fields

The aesy way to make complex operations with aggregators is becouse you treat this like standar fields, being abble to do for ex:

const result = UserPost.findAll({
    attributes: ['username', 'posts'],
    where: {
      posts: {
        [Op.gte]: 10,
      },
    },
    order: [
      ['posts', 'DESC'],
    ],
});

In this case, we are filtering and ordering using the final count of posts, in a squelize simple query, this is very complex becaouse we need to group and define aggregators in the query. Using views, there are simplest.

Using Views instead of virtual fields

Another usage for the Views impplementation must to be the use of pre-calculate or aggregated values instead of virtual fields, for querying implementation and performance.

In the sequelize style, we need to create a virtual field and define the get property to for example do a concat.

Using views we are abble to use these fields for querying, filter, order, etc.

Example

class User extends Model {};

User.init({
  name: DataTypes.STRING,
  lastname: DataTypes.STRING,
}, { sequelize, modelName: 'user' });

class FullNameUser extends View {}; 

FullNameUser.init({
    fullname: DataTypes.STRING,
}, {
    sequelize,
    modelName: 'fullname_user',
    timestamps: false,
    viewQueryOptions: {
        model: User,
        attributes: [
            'id',
            // Use fn('concat', ...) in postgres
            [literal(`name || ' ' || lastname `), 'fullname']
        ],
    },
});

(async () => {
  await sequelize.sync();

  const jane = await User.create({
    name: 'Jane',
    lastname: 'Doe',
  });

  const robert = await User.create({
    name: 'Robert',
    lastname: 'Jhonson',
  });

  const enableds = await FullNameUser.findAll();

  console.log(enableds.map((user) => user.toJSON()));
})();

This should to output:

[
  { id: 1, fullname: 'Jane Doe' },
  { id: 2, fullname: 'Robert Jhonson' }
]

Obviuoly, we are limmited to use logic from the dialect and not in Javscript, but with the adventage to use it in queries, for ex:

const result = await FullNameUser.findAll({
  where: {
    fullname: {
      [Op.like]: 'J% D%',
    },
  },
});

Combine concepts

If you read the previous examples, you are aware the power of Views, in this example, we are going to combine some concepts like associations and aggregations to make something more complex, in this case we are going to create a View thats resolve a User balance.

In this example with have two models:

• User: The users model getting info from simple users
• Pay: Pays to user, each register add an amount to a user

Example

// Create User Model
class User extends Model {};

User.init({
  username: DataTypes.STRING,
  birthday: DataTypes.DATE,
}, { sequelize, modelName: 'user' });

// Create Pays Model 

class Pay extends Model {};

Pay.init({
  amount: DataTypes.DECIMAL(),
  observation: DataTypes.STRING,
}, { sequelize, modelName: 'pay' });

// Create Pay -> User sssociation
Pay.belongsTo(User, {allowNull: false});
User.hasMany(Pay);

// Create View
class UserBalance extends View {}; 

UserBalance.init({
    username: DataTypes.STRING,
    amount: DataTypes.DECIMAL,
}, {
    sequelize,
    modelName: 'user_balance',
    timestamps: false,
    viewQueryOptions: {
        model: User, 
        attributes: [
            'id',
            'username',
            [fn('coalesce', fn('sum', col('pays.amount')), 0), 'amount']
        ],
        group: ['user.id'],
        include: [{
            model: Pay,
            required: false,
        }],
    },
});

(async () => {
  await sequelize.sync();

  const jane = await User.create({
    username: 'janedoe',
    birthday: new Date(1980, 6, 20),
  });

  const robert = await User.create({
    username: 'robert',
    birthday: new Date(1987, 1, 25),
  });

  await Pay.create({
    amount: 100,
    observation: 'Salary',
    userId: jane.id,
  });

  await Pay.create({
    amount: 50,
    observation: 'Gift',
    userId: jane.id,
  });

  await Pay.create({
    amount: 0.57,
    observation: 'Interest',
    userId: jane.id,
  });

    const result = await UserBalance.findAll({
        // Use aggreation functions in order like a sr.
        order: [['amount', 'ASC']],
    });

  console.log(result.map((user) => user.toJSON()));
})();

The output should to be:

[
  { id: 2, username: 'robert', amount: 0 },
  { id: 1, username: 'janedoe', amount: 150.57 }
] 

Explanation

In this case we use an association with Pay model, and an aggregation function to sum the amounts, getting a reduced balance from user, in this case don't map fields becouse the amount field is declared in the main Model, this View can be used for obtain single registers without use aggregations:

const janedoeBalance = await UserBalance.findOne({
  attributes: ['amount'],
  where: {id: 1},
  raw: true,
})
  .then((result) => result.amount)
;

Polymorphism using Views

One of pilars of the object oriented programming is the polymorphism, but this paradigm is very hard to apply in relational databases, and this complexity is increased in the ORM layer.

Sequelie have an example using the beforeFind hook and scopes.

There we explore an alternative using Views, with the adventage of the transparent usage in queries.

Stage

In this stage with have and entity called Vehicle, with a serie of common vehicles attributes, as the price or the name, and a attribute called type that define who type of vehicle is, the rest of properties are in two separate models: Car and Bike, this referers to the Vehicle model with an one to one relation.

Propouse

We propose to create two abstract class, VehicleChild extending of Model, and VehicleChildView extending of View.

Implement

Create parent model

First, we are going the Parent entity model:

// Create Vehicle Model
class Vehicle extends Model {};

Vehicle.init({
  type: DataTypes.ENUM('car', 'bike'),
  name: DataTypes.STRING,
  price: DataTypes.DECIMAL,
}, { sequelize, modelName: 'vehicle' });

This has the type of vehicle and the common information.

Create child abstract

Next, we are going to code a abstract class for use by the childs of Vehicle:

// Create Child Model Abstract
class VehicleChildren extends Model {
  // Override init for add id field
  static init(fields, options = {}) {
    super.init({
      id: {
        type: DataTypes.INTEGER,
        allowNull: false,
        primaryKey: true,
        autoIncrement: false,
      },
      ...fields,
    }, {
      timestamps: false,
      ...options,
    });
  }

  static associate(models) {
    Vehicle.hasOne(this, {
        foreignKey: 'id',
        sourceKey: 'id',
        constraints: true,
    });
  }
};

In this case, we are overriding the method init, to add an id field, in this case, it's neccesary the child id was the same of the parent model, for this reason set autoIncrement in false.

By other hand, set timestamps in false becouse the creation time is in the parent model. By practical propouses we will not to worry for the updatedAt field.

In the associate method, we are associating own child model with the parent.

Create child models

// Create Car Model
class Car extends VehicleChildren {};

Car.init({
    transmission: DataTypes.ENUM('automatic', 'manual'),
    doors: DataTypes.INTEGER,
    style: DataTypes.ENUM('sedan', 'coupe', 'convertible'),
    hp: DataTypes.INTEGER,
}, { sequelize, modelName: 'car'});

// Create Bike Model
class Bike extends VehicleChildren {};

Bike.init({
    style: DataTypes.ENUM('road', 'mountain', 'hybrid'),
    cicles: DataTypes.INTEGER,
    cc: DataTypes.INTEGER,
}, { sequelize, modelName: 'bike'});

// Create associations 
Car.associate();
Bike.associate();

We extends the VehicleChildren and define a serie of own properties for each vehicle type.

Create the abstract View

For DRY, we have to create an abstract View overriding some methods:

// Create View Base
class VehicleChildView extends View {

    // Override getQueryOptions using props
    static getQueryOptions(options) {
        return {
            model: Vehicle, 
            include: [{
                model: this.model,
                required: true,
            }],
            where: {
                type: this.type,
            },
            ...(options.viewQueryOptions || {}),
        };
    }

    static init(fields, options = {}) {

        const extraFields = Object.keys(fields).reduce((acc, key) => {
            acc[key] = `${this.type}.${key}`;
            return acc;
        }, {});

        const {viewQueryOptions}  = options;

        super.init({
            name: DataTypes.STRING,
            price: DataTypes.DECIMAL,
            ...fields,
        }, {
            ...options,
            viewQueryOptions: {
                fieldsMap: {
                    ...extraFields,
                    ...(viewQueryOptions || {}),
                },
                ...(viewQueryOptions && options.viewQueryOptions || {}),
            }, }); }

    // Override create 
    static async create(data, options = {}) {
        return sequelize.transaction(async (transaction) => {
            const {
                name,
                price,
                ...extra
            } = data;

            const vehicle = await Vehicle.create({
                type: this.type,
                name,
                price,
            }, { transaction });

            const child = await this.model.create({
                id: vehicle.id,
                ...extra,
            }, { transaction });

            return this.build({
                ...vehicle.get({plane: true}),
                ...child.get({plane: true}),
            });
        });
    }
};

In this case we are using the method getQueryOptions to define the View query, equal for all childrens. We expect two static properties in the definition of a VehicleChildView; type and model, this properties are used for abstract View to create the query options, the relation and to be used in the overrided logic of create method, in this case we are overriting create to desmotrate a use of a View like some kind of serializer.

We are auto-mapping the own-properties too.

Create the child views

With the code abstracted bellow, the definition of new Views that represent a child of Vehicle, must to be simple:

// Create Car View
class VehicleCar extends VehicleChildView {
    static type = 'car';
    static model = Car;
};

VehicleCar.init({
    transmission: DataTypes.STRING,
    doors: DataTypes.INTEGER,
    style: DataTypes.STRING,
    hp: DataTypes.INTEGER,
}, {
    sequelize,
});

// Create Bike View
class VehicleBike extends VehicleChildView {
    static type = 'bike';
    static model = Bike;
};

VehicleBike.init({
    style: DataTypes.STRING,
    cicles: DataTypes.INTEGER,
    cc: DataTypes.INTEGER,
}, {
    sequelize,
});

Only extending the class VehicleChildView and creating the properties typeand model, the rest of the logic runs over the abstract View.

Usage

The usage is very simple, we treat the final views like models with the full information of entitiy (parent and child attributes):

(async () => {
  await sequelize.sync();

  const car = await VehicleCar.create({
    name: 'Audi',
    price: 100000,
    transmission: 'automatic',
    doors: 4,
    style: 'sedan',
    hp: 200,
  });

  const bike = await VehicleBike.create({
    name: 'BMX',
    price: 10000,
    style: 'road',
    cicles: 100,
    cc: 100,
  });

  const cars = await VehicleCar.findAll();
  console.log(cars.map((car) => car.toJSON()));

  const bikes = await VehicleBike.findAll();
  console.log(bikes.map((bike) => bike.toJSON()));
})();

The result should to output:

[
  {
    id: 1,
    name: 'Audi',
    price: 100000,
    transmission: 'automatic',
    doors: 4,
    style: 'sedan',
    hp: 200,
    createdAt: 2022-03-01T03:58:18.941Z,
    updatedAt: 2022-03-01T03:58:18.941Z
  }
]
[
  {
    id: 2,
    name: 'BMX',
    price: 10000,
    style: 'road',
    cicles: 100,
    cc: 100,
    createdAt: 2022-03-01T03:58:18.962Z,
    updatedAt: 2022-03-01T03:58:18.962Z
  }
]