[BUG]: When using RLS policies and Views, the view is the last clause generated

[pffigueiredo]

What version of drizzle-orm are you using?

0.36.0

What version of drizzle-kit are you using?

0.27.0

Describe the Bug

Description

When we need to combine the view with RLS policies, the view clause needs to come before these policies to make sure there are no reference errors.

However, as of right now, when defining a view after some tables with RLS policies, the create view clause is being placed after these policies, which lead to errors when running the generated .sql.

PROBLEM EXAMPLE

Running drizzle-kit generate:

export const chats = pgTable(
  'chats',
  {
  ...
  },
  (table) => [
    crudPolicy({
      role: authenticatedRole,
      read: sql`((select auth.user_id()) in (select user_id from MY_CHATS_PARTICIPANTS where chat_id = ${table.id}))`,
      modify: authUid(table.ownerId),
    }),
  ]
);

export const myChatParticipantsView = pgView('my_chats_participants')
  .with({
    securityInvoker: true,
  })
  .as((qb) => {
    ....
  });

Will generate 👇

create table 'chats' ...;

# error here ❌ , the view hasn't been created yet
CREATE POLICY "crud-authenticated-policy-select" ON "chats" AS PERMISSIVE FOR SELECT TO "authenticated" USING (((select auth.user_id()) in (select user_id from MY_CHATS_PARTICIPANTS where chat_id = "chats"."id")));--> statement-breakpoint


CREATE VIEW "public"."my_chats_participants" WITH (security_invoker = true) AS (select "chat_id", "user_id" from "chat_participants" where "chat_participants"."chat_id" in (select "chat_id" from "chat_participants" where "chat_participants"."user_id" = auth.user_id()));

Expected behavior

The create view clause ,or any database objects that can be referenced in the RLS policies, should always be defined BEFORE the RLS policies clause.

Environment & setup

No response

[AndriiSherman]

fixed in [email protected]