Mastering One-to-Many Relationships with Drizzle, Next.js, and Supabase
Written on
Understanding One-to-Many Relationships
In the previous installment, we set the stage for our project and explored how to implement a one-to-one relationship. However, one-to-one relationships are less frequently encountered in applications. In contrast, one-to-many relationships are much more prevalent, as they mirror real-life scenarios. For instance, a person can own several phones, cars, or computers.
To get the most out of this guide, it's advisable to review the earlier part where we initiated the project.
Defining One-to-Many Relationships
We'll proceed similarly by outlining schemas and the relationships they share. This approach eliminates the need for manual data access, allowing us to retrieve information through a single query.
import { createId } from "@paralleldrive/cuid2";
import { pgTable, text, timestamp } from "drizzle-orm/pg-core";
import { relations } from "drizzle-orm/relations";
export const courses = pgTable("courses", {
id: text("id").primaryKey().$defaultFn(() => createId()),
name: text("name").notNull(),
startDate: timestamp("startDate").notNull(),
endDate: timestamp("endDate").notNull(),
});
export const students = pgTable("students", {
id: text("id").primaryKey().$defaultFn(() => createId()),
firstName: text("firstName").notNull(),
lastName: text("lastName").notNull(),
email: text("email").notNull(),
courseId: text("courseId").references(() => courses.id, { onDelete: "cascade" }).notNull(),
});
Understanding Relations
In relational databases, connections between tables are established using foreign keys. This schema relationship helps define interactions between our custom schemas, so we can avoid manual queries and let Drizzle handle this task.
export const coursesRelations = relations(courses, ({ many }) => ({
students: many(students),
}));
export const studentsRelations = relations(students, ({ one }) => ({
courses: one(courses, {
fields: [students.courseId],
references: [courses.id],
}),
}));
Seeding the Database
Before querying, we need to populate our database with some initial data.
import "dotenv/config";
import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "../db/schema";
import { createId } from "@paralleldrive/cuid2";
const sql = postgres(process.env.DATABASE_URL!);
// @ts-ignore
const db = drizzle(sql, { schema });
const main = async () => {
try {
console.log("Seeding database");
const courses = await db.insert(schema.courses).values([
{
name: "Learning Python",
startDate: new Date(),
endDate: new Date(),
},
{
name: "Learning Javascript",
startDate: new Date(),
endDate: new Date(),
},
{
name: "Learning Next",
startDate: new Date(),
endDate: new Date(),
},
{
name: "Learning Express",
startDate: new Date(),
endDate: new Date(),
},
{
name: "Learning Qwik",
startDate: new Date(),
endDate: new Date(),
},
]).returning({ id: schema.courses.id });
for (const course of courses) {
await db.insert(schema.students).values([
{
courseId: course.id,
firstName: "Tomas",
lastName: "Svojanovsky",
email: "[email protected]",
},
{
courseId: course.id,
firstName: "John",
lastName: "Doe",
email: "[email protected]",
},
{
courseId: course.id,
firstName: "Olivia",
lastName: "Green",
email: "[email protected]",
},
]);
}
console.log("Seeding finished");
} catch (error) {
console.error(error);
throw new Error("Failed to seed the database");
}
};
main();
Executing a Query
We will follow a similar process to what we used in one-to-one relationships, but this time we'll retrieve an array of students rather than a single student.
import db from "@/db/drizzle";
import { eq } from "drizzle-orm/sql/expressions/conditions";
import { courses } from "@/db/schema";
export default async function Page() {
const coursesData = await db.query.courses.findFirst({
where: eq(courses.id, "zbhezdvevr6le35bbra9t9vf"),
with: {
students: true,},
});
console.log(coursesData);
return (
One to Many Relation - Courses x Students);
}
Now, you'll notice that the output includes the associated students.
Conclusion
Thank you for taking the time to read this guide. I hope you found it insightful. If you have any feedback or comments, please feel free to share!
In Plain English 🚀
Thank you for being a part of the In Plain English community!
Before you leave:
- Be sure to clap and follow the writer ️👏️️
- Follow us on: X | LinkedIn | YouTube | Discord | Newsletter
- Explore more on our other platforms: Stackademic | CoFeed | Venture | Cubed
- Check out more content at PlainEnglish.io
In this video, we dive deep into many-to-many relationships using Drizzle ORM, illustrating how to effectively manage these complex associations.
This video explains how to integrate Drizzle ORM into a Next.js application, providing a practical project example for better understanding.