👋 Hey! New to the slack, love Prisma. I’m using Prisma Migrate to maintain our database migrations. Has anyone done a migration to move a foreign key from one side of a 1-1 relationship to the other model? Ideally this is straightforward, but it looks like I’d have to do a manual migration which is pretty tricky

Hey @Jeff Barg 👋 In this case you would need to run a script to convert all foreign keys from on table the other one where the id’s are reversed. Currently no straightforward way comes to mind though for this one if you already have existing data.

Hello @Jeff Barg, this can be done with the following steps: 1. Optional: make sure to have some relations in your DB to validate the migration is working properly 2. Generate the migration to move the relation foreign key from one model to the other with

--create-only

3. In the generated

migration.sql

file, before the original foreign key column is dropped, write a SQL to insert the required values to keep the relations in place. Example: changing from

user.profileId

to

profile.userId

. Initial schema:

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["microsoftSqlServer"]
}

datasource db {
  provider = "sqlserver"
  url      = env("DATABASE_URL")
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User?   @relation(fields: [authorId], references: [id])
  authorId  Int?
}

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
  profileId Int?
  profile Profile? @relation(fields: [profileId], references: [id])
}

model Profile {
  id    Int     @id @default(autoincrement())
  name  String?
  user  User?
}

New schema:

generator client {
  provider        = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  content   String?
  published Boolean @default(false)
  author    User?   @relation(fields: [authorId], references: [id])
  authorId  Int?
}

model User {
  id      Int      @id @default(autoincrement())
  email   String   @unique
  name    String?
  posts   Post[]
  profile Profile?
}

model Profile {
  id   Int     @id @default(autoincrement())
  name String?

  userId Int?
  user   User? @relation(fields: [userId], references: [id])
}

Custom SQL to insert the foreign key column values:

UPDATE "Profile"
SET
	"userId" = "User".id
FROM
	"User"
WHERE
	"User"."profileId" = "Profile".id;

Migration with custom SQL to backfill the new relation scalar column:

/*
  Warnings:

  - You are about to drop the column `profileId` on the `User` table. All the data in the column will be lost.
  - The migration will add a unique constraint covering the columns `[userId]` on the table `Profile`. If there are existing duplicate values, the migration will fail.

*/
-- DropForeignKey
ALTER TABLE "User" DROP CONSTRAINT "User_profileId_fkey";

-- DropIndex
DROP INDEX "User_profileId_unique";

-- AlterTable
ALTER TABLE "Profile" ADD COLUMN     "userId" INTEGER;

-- REPLACE THE FOREIGN KEYS TO KEEP RECORDS LINKED BEFORE DROPPING THE OLD FOREIGN RELATION COLUMN

UPDATE "Profile"
SET
	"userId" = "User".id
FROM
	"User"
WHERE
	"User"."profileId" = "Profile".id;

-- AlterTable
ALTER TABLE "User" DROP COLUMN "profileId";

-- CreateIndex
CREATE UNIQUE INDEX "Profile_userId_unique" ON "Profile"("userId");

-- AddForeignKey
ALTER TABLE "Profile" ADD FOREIGN KEY ("userId") REFERENCES "User"("id") ON DELETE SET NULL ON UPDATE CASCADE;

This is incredibly helpful, thanks so much @Alberto Perdomo. I will try this ASAP (I found a workaround for my original problem which required moving the foreign key, but want to move it to clean up the relationship)