Kysely•12mo ago

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.

import { sql } from 'kysely';

async function materializedQuery() {
    // this func never gets called, just using it to create a type
    return await db.selectFrom('countries').selectAll().execute();
}

async function materializedView() {
    return await sql`select * from my_materialized_view`.execute(db) as unknown as ReturnType<typeof materializedQuery>;
}

/** 
    function materializedView(): Promise<{
        id: number;
        name: string;
    }[]>
 */

import { sql } from 'kysely';

async function materializedQuery() {
    // this func never gets called, just using it to create a type
    return await db.selectFrom('countries').selectAll().execute();
}

async function materializedView() {
    return await sql`select * from my_materialized_view`.execute(db) as unknown as ReturnType<typeof materializedQuery>;
}

/** 
    function materializedView(): Promise<{
        id: number;
        name: string;
    }[]>
 */

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:

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....

Jump to solution

19 Replies

Igal•12mo ago

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.

decho•12mo ago

hey! thanks, that's a wonderful idea i did not think of, I assume you mean something like this, right?

interface DbWithMaterialized extends DB {
    materializedView: {
        column: string;
    }
}

export const db = new Kysely<DbWithMaterialized>({
    dialect: new PostgresDialect({
        pool: new Pool({
            // credentials
        }),
    }),
    plugins: [new CamelCasePlugin()],
    log: ['query'],
});

interface DbWithMaterialized extends DB {
    materializedView: {
        column: string;
    }
}

export const db = new Kysely<DbWithMaterialized>({
    dialect: new PostgresDialect({
        pool: new Pool({
            // credentials
        }),
    }),
    plugins: [new CamelCasePlugin()],
    log: ['query'],
});

that seems to work

Igal•12mo ago

yeah

decho•12mo ago

👍 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

Igal•12mo ago

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.us

and 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 …

decho•12mo ago

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 incorrect

Igal•12mo ago

I 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

decho•12mo ago

oh yeah, i saw this thread a few hours ago when researching, but it got no replies

decho•12mo ago

https://github.com/kysely-org/kysely/issues/270

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

decho•12mo ago

interface DBWithMaterializedViews extends Omit<DB, 'leaderboardView'> {
    leaderboardMaterializedView: { [K in keyof DB['leaderboardView']]: NonNullable<DB['leaderboardView'][K]> }
}

export const db = new Kysely<DBWithMaterializedViews>({
    dialect: new PostgresDialect({
        pool: new Pool({
          // credentials
        }),
    }),
    plugins: [new CamelCasePlugin()],
    log: ['query'],
});

interface DBWithMaterializedViews extends Omit<DB, 'leaderboardView'> {
    leaderboardMaterializedView: { [K in keyof DB['leaderboardView']]: NonNullable<DB['leaderboardView'][K]> }
}

export const db = new Kysely<DBWithMaterializedViews>({
    dialect: new PostgresDialect({
        pool: new Pool({
          // credentials
        }),
    }),
    plugins: [new CamelCasePlugin()],
    log: ['query'],
});

decho•12mo ago

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 👍

decho•12mo ago

btw, not sure why but all generated properties for view types are nullable, maybe that's intended too.

export interface LeaderboardView {
  id: number | null;
  platformId: string | null;
  platform: PlatformName | null;
}

export interface LeaderboardView {
  id: number | null;
  platformId: string | null;
  platform: PlatformName | null;
}

Igal•12mo ago

interesting, not sure could be a bug

decho•12mo ago

all right

Igal•12mo ago

worth raising in codegen repo

decho•12mo ago

https://github.com/RobinBlomberg/kysely-codegen/issues/72#issuecomment-1631444189

GitHub

Not generating types for a Materialized View · Issue #72 · RobinBlo...

Hey! Thanks for the repo it's been saving me so much time! I've got a table and a corresponding materialized view that I've spun off of it to add further indices. It seems like the code...

Igal•12mo ago

was talking about the everything being nullable in views

decho•12mo ago

oh after some quick research, apparently that's a postgres thing

decho•12mo ago

https://stackoverflow.com/a/63980243/3258251

Stack Overflow

Why are my view's columns nullable?

I'm running PostgreSQL 9.2 on Windows. I have an existing table with some non nullable columns : CREATE TABLE testtable ( bkid serial NOT NULL, bklabel character varying(128), lacid integer...

Gaming

Programming

Ways to work with materialized views