8000 How to create materialized view? · Issue #360 · romeerez/orchid-orm · GitHub
[go: up one dir, main page]
More Web Proxy on the site http://driver.im/
Skip to content

How to create materialized view? #360

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Open
mordechaim opened this issue Aug 12, 2024 · 5 comments
Open

How to create materialized view? #360

mordechaim opened this issue Aug 12, 2024 · 5 comments

Comments

@mordechaim
Copy link

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?
@mordechaim
Copy link
Author

Seems like adding an option would be the simplest:

orchid-orm/packages/rake-db/src/migration/createView.ts

Lines 48 to 50 in c9a3f8a

if (options?.createOrReplace) sql.push('OR REPLACE');
if (options?.temporary) sql.push('TEMPORARY');
if (options?.recursive) sql.push('RECURSIVE');

@mordechaim
Copy link
Author

We probably also need a way to refresh the view in js code, without the need to use raw sql

@romeerez
Copy link
Owner

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.

@mordechaim
Copy link
Author
mordechaim commented Sep 1, 2024 •
edited
Loading

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!

@romeerez
Copy link
Owner
romeerez commented Oct 6, 2024

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@mordechaim @romeerez
0