How to delete with cascade?

I'm using postgres with the following schema (reduced to the important parts):

export const worlds = pgTable('worlds', {
  id: uuid('id').defaultRandom().primaryKey()
})
export const users_to_worlds = pgTable(
  'users_to_worlds',
  {
    userId: varchar('user_id', { length: 32 })
      .references(() => users.id)
      .notNull(),
    worldId: uuid('world_id')
      .references(() => worlds.id)
      .notNull(),
  },
  (table) => {
    return {
      pk: primaryKey(table.userId, table.worldId),
    }
  }
)

And I'm trying to implement an api call to delete a world. Due to the reference to the world in the users_to_worlds I get the error:
Error: update or delete on table "worlds" violates foreign key constraint "users_to_worlds_world_id_worlds_id_fk" on table "users_to_worlds"

I believe what I want to use is a CASCADE delete where everything referencing a world is deleted when I delete a world. Is this possible through Drizzle? I can't seem to find anything on this.

You can add CASCADE
We didn't update web docs with it, thanks for pointing that out!

userId: varchar('user_id', { length: 32 }).references(() => users.id, { onDelete: 'cascade' }).notNull(),

This object has onUpdate as well and all possible values for it

Also if you will just add those and generate migrations you may not see migrations files for it. We have this issue for MySQL, which I almost fixed and soon well deploy to latest

You can try it with Postgres and if it won't generate new migration - just ping me I'll fix it for Postgres as well

https://github.com/drizzle-team/drizzle-kit-mirror/issues/56

ALTER TABLE "users_to_worlds" DROP CONSTRAINT "users_to_worlds_user_id_users_id_fk";
DO $$ BEGIN
 ALTER TABLE "users_to_worlds" ADD CONSTRAINT "users_to_worlds_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "users"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

ALTER TABLE "users_to_worlds" DROP CONSTRAINT "users_to_worlds_world_id_worlds_id_fk";
DO $$ BEGIN
 ALTER TABLE "users_to_worlds" ADD CONSTRAINT "users_to_worlds_world_id_worlds_id_fk" FOREIGN KEY ("world_id") REFERENCES "worlds"("id") ON DELETE cascade ON UPDATE no action;
EXCEPTION
 WHEN duplicate_object THEN null;
END $$;

Looks good to me, but I don't read migrations normally 😄

Works 👍

Great!

so it's just MySQL bug