MySQL version control and team collaboration
Patchr allows you to version control your database changes, allowing a team to easily share and review database changes.
Database version control is a very important process of modern web development, providing an accurate history of schema and data changes.
Most modern framework offers some form of version control under the term "migrations".
Patchr uses a different approach, allowing you to commit raw SQL, more compatible with legacy system and easier to understand by any new dev coming onboard.
Patchr is developed with deployment in mind, exposing a comprehensive CLI.
- MySQL version control solution
- Developed for team collaboration
- Powerful CLI for deployment
- Powerful GUI for development
- Supports any numbers of patches
- Tested in the real world: currently used in large scale corporate applications
- Framework agnostic: can be used with any framework
- GIT submodule ready
- Fast and secure
If you want to jump right into the action:
All configuration variables are stored into the
file. Open it in any text editor of your choice and edit the configuration variables.
Database name. Create one or use an existing one. Patchr needs to create a single table.
Table name. Defaults to patchr_dbpatches. Usually there is no need to update this except in cases such as the ones outlined in the advanced section.
Override MySQLi default timeout. Useful for large datasets. The default 500 will work in almost every case.
Spawn multiple connections, one for each patch. If using this option set a smaller timeout.
This option, is an array list of all the domain names on which the GUI can run. Ideally you should never run the GUI on production, just use the CLI. It defaults to localhost and patchr.local. Change it to suit your dev environment. Please do not add protocol or subdomains, just the top level domain.
If set to true adds a verbose output to the commands results.
With the default options filenames will be: patch-xxxxx.sql, usually there is no reason to change these.
Directory that will contain the patches. This is relative to the patchr.class.php file.
This is the prefix that you will append to each patch file.
This is the amount of digits that patchr will expect on the filename.
Extension of the patches file. Usually .sql is the reasonable one.
Base url to patchr.php file. This option is used exclusively for the GUI to construct internal links. This value is the base file that will receive the GET requests.
This options, forces patchr to skip certain patches. You might want to commit the existing structure of a database without patchr actually applying it. This option allows you to explicitly skip any number of patches. It accepts an array of filenames: array('patch-00001.sql','patch-00002.sql')
Configure all the options and upload to the server. Patchr will create the required table and setup everything automagically.
A better way to work with patchr is to version it inside a git repository (such as Bitbucket) and use it as a submodule of your projects.
git submodule add [email protected]:user/your/repo.git ext/patchr/.
You can then initialise the module after cloning with
git submodule update --init --recursive
All frameworks example assume you are using the ext/patchr directory.
php patchr.php applyDatabasePatches
Attempts to apply all the unapplied database patches. If the patch contains invalid SQL it will revert the transaction and will print the SQL error. Please read carefully the gotcha section for more information.
php patchr.php reservePatches [int]
Creates an [int] amount of empty patches in the patches folder. Useful for teams to avoid merging conflicts in GIT.
- Developer one plans to do some database changes
- Developer one reserves 10 patches
- Developer one commits these empty patches to the database
- Developer two pulls and notices these new patches
- Developer two will add his own patches after these ones
- Developer one will add SQL to these empty patches at his own leisure
php patchr.php addNewPatch [string]
Creates a new patch containing the provided [string] SQL in quotes. Be very careful about escaping the SQL
It's discouraged to use this command to create complex patches. The GUI provides a safer alternative to this command. Here is an example workflow.
- php patchr.php addNewPatch ""
- Open the newly created patch in your favorite text editor
- Edit or paste the required SQL in it and save
php patchr.php getUnappliedPatches
Returns a list of all currently unapplied patches.
php patchr.php getLatestAppliedPatch
Returns the name of the latest applied patch.
To access the GUI, hit the patchr.php file in your browser.
Head to the configuration session and change the allowed_gui_domains options in the security section.
Once inside you will be greeted by this clean graphical interface:
To access the GUI within a framework, please refer to the section.
You can play with a GUI demo at demos.0plus1.com
You might want to add patchr to your favorite framework, we've made it easy to achieve this goal.
By default patchr load the configuration options found int the patchr_conf.php file, you can ignore the option in that file by passing a:
This makes it easy to programmatically change certain options and some of its usages can be found in the advanced section.
$patchr_config_override = array();
You will find an ever updated list of wrappers for common php frameworks on github. Please feel free to contribute!
because framework are often updated and these updates might break the wrapper. We want to try to keep them always updated and working as well as giving the opportunity to commit your own wrappers for your favorite framework.
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:
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.
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`;
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`;
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
By using the config_override array, you can run several parallel instances of patchr from the same codebase.
This is particularly useful to track submodules database changes without polluting the codebase with copies of patchr.
You can achieve this by creating different patchr wrappers files, each with a different table.
// wrapper1.php $patchr_config_override = array(); $patchr_config_override['db']['table'] = 'patchr_one'; $patchr_config_override['naming']['patches_dir'] = './database-patches-one'; // Your other options ... require_once('./ext/patchr/patchr.php'); // wrapper2.php $patchr_config_override = array(); $patchr_config_override['db']['table'] = 'patchr_two'; $patchr_config_override['naming']['patches_dir'] = './database-patches-two'; // Your other options ... require_once('./ext/patchr/patchr.php');
As you can see, you will have two independent tables and patches folders, running in parallel from the same codebase