How Can PHP Synchronize MySQL Databases From Your Local Environment to the Production Quickly - PHP MySQL Scheman package blog

By Shabeer ALi M

<email contact>

In this article you will learn:

What is the Problem of Database Schema Synchronization

Usually, what we used to do when we need to update a database schema is to create an SQL script when we release a new application update and then we run the SQL script. The risk of doing without much testing is that we may miss some columns and our code may not work.

What Can be a Solution to Implement a Secure Database Schema Synchronization Process

One possible solution to this problem is to use any MySQL synchronization tools like SQL Examiner, dbForge, etc. And most of these tools are paid.

What is the PHP MySQL Scheman Package

The PHP MySQL scheman package, is a simple MySQL schema synchronization tool. It can execute SQL queries automatically based on the differences between two database schemata.

Installation

If you use Composer, you can install MySQL Scheman with the following command: composer require smart-php/mysql-scheman

Or alternatively, include a dependency for MySQL Scheman in your composer.json file. For example:

{
 "require-dev": {
 "smart-php/mysql-scheman": "dev-master"
 }
}

Basic Usage

The first step is to create a configuration file either in JSON or XML where you can put your database configuration details.

config.xml

<config> 
 <hostname>127.0.0.1</hostname>
 <username>root</username>
 <password>mypassword</password>
 <database>test</database>
 <driver>pdo</driver>
</config>

Next, you can create a schema file and add your tables and fields' information.

myscheman.xml

<?xml version="1.0"?>
<database name="shcool">
 <table name="students">
 <columns Field="id" Type="int(11)" Null="NO" Key="PRI" Default="" Extra="auto_increment" Comment=""/>
 <columns Field="name" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" Comment=""/>
 </table>
 <table name="teachers">
 <columns Field="id" Type="int(11)" Null="NO" Key="PRI" Default="" Extra="auto_increment" Comment=""/>
 <columns Field="name" Type="varchar(255)" Null="NO" Key="" Default="" Extra="" Comment=""/>
 </table>
</database>

Syncronization

By running the --sync command MySQL Scheman will find the changes required in your server and create an SQL Query and it will execute. In the above example imagine if your database doesn't have table teachers. With sync, it will create a table in the database with fields mentioned in the schema file.

./vendor/bin/scheman --config config.xml --sync myscheman.xml

Reverse Engineering

For existing database structures MySQL Scheman supports reverse engineering of your current database schema. By using command export you can create myscheman.xml file with your existing database.

./vendor/bin/scheman --config config.xml --sync myscheman.xml

Download and Install the PHP MySQL Scheman Package Using PHP Composer

You can download or install the PHP MySQL Scheman package using PHP Composer tool by going to this download page to get the package code. That page also contains instructions on how to install package using PHP Composer from the PHP Classes site.