Confused about Relationships in Drizzle?

i have relationships like this in https://lucia-auth.com

users -> sessions, keys, email_verification_tokens (1:many)

sessions, keys, email_verification_tokens -> users (many:1)

users -> sessions, keys, email_verification_tokens (1:many)

sessions, keys, email_verification_tokens -> users (many:1)

and my code in drizzle looks like:

export const usersRelations = relations(users, ({ many }) => ({
  sessions: many(sessions),
  keys: many(keys),
  emailVerificationTokens: many(emailVerificationTokens),
}))

export const sessionsRelations = relations(sessions, ({ one }) => ({
  users: one(users),
}))

export const keysRelations = relations(keys, ({ one }) => ({
  users: one(users),
}))

export const emailVerificationTokensRelations = relations(
  emailVerificationTokens,
  ({ one }) => ({
    users: one(users),
  })
)

export const usersRelations = relations(users, ({ many }) => ({
  sessions: many(sessions),
  keys: many(keys),
  emailVerificationTokens: many(emailVerificationTokens),
}))

export const sessionsRelations = relations(sessions, ({ one }) => ({
  users: one(users),
}))

export const keysRelations = relations(keys, ({ one }) => ({
  users: one(users),
}))

export const emailVerificationTokensRelations = relations(
  emailVerificationTokens,
  ({ one }) => ({
    users: one(users),
  })
)

i would love to know if it's correct or wrong? idk coding relationships was a bit confusing in drizzle.

Lucia Documentation

Lucia

6 Replies

Noahh•14mo ago

There are two types of relations you'd be interested in when using Drizzle: - Foreign keys via SQL - Relations via Drizzle You can use either one of these, or both at the same time. To define a foreign key and enforce the relationship between the tables at the database level, you'd want to follow this guide: https://orm.drizzle.team/docs/indexes-constraints#foreign-key For your case, it'd be something like:

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  ...
});

export const keys = pgTable('keys', {
  id: serial('id').primaryKey(),
  userId: serial('user_id').references(() => users.id) // Create a foreign key linking to the user in the user's table
  ...
});

...

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  ...
});

export const keys = pgTable('keys', {
  id: serial('id').primaryKey(),
  userId: serial('user_id').references(() => users.id) // Create a foreign key linking to the user in the user's table
  ...
});

...

If you want to be able to query those relations simply, you'd also want to do what you are already doing, but add a little bit to it, as Drizzle needs to know which fields to match on, similarly to how you do it with foreign keys: https://orm.drizzle.team/docs/rqb#one-to-many

export const usersRelations = relations(users, ({ many }) => ({
  sessions: many(sessions),
  keys: many(keys),
  emailVerificationTokens: many(emailVerificationTokens),
}))

export const sessionsRelations = relations(sessions, ({ one }) => ({
  users: one(users, {
    fields: [sessions.userId], // The field in 'sessions' that holds the user's id
    references: [users.id] // the field in 'users' that would match the user id in 'sessions'
  }),
}))

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

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

export const usersRelations = relations(users, ({ many }) => ({
  sessions: many(sessions),
  keys: many(keys),
  emailVerificationTokens: many(emailVerificationTokens),
}))

export const sessionsRelations = relations(sessions, ({ one }) => ({
  users: one(users, {
    fields: [sessions.userId], // The field in 'sessions' that holds the user's id
    references: [users.id] // the field in 'users' that would match the user id in 'sessions'
  }),
}))

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

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

Indexes & Constraints – DrizzleORM

Drizzle ORM | %s

Relational queries – DrizzleORM

Drizzle ORM | %s

Startup Spells 🪄 Newsletter Guy•14mo ago

thank you noahh, but im using planetscale which doesnt support fk constraints so i had to remove all the references part. so im curious if i should use references here or remove it from relationships too?

Noahh•14mo ago

Ah, on that case you can just do the relations as I showed above (define fields and references arrays)

Startup Spells 🪄 Newsletter Guy•14mo ago

will references work here? bcz i was told to remove references in single tables like i had to make this:

userId: varchar('user_id', {
      length: 255,
    }).notNull()
    .references(() => user.id),

userId: varchar('user_id', {
      length: 255,
    }).notNull()
    .references(() => user.id),

into this:

userId: varchar('user_id', {
      length: 255,
    }).notNull()

userId: varchar('user_id', {
      length: 255,
    }).notNull()

thats why im asking if references will work like in your 2nd codeblock? im also curious how to connect relationships? like i did drizzle generate & drizzle push but i dont see any relationships created with your 2nd codeblock

Noahh•14mo ago

yes they'll work, they're completely separate from the references in table definitions. the Drizzle relations are not SQL relationships, Drizzle will make a join behind the scenes and format the results properly. to make use of these relations you have to query differently. instead of db.select().from(table) you do db.query.table.findMany() and can define which relations to include there. Check out the "Relational queries" docs linked above to see how to query and use them. As for if it will work with Lucia, I have no idea. I know it has a planet scale adapter, so that would probably work better than Drizzle, and you can use Drizzle's relations when it's not Lucia interacting with it. https://lucia-auth.com/adapters/planetscale?

Lucia Documentation

PlanetScale serverless - Lucia

Startup Spells 🪄 Newsletter Guy•14mo ago

thank you noahh, that cleared it up

Gaming

Programming

Confused about Relationships in Drizzle?