Kevin Powell - Community•3mo ago

Does anyone use Drizzle here? Many-to-Many relationships and types

Hi, Am a bit confused with Drizzle tbh. I think this is how you set up and query a M:N relationship, but it seems very long-winded, so it kinda feels like a skill issue: Let's say I've got a Users and a Jobs table

// Drizzle relationships:
export const usersRelations = relations(users, ({ many }) => ({
  usersToJobs: many(usersToJobs),
}));

export const jobsRelations = relations(jobs, ({ many }) => ({
  usersToJobs: many(usersToJobs),
}));

export const usersToJobsRelations = relations(
  usersToJobs,
  ({ one }) => ({
    user: one(users, {
      fields: [userToJobTable.userId],
      references: [userTable.id],
    }),
    job: one(jobs, {
      fields: [usersToJobs.jobId],
      references: [jobs.id],
    }),
  }),
);

// Querying all users and the jobs they've applied for
  const users = await db.query.users.findMany({
    with: {
      usersToJobs: {
        with: {
          jobs: true,
        },
      },
    },
  });

// Drizzle relationships:
export const usersRelations = relations(users, ({ many }) => ({
  usersToJobs: many(usersToJobs),
}));

export const jobsRelations = relations(jobs, ({ many }) => ({
  usersToJobs: many(usersToJobs),
}));

export const usersToJobsRelations = relations(
  usersToJobs,
  ({ one }) => ({
    user: one(users, {
      fields: [userToJobTable.userId],
      references: [userTable.id],
    }),
    job: one(jobs, {
      fields: [usersToJobs.jobId],
      references: [jobs.id],
    }),
  }),
);

// Querying all users and the jobs they've applied for
  const users = await db.query.users.findMany({
    with: {
      usersToJobs: {
        with: {
          jobs: true,
        },
      },
    },
  });

Is that right? I don't seem to be able to access the other side of the relationship, so it can't be (so like users.jobs or whatever it should be). I feel like I must be missing something. Wouldn't the equivalent in Sequelize just be:

User.belongsToMany(Job, { through: User_Job });
Job.belongsToMany(User, { through: User_Job });

const results = await User.findAll({
  include: Job,
});

User.belongsToMany(Job, { through: User_Job });
Job.belongsToMany(User, { through: User_Job });

const results = await User.findAll({
  include: Job,
});

Also, if I'm using my schema to create Zod types with drizzle-zod:

import { createInsertSchema } from "drizzle-zod";
export const userSchema = createInsertSchema(users);

import { createInsertSchema } from "drizzle-zod";
export const userSchema = createInsertSchema(users);

I'm a bit stumped as to how to type it now it includes that nested usersToJobs and the jobs? I know I should extend the userSchema, just no idea how to do it correctly! Appreciate any help 🙂 Nick

5 Replies

ἔρως•3mo ago

i know nothing about drizzle but i know about relational databases in relational databases, you DO NOT do n:n relationships. you have always an intermediary table which has the foreign key of both n:n tables (and maybe some extra data) for example, you dont co-related the jobs and users directly, if a job can have multiple users what you do is have a table for users, a table for jobs and a table for users_jobs (in which you have a foreign key for users and one for jobs) if you need all the jobs that carl has done, you look for carl's id in users_jobs, and then get all jobs from that set if you need to look for which users were in the job xyz, look for all the jobs in users_jobs and then get the list of users from that if you create a covering index on the jobs, users and users_jobs tables, this process is just incredibly fast since it will always be obtained from the indexes

Nick•3mo ago

hi @ἔρως, yeah I guess I was just expecting the convenience that you get with that Sequelize example (where you can access the other side of the M:N relationship because you've specified the join table in the relationship itself). I guess it's doing work behind the scenes to mutate and flatten the data. It also confused me because there's a Drizzle youtube tutorial that looks to be doing exactly that, but I must be missing something about his code But you're right, that's definitely not how sql works!

ἔρως•3mo ago

sadly, i can't help much more than that probably try to do it in a more "raw" way, without relying on "magic"

Nick•3mo ago

Yeah that's the conclusion I came to as well last night, ended up brushing up on my joins 🙂 Thanks for your help

ἔρως•3mo ago

you're welcome

Gaming

Programming

Does anyone use Drizzle here? Many-to-Many relationships and types