DT
Drizzle Team•11mo ago
arush

drizzle-studio doesn't see pgView materialized views

I have existing materialized views and marked as such in my schema, but drizzle studio doesn't see them. the drizzle studio schema seems to have left them out when being generated here is the relevant part of my schema that drizzle studio leaves out export const claimsView = pgView("claims_view", { customerCode: varchar("customer_code", { length: 8 }).notNull(), totalPetitionValue: numeric("total_petition_value"), totalPetitionValueCurrency: text("total_petition_value").notNull(), totalLatestValue: numeric("total_latest_value"), totalLatestValueCurrency: text("total_latest_value").notNull(), }).existing(); // existing() tells drizzle that this view already exists in the db export const claimAssetsView = pgView("claim_assets_view", { customerCode: varchar("customer_code", { length: 8 }).notNull(), name: text("name").notNull(), type: text("type").notNull(), balance: numeric("balance").notNull(), usdPetition: numeric("usd_petition"), usdPetitionCurrency: text("usd_petition").notNull(), usdLatest: numeric("usd_latest"), usdLatestCurrency: text("usd_latest").notNull(), }).existing(); // existing() tells drizzle that this view already exists in the db
6 Replies
Rami
Rami•11mo ago
arush
arushOP•11mo ago
@Rami that thread isn't helping. pls take a look at my code above
Rami
Rami•11mo ago
Drizzle doesn't yet support views. You will have to manually write an SQL script that would create that view. Then create a custom migration by running: drizzle-kit generate:pg --custom. Insert that SQL script into that generated migration file. Then run your migrations to update your database to have that view. This is my migration.ts file if you haven't made one: 
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { createConnection } from "../utils";

async function runMigrations() {
  const { db, pg } = await createConnection();

  try {
    await migrate(db, { migrationsFolder: "./migrations" });
  } finally {
    await pg.end();
  }
}

runMigrations().catch((error) => {
  console.error(error);
  process.exit(1); // eslint-disable-line unicorn/no-process-exit
});
import { migrate } from "drizzle-orm/node-postgres/migrator";
import { createConnection } from "../utils";

async function runMigrations() {
  const { db, pg } = await createConnection();

  try {
    await migrate(db, { migrationsFolder: "./migrations" });
  } finally {
    await pg.end();
  }
}

runMigrations().catch((error) => {
  console.error(error);
  process.exit(1); // eslint-disable-line unicorn/no-process-exit
});
Check this page for more info: https://orm.drizzle.team/docs/migrations
Drizzle ORM - Migrations
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Rami
Rami•11mo ago
Also for the SQL query that creates the materialized views, this is an example: 
DO
$$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_matviews
        WHERE matviewname = 'claim_assets_view'
    ) THEN
        CREATE MATERIALIZED VIEW claim_assets_view AS
        SELECT 
            date_trunc('day', created_at)::DATE AS date,
            COUNT(DISTINCT id) AS count
        FROM 
            claims
        GROUP BY 
            date_trunc('day', created_at)
        ORDER BY 
            date_trunc('day', created_at);
    END IF;
END $$;
DO
$$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_matviews
        WHERE matviewname = 'claim_assets_view'
    ) THEN
        CREATE MATERIALIZED VIEW claim_assets_view AS
        SELECT 
            date_trunc('day', created_at)::DATE AS date,
            COUNT(DISTINCT id) AS count
        FROM 
            claims
        GROUP BY 
            date_trunc('day', created_at)
        ORDER BY 
            date_trunc('day', created_at);
    END IF;
END $$;
P.S I'm just someone who started working with drizzle like 2 month back and this is my workflow with creating materialized views I recommend building and testing out the SQL query for whatever you want the view to be inside drizzle studio, so for example if I want to test out the previous materialized view, I would run: 
        SELECT 
            date_trunc('day', created_at)::DATE AS date,
            COUNT(DISTINCT id) AS count
        FROM 
            claims
        GROUP BY 
            date_trunc('day', created_at)
        ORDER BY 
            date_trunc('day', created_at);
        SELECT 
            date_trunc('day', created_at)::DATE AS date,
            COUNT(DISTINCT id) AS count
        FROM 
            claims
        GROUP BY 
            date_trunc('day', created_at)
        ORDER BY 
            date_trunc('day', created_at);
In there to see if it gives me what I want
lundqueue
lundqueue•2mo ago
Hi, is still the case? I can see that support for pgView was added in https://github.com/drizzle-team/drizzle-orm/blob/866c257b2d5e7e98415b77f726e72862360d5502/changelogs/drizzle-kit/0.26.0.md 2 months ago. I am trying to add a pgView in my schema but it wont add them on generate. I checked adding test table so I know for a fact that it can identify changes, but not picking up pgView and pgMateralizedView. Thanks for your attention
GitHub
drizzle-orm/changelogs/drizzle-kit/0.26.0.md at 866c257b2d5e7e98415...
Headless TypeScript ORM with a head. Runs on Node, Bun and Deno. Lives on the Edge and yes, it's a JavaScript ORM too 😅 - drizzle-team/drizzle-orm
lundqueue
lundqueue•2mo ago
Got no error messages or so, and checked the validity of the view creation with pure SQL script

Did you find this page helpful?