Drizzle Team•12mo ago

mysql2 many-to-many

Is there an example of a mysql many-to-many relationship? I haven't been able to find one and I'm getting a foreign key constraint db error similar to this post https://planetscale.com/blog/working-with-related-data-using-drizzle-and-planetscale. I've created a join table with foreign keys and relations in line with the example here https://orm.drizzle.team/docs/rqb#many-to-many. Many thanks!

Drizzle Queries - Drizzle ORM

Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.

4 Replies

Angelelz•12mo ago

The example in the docs will serve you well For planetscale you just need to delete the .references method on the columns. The rest will be the same

errorsmyownOP•12mo ago

Thanks @Angelelz for support! I've tried a direct copy of the example but switching it from pgTable to mysqlTable (see below) and bump into the same error when I try to use db push for drizzle kit 'ALTER TABLE users_to_groups ADD CONSTRAINT users_to_groups_user_id_users_id_fk FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE no action ON UPDATE no action;'.

import {
  mysqlTable,
  serial,
  text,
  int,
  primaryKey,
} from "drizzle-orm/mysql-core";
import { relations } from "drizzle-orm";

export const users = mysqlTable("users", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

export const usersRelations = relations(users, ({ many }) => ({
  usersToGroups: many(usersToGroups),
}));

export const groups = mysqlTable("groups", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

export const groupsRelations = relations(groups, ({ many }) => ({
  usersToGroups: many(usersToGroups),
}));

export const usersToGroups = mysqlTable(
  "users_to_groups",
  {
    userId: int("user_id")
      .notNull()
      .references(() => users.id),
    groupId: int("group_id")
      .notNull()
      .references(() => groups.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.groupId),
  })
);

export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
  group: one(groups, {
    fields: [usersToGroups.groupId],
    references: [groups.id],
  }),
  user: one(users, {
    fields: [usersToGroups.userId],
    references: [users.id],
  }),
}));

import {
  mysqlTable,
  serial,
  text,
  int,
  primaryKey,
} from "drizzle-orm/mysql-core";
import { relations } from "drizzle-orm";

export const users = mysqlTable("users", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

export const usersRelations = relations(users, ({ many }) => ({
  usersToGroups: many(usersToGroups),
}));

export const groups = mysqlTable("groups", {
  id: serial("id").primaryKey(),
  name: text("name"),
});

export const groupsRelations = relations(groups, ({ many }) => ({
  usersToGroups: many(usersToGroups),
}));

export const usersToGroups = mysqlTable(
  "users_to_groups",
  {
    userId: int("user_id")
      .notNull()
      .references(() => users.id),
    groupId: int("group_id")
      .notNull()
      .references(() => groups.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.groupId),
  })
);

export const usersToGroupsRelations = relations(usersToGroups, ({ one }) => ({
  group: one(groups, {
    fields: [usersToGroups.groupId],
    references: [groups.id],
  }),
  user: one(users, {
    fields: [usersToGroups.userId],
    references: [users.id],
  }),
}));

Using mysql locally at the moment. Is this something to do with .serial ?

Angelelz•12mo ago

Like I said, for planetscale, all you need to do is delete the references method in the column definitions:

export const usersToGroups = mysqlTable(
  "users_to_groups",
  {
    userId: int("user_id")
      .notNull()
    groupId: int("group_id")
      .notNull()
  },
  (t) => ({
    pk: primaryKey(t.userId, t.groupId),
  })
);

export const usersToGroups = mysqlTable(
  "users_to_groups",
  {
    userId: int("user_id")
      .notNull()
    groupId: int("group_id")
      .notNull()
  },
  (t) => ({
    pk: primaryKey(t.userId, t.groupId),
  })
);

The .references method create a foreign key constraint that is not supported in planetscale

errorsmyownOP•12mo ago

Ah my misunderstanding! Thank you 🙏

Gaming

Programming

mysql2 many-to-many