PHP database diff tool
This tool is written in PHP and is using doctrine to create diffs between database schemes. You can create a diff between a sql schema dump and a database. The output format is either a list of ALTER-SQL commands or a pretty text output. Another option is to get the diff programatically and use the result in your code.
Installation
Use composer
composer require gared/database-structure-diff
or clone this repository
git clone https://github.com/gared/database-structure-diff.git
composer install --no-dev
Configuration
Copy the file config.example.php or copy this example in a file
<?php
return [
[
[
'dbname' => 'database_name',
'user' => 'username',
'password' => 'password',
'host' => 'hostname',
'driver' => 'pdo_mysql',
],
[
'dbname' => 'database_name',
'path' => 'path/to/file.sql',
'driver' => 'file',
],
],
];
You can also define multiple groups to make diff
<?php
return [
[
[
'dbname' => 'database_name',
'user' => 'username',
'password' => 'password',
'host' => 'hostname',
'driver' => 'pdo_mysql',
],
[
'dbname' => 'database_name',
'path' => 'path/to/file.sql',
'driver' => 'file',
],
],
[
[
'dbname' => 'database_name',
'user' => 'username',
'password' => 'password',
'host' => 'hostname',
'driver' => 'pdo_mysql',
],
[
'dbname' => 'database_name',
'user' => 'username',
'password' => 'password',
'host' => 'hostname',
'driver' => 'pdo_mysql',
],
],
];
For more database configuration read the doctrine configuration: https://www.doctrine-project.org/projects/doctrine-dbal/en/stable/reference/configuration.html
Usage
If you cloned this repository execute
php bin/console database:calculate-diff config.php
or if you installed it with composer
php vendor/gared/database-structure-diff/bin/console database:calculate-diff config.php
or use the option "output-file" to store an ALTER script to a file
php bin/console database:calculate-diff config.php --output-file alter.sql
Example output
$ php vendor/gared/database-structure-diff/bin/console database:calculate-diff config.php
Database: example@10.10.1.1 => File: strcture.sql
-------------------------------------------------
New tables
==========
* user: user_id, name
Removed tables
==============
* player
Changed tables
==============
team
----
Added columns
* team_short_name: String 10
Changed columns
path
* length: 100 => 255
Removed columns
* user_id
Removed indexes
* FK_team_user
Added foreign keys
* FK_C4E0A61F3A35FDA4: (team_type_id) => team_type (team_type_id)
group
-----
Renamed indexes
* fk_group => fk_group_idx
Use in your code
You can also work with the response of the diff in your code. Example code:
<?php
require __DIR__ . '/vendor/autoload.php';
$fromConnection = new \DatabaseDiffer\Model\Config\Connection([
'dbname' => 'database_name',
'user' => 'username',
'password' => 'password',
'host' => 'hostname',
'driver' => 'pdo_mysql',
]);
$toConnection = new \DatabaseDiffer\Model\Config\Connection([
'dbname' => 'database_name',
'path' => 'path/to/file.sql',
'driver' => 'file',
]);
$group = new \DatabaseDiffer\Model\Config\Group($fromConnection, $toConnection);
$diffService = new \DatabaseDiffer\Service\SchemaDiffService($group);
$schemaDiff = $diffService->getSchemaDiff();
// $schemaDiff has all informations about changed tables, sequences, etc.
Supported Platforms
- Doctrine supported databases (MySQL, MariaDB, Oracle, etc.)
- You need at least PHP 7.4