abelosorio / sequelize-views-support

Adds VIEWS support to Sequelize

Add Views support to Sequelize

This package adds support to Views in Sequelize.

NOTE: Currently it's only supported in PostgreSQL.

Motivation

I've been using PostgreSQL and other RDBMS for a long time, and I can't stress enough about how useful is the View mechanism. For that reason (and others that you'd get bored reading them) an ORM without Views support is incomplete.

This package needs a lot of further work, tests, discussion... but for the moment it works as expected and fulfills its purpose. Feel free of discuss changes, ideas, or whatever you think. Thanks.

Read

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

Install

npm install --save sequelize-views-support

How to use

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-views-support');

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

module.exports = sequelize;

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

models/foo.js:

module.exports = (sequelize, DataTypes) => {
  const Foo = sequelize.define('foo', {
    field1: DataTypes.DATE,
    field2: DataTypes.STRING,
    // etc...
  }, {
    treatAsView: true,
    viewDefinition: `
      CREATE VIEW "foo" AS
        -- Put here your view's definition
        -- DO NOT USE "CREATE OR REPLACE", JUST "CREATE"
        -- You can create complex Views using any of the PostgreSQL DSL's supported features
    `
  });

  return Foo;
};

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

Migrations

The next step is to maintain your views. You're going to need to write migrations to change your views' definition, as well as changing it in the viewDefinition option.

You can ignore the viewDefinition option, but you won't be able to sync that view.

Mantainers

• Abel M. Osorio

sequelize-views-support's Issues

Any plans on upgrading to latest sequelize with typescript support?

upgrade to pg v8 and sequelize v6

"dependencies": {
"pg": "^8.5.1",
"sequelize": "^6.5.0"
},

If I upgrade to the latest version of pg and sequelize, queryInterface becomes camelCase.

 static drop(options) {
    const method = this.options.treatAsView ? 'dropView' : 'dropTable';

    return this.queryInterface[method](
      this.getTableName(options), options
    );
  }

static syncView(options) {
    return this.queryInterface.createView(
      this.getTableName(options),
      this.getViewDefinition(),
      options
    );
  }

View couldn't be created

I couldn't create a view. Below is my code.

Model.js

module.exports = (sequelize, DataTypes) => {
    const VwBackscattering = sequelize.define('vw_backscattering', {
            capture_datetime:  DataTypes.DATE,
            bb_470nm:DataTypes.DOUBLE,
            bb_532nm:DataTypes.DOUBLE,
            bb_650nm: DataTypes.DOUBLE,
            boat_datetime:  DataTypes.DATE,
            rainfall: DataTypes.DOUBLE,
            temperature: DataTypes.DOUBLE,
            latitude: DataTypes.DOUBLE,
            longitude: DataTypes.DOUBLE
        },
        {
            treatAsView: true,
            viewDefinition: `
              CREATE VIEW "vw_backscattering" AS
            'SELECT t1.*, t2.*,
            'ST_SetSRID(ST_MakePoint(t2.longitude, t2.latitude), 4326) as point_geom
            'FROM backscatterings t1 INNER JOIN 
            '    ancillary_data t2 ON t1.capture_datetime = t2.boat_datetime;`
        });
    return VwBackscattering;
};

index.js

const Sequelize = require('sequelize-views-support');
const view = require('./models/VwBackscattering');
const VwBackscattering = view(db, Sequelize.DataTypes)
VwBackscattering.sync().then(() => {
    console.log('VwBackscattering table created');
});
module.exports = VwBackscattering;

DROP VIEW failing

Inside query-interface-views-support.js, on line 4, you have

const sql = DROP VIEW IF EXISTS "${viewName}"${options.cascade && ' CASCADE'};

That creates "DROP VIEW IF EXISTS someViewundefined"

It should be
const sql = DROP VIEW IF EXISTS "${viewName}" ${options.cascade ? 'CASCADE': ''};

SyntaxError: Unexpected token import

import Sequelize from 'sequelize-views-support';
^^^^^^

SyntaxError: Unexpected token import

$ node -v
v8.2.0

"Table 'myView' already exists" on soft sync

The situation is that i have a already defined schema so I cannot do force sync. On normal table the soft sync does work. You even can add a view on a already existent schema. But when trying to do a soft sync when you have already, the error is thrown: Table 'myView' already exists.

SequelizeDatabaseError: relation "my_view" does not exist

View creation works as expected but when I try to issue a model.findAll on the view I get a UnhandledPromiseRejectionWarning: SequelizeDatabaseError: relation "my_view" does not exist.

Am I using the package in a way that was not intended?

Thanks!

Use latest version of Sequelize

When I installed this package, I got this dependency tree:

info All dependencies
├─ @types/[email protected]
├─ [email protected]
├─ [email protected]
├─ [email protected]
├─ [email protected]
├─ [email protected]
├─ [email protected]
└─ [email protected]

However in the package.json of this package sequelize@^6.0.0 is in the dependencies. And Sequelize 6 is not even out yet. Can someone explain this discrepancy?

I am currently using Sequelize 5 in my project. Is this package only compatible with Sequelize 4?

[email protected] support

Is there any reason why you are using the pg@7 package. That package has known issues with Sequelize. Would you be willing to set your package.json file to ^6.1.2

Cannot sync individual model

In some specific cases you may need sync the model individually MyModel.sync. For some reasons you disabled this capability for views.
https://github.com/abelosorio/sequelize-views-support/blob/master/src/model-views-support.js#L15s
Is there a reason for that?

My specific situation is related to my other issue #4.
After seeing that I cannot do soft sync, I thought I can sync the view individually and catch that exception and ignore it.