romeerez
How to create materialized view?
There's the createView() method I can use in migrations, but there's no option to make it a materialized view. How can I create one inside a migration?
Seems like adding an option would be the simplest:
https://github.com/romeerez/orchid-orm/blob/c9a3f8adf970c964d7d7bd60022b6f60edf0cc06/packages/rake-db/src/migration/createView.ts#L48-L50
We probably also need a way to refresh the view in js code, without the need to use raw sql
I'll need to learn this topic and see what's needed besides the MATERIALIZED keyword.
We probably also need a way to refresh the view in js code
Right, I see there is a REFRESH SQL statement, it should be supported.
Some time has passed, and I changed my view (pun intended) on managing views in orchid.
Instead of writing a migration with createView() I'm using raw SQL in the migrations/recurrent directory and it works beautifully. I use CREATE OR REPLACE since it runs on every migration.
With this approach it's much simpler to keep the view in source control, and updating the view declaration doesn't require creating new migrations every time.
Such a great feature!
Cool, I finally got here to answer that I'm glad you found a way with recurring migrations and raw SQL.
When something is missing in the ORM, it's generally possible to bypass it with raw SQL, and eventually I'll cover what's missing with a TypeScript DSL.
In this case, it would be ideal to write your views (materialized or not) closer to the app logic, and to be able to rely on view types, and the migration generator should learn how to overwrite views in the recurring directory.