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

[machadinhos]

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?

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:

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:

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:

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:

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:

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!

[AleksandrSherman]

Hello, @machadinhos

Thanks for opening the issue

Let us take a closer look at this

[machadinhos]

@AleksandrSherman I did some more research regarding other db vendors and here is what I found regarding what happens if you alter the type of a column of a table that a view depends on:

• CockroachDB: gives an error because the column has a dependent view.
• DuckDB: gives an error due to the dependent view.
• MSSQL / SQL Server: allows it for a normal view, but the view metadata can become stale. Allows refresh with sp_refreshview.
• MySQL: allows it; the view can become invalid/stale, problems only appear when you query the view.
• PostgreSQL: gives an error because the view depends on the column.
• SingleStore: allows it for the default SCHEMA_BINDING=OFF setting, but it's not clear if it becomes stale or automatically alters the view. With SCHEMA_BINDING=ON, it gives an error.
• SQLite: doesn't support changing a column type directly; you have to rebuild the table, so the view has to be handled as part of that migration. (same for cloudflare d1)
• Turso: same as SQLite.

Given this, I would say that dropping and recreating the view is the best course of action, the only db vendor that would allow for simpler migrations is mssql, but I don't know if you want to introduce vendor specific logic for this for a single db vendor.
Another important caveat that I'm not sure if drizzle already handles is that views can depend on other views, and when that is the case, those views should also be dropped and recreated in the right order.