Ways to work with materialized views
Hey guys, I just wanted to ask before I embark on a journey on solving this problem on my own, if someone has found a good way to work with and implement materialized views with Kysely? I am currently using
kysely-codegen
but unfortunately it does not generate types for materialized views. kysely.introspect
also does not return any data for materialized views either.
My ultimate goal is to put a complex query in a materialized view (my leaderboard https://discord.com/channels/890118421587578920/1125059913551458406) and be able to do 3 things:
1. query the materialized view itself with kysely
2. use kysely to query the view itself SELECT * FROM my_materialized_view WHERE column = something
3. have types for it
all 3 issues are solved if you have a tool that generates types for the materialized view (I think).
---
So far I am thinking of two approaches. First one to be to just write my own types generator by querying pg_catalog
and stuff, similar to kysely-codgen. Gonna take a while.
Second solution would be to write a simple query in kysely which produces the result of the materialized view, wrap that in a function (which never gets called), and then write another function that returns raw query with type assertions.
This is obviously very hacky, and won't even solve problem #2. So I was just wondering if anyone faced this problem before and has any tips. Cheers!Discord
Discord - A New Way to Chat with Friends & Communities
Discord is the easiest way to communicate over voice, video, and text. Chat, hang out, and stay close with your friends and communities.
Solution:Jump to solution
ok so here is what i ended up doing.
1. create a normal view in my database so kysely-codegen can generate types.
2. create exactly the same materialized view.
3. extend the generated
DB
and pass that to kysely, also exclude the normal view from it since I don't need it....19 Replies
Hey 👋🏻
We could probably just add this to the introspector, and then do the same in kysely-codegen
regardless, you can extend the types generated by
kysely-codegen
before passing them to Kysely instance, and provide the materialized views as regular tables.hey!
thanks, that's a wonderful idea i did not think of, I assume you mean something like this, right?
that seems to work
yeah
👍
i also figured that instead of creating a function solely for the purpose of inferring it's type, i can just create a view in my database, then the codegen will create a type for it
Btw, this is the why we don't get materialized views in getTables atm:
It's on purpose. The information_schema can only show objects that exist in the SQL standard. https://www.postgresql.org/message-id/3794.1412980686%40sss.pgh.pa.usand this is what we may have to query, if we decide to support it https://www.postgresql.org/docs/current/view-pg-matviews.html
PostgreSQL Mailing List Archives
Re: Materialized views don't show up in information_schema
Sehrope Sarkuni writes: > I've been testing out some of the new materialized view functionality > in 9.4 and …
PostgreSQL Documentation
54.13. pg_matviews
54.13. pg_matviews The view pg_matviews provides access to useful information about each materialized view in the database. Table 54.13. pg_matviews Columns Column Type …
i assume that for some specific reason you're using
information_schema
only to introspect?
something to do with the sql standard
nvm that's incorrectI think its just an afterthought, same as with views, we initially didn't retrieve them. https://github.com/kysely-org/kysely/pull/273
GitHub
Introspect views by diogob · Pull Request #273 · kysely-org/kysely
Closes #270
Ensure all instrospector return database views and add a boolean isView to the TableMetadata so users can tell them apart.
Todo
document
oh yeah, i saw this thread a few hours ago when researching, but it got no replies
GitHub
Should the instrospectors' getTables method return database views? ...
As I was investigating how to implement the type generation for database views I found out that the MysqlIntrospector already returns views on a getTables call. If all introspectors would behave in...
Solution
ok so here is what i ended up doing.
1. create a normal view in my database so kysely-codegen can generate types.
2. create exactly the same materialized view.
3. extend the generated
DB
and pass that to kysely, also exclude the normal view from it since I don't need it.
now to see if it works
it works
thanks again Igal, hopefully sometime in the future codegen can generate types for materialized views, but for now this is an okay solution 👍
btw, not sure why but all generated properties for view types are nullable, maybe that's intended too.
interesting, not sure
could be a bug
all right
worth raising in codegen repo