DT
Drizzle Teamiqrow

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),
    }
  }
)
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.
AS
Andrii Shermanā€¢341d ago
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(),
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
I
iqrowā€¢341d ago
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 $$;
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 šŸ‘
AS
Andrii Shermanā€¢341d ago
Great! so it's just MySQL bug
Want results from more Discord servers?
Add your server
More Posts
PSQL SQL query does not workUnable to achieve it with Drizzle helpers - I had to write my query in plain SQL. I'm trying to budrop tablesIs there a way to do a migration to drop tables? Other ORMs like Sequelize and Prisma have a conceptdb.query error with relationI have created a schema.ts, with two tables, with a one-to-one relationship. I have also create the How to transform to camelCase with json_agg()?As topic. I can't find any example on how to go about this. The closest I can find is https://orm.drHow to use select?I have a SQL query ``` SELECT row_to_json(departments.*) as department, row_to_jsonCount in relational queriesHow do I count in relational queries? For example if i'm querying db.query.posts.findMany() and wantCreate a type of VARCHAR[]I want to create a type for my column of `VARCHAR[]` but when I used Drizzle-Kit to generate it, I gSelect with relationIs there a way to get relations when using `db.select().from(table)`I can't get the `db.query.table.Any plan to support ClickHouse db?Any plan to support ClickHouse db? https://clickhouse.com/`where` inside relational queries `with` does not workHi there! I'm new to drizzle and tried out the relational queries. I want to do a nested `where` rIssue with 'insert on conflict do update where'I am using db.insert(lastHeaterMetricsTable) .values(heaterEntityWithMaxTime) Duplicate relations when using `with`I'm running into a problem where some duplicate values are being returned through a relation with `wUsing BIN_TO_UUID / UUID_TO_BINIā€™m trying to understand the best way to use `BIN_TO_UUID` and `UUID_TO_BIN` (MySQL). The below is drizzle-kit drop config file does not existI have a `/foo/drizzle.config.ts` file as suggested in the docs (https://orm.drizzle.team/kit-docs/