Best practices & gotchas
While Patchr will always try to revert a patch with malformed or unappliable SQL, there is one thing to always keep in mind:
Implicit commits
Keep in mind that some SQL statements are not transaction safe. For effortless usage of Patchr, ensure only 1 non-transaction safe statement exists in a single patch
What does that mean for you?
It means that if you add in the same patch two ALTER TABLES statements and the second one cannot be applied, the first one will still be applied, resulting in an incomplete patch.
This is the complete lists of statements that cause an implicit commit.
DON'T
Patch 1
ALTER TABLE `companies_history` ADD COLUMN `action_at` DATE NOT NULL AFTER `action`;
ALTER TABLE `companies_history` CHANGE COLUMN `company_contact_id` `contacted` VARCHAR(255) NOT NULL AFTER `job_id`;
DO
Patch 1
ALTER TABLE `companies_history` ADD COLUMN `action_at` DATE NOT NULL AFTER `action`;
Patch 2
ALTER TABLE `companies_history` CHANGE COLUMN `company_contact_id` `contacted` VARCHAR(255) NOT NULL AFTER `job_id`;
DO
Patch 1
UPDATE `users` SET `complete`=NULL WHERE `id` = 87;
UPDATE `users` SET `complete`=NULL WHERE `id`= 0;
These two statements, being transaction safe, can be part of the same patch, if one fails, both won't be committed.
With that in mind, these are our golden rules for patches handling
- One implicit commit statement in each patch
- Always test patches locally before committing
- Only apply database changes through patchr, this will ensure that no issue will raise on deployment
- With a tool such as MySQL workbench make your changes
- Copy the preview SQL without applying the change
- Paste into a new patch
- Apply patch
- Reserve patches to avoid conflicts in a team environment