floppydisk Posts - Answer Overflow

floppydisk

•Created by floppydisk on 5/21/2024 in #help

many-to-many and querying

Hey, I just started using Drizzle and I would appreciate it if someone could chime in and tell me that I'm using it right. I read the documentation and looked at examples, and I'm positive I replicated it correctly. It also works fine. I just would like to confirm, that I can continue to build my application like this. I'm tracking transactions. Every transactions can have many tags, hence the many-to-many relation:

export const transactions = createTable("transaction", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  amount: integer("amount").notNull(),
  date: timestamp("timestamp", { mode: "date" }),
  description: text("description"),
  reference: text("reference"),
});

export const tags = createTable("tags", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  label: text("label").notNull(),
});

export const transactionTags = createTable(
  "transaction_tags",
  {
    transactionId: varchar("transaction_id", { length: 255 })
      .notNull()
      .references(() => transactions.id),
    tagId: varchar("tag_id", { length: 255 })
      .notNull()
      .references(() => tags.id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.transactionId, t.tagId] }),
  }),
);

export const transactionRelations = relations(transactions, ({ many }) => ({
  transactionTags: many(transactionTags),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  transactionTags: many(transactionTags),
}));

export const transactionTagsRelations = relations(
  transactionTags,
  ({ one }) => ({
    transaction: one(transactions, {
      fields: [transactionTags.transactionId],
      references: [transactions.id],
    }),
    tag: one(tags, {
      fields: [transactionTags.tagId],
      references: [tags.id],
    }),
  }),
);

export const transactions = createTable("transaction", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  amount: integer("amount").notNull(),
  date: timestamp("timestamp", { mode: "date" }),
  description: text("description"),
  reference: text("reference"),
});

export const tags = createTable("tags", {
  id: varchar("id", { length: 255 }).notNull().primaryKey(),
  label: text("label").notNull(),
});

export const transactionTags = createTable(
  "transaction_tags",
  {
    transactionId: varchar("transaction_id", { length: 255 })
      .notNull()
      .references(() => transactions.id),
    tagId: varchar("tag_id", { length: 255 })
      .notNull()
      .references(() => tags.id),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.transactionId, t.tagId] }),
  }),
);

export const transactionRelations = relations(transactions, ({ many }) => ({
  transactionTags: many(transactionTags),
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  transactionTags: many(transactionTags),
}));

export const transactionTagsRelations = relations(
  transactionTags,
  ({ one }) => ({
    transaction: one(transactions, {
      fields: [transactionTags.transactionId],
      references: [transactions.id],
    }),
    tag: one(tags, {
      fields: [transactionTags.tagId],
      references: [tags.id],
    }),
  }),
);

7 replies

DTDrizzle Team

•Created by floppydisk on 4/6/2024 in #help

inserting many-to-many relationship correctly?

Hey, I'm in the process of making the switch from Prisma to Drizzle and I want to make sure that I nail the basics, before messing up the more complex things. My scenario: - Users are authenticated using Supabase Auth - Users can create clubs - Users join as many clubs as they want Thus I created the following schema:

export const users = pgTable("users", {
  id: varchar("id").primaryKey(),
});

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

export const clubs = pgTable("clubs", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 64 }).notNull(),
  createdAt: timestamp("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
});

export const clubsRelations = relations(clubs, ({ many }) => ({
  usersToClubs: many(usersToClubs),
}));

export const usersToClubs = pgTable(
  "users_to_clubs",
  {
    userId: varchar("user_id")
      .notNull()
      .references(() => users.id),
    clubId: integer("club_id")
      .notNull()
      .references(() => clubs.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.clubId),
  }),
);

export const usersToClubsRelations = relations(usersToClubs, ({ one }) => ({
  group: one(clubs, {
    fields: [usersToClubs.clubId],
    references: [clubs.id],
  }),
  user: one(users, {
    fields: [usersToClubs.userId],
    references: [users.id],
  }),
}));

export const users = pgTable("users", {
  id: varchar("id").primaryKey(),
});

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

export const clubs = pgTable("clubs", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 64 }).notNull(),
  createdAt: timestamp("created_at")
    .default(sql`CURRENT_TIMESTAMP`)
    .notNull(),
});

export const clubsRelations = relations(clubs, ({ many }) => ({
  usersToClubs: many(usersToClubs),
}));

export const usersToClubs = pgTable(
  "users_to_clubs",
  {
    userId: varchar("user_id")
      .notNull()
      .references(() => users.id),
    clubId: integer("club_id")
      .notNull()
      .references(() => clubs.id),
  },
  (t) => ({
    pk: primaryKey(t.userId, t.clubId),
  }),
);

export const usersToClubsRelations = relations(usersToClubs, ({ one }) => ({
  group: one(clubs, {
    fields: [usersToClubs.clubId],
    references: [clubs.id],
  }),
  user: one(users, {
    fields: [usersToClubs.userId],
    references: [users.id],
  }),
}));

7 replies

Gaming

Programming