Mastering Many-to-Many Relationships with Drizzle, Next.js, and Supabase
Written on
Understanding Many-to-Many Relationships
In this concluding part of our series, we will delve into many-to-many relationships, which are facilitated through a junction table. We'll demonstrate how to implement this in Drizzle.Tables by creating three distinct tables: publishers, books, and contributors.
The objective is to associate books with publishers while recognizing that a single book may involve multiple contributors, such as authors and editors.
Defining the Tables
The following code snippets illustrate how to define the three tables:
// Publishers Table
export const publishers = pgTable("publishers", {
id: text("id").primaryKey().$defaultFn(() => createId()),
name: text("name").notNull(),
website: text("website").notNull().unique(),
email: text("email").notNull().unique(),
});
// Books Table
export const books = pgTable("books", {
id: text("id").primaryKey().$defaultFn(() => createId()),
title: text("name").notNull(),
publicationDate: timestamp("publicationDate").notNull(),
isbn: text("isbn").notNull().unique(),
publisherId: text("publisherId").references(() => publishers.id, { onDelete: "cascade" }).notNull(),
});
// Contributors Table
export const contributors = pgTable("contributors", {
id: text("id").primaryKey().$defaultFn(() => createId()),
firstName: text("firstName").notNull(),
lastName: text("lastName").notNull(),
email: text("isbn").notNull().unique(),
});
Next, we will seed the database with sample data.
Seeding the Database
We will create one publisher, two books, and two contributors. The first book will have two contributors, while the second will feature one.
const [oreilly] = await db.insert(publishers).values({
email: "[email protected]",
name: "Oreilly",
}).returning({
id: publishers.id,
});
const [book1] = await db.insert(books).values({
isbn: "9781491914915",
publicationDate: new Date("2020-01-01"),
title: "Learning Javascript",
publisherId: oreilly.id,
}).returning({
id: books.id,
});
const [book2] = await db.insert(books).values({
isbn: "9791492914916",
publicationDate: new Date("2021-01-01"),
title: "Learning Python",
publisherId: oreilly.id,
}).returning({
id: books.id,
});
const [contributor1] = await db.insert(contributors).values({
email: "[email protected]",
firstName: "John",
lastName: "Doe",
}).returning({
id: contributors.id,
});
const [contributor2] = await db.insert(contributors).values({
email: "[email protected]",
firstName: "Olivia",
lastName: "Green",
}).returning({
id: contributors.id,
});
await db.insert(bookContributors).values([
{
bookId: book1.id,
contributorId: contributor1.id,
},
{
bookId: book1.id,
contributorId: contributor2.id,
},
{
bookId: book2.id,
contributorId: contributor2.id,
},
]);
Accessing Data
Now, let's explore how to retrieve publishers and their associated books and contributors.
Fetching Publishers
To retrieve all publishers, use the following query:
const publishersData = await db.query.publishers.findMany();
You can display this data in your application:
// Rendering Publishers
<Drizzle - Many to Many>
Publishers
{publishersData.map(publisher => (
{publisher.name}))}
Fetching Books by Publisher
To get the books associated with a specific publisher, execute the following code:
const booksData = await db.query.books.findMany({
where: eq(books.publisherId, publishersData[0].id),
});
console.log(booksData);
Establishing Relationships
To properly establish relationships between the books and publishers in Drizzle, define the relations as follows:
export const publishersRelations = relations(publishers, ({ many }) => ({
books: many(books),
}));
export const booksRelations = relations(books, ({ many, one }) => ({
bookContributors: many(bookContributors),
publishers: one(publishers, {
fields: [books.publisherId],
references: [publishers.id],
}),
}));
export const contributorsRelation = relations(contributors, ({ many }) => ({
contributors: many(bookContributors),
}));
Querying with Relations
To fetch publishers along with their books and contributors, use the following query:
const publishersData = await db.query.publishers.findMany({
with: {
books: {
with: {
bookContributors: {
with: {
contributors: true,},
},
},
},
},
});
Final Output
Now you can display the complete structure, showing publishers, their books, and the contributors involved with each book:
// Displaying Final Results
<Drizzle - Many to Many>
Publishers
{publishersData.map(publisher => (
{publisher.name}))}
Books
{publishersData[0].books.map((book) => (
Title: {book.title}
{book.bookContributors.map(bookContributor => (
{bookContributor.contributors.firstName} {bookContributor.contributors.lastName}))}
))}
Conclusion
By following these steps, you've successfully implemented many-to-many relationships between publishers, books, and contributors using Drizzle and Next.js.
In this video, we explore many-to-many relations in Drizzle ORM, detailing the process and intricacies involved.
This video demonstrates how to effectively utilize Drizzle ORM within a Next.js application, complete with a practical project example.
Thank you for your interest in this topic. Your feedback is always welcome!