Drizzle Team•10mo ago

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

4 Replies

Rami•9mo ago

Refer to this thread: https://discord.com/channels/1043890932593987624/1184451142718062592

arushOP•9mo ago

@Rami that thread isn't helping. pls take a look at my code above

Rami•9mo 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•9mo 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

Gaming

Programming

drizzle-studio doesn't see pgView materialized views