© 2026 Hedgehog Software, LLC

TwitterGitHubDiscord
More
CommunitiesDocsAboutTermsPrivacy
Search
Star
Setup for Free
Drizzle TeamDT
Drizzle Team•16mo ago•
4 replies
Nico

Help with relations: many-to-many + one-to-one

I'm having trouble figuring out how to model the following relationships between papers, authors, and author's names with Drizzle
relations
relations
. The schema looks like this (I've ommitted some columns for brevity)

export const authors = sqliteTable("authors", (t) => ({
   authorId: t.integer().primaryKey(),
   firstName: t.text(),
   lastName: t.text(),
}));

export const papers = sqliteTable("papers", (t) => ({
   paperId: t.integer().primaryKey(),
}));

export const authorsToPapers = sqliteTable("authors_to_papers", (t) => ({
   authorId: t.integer().notNull().references(() => authors.authorId),
   paperId: t.integer().notNull().references(() => papers.paperId),
}));
export const authors = sqliteTable("authors", (t) => ({
   authorId: t.integer().primaryKey(),
   firstName: t.text(),
   lastName: t.text(),
}));

export const papers = sqliteTable("papers", (t) => ({
   paperId: t.integer().primaryKey(),
}));

export const authorsToPapers = sqliteTable("authors_to_papers", (t) => ({
   authorId: t.integer().notNull().references(() => authors.authorId),
   paperId: t.integer().notNull().references(() => papers.paperId),
}));


Conceptually, the relations are as follows:

- each row in
authors
authors
can be related to many rows in
papers
papers
, and each row in
papers
papers
can be related to many rows
authors
authors
(many to many)
- each row in
authors_to_papers
authors_to_papers
is be associated with one row of
authors
authors


Here are the relations I have set up:

export const authorsRelations = relations(authors, ({ one, many }) => ({
   authorsToPapers: many(authorsToPapers),
}));

export const papersRelations = relations(papers, ({ many }) => ({
   authorsToPapers: many(authorsToPapers),
}));

export const authorsToPapersRelations = relations(
   authorsToPapers,
   ({ one }) => ({
      author: one(authors, {
         fields: [authorsToPapers.authorId],
         references: [authors.authorId],
      }),
      paper: one(papers, {
         fields: [authorsToPapers.paperId],
         references: [papers.paperId],
      }),
   }),
);
export const authorsRelations = relations(authors, ({ one, many }) => ({
   authorsToPapers: many(authorsToPapers),
}));

export const papersRelations = relations(papers, ({ many }) => ({
   authorsToPapers: many(authorsToPapers),
}));

export const authorsToPapersRelations = relations(
   authorsToPapers,
   ({ one }) => ({
      author: one(authors, {
         fields: [authorsToPapers.authorId],
         references: [authors.authorId],
      }),
      paper: one(papers, {
         fields: [authorsToPapers.paperId],
         references: [papers.paperId],
      }),
   }),
);


I know that I can do the following to return a paper with it's author(s):

await db.query.papers.findFirst({
   with: {
      authorsToPapers: true,
   },
});
await db.query.papers.findFirst({
   with: {
      authorsToPapers: true,
   },
});


But how do I get from the
authorId
authorId
s returned in the output of the above to the
firstName
firstName
and
lastName
lastName
associated with those
authorId
authorId
s?
Drizzle TeamJoin
The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!
11,879Members
Resources
Was this page helpful?

Similar Threads

Recent Announcements

Similar Threads

Creating One-to-Many relations
Drizzle TeamDTDrizzle Team / help
2y ago
One to Many Self Relations...
Drizzle TeamDTDrizzle Team / help
3y ago
one-to-many relation - multiple relations
Drizzle TeamDTDrizzle Team / help
3y ago
Problem with findMany with many-to-many relations
Drizzle TeamDTDrizzle Team / help
16mo ago