Skip to content

Commit f500011

Browse files
NiallJoeMaherclaude
NiallJoeMaher
and
claude
committed
fix: update notification migration to convert column types before adding FK constraints
The migration was failing because postId (text) and commentId (integer) couldn't reference posts.id and comments.id (both uuid). This updates the migration to: - Convert postId from text to uuid using legacy_post_id lookup - Convert commentId from integer to uuid using legacy_comment_id lookup - Make the migration idempotent (safe to run multiple times) - Update snapshot to reflect the new column types Co-Authored-By: Claude Opus 4.5 <[email protected]>
1 parent 28297b3 commit f500011

2 files changed

Lines changed: 89 additions & 6 deletions

File tree

‎drizzle/0019_update-notification-fk.sql‎

Lines changed: 87 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,89 @@
1-
ALTER TABLE "Notification" DROP CONSTRAINT "Notification_postId_Post_id_fk";
1+
-- Update Notification table to reference new unified schema
2+
-- This migration is idempotent - safe to run multiple times
3+
-- 1. Drop old FK constraints (if they exist)
4+
-- 2. Convert column types (text/integer -> uuid) if needed
5+
-- 3. Add new FK constraints (if they don't exist)
6+
7+
-- Drop old foreign key constraints (safe - uses IF EXISTS)
8+
ALTER TABLE "Notification" DROP CONSTRAINT IF EXISTS "Notification_postId_Post_id_fk";
9+
ALTER TABLE "Notification" DROP CONSTRAINT IF EXISTS "Notification_commentId_Comment_id_fk";
210
--> statement-breakpoint
3-
ALTER TABLE "Notification" DROP CONSTRAINT "Notification_commentId_Comment_id_fk";
11+
12+
-- ============================================
13+
-- MIGRATE postId: text -> uuid (if needed)
14+
-- ============================================
15+
DO $$
16+
BEGIN
17+
-- Only migrate if postId is still text type
18+
IF EXISTS (
19+
SELECT 1 FROM information_schema.columns
20+
WHERE table_name = 'Notification'
21+
AND column_name = 'postId'
22+
AND data_type = 'text'
23+
) THEN
24+
-- Add new uuid column
25+
ALTER TABLE "Notification" ADD COLUMN "postId_new" uuid;
26+
27+
-- Migrate data using legacy_post_id lookup
28+
UPDATE "Notification" n
29+
SET "postId_new" = p.id
30+
FROM posts p
31+
WHERE p.legacy_post_id = n."postId";
32+
33+
-- Drop old column and rename new one
34+
ALTER TABLE "Notification" DROP COLUMN "postId";
35+
ALTER TABLE "Notification" RENAME COLUMN "postId_new" TO "postId";
36+
END IF;
37+
END $$;
438
--> statement-breakpoint
5-
ALTER TABLE "Notification" ADD CONSTRAINT "Notification_postId_posts_id_fk" FOREIGN KEY ("postId") REFERENCES "public"."posts"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
6-
ALTER TABLE "Notification" ADD CONSTRAINT "Notification_commentId_comments_id_fk" FOREIGN KEY ("commentId") REFERENCES "public"."comments"("id") ON DELETE cascade ON UPDATE cascade;
39+
40+
-- ============================================
41+
-- MIGRATE commentId: integer -> uuid (if needed)
42+
-- ============================================
43+
DO $$
44+
BEGIN
45+
-- Only migrate if commentId is still integer type
46+
IF EXISTS (
47+
SELECT 1 FROM information_schema.columns
48+
WHERE table_name = 'Notification'
49+
AND column_name = 'commentId'
50+
AND data_type = 'integer'
51+
) THEN
52+
-- Add new uuid column
53+
ALTER TABLE "Notification" ADD COLUMN "commentId_new" uuid;
54+
55+
-- Migrate data using legacy_comment_id lookup
56+
UPDATE "Notification" n
57+
SET "commentId_new" = c.id
58+
FROM comments c
59+
WHERE c.legacy_comment_id = n."commentId";
60+
61+
-- Drop old column and rename new one
62+
ALTER TABLE "Notification" DROP COLUMN "commentId";
63+
ALTER TABLE "Notification" RENAME COLUMN "commentId_new" TO "commentId";
64+
END IF;
65+
END $$;
66+
--> statement-breakpoint
67+
68+
-- ============================================
69+
-- ADD NEW FOREIGN KEY CONSTRAINTS (if not exist)
70+
-- ============================================
71+
DO $$
72+
BEGIN
73+
IF NOT EXISTS (
74+
SELECT 1 FROM pg_constraint WHERE conname = 'Notification_postId_posts_id_fk'
75+
) THEN
76+
ALTER TABLE "Notification" ADD CONSTRAINT "Notification_postId_posts_id_fk"
77+
FOREIGN KEY ("postId") REFERENCES "public"."posts"("id") ON DELETE cascade ON UPDATE cascade;
78+
END IF;
79+
END $$;
80+
--> statement-breakpoint
81+
DO $$
82+
BEGIN
83+
IF NOT EXISTS (
84+
SELECT 1 FROM pg_constraint WHERE conname = 'Notification_commentId_comments_id_fk'
85+
) THEN
86+
ALTER TABLE "Notification" ADD CONSTRAINT "Notification_commentId_comments_id_fk"
87+
FOREIGN KEY ("commentId") REFERENCES "public"."comments"("id") ON DELETE cascade ON UPDATE cascade;
88+
END IF;
89+
END $$;

‎drizzle/meta/0019_snapshot.json‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2847,13 +2847,13 @@
28472847
},
28482848
"postId": {
28492849
"name": "postId",
2850-
"type": "text",
2850+
"type": "uuid",
28512851
"primaryKey": false,
28522852
"notNull": false
28532853
},
28542854
"commentId": {
28552855
"name": "commentId",
2856-
"type": "integer",
2856+
"type": "uuid",
28572857
"primaryKey": false,
28582858
"notNull": false
28592859
},

0 commit comments

Comments
 (0)