Creating relationships across tables is a crucial aspect of working with SQL databases. Previously, this series focused on using the Drizzle ORM to create simple relationships, such as one-to-one and many-to-one relationships. In this article, we learn about many-to-many relationships, which are slightly more complex.
Introducing many-to-many relationships
A many-to-many relationship is necessary when multiple records in one table connect to multiple records in another table. A good example of this is the relationship between categories and articles. A single category can relate to various articles, and likewise, an article can belong to multiple categories. For instance, the article you are reading is listed under both SQL and JavaScript categories.
Until now, with the Drizzle ORM, we have set up one-to-one and many-to-one relationships. We accomplished this by using a basic column containing a foreign key corresponding to a row in the related table.
The design becomes more complex when linking a specific article to multiple categories. We can’t put multiple IDs in the category_id column. To address this, we need to create a joining table.
Creating the categories_articles table allows us to store the connections between individual articles and categories.
Many-to-many relationships with Drizzle ORM
The first step to implementing a many-to-many relationship with the Drizzle ORM is to modify the database schema.
1import {
2 serial,
3 text,
4 integer,
5 pgTable,
6 primaryKey,
7} from 'drizzle-orm/pg-core';
8
9export const articles = pgTable('articles', {
10 id: serial('id').primaryKey(),
11 title: text('title').notNull(),
12 // ...
13});
14
15export const categories = pgTable('categories', {
16 id: serial('id').primaryKey(),
17 name: text('title').notNull(),
18});
19
20export const categoriesArticles = pgTable(
21 'categories_articles',
22 {
23 categoryId: integer('category_id')
24 .notNull()
25 .references(() => categories.id),
26 articleId: integer('article_id')
27 .notNull()
28 .references(() => articles.id),
29 },
30 (columns) => ({
31 pk: primaryKey({ columns: [columns.categoryId, columns.articleId] }),
32 }),
33);
34
35// ...
36
37export const databaseSchema = {
38 articles,
39 categories,
40 categoriesArticles,
41 // ...
42};