Drizzle Team•12mo ago

How to use a view after creating it?

Disclaimer: I know that the views functionality is WIP I have set up a view as described in https://orm.drizzle.team/docs/views/. Now I want to use it to perform a query, but I don't know how. Can I get a pointer to how this is done?

Drizzle ORM - next gen TypeScript ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

6 Replies

Rafael Rossi•12mo ago

like a regular table, just using view schema.

Rami•10mo ago

@Rafael Rossi await db.select().from(View) doesn't work for me. It is saying that relation "pg_view" does not exist. What is going on? I ran drizzle-kit push:pg and generate, am I missing something?

nk•10mo ago

@Rami works for me

export const productReviewsView = pgMaterializedView("product_ratings", {
  id: serial("id").primaryKey(),

  averageRating: numeric("average_rating"),
  totalReviews: bigint("total_reviews", { mode: "number" }),

  productID: integer("product_id")
    .notNull()
    .references(() => productTable.id),
}).as(sql`select * from ${productTable}}`);

export const productReviewsView = pgMaterializedView("product_ratings", {
  id: serial("id").primaryKey(),

  averageRating: numeric("average_rating"),
  totalReviews: bigint("total_reviews", { mode: "number" }),

  productID: integer("product_id")
    .notNull()
    .references(() => productTable.id),
}).as(sql`select * from ${productTable}}`);

Rami•10mo ago

@nk Did you not have to manually create the materialized view outside of drizzle? It didn't work for me until I manually created the view with SQL. From what I understand drizzle-kit doesn't yet generate the views for us, so how else did you create the view if not manually?

nk•10mo ago

@Rami I just created it manually and defined it there You could run a sql`` command to create it if you want

Rami•10mo ago

Yeah makes sense, appreciate the help😊

Gaming

Programming

How to use a view after creating it?