whats the difference.

Whats the difference of using references like this:

export const profileInfo = pgTable('profile_info', {
    id: serial('id').primaryKey(),
    userId: integer("user_id").references(() => users.id),
    metadata: jsonb("metadata"),
});

export const profileInfo = pgTable('profile_info', {
    id: serial('id').primaryKey(),
    userId: integer("user_id").references(() => users.id),
    metadata: jsonb("metadata"),
});

and like this:

export const usersRelations = relations(users, ({ one }) => ({
    profileInfo: one(profileInfo, {
        fields: [users.id],
        references: [profileInfo.userId],
    }),
}));

export const usersRelations = relations(users, ({ one }) => ({
    profileInfo: one(profileInfo, {
        fields: [users.id],
        references: [profileInfo.userId],
    }),
}));

5 Replies

Noahh•16mo ago

The first one creates actual foreign keys in your database. This will validate on insert or update that your value of userId is present in the id column of your users table. To get the profile_info for a user, you would have to do a JOIN, subquery, or something similar yourself. You can read more about FKs here: https://www.w3schools.com/sql/sql_foreignkey.asp The second one is Drizzle's own RQB. This requires a little bit more setup, as you can tell, but it allows Drizzle to do the hard part of querying based on those relations. So, the RQB relations do not required foreign keys to be set on those fields (you can use either one without the other, or both together). The RQB allows you to skip the joins between tables and do something simple like:

const user = await db.query.users.findFirst({
  where: eq(users.id, 123456),
  with: {
    profileInfo: true
  }
});

/*
{
  "id": 123456,
  ...rest of user,
  profileInfo: {
    "id": 543210,
    "userId": 123456,
    "metadata": {...}
  }
}
*/

const user = await db.query.users.findFirst({
  where: eq(users.id, 123456),
  with: {
    profileInfo: true
  }
});

/*
{
  "id": 123456,
  ...rest of user,
  profileInfo: {
    "id": 543210,
    "userId": 123456,
    "metadata": {...}
  }
}
*/

The with clause can be nested and it makes querying multiple joins a lot simpler. Behind the scenes, Drizzle is doing the heavy lifting of making the complex queries and it makes it simple for you. Feel free to read up more about how you can use the RQB here: https://orm.drizzle.team/docs/rqb

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

Relational queries – DrizzleORM

Drizzle ORM | %s

focused_morningOP•16mo ago

@Noahh does this work in mysql also?

Noahh•16mo ago

it should! it's mentioned throughout the docs: https://orm.drizzle.team/docs/rqb#modes

Relational queries – DrizzleORM

Drizzle ORM | %s

focused_morningOP•16mo ago

@Noahh can we use either or?

Noahh•16mo ago

yep! you can use foreign keys, the RQB, both together, or neither!

Gaming

Programming

whats the difference.