[BUG]: SQL Generation - Views with Dependencies Created in Alphabetical Order Instead of Schema Definition Order

[kei-ichi]

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.39.1

What version of drizzle-kit are you using?

0.30.4

Other packages

No response

Describe the Bug

Issue Description

When generating SQL migrations, Drizzle Kit creates tables and views in alphabetical order rather than following the schema definition order. This causes issues particularly with views that depend on other views.

Example Case

In our schema, we have two views where one depends on another:

// Schema definition order
export const userTokenUsageView = mysqlView('user_token_usage').as((qb) => {
  // Base view that aggregates user token usage from chat_messages
});

export const teamTokenUsageView = mysqlView('team_token_usage').as((qb) => {
  // Depends on user_token_usage view
  // Aggregates team level statistics from user_token_usage
});

However, the generated SQL creates them in alphabetical order:

VIEW `team_token_usage` AS (
  // References user_token_usage which doesn't exist yet
);

VIEW `user_token_usage` AS (
  // Base view definition
);

Current Behavior:

• Tables and views are created in alphabetical order
• Dependencies are not considered in creation order
• Results in SQL errors when views reference not-yet-created views or tables

Expected Behavior:

• Tables and views should be created following the schema definition order
• Or consider dependencies when determining creation order
• Views with dependencies should be created after their dependencies

Workaround

Currently, we need to either:

• Rename views with prefixes to force correct alphabetical order
• Split migrations into multiple files
• Use numerical prefixes in view names

Impact

This issue affects any schema where:

• Views depend on other views
• Tables have complex foreign key relationships
• The alphabetical order doesn't match the required creation order

[kei-ichi]

Additional Finding: View Creation Order is Unpredictable

After further investigation, I discovered that the view creation order isn't even following alphabetical order as initially thought. Even when adding numerical prefixes to force ordering, the generation still produces views in an unpredictable order.

Example

Using numbered prefixes in schema:

export const userTokenUsageView = mysqlView('token_usage_01_user')...
export const teamTokenUsageView = mysqlView('token_usage_02_team')...

Generated SQL still puts the dependent view first:

VIEW `token_usage_02_team` AS (
  // References token_usage_01_user which doesn't exist yet
  select ... from `token_usage_01_user` ...
);--> statement-breakpoint

VIEW `token_usage_01_user` AS (
  // Base view definition
  select ... from `chat_messages` ...
);

This means:

• View creation order isn't following alphabetical order
• View creation order isn't following schema definition order
• View creation order appears to be unpredictable
• Previously suggested workaround (using numerical prefixes) doesn't solve the issue

This makes the issue more critical as there currently appears to be no reliable workaround except writing raw SQL migrations.

[juanvilladev]

This is also an issue with generated migrations for Postgres. I always end up needing to modify the migrations file manually to drop views and add them in the right order.

[kei-ichi]

@juanvilladev
Thank you for shared your issue.

I have no idea why Drizzle Kit team decided to generate the SQL migrations in alphabetical order instead of following the definition order in the schema file. My current solution for this issue is define the view using raw SQL code then create a view in the schema with .existing() to get the type support and prevent the wrong order creation....

[juanvilladev]

@kei-ichi can you perhaps show an example please? Unsure exactly what you mean by this.
So you define them in raw SQL (which is fine), but how do you then get the inferred types? Unsure how existing() works.

[kei-ichi]

@juanvilladev
Sorry for the confusion! Below are the steps I'm doing in my current project:

1. Use Drizzle ORM to execute the raw SQL to create a view:

import { sql } from 'drizzle-orm' 

await db.execute(sql`SQL TO CREATE VIEW HERE`);

2. Then, in the schema file add a schema for the created view but add .existing() to it:

export const dummyView = pgView("dummy_view", {
  id: serial("id"),
  name: text("name"),
  email: text("email"),
}).existing();

Note: You can declare the view inside the schema view before you execute the raw SQL without any issue at all.

With .existing(), Drizzle Kit will not try to create that view but you can still $inferSelect from it and get the select type.