DT
Drizzle Team•2w ago
LifeLineLogics

Mysql Conditional Migration statements

I'm running into an issue where migrations are sometimes applied partially and I need to re run them using conditional statements. For example 
-- Check if name column exists before renaming
SET @column_exists = 0;
SELECT COUNT(*) INTO @column_exists
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'business_type'
  AND COLUMN_NAME = 'name';

-- Only rename if column exists
SET @sql = IF(@column_exists > 0,
  'ALTER TABLE `business_type` RENAME COLUMN `name` TO `name_en`;',
  'SELECT "Column name does not exist, skipping rename";'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Continue with other operations regardless
ALTER TABLE `business_type` DROP INDEX `business_type_name_unique`;--> statement-breakpoint
ALTER TABLE `business_type` ADD `name_es` varchar(100) NOT NULL;--> statement-breakpoint
ALTER TABLE `business_type` ADD CONSTRAINT `business_type_name_en_unique` UNIQUE(`name_en`);--> statement-breakpoint
ALTER TABLE `business_type` ADD CONSTRAINT `business_type_name_es_unique` UNIQUE(`name_es`);
-- Check if name column exists before renaming
SET @column_exists = 0;
SELECT COUNT(*) INTO @column_exists
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'business_type'
  AND COLUMN_NAME = 'name';

-- Only rename if column exists
SET @sql = IF(@column_exists > 0,
  'ALTER TABLE `business_type` RENAME COLUMN `name` TO `name_en`;',
  'SELECT "Column name does not exist, skipping rename";'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Continue with other operations regardless
ALTER TABLE `business_type` DROP INDEX `business_type_name_unique`;--> statement-breakpoint
ALTER TABLE `business_type` ADD `name_es` varchar(100) NOT NULL;--> statement-breakpoint
ALTER TABLE `business_type` ADD CONSTRAINT `business_type_name_en_unique` UNIQUE(`name_en`);--> statement-breakpoint
ALTER TABLE `business_type` ADD CONSTRAINT `business_type_name_es_unique` UNIQUE(`name_es`);
However, this fails when attempting to migrate
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?