drizzle-team / drizzle-team/drizzle-orm
[BUG]: Creating a Materialized View that depends on another fails because of creation order
- 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
Assessment
This issue has not been assessed yet.