patchr

MySQL version control and team collaboration

The latest version of this documentation can be found at docs.0plus1.com/envato/patchr

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.

Features

  • 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

tl;dr

Reading the whole documentation is highly recommended

If you want to jump right into the action:

  • Edit the database section of the configuration file (patchr/patchr_conf.php). Leave the rest of the options to their default values.
  • Upload patchr to the public directory of your webserver.
  • Use the CLI or the GUI to add and apply patches.

Configuration

All configuration variables are stored into the

./patchr/patchr_conf.php
file. Open it in any text editor of your choice and edit the configuration variables.

Options:

Database

host

Database host

user

Database user

password

Database password

database

Database name. Create one or use an existing one. Patchr needs to create a single table.

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.

timeout

Override MySQLi default timeout. Useful for large datasets. The default 500 will work in almost every case.

multiple

Spawn multiple connections, one for each patch. If using this option set a smaller timeout.

Security

allowed_gui_domains

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.

Debug

verbose

If set to true adds a verbose output to the commands results.

Naming

patches_dir

Directory that will contain the patches. This is relative to the patchr.class.php file.

prefix

This is the prefix that you will append to each patch file.

digits

This is the amount of digits that patchr will expect on the filename.

extension

Extension of the patches file. Usually .sql is the reasonable one.

CLI

offset

Offset for the command line parameters. Better described in the frameworks section.

Routes

base

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.

Advanced

skip_patches

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')

Install

Basic

Configure all the options and upload to the server. Patchr will create the required table and setup everything automagically.

Advanced

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.

CLI

applyDatabasePatches

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.

reservePatches

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
Patchr takes care of skipping empty patches and will come back to them once filled with SQL commands.

addNewPatch

php patchr.php addNewPatch [string]

Creates a new patch containing the provided [string] SQL in quotes. Be very careful about escaping the SQL

Unescaped SQL in command line are very dangerous.

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

getUnappliedPatches

php patchr.php getUnappliedPatches

Returns a list of all currently unapplied patches.

getLatestAppliedPatch

php patchr.php getLatestAppliedPatch

Returns the name of the latest applied patch.

Responsive image

GUI

The GUI simply exposes the command line into a neat and clean interface. Please refer to the CLI for a comprehensive explanation of each command.
The GUI doesn't require any username/password as it can only run in the allowed_gui_domains.

To access the GUI, hit the patchr.php file in your browser.

Have you been greeted with a 'Domain is not allowed to run in GUI mode.' exception?
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:

Responsive image

To access the GUI within a framework, please refer to the section.

You can play with a GUI demo at demos.0plus1.com

Frameworks

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:

$patchr_config_override = array();
This makes it easy to programmatically change certain options and some of its usages can be found in the advanced section.

You will find an ever updated list of wrappers for common php frameworks on github. Please feel free to contribute!

Why are the wrappers on github?:
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.

Wrappers are not covered by the product support. Frameworks are often complicated and we cannot offer any support regarding wrappers.

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

Advanced usages

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