Many-to-Many Relations in Drizzle ORM

Many-to-Many Tables in Drizzle

Many-to-many relationships in SQL use a junction table (also called a join table or bridge table) with foreign keys to both related tables. The converter handles these by generating a Drizzle table with composite primary keys and references.

Example SQL

CREATE TABLE students (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE courses (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200) NOT NULL
);

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_id INTEGER NOT NULL REFERENCES courses(id),
  enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id, course_id)
);

Generated Drizzle Schema

import { integer, pgTable, primaryKey, serial, timestamp, varchar } from "drizzle-orm/pg-core";
import { sql } from "drizzle-orm";

export const students = pgTable("students", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 100 }).notNull(),
});

export const courses = pgTable("courses", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 200 }).notNull(),
});

export const enrollments = pgTable("enrollments", {
  studentId: integer("student_id").notNull().references(() => students.id),
  courseId: integer("course_id").notNull().references(() => courses.id),
  enrolledAt: timestamp("enrolled_at").default(sql`CURRENT_TIMESTAMP`),
}, (table) => [
  primaryKey({ columns: [table.studentId, table.courseId] }),
]);

Key Points

1. Composite Primary Key: The PRIMARY KEY (student_id, course_id) becomes a primaryKey() constraint in the table's third argument (the constraints callback). The primaryKey function must be imported from the dialect module.

2. Foreign Keys: Both foreign key columns get .references() chains pointing to their respective parent tables.

3. No individual .primaryKey(): When a composite primary key exists, individual columns do not get the .primaryKey() chain. Instead, the composite constraint is used.

4. Extra columns: Junction tables often have additional columns like enrolled_at or role. These are handled normally alongside the key columns.

Adding the Relations API

To use Drizzle's relational query API with many-to-many tables, you would add separate relations() definitions. This is beyond what the SQL converter generates but is the recommended next step after conversion.