[BUG]: Creating a Materialized View that depends on another fails because of creation order

[pinarruiz]

Report hasn't been filed before.

• I have verified that the bug I'm about to report hasn't been filed before.

What version of drizzle-orm are you using?

0.43.1

What version of drizzle-kit are you using?

0.31.1

Other packages

No response

Describe the Bug

When creating a materialized view that depends on another, the first pushed MV is the one that depends on the other, resulting in error code 42P01: error: relation "x" does not exist.

On my files i have the following MVs:

// ...
export const accountBalancesPMMV = pgMaterializedView("accountBalancesPM").as(
  (qb) => {
    const dateMonthSQL = sql<Date>`DATE_TRUNC('month', ${transfersTable.when})`;
    return qb
      .select({
        accountId: transfersTable.accountId,
        monthYear: dateMonthSQL.as("monthYear"),
        balance: sum(transfersTable.amount).as("balance"),
      })
      .from(transfersTable)
      .groupBy(transfersTable.accountId, dateMonthSQL);
  },
);

export const accountBalancesMV = pgMaterializedView("accountBalances").as(
  (qb) =>
    qb
      .select({
        accountId: accountBalancesPMMV.accountId,
        balance: sum(accountBalancesPMMV.balance).as("balance"),
      })
      .from(accountBalancesPMMV)
      .groupBy(accountBalancesPMMV.accountId),
);
// ...

And then it gets exported to an index file with the rest of schemas like:

//...
export {
  // ...
  accountBalancesPMMV,
  accountBalancesMV,
  // ....
} from "@/db/schema/expense";
// ...

The issue happens when I do a push:

[✓] Pulling schema from database...

 Warning  You are about to execute current statements:

CREATE MATERIALIZED VIEW "public"."accountBalances" AS (select "accountId", sum("balance") as "balance" from "accountBalancesPM" group by "accountBalancesPM"."accountId");
CREATE MATERIALIZED VIEW "public"."accountBalancesPM" AS (select "accountId", DATE_TRUNC('month', "when") as "monthYear", sum("amount") as "balance" from "transfers" group by "transfers"."accountId", DATE_TRUNC('month', "transfers"."when"));

As it can be seen it tries to first create the dependant MV, using generate will give the same result:

-- ...
CREATE MATERIALIZED VIEW "public"."accountBalances" AS (select "accountId", sum("balance") as "balance" from "accountBalancesPM" group by "accountBalancesPM"."accountId");--> statement-breakpoint
CREATE MATERIALIZED VIEW "public"."accountBalancesPM" AS (select "accountId", DATE_TRUNC('month', "when") as "monthYear", sum("amount") as "balance" from "transfers" group by "transfers"."accountId", DATE_TRUNC('month', "transfers"."when"));

Giving error code 42P01 once again and the following log:

pnpm drizzle-kit migrate

No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/REDACTED]/repos/[REDACTED]/drizzle.config.ts'
Using 'pg' driver for database querying
[⣷] applying migrations...error: relation "accountBalancesPM" does not exist

I don't know if there is a way of handling this, but at least i have found no way of doing it. Changing the names on the MVs seem to not help and moving them in the exports either.

Thanks for the help in advance, if any more information is needed i will be happy to provide it.