Drizzle Team•3w ago

Disambiguating relations

Hi guys, started using drizzle and love it but ran into an issue when moving from Prisma to drizzle with relations. I have two tables, Users and Classes. The user table has the fields id, name, username, email and role enum which is either student or teacher. The class table has the fields id and name. For the relations I want the class to have many students and many teachers and for the user they can be a teacher of many classes but only a student to one class. How can I create the tables and relations to reflect this? Any help would be appreciated

21 Replies

TOSL•3w ago

Junction tables for class_students and class_teachers. for the user relations you'll do one(class_students) many(class_teachers) for class relations you do many for both the junction tables so the junction tables you need to ensure db level FK as well as unqiue constraint for class_student userId for class_student relation you do one relations for both user and class for class_teachers you do one for both user and class I don't want to write the whole schema so you try and then post it here and I'll help you get it to work if you can't

Frost7994OP•3w ago

Hi @TOSL thank you for getting back to me! I'm struggling a little to understand what you mean, my current schema is as follows:

export const userRoles = [STUDENT, TEACHER, ADMIN] as const;
export type UserRole = (typeof userRoles)[number];
export const userRoleEnum = pgEnum("role", userRoles);

export const UserTable = pgTable("users", {
  id,
  // add clerk user id here

  firstName: varchar().notNull(),
  lastName: varchar().notNull(),

  username: varchar().unique(),
  role: userRoleEnum().default("student"),

  email: varchar().unique(),

  ...timestamps,
});

export const ClassTable = pgTable("classes", {
  id,
  name: varchar().notNull().unique(),

  ...timestamps,
});

export const userRoles = [STUDENT, TEACHER, ADMIN] as const;
export type UserRole = (typeof userRoles)[number];
export const userRoleEnum = pgEnum("role", userRoles);

export const UserTable = pgTable("users", {
  id,
  // add clerk user id here

  firstName: varchar().notNull(),
  lastName: varchar().notNull(),

  username: varchar().unique(),
  role: userRoleEnum().default("student"),

  email: varchar().unique(),

  ...timestamps,
});

export const ClassTable = pgTable("classes", {
  id,
  name: varchar().notNull().unique(),

  ...timestamps,
});

I'm not sure how to implement the junction tables as you've suggested, each id and timestamp is a helper function to generate an id which is uuid().notNull().primary key() and timestamps creates a createdAt and updatedAt field. If you could help implement your suggestion I'd really appreciate it

francis•3w ago

this is a bit of a weird model where you have two distinct relation types that are distinguished by only a field on the table oh wait, this doesn't even make any sense The user table has the fields id, name, username, email and role enum which is either student or teacher. ...for the user they can be a teacher of many classes but only a student to one class. so the user itself is not a student or teacher, it's the user to class relation that has the data of enum or teacher. Otherwise, if you have user 1 which is student in class A and teacher in class B, how would this work? what would the student/teacher value on the user be for this scenario? this isn't a drizzle problem, or even a sql problem, this is a data modeling problem. Resolve that first before you consider your drizzle or sql implementations

TOSL•3w ago

I don't Francis is entirely wrong but it's not that big a deal for a simple project. Unless you want a user to be both a student and a teacher at the same time which would obviously not work. I don't think you mean that a user can be a student in one class and a teacher in another however so it shouldn't be a problem You know what junction/join tables are? My suggestion is that you use such a table to define the relations you want. There are probably a lot of ways to do it so just investigate that

francis•3w ago

Unless you want a user to be both a student and a teacher at the same time which would obviously not work

I'm not sure how else to interpret "for the user they can be a teacher of many classes but only a student to one class" unless they mean that a user record can be a student with a single class relation, or a teacher with many class relations, which seems incredibly arbitrary and fragile I can't think of a good way to enforce this with sql constraints, and data model invariants that you can't enforce in sql should be done with caution

Frost7994OP•3w ago

This is exactly what I mean. A user of the platform can be either a teacher or a student. If they're a student they can be a student to one class. If they're a teacher they can teach many classes. As Teacher and Student have all the same fields other than their role I thought it made the most sense to have them as a User table and differentiated by a role enum. I have seen many implementations of something similar which is why it seemed like the best result. If it's incredibly arbitrary and fragile I'd be delighted to know what setup would be better? I am new enough to using SQL hence the issue I'm facing

TOSL•3w ago

I see. This just a misunderstanding on my part I guess but the solution is the same. @Frost7994 A user can be both a teacher and a student at the same time, correct? But not in the same class?

Frost7994OP•3w ago

No a user can only be one or the other, determined by their role. A studentTable and teacherTable would share all the same fields, i.e id, name, email, username, createdAt and updatedAt and this felt repetitive so it made more sense to me to create a user table and differentiate between the students and teachers by giving the user a role of one OR the other.

TOSL•3w ago

That was my initial thinking. So I didn't misunderstand. Now, can you try to explain the relationship you want to achieve more clearly?

Frost7994OP•3w ago

I want every class to have many teachers and many students. Each user who is a student, dictated by their role, can only be a student of one class. Each user who is a teacher, again dictated by their role, can be a teacher of many classes. Thanks for the patience and helping me sort this out

francis•3w ago

so to clarify: a user who is a student can only be in one class, and cannot be a teacher for any other class? it still makes sense to me to have roles be on the relationship since that would make this more flexible anyway - but your call I would still actually probably put roles on the relationship with a: - user - class - user_class_join with user_id, class_id, role then you can enforce the restriction you want with a create unique index on user_id where role = 'student' a partial unique index that only applies to students would solve that problem, and still let you have that student be a teacher of any number of other classes, if you want it I believe it is not possible to define this restriction with a SQL constraint if you have the role on the user itself, because you want to restrict the number of join entities for a given user_id based on the user role

TOSL•3w ago

So a join table like this where you move the role away from the user table might be better

export const classRoles = pgTable("class_Roles", {
  id: serial("id").primaryKey(), // Auto-incrementing primary key
  userId: integer("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  classId: integer("class_id")
    .notNull()
    .references(() => classes.id, { onDelete: "cascade" }),
  role: roleEnum().notNull(), // Either "student" or "teacher"
}, (table) => [
  uniqueIndex("unique_student_per_class")
    .on(table.userId)
    .where(sql`${table.role} = 'student'`), // Ensures user is a student in only one class
  uniqueIndex("no_duplicate_roles_per_class")
    .on(table.userId, table.classId), // Prevents duplicate roles in the same class
]);

export const classRoles = pgTable("class_Roles", {
  id: serial("id").primaryKey(), // Auto-incrementing primary key
  userId: integer("user_id")
    .notNull()
    .references(() => users.id, { onDelete: "cascade" }),
  classId: integer("class_id")
    .notNull()
    .references(() => classes.id, { onDelete: "cascade" }),
  role: roleEnum().notNull(), // Either "student" or "teacher"
}, (table) => [
  uniqueIndex("unique_student_per_class")
    .on(table.userId)
    .where(sql`${table.role} = 'student'`), // Ensures user is a student in only one class
  uniqueIndex("no_duplicate_roles_per_class")
    .on(table.userId, table.classId), // Prevents duplicate roles in the same class
]);

francis•3w ago

^ yep, the partial unique index here is the key. though this still doesn't enforce "either a student in one class or a teacher in many classes" - if that's a requirement, it's a little trickier you could do it by putting the role also on the user and referencing both the user id and the role in the foreign key - but that's ugly i.e. if you have a user A, class B, student, the user A would also have "student" as a role, so you couldn't then create a new entry for user A and teacher I do actually do this in my production application for a similar use case but if you can drop this requirement I would strongly encourage it - it makes everything much more difficult

TOSL•3w ago

I'm not sure what you mean but the doesn't enforce "either a student in one or a teacher in many". This should allow for a user to be a teacher for MANY classes. It should allow MANY students in one class. It should disallow a student to have MANY classes. It should disallow a user from somehow being both a student a teacher

francis•3w ago

It should disallow a user from somehow being both a student [and] a teacher

how so? that's the part I was highlighting, I don't think it does that, no? it just stops the user from being a student and a teacher in the same class but if I am a teacher of classes A and B and a student in class C, that is totally fine by this schema

TOSL•3w ago

Oh, yes you are correct. The key is making sure that a user can be either a teacher in many classes or a student in one class, but never both. I think we need a check constraint @Frost7994 I tried but I can't think of a way to enforce this particular requirement at the db level with constraints. I think what @francis suggested with the having the role on both the user table is as good as anything. It's redundant but normalization isn't some unbreakable law. You just have to weigh the tradeoff It's pretty minimal

Frost7994OP•3w ago

import { pgTable, serial, text, integer } from 'drizzle-orm/pg-core'; import { relations } from 'drizzle-orm'; export const users = pgTable('users', { id: serial('id').primaryKey(), name: text('name'), }); export const usersRelations = relations(users, ({ many }) => ({ author: many(posts, { relationName: 'author' }), reviewer: many(posts, { relationName: 'reviewer' }), })); export const posts = pgTable('posts', { id: serial('id').primaryKey(), content: text('content'), authorId: integer('author_id'), reviewerId: integer('reviewer_id'), }); export const postsRelations = relations(posts, ({ one }) => ({ author: one(users, { fields: [posts.authorId], references: [users.id], relationName: 'author', }), reviewer: one(users, { fields: [posts.reviewerId], references: [users.id], relationName: 'reviewer', }), })); Maybe I'm over complicating what I want to achieve with the constraints I want as in not allowing a user to be a student and teacher at the same time as that's something I can manage outside of the database. This is the example in the docs which shows two one to many relations. If we were to use this example what I want to achieve is one to many relation with post and author but a many to many relation with posts and reviewers which is the part I'm struggling with as I know you need to implement a join table in the fashion of reviews to posts and use that but I can't seem to execute that properly

francis•3w ago

you really need to think about this from sql first, rather than from drizzle

TOSL•3w ago

@Frost7994 I was testing in this playground yesterday. You use it to figure out the rest of that you need. https://drizzle.run/w7cc2lxxzdec2wh5trywxn38

Drizzle Run

undefined - Drizzle Run

Frost7994OP•3w ago

@TOSL thanks again for the continued help. That link just takes me to the playground landing, not to a specific playground. Is there a name I can search in the saved playgrounds to access it?

TOSL•3w ago

Try again?

Gaming

Programming

Disambiguating relations

Did you find this page helpful?