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_col1
,
old_col2
old_col2
) into a new JSONB column (
new_jsonb_col
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
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
drizzle-kit generate --custom
instead? 3. Are there any gotchas I should be aware of?