can i use VIEW with sequelize ?

To whomever is struggling to read @KeyOnTech’s tutorials:

1. Create a model file with the fields you need from your view, in this model there has to be a primary index.

// sequelize/model/view_model_vwloginslist.js;

const Sequelize = require("sequelize");
const sequelize = require("../database/connection");
module.exports = sequelize.define(
  "vwloginslist",
  {
    loginid: {
      field: "id",
      type: Sequelize.INTEGER,
      allowNull: false,
      autoIncrement: true,
      primaryKey: true,
    },
    name: {
      type: Sequelize.STRING(255),
    },
    login: {
      type: Sequelize.STRING(255),
    },
    roledescription: {
      type: Sequelize.STRING(255),
    },
  },
  {
    timestamps: false,
  }
);

1. Next make a migration file. The special thing about this file is you will delete the table model auto created by sequelize then create or replace the view with the same name. Once this is done you will be able to do a findAll against the model because it will call the view.

// 20200102010201-vwloginslists_create_view_delete_model_table.js

"use strict";
const sequelize = require("../database/connection");
/***
if you are using views make sure you add the s to the end of the name below in proper grammer format and this way you will
auto delete the model on sync

migrations file no mvw just vw name, add the s to the end for proper grammar
vwLoginsLists
ex:
20200102010201-vwloginslists_create_view_delete_model_table.js
const view_name = 'vwloginslists';

models file no s, s will automatically be added
vwLoginsList
s will automatically be added
ex:
vwloginslist.js
module.exports = sequelize.define("vwloginslist",
vwloginslist.js

now on your routes you can use the vw model file to do a findAll
ex:
loginsroles-routes.js
const vwLoginsList = require('../sequelize/models/vwloginslist');
await vwLoginsList.count()
return vwLoginsList.findAll(
whereCondition
)

/
/** this is only used for views if this is not related to a view delete these sections --- start /
const auto_created_model_table_name = 'vwloginslists';
/** this is only used for views if this is not related to a view delete these sections --- end */

const view_name = "vwloginslists";
const original_query = [
  "SELECT ",
  " logins.id, logins.name, logins.login, logins.loginenabled, logins.role, ",
  " CASE ",
  " WHEN logins.loginenabled = 0 THEN 'DISABLED' ",
  " WHEN logins.loginenabled = 1 THEN 'ENABLED' ",
  " END AS loginenableddescription, ",
  " loginroles.description AS roledescription ",
  " FROM logins ",
  " LEFT JOIN loginroles ",
  " ON loginroles.id = logins.role ",
].join("");

const new_query = [
  "SELECT ",
  " logins.id, logins.name, logins.login, logins.loginenabled, logins.role, ",
  " CASE ",
  " WHEN logins.loginenabled = 0 THEN 'DISABLED' ",
  " WHEN logins.loginenabled = 1 THEN 'ENABLED' ",
  " END AS loginenableddescription, ",
  " loginroles.description AS roledescription ",
  " FROM logins ",
  " LEFT JOIN loginroles ",
  " ON loginroles.id = logins.role ",
].join("");

module.exports = {
  up: function(database, Sequelize) {
    return Promise.all([]);
  },
  down: function(database, Sequelize) {
    return Promise.all([
      sequelize.query(`DROP TABLE IF EXISTS ${auto_created_model_table_name}`).then(result => {
        sequelize.query(`CREATE OR REPLACE VIEW ${view_name} AS ${new_query}`).then(result => {
          return result;
        });
      }),
    ]);
  },
};

1. Go to your route file and here’s is how you can now findAll

const vwLoginsList = require("../sequelize/models/view_model_vwloginslist");
await vwLoginsList.count(whereConditionCount);

// or;
return vwLoginsList.findAll(whereCondition);

• next npm run start to auto create all the models and tables
• stop the node server
• next run sequelize migration to clean up the auto created tables and create your view for you npx sequelize db:migrate

now start your server again npm run start and your findAll should work great

hope this helps the next person or myself in the future

Note to future self… your awesome and its all gonna be worth it one day 😃

There is no way to create views on sequelize, but you can create the view manually and query it like a regular sequelize model.

So you use a raw query to create the view, and query the table using a regular sequelize model:

sequelize.define('schema_content', {
  ...
});

Just remember not to .sync the schema_content model