DT

Drizzle Team•4mo ago

khalo

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_col1old_col1, old_col2old_col2) into a new JSONB column (new_jsonb_colnew_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 generatedrizzle-kit generate
3. Manually edit the generated migration to add data transformation steps:```sql-- Add new columnALTER TABLE "table" ADD COLUMN "new_jsonb_col" JSONB;--> statement-breakpoint-- Transform dataUPDATE "table" SET "new_jsonb_col" = jsonb_build_object( 'old_col1_value', "old_col1", 'old_col2_value', "old_col2");--> statement-breakpoint-- Make NOT NULLALTER TABLE "table" ALTER COLUMN "new_jsonb_col" SET NOT NULL;--> statement-breakpoint-- Drop old columnsALTER TABLE "table" DROP COLUMN "old_col1";--> statement-breakpointALTER TABLE "table" DROP COLUMN "old_col2";```

Questions:

1. Is this the recommended approach for complex data migrations?
2. Should I use drizzle-kit generate --customdrizzle-kit generate --custom instead?
3. Are there any gotchas I should be aware of?

I appreciate your support