The problems with SQL views is that every time I need to make a small change I need to create another migration. Being in a small startup, that's quite a hindrance to have to change something small to change the view.
Is it advisable to do the following
- Drop and recreate view everytime I deploy my app;
- This way, when I change something in the view, it will get updated in the database as soon as I deploy my app.
Best How To :
What you are describing is just another type of migration that get reversed on deploy. This may make sense for your business needs, and if you get blocked by this technique, you can always fall back to the regular migration system.
The best way to implement such a system in PostgreSQL is to create a schema that you drop on deploy. This way you don't have to create all the
DROP VIEW ... commands, just
DROP SCHEMA and everything in there will be deleted. Then you can run you procedure to rebuild it.
Example deploy script to execute on deploy:
/* Drop and rebuild the schema */
DROP SCHEMA IF EXISTS view_schema;
CREATE VIEW view_schema.my_users AS (SELECT * FROM users);
CREATE VIEW view_schema.my_products AS (SELECT * FROM products);