Sequelize with Views & Materialized Views

This package adds support for Views and Materialized Views in Sequelize.

NOTE: Currently it's only supported in PostgreSQL.

Motivation

We're heavy users of views and materialized views, but we use Sequelize a lot for bootstrapping and testing database schema. This module was based on Abel Osorio's sequelize-views-support. In addition to the view support in Abel's module, this module adds support for both views and materialized views to Sequelize, as well as properly exporting typescript declarations. I also added integration tests for views and materialized views.

Read

• https://stackoverflow.com/questions/48407329/cant-able-to-create-views-in-mysql-using-sequelize-orm
• https://github.com/sequelize/sequelize/issues/7197
• https://github.com/sequelize/sequelize/issues/3078

Install

npm install --save sequelize-mv-support

How to use

All the original Sequelize methods and options still work with this module, you can read about those in the sequelize api docs. New and updated methods are documented in the api docs section of this readme.

First, when creating the sequelize instance, you have to do it using this package and not the Sequelize's:

sequelize.js:

const Sequelize = require('sequelize-mv-support');
// or
const { Sequelize, DataTypes } = require('sequelize-mv-support');
// or
import Sequelize from 'sequelize-mv-support';
// or
import { Sequelize } from 'sequelize-mv-support';

const sequelize = new Sequelize(
  // Use the same construction call you've been using so far
);

Then, when creating your view models you have to set two more options (let's call this view Foo):

models/foo.js:

import Sequelize from 'sequelize-mv-support';

// create an instance of sequelize
const sequelize = new Sequelize(
  // Use the same construction call you've been using so far
);

const Foo = sequelize.define('foo', {
    field1: Sequelize.DATE,
    field2: Sequelize.STRING,
    // etc...
  },
  {
    treatAsView: true,
    viewDefinition: 'SELECT field1, field2 from items'
  }
);

(async () = {
  // sync your models
  await sequelize.sync();

  // query the model as usual
  const fooItems = await Foo.findAll();
})();

Then, when creating materialized view models you have to set two more options (let's call this materialized view Bar):

models/bar.js:

import Sequelize from 'sequelize-mv-support';

// create an instance of sequelize
const sequelize = new Sequelize(
  // Use the same construction call you've been using so far
);

// define your materialized view model
const Bar = sequelize.define('bar', {
    field1: Sequelize.DATE,
    field2: Sequelize.STRING,
    // etc...
  },
  {
    treatAsMaterializedView: true,
    materializedViewDefinition: 'SELECT field1, field2 from items'
  }
);

(async () => {
  // sync your models
  await sequelize.sync();

  // refresh your materialized views as needed
  await Bar.refreshMaterializedView();

  // query the model as usual
  const barItems = await Bar.findAll();
})();

That's it. Take into account that views will be created after syncing all your models. This is because your views may depend on models.

API

Table of Contents

• ModelOptionsWithViews
• Model
  • options
  • queryInterface
  • drop
    • Parameters
  • sync
    • Parameters
  • syncView
  • syncMaterializedView
  • getViewDefinition
  • getMaterializedViewDefinition
  • refreshMaterializedView
• QueryInterfaceWithViews
• Sequelize
  • getQueryInterface
  • define
    • Parameters
    • Examples
  • sync
    • Parameters
  • syncViews
  • getViews
  • syncMaterializedViews
  • getMaterializedViews

ModelOptionsWithViews

src/ModelWithViews.ts:17-23

Extends ModelOptions

Interface describing the options property on a model

Model

src/ModelWithViews.ts:42-135

Extends ModelOrig

Model with view support added

options

src/ModelWithViews.ts:49-49

Type: OptionsType

queryInterface

src/ModelWithViews.ts:52-52

Type: QueryInterfaceWithViews

drop

src/ModelWithViews.ts:55-63

Parameters

• options DropOptionsType (optional, default {})

Returns any

sync

src/ModelWithViews.ts:66-71

Parameters

• options SyncOptions

Returns any

syncView

src/ModelWithViews.ts:80-84

Executes the query to create a view

Returns Promise<[Array<unknown>, unknown]> Result of the create view request

syncMaterializedView

src/ModelWithViews.ts:93-101

Executes the query to create a materialized view

Returns Promise<[Array<unknown>, unknown]> Result of the create materialized view request

getViewDefinition

src/ModelWithViews.ts:110-112

Gets the sql definition for this view

Returns string SQL query string to create a view

getMaterializedViewDefinition

src/ModelWithViews.ts:121-123

Gets the sql definition for this materialized view

Returns string SQL query string to create the materialized view

refreshMaterializedView

src/ModelWithViews.ts:132-134

Refreshes the materialized view in the database

Returns Promise<[Array<unknown>, unknown]>

QueryInterfaceWithViews

src/SequelizeWithViews.ts:81-99

Extends SequelizeOrig.QueryInterface

Extended query interface including support for creating and dropping views

Sequelize

src/SequelizeWithViews.ts:107-386

Extends SequelizeOrig.Sequelize

Sequelize class with view support

getQueryInterface

src/SequelizeWithViews.ts:150-205

Returns QueryInterfaceWithViews

define

src/SequelizeWithViews.ts:292-308

Define a new model, representing a table in the database.

The table columns are defined by the object that is given as the second argument. Each key of the object represents a column

Parameters

• modelName string The name of the model. The model will be stored in sequelize.models under this name

• attributes Object An object, where each attribute is a column of the table. Each column can be either a DataType, a string or a type-description object, with the properties described below:

  • attributes.column (string | DataTypes | Object) The description of a database column

    • attributes.column.type (string | DataTypes) A string or a data type

    • attributes.column.allowNull boolean If false, the column will have a NOT NULL constraint, and a not null validation will be run before an instance is saved. (optional, default true)

    • attributes.column.defaultValue any A literal default value, a JavaScript function, or an SQL function (see sequelize.fn) (optional, default null)

    • attributes.column.unique (string | boolean) If true, the column will get a unique constraint. If a string is provided, the column will be part of a composite unique index. If multiple columns have the same string, they will be part of the same unique index (optional, default false)

    • attributes.column.primaryKey boolean If true, this attribute will be marked as primary key (optional, default false)

    • attributes.column.field string If set, sequelize will map the attribute name to a different name in the database (optional, default null)

    • attributes.column.autoIncrement boolean If true, this column will be set to auto increment (optional, default false)

    • attributes.column.autoIncrementIdentity boolean If true, combined with autoIncrement=true, will use Postgres GENERATED BY DEFAULT AS IDENTITY instead of SERIAL. Postgres 10+ only. (optional, default false)

    • attributes.column.comment string Comment for this column (optional, default null)

    • attributes.column.references (string | Model) An object with reference configurations (optional, default null)

      • attributes.column.references.model (string | Model)? If this column references another table, provide it here as a Model, or a string
      • attributes.column.references.key string The column of the foreign table that this column references (optional, default 'id')

    • attributes.column.onUpdate string? What should happen when the referenced key is updated. One of CASCADE, RESTRICT, SET DEFAULT, SET NULL or NO ACTION

    • attributes.column.onDelete string? What should happen when the referenced key is deleted. One of CASCADE, RESTRICT, SET DEFAULT, SET NULL or NO ACTION

    • attributes.column.get Function? Provide a custom getter for this column. Use this.getDataValue(String) to manipulate the underlying values.

    • attributes.column.set Function? Provide a custom setter for this column. Use this.setDataValue(String, Value) to manipulate the underlying values.

    • attributes.column.validate Object? An object of validations to execute for this column every time the model is saved. Can be either the name of a validation provided by validator.js, a validation function provided by extending validator.js (see the DAOValidator property for more details), or a custom validation function. Custom validation functions are called with the value of the field and the instance itself as the this binding, and can possibly take a second callback argument, to signal that they are asynchronous. If the validator is sync, it should throw in the case of a failed validation; if it is async, the callback should be called with the error text.

• options Object These options are merged with the default define options provided to the Sequelize constructor

  • options.sequelize Object Define the sequelize instance to attach to the new Model. Throw error if none is provided.

  • options.modelName string? Set name of the model. By default its same as Class name.

  • options.defaultScope Object Define the default search scope to use for this model. Scopes have the same form as the options passed to find / findAll (optional, default {})

  • options.scopes Object? More scopes, defined in the same way as defaultScope above. See Model.scope for more information about how scopes are defined, and what you can do with them

  • options.omitNull boolean? Don't persist null values. This means that all columns with null values will not be saved

  • options.timestamps boolean Adds createdAt and updatedAt timestamps to the model. (optional, default true)

  • options.paranoid boolean Calling destroy will not delete the model, but instead set a deletedAt timestamp if this is true. Needs timestamps=true to work (optional, default false)

  • options.underscored boolean Add underscored field to all attributes, this covers user defined attributes, timestamps and foreign keys. Will not affect attributes with explicitly set field option (optional, default false)

  • options.freezeTableName boolean If freezeTableName is true, sequelize will not try to alter the model name to get the table name. Otherwise, the model name will be pluralized (optional, default false)

  • options.name Object? An object with two attributes, singular and plural, which are used when this model is associated to others.

    • options.name.singular string Singular name for model (optional, default Utils.singularize(modelName))
    • options.name.plural string Plural name for model (optional, default Utils.pluralize(modelName))

  • options.indexes Array<Object>? indexes definitions

  • options.createdAt (string | boolean)? Override the name of the createdAt attribute if a string is provided, or disable it if false. Timestamps must be true. Underscored field will be set with underscored setting.

  • options.updatedAt (string | boolean)? Override the name of the updatedAt attribute if a string is provided, or disable it if false. Timestamps must be true. Underscored field will be set with underscored setting.

  • options.deletedAt (string | boolean)? Override the name of the deletedAt attribute if a string is provided, or disable it if false. Timestamps must be true. Underscored field will be set with underscored setting.

  • options.tableName string? Defaults to pluralized model name, unless freezeTableName is true, in which case it uses model name verbatim

  • options.schema string schema (optional, default 'public')

  • options.engine string? Specify engine for model's table

  • options.charset string? Specify charset for model's table

  • options.comment string? Specify comment for model's table

  • options.collate string? Specify collation for model's table

  • options.initialAutoIncrement string? Set the initial AUTO_INCREMENT value for the table in MySQL.

  • options.hooks Object? An object of hook function that are called before and after certain lifecycle events. The possible hooks are: beforeValidate, afterValidate, validationFailed, beforeBulkCreate, beforeBulkDestroy, beforeBulkUpdate, beforeCreate, beforeDestroy, beforeUpdate, afterCreate, beforeSave, afterDestroy, afterUpdate, afterBulkCreate, afterSave, afterBulkDestroy and afterBulkUpdate. See Hooks for more information about hook functions and their signatures. Each property can either be a function, or an array of functions.

  • options.validate Object? An object of model wide validations. Validations have access to all model values via this. If the validator function takes an argument, it is assumed to be async, and is called with a callback that accepts an optional error.

  • options.treatAsMaterializedView boolean Whether to treat this model as a materialised view (optional, default false)

  • options.treatAsView boolean Whether to treat this model as a view (optional, default false)

  • options.viewDefinition string? The query to be represented by a view

  • options.materializedViewDefinition string? The query to be represented by a materialized view

• string string [].name] The name of the index. Defaults to model name + _ + fields concatenated

• string string [].type] Index type. Only used by mysql. One of UNIQUE, FULLTEXT and SPATIAL

• string string [].using] The method to create the index by (USING statement in SQL). BTREE and HASH are supported by mysql and postgres, and postgres additionally supports GIST and GIN.

• string string [].operator] Specify index operator.

• boolean boolean [].unique=false] Should the index by unique? Can also be triggered by setting type to UNIQUE

• boolean boolean [].concurrently=false] PostgresSQL will build the index without taking any write locks. Postgres only

Examples

sequelize.define(
   viewName,
   {
     id: {
       type: Sequelize.INTEGER,
       primaryKey: true,
     },
     name: Sequelize.STRING,
   },
   {
     freezeTableName: true,
     treatAsView: true,
     viewDefinition: 'SELECT id, name from items',
   }
 );

sequelize.models.modelName // The model will now be available in models under the name given to define

Returns Model Newly defined model

sync

src/SequelizeWithViews.ts:325-331

Sync all defined models to the DB.

Parameters

• options Object sync options (optional, default {})

  • options.force boolean If force is true, each Model will run DROP TABLE IF EXISTS, before it tries to create its own table (optional, default false)

  • options.match RegExp? Match a regex against the database name before syncing, a safety check for cases where force: true is used in tests but not live code

  • options.logging (boolean | Function) A function that logs sql queries, or false for no logging (optional, default console.log)

  • options.schema string The schema that the tables should be created in. This can be overridden for each table in sequelize.define (optional, default 'public')

  • options.searchPath string An optional parameter to specify the schema search_path (Postgres only) (optional, default DEFAULT)

  • options.hooks boolean If hooks is true then beforeSync, afterSync, beforeBulkSync, afterBulkSync hooks will be called (optional, default true)

  • options.alter (boolean | Object) Alters tables to fit models. Provide an object for additional configuration. Not recommended for production use. If not further configured deletes data in columns that were removed or had their type changed in the model. (optional, default false)

    • options.alter.drop boolean Prevents any drop statements while altering a table when set to false (optional, default true)

Returns Promise

syncViews

src/SequelizeWithViews.ts:339-343

Executes the create view query for each of the view definitions

Returns Promise<Array<any>> The results of the create view queries

getViews

src/SequelizeWithViews.ts:351-357

Gets all the defined models which represent views

Returns Array<any>

syncMaterializedViews

src/SequelizeWithViews.ts:365-371

Executes the create materialized view query for each of the definitions

Returns Promise<Array<any>> The results of the create view queries

getMaterializedViews

src/SequelizeWithViews.ts:379-385

Gets all the defined models which represent materialized views

Returns Array<any>

Maintainers

• Carrie Beesley
• Bill Beesley