Convert SQL Foreign Keys to Prisma One-to-Many Relations

One-to-Many Relations

The most common SQL relationship is one-to-many: one parent record has many child records via a foreign key. Prisma models this with relation fields on both sides.

Example SQL

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT,
  author_id INTEGER NOT NULL,
  CONSTRAINT fk_posts_author FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE INDEX idx_posts_author_id ON posts (author_id);

Generated Prisma Schema

model User {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]

  @@map("users")
}

model Post {
  id       Int     @id @default(autoincrement())
  title    String
  content  String? @db.Text
  authorId Int     @map("author_id")
  author   User    @relation(fields: [authorId], references: [id], onDelete: Cascade)

  @@index([authorId])
  @@map("posts")
}

How the Conversion Works

1. Foreign key column (author_id) becomes a scalar field authorId Int with @map.
2. Relation field (author) is added as User type with @relation(...) specifying the link.
3. Back-reference (posts Post[]) is added on the parent model. The [] indicates one-to-many.
4. ON DELETE action maps to Prisma's onDelete parameter.

Referential Actions

SQL action	Prisma action	Behavior
ON DELETE CASCADE	Cascade	Delete children when parent is deleted
ON DELETE SET NULL	SetNull	Set foreign key to null
ON DELETE RESTRICT	Restrict	Prevent parent deletion
ON DELETE SET DEFAULT	SetDefault	Reset to default value
ON DELETE NO ACTION	NoAction	Database checks at transaction end

Naming the Relation

When a model has multiple relations to the same target, Prisma requires a relation name:

model Post {
  authorId    Int
  author      User @relation("PostAuthor", fields: [authorId], references: [id])
  reviewerId  Int?
  reviewer    User? @relation("PostReviewer", fields: [reviewerId], references: [id])
}

The converter detects multiple foreign keys to the same table and auto-generates descriptive relation names.

Index on Foreign Key

Prisma recommends an explicit @@index on foreign key fields for query performance. The converter adds this automatically, mirroring any CREATE INDEX on the FK column.