drizzle-team / drizzle-team/drizzle-orm

[BUG]: drizzle-kit generate doesn't drop/recreate dependent views when altering column types

Open
#6,194 2 comments 0 reactions 0 assignees View on GitHub
bug
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?

1.0.0-rc.4

### What version of `drizzle-kit` are you using?

1.0.0-rc.4

### Other packages

_No response_

### Describe the Bug

Hi!

I've noticed that when altering columns on tables that are used by views, `drizzle-kit` does not automatically wrap the column alterations with view drops and recreations. This leads to migration execution failures because the database blocks altering column types for any table referenced by a view (`PostgresError: cannot alter type of a column used by a view or rule`).

### Minimal Reproducible Example

**Initial Schema:**

```typescript
import { sql } from "drizzle-orm";
import { integer, pgTable, pgView, timestamp } from "drizzle-orm/pg-core";

export const accessLogsTable = pgTable("access_logs", {
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
accessedAt: timestamp("accessed_at", { mode: "string" }).notNull().defaultNow(),
});

export const evenAccessLogsView = pgView("even_access_logs").as((qb) =>
qb.select().from(accessLogsTable).where(sql`${accessLogsTable.id} % 2 = 0`)
);

```

**Updated Schema:**

```typescript
import { sql } from "drizzle-orm";
import { integer, pgTable, pgView, timestamp } from "drizzle-orm/pg-core";

export const accessLogsTable = pgTable("access_logs", {
id: integer("id").primaryKey().generatedAlwaysAsIdentity(),
accessedAt: timestamp("accessed_at", { mode: "string", withTimezone: true }).notNull().defaultNow(),
});

export const evenAccessLogsView = pgView("even_access_logs").as((qb) =>
qb.select().from(accessLogsTable).where(sql`${accessLogsTable.id} % 2 = 0`)
);

```

### Generated Migration

`drizzle-kit generate` produces the following migration:

```sql
ALTER TABLE "access_logs" ALTER COLUMN "accessed_at" SET DATA TYPE timestamp with time zone USING "accessed_at"::timestamp with time zone;

```

### Expected Behavior

Because `even_access_logs` depends on `accessLogsTable`, the migration should drop the view before altering the column and recreate it afterward:

```sql
DROP VIEW IF EXISTS "even_access_logs";

ALTER TABLE "access_logs" ALTER COLUMN "accessed_at" SET DATA TYPE timestamp with time zone USING "accessed_at"::timestamp with time zone;

CREATE VIEW "even_access_logs" AS ...;

```

### Actual Behavior

The migration runs the `ALTER TABLE` statement directly, resulting in the following database error:

```text
PostgresError: cannot alter type of a column used by a view or rule
```

When adding new columns to a table, `drizzle-kit` already handles dropping, updating, and recreating dependent views. Applying this same view-recreation lifecycle to `ALTER COLUMN` actions would prevent failed migrations.

*Note: I reproduced and verified this issue using PostgreSQL, but it is likely an issue with other database vendors (such as MySQL) as well, given how view dependencies are handled during table alterations.*

Thank you!

Contributor guide

Open the contributing guide

Research direction

Start at drizzle-kit generate and compare the existing view handling for added columns with ALTER COLUMN generation. Reproduce the issue using the provided PostgreSQL schema, then verify that the generated migration drops the dependent view before altering the column and recreates it afterward.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, typescript
Domain
databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.