Unable to create views in mysql using sequelize ORM

Question

Currently, I am building a web app with nodejs + mysql and sequelize as ORM. I want to create some views like we do in mysql, but I can't find any option in Sequelize to create views.

Is there any ORM where it's possible to create views? Or is it possible to do it with sequelize?

Answer 1

There are no builtin methods for managing views in Sequelize, but you can create them using plain SQL queries and manage them with normal Sequelize models.

If you're using umzug for your migrations as recommended by the Sequelize docs, you can create your view using a migration similar to this:

const view_name = 'my_view';
const query = '<SQL QUERY THAT RETURNS YOUR VIEW>';
module.exports = {
  up: function (database, Sequelize) {
    return database.query(`CREATE VIEW ${view_name} AS ${query}`);
  },
  down: function (database, Sequelize) {
    return database.query(`DROP VIEW ${view_name}`);
  }
}

For view changes or updates, you should use the CREATE OR REPLACE VIEW syntax to ensure you can roll back your schema changes - avoid the tempation to DROP the old view and CREATE a new one!

const view_name = 'my_view';
const original_query = '<SQL QUERY THAT RETURNS YOUR VIEW>';
const new_query = '<SQL QUERY THAT RETURNS YOUR UPDATED VIEW>';
module.exports = {
  up: function (database, Sequelize) {
    return database.query(`CREATE OR REPLACE VIEW ${view_name} AS ${new_query}`);
  },
  down: function (database, Sequelize) {
    return database.query(`CREATE OR REPLACE VIEW ${view_name} AS ${original_query}`);
  }
}

The exact code snippets will, of course, vary depending on how you've been setting up migrations so far.

Once your migration is all set, create a Sequelize model representing your view schema as usual, using the view name as your table name. All of the find family functions should work as expected, while update, delete, and create should be expected to fail.

You can reference the Sequelize docs for raw queries, and this example repo for using Umzug with Sequelize. The only formal documentation I can find for Umzug itself is on its NPM page.