DT
Drizzle Team•13h ago
khalobert

Best approach for data migration: moving data from two columns to JSONB

Hi Drizzle community! 👋 I'm working on a schema migration where I need to: 1. Move data from two existing string columns (old_col1, old_col2) into a new JSONB column (new_jsonb_col) 2. Drop the old columns 3. Ensure no schema drift My current idea: 1. Update TypeScript schema (add JSONB col, remove old cols) 2. Run drizzle-kit generate 3. Manually edit the generated migration to add data transformation steps: 
   -- Add new column
   ALTER TABLE "table" ADD COLUMN "new_jsonb_col" JSONB;
   --> statement-breakpoint
   
   -- Transform data
   UPDATE "table" SET "new_jsonb_col" = jsonb_build_object(
       'old_col1_value', "old_col1",
       'old_col2_value', "old_col2"
   );
   --> statement-breakpoint
   
   -- Make NOT NULL
   ALTER TABLE "table" ALTER COLUMN "new_jsonb_col" SET NOT NULL;
   --> statement-breakpoint
   
   -- Drop old columns
   ALTER TABLE "table" DROP COLUMN "old_col1";
   --> statement-breakpoint
   ALTER TABLE "table" DROP COLUMN "old_col2";
   
   -- Add new column
   ALTER TABLE "table" ADD COLUMN "new_jsonb_col" JSONB;
   --> statement-breakpoint
   
   -- Transform data
   UPDATE "table" SET "new_jsonb_col" = jsonb_build_object(
       'old_col1_value', "old_col1",
       'old_col2_value', "old_col2"
   );
   --> statement-breakpoint
   
   -- Make NOT NULL
   ALTER TABLE "table" ALTER COLUMN "new_jsonb_col" SET NOT NULL;
   --> statement-breakpoint
   
   -- Drop old columns
   ALTER TABLE "table" DROP COLUMN "old_col1";
   --> statement-breakpoint
   ALTER TABLE "table" DROP COLUMN "old_col2";
Questions: 1. Is this the recommended approach for complex data migrations? 2. Should I use drizzle-kit generate --custom instead? 3. Are there any gotchas I should be aware of? I appreciate your support 🙏
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?