Author: Shabeer ALi M
Updated on: 2021-10-21
Posted on: 2021-10-21
Package: PHP MySQL Scheman
The PHP MySQL Scheman package can help you to perform this task quickly.
Read this tutorial article on how to use this package to perform database schema synchronization in practice.
In this article you will learn:
What is the Problem of Database Schema Synchronization
What Can be a Solution to Implement a Secure Database Schema Synchronization Process
What is the PHP MySQL Scheman Package
Installation
Basic Usage
Syncronization
Reverse Engineering
Download and Install the PHP MySQL Scheman Package Using PHP Composer
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.
You need to be a registered user or login to post a comment
1,586,460 PHP developers registered to the PHP Classes site.
Be One of Us!
Login Immediately with your account on:
Comments:
No comments were submitted yet.