drizzle-team / drizzle-team/drizzle-orm

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

Open
#4,520 0 comments 6 reactions 0 assignees View on GitHub
bug bug/fixed-in-beta
Dominant language
TypeScript
Stars
35.8k
Forks
1.6k
Avg merge
2d 7h
Merged PRs (30d)
4

Description

### Report hasn't been filed before.

- [x] 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:

```ts
// ...
export const accountBalancesPMMV = pgMaterializedView("accountBalancesPM").as(
(qb) => {
const dateMonthSQL = sql`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:

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

The issue happens when I do a push:

```console
[✓] 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:

```sql
-- ...
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:

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

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.