Working with migration script is not new to me, as I have used it all the time in dotnet and C# stacks using Microsoft Entity framework. The working principles are the same in Phinx.
Here are several assumptions before we start:
- I work on WINDOWS 10 operating system
- I used Vscode and its terminal,
- I have downloaded and installed PHP, Make sure path to PHP is set in the PATH environment
- I have downloaded and installed Composer (PHP package manager). Make sure the path to Composer is set in the path environment
- I have installed MySQL with username: root, password: admin
- I run command line (not bash , nor powershell)
- Create a project folder. eg. called MyApp
- Open the folder using VSCode
- Open a command line terminal
- Install phinx using composer by running: composer require robmorgan/phinx
- The command will create composer.json, and composer.lock, and also package folder called vendor. The composer files are to manage packages for the project. The The package and all its dependencies will be contained within the vendor folder. The executable is placed the inside vendor/bin (the phinx command). See the pictureelow
- The whole folder of vendor folder can be copied to another project if all you need just the migration scripts and you don't need the composer. Phinx can be installed system-wide like the composer, but when porting the app to another machine, the command will be lost, and we need the command to be embedded in the application bootstrap.
- Now every time you want to run migration scripts, you need to execute 'vendor/bin/phinx'. To make this shorter in the VSCODE terminal, you can set the variable in the
- select File->Preference-Settings, and
- type: terminal.integrated.profiles.windows,and on the right panel,
- click on Edit in settings.json.
- create a new element call PATH, and fill the value with computer PATH in addition to phinx folder ".\\vendor\\bin"
- reopen a new command line terminal in VSCODE, and you can type "phinx" directly, however, when you want to incorporate the command in the app bootstrap you will still need to use the relative path which is 'vendor/bin/phinx". But for the development, we can use phinx command directly.
- You can run 'phinx' command to see what command you can use. For data migrations, we will need this command
- init
- create
- seed:create
- migrate
- seed:run
- status (usefull to see what migration has been applied to DB)
- To initiate migration, first run: phinx init
- This command will create phinx.php in the root folder.
- Change the line 'migrations' => '%%PHINX_CONFIG_DIR%%/db/migrations', to 'migrations' => __DIR__ . '/db/migrations'
- Also change the line 'seeds' => '%%PHINX_CONFIG_DIR%%/db/seeds' to 'seeds' => __DIR__ . '/db/seeds'
- In the production, development, and testing element, change the name, user, and pass values to dabatase, user, password that are set in your mySQL database. You can configure different value for each environment, but for this article we just set it the same.
- Also create directory db/migrations, and db/seeds in the project directory.
- The reason to change to __DIR__ is that we are going to base the phinx migrations folder on the root directory of the projects. You can also go with the default, but you will need to set PHINX_CONFIG_DIR in the environment variables, either by setting in the computer environment variables, in the running terminal, or the VSCode settings.
- Next, is to create migration scripts. Run: phinx create [Name of the migration)
- The phinx create command requires a name, which must be a camelcase and cannot start with a number.
- For example we will run : phinx create FirstScript
- This will create our first migration script in the db/migrations folder
- The file name prefix with a timestamp and the name are changed to lower case with each word separated by underscore.
- The file is a php with the class with the name matching the the command argument, and with a single 'change' method.
- With this single method, the phinx can figure out the rollback command from this migration, if the migration script in written in the PHINX API syntax. However there are couple limitation of this behaviour:
- If the the table is created using raw sql query, the rollback will not attempt to figure out the rollback query during rollback operation.
- The same if is there is query execution in the script.
- If you prefer to handle the rollback manually in your migratin script, you can use two methods ('up' and 'down') instead of 'change' method. The 'up' method is run during migrate command, while the 'down' is executed during rollback command.
- I will give example for two approaches in two migrations, FirstScript and SecondScript
- FirstScript
- public function change(): void{$table = $this->table('users');$table->addColumn('username', 'string', ['limit' => 50])->addColumn('email', 'string', ['limit' => 100])->addColumn('password', 'string', ['limit' => 255])->addColumn('created_at', 'datetime', ['default' => 'CURRENT_TIMESTAMP'])->addColumn('updated_at', 'datetime', ['default' => 'CURRENT_TIMESTAMP', 'update' => 'CURRENT_TIMESTAMP'])->addIndex(['username'], ['unique' => true])->addIndex(['email'], ['unique' => true])->create();}
- SecondScript
- public function up(): void{$sql = 'CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY,orderItem VARCHAR(50) NOT NULL,quantity int NOT NULL,created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)';$this->execute($sql);}public function down():void{$sql = 'DROP TABLE orders';$this->execute($sql);}
- Next we will run the migration to update the DB with these two migratin scripts.
- Run: phinx migrate -e development
- The option -e is for the environment. The environment list is configured in the phinx.php
- you can check your DB, to make sure it is
- Notice that there is a phinxlog table, which track the migration scripts that are run already
- We will also create a third migration for report table, run: phinx create ThirdScript
- Before we run migrate again, we can check the status, run: phinx status
- You can see from the status, the ThirdScript has not been migrated yet and marked as 'down
- Run the the migration again: run: phinx migrate -e development
- Now all scripts has been run, Please check the DB, to make sure all table has been created including the new 'report' table.
- When you run the status again, all scripts are shown as up.
- You can rollback change by running: phinx rolback
- Please check 'report' table has gone, and the entry for the third script in the phinxlog is gone.
- You can also check the status again, the third script will be shown as 'down' again.
- You can rollback more that one, for example two previous scripts, by running: phinx rollback -t 2
- Next,for development, you would normally need some sample operational data. In phinx can put them in the seeder. In phinx configuration, there is an option to specify where you put the seeder folder. The default folder is '/db/seeds'.
- In this example, we will create a seeder called User Data, run : phinx seed:create UserData
- The file UserData.php will be created in seeder folder
- And the contain of file, and you can execute insertion query there.
- <?phpdeclare(strict_types=1);use Phinx\Seed\AbstractSeed;class UserData extends AbstractSeed{public function run(): void{
$table = $this->table('users');
// inserting only one row$singleRow = ['username' => 'bb','email' => 'bb@yahoo.com','password' => 'bb'];$table->insert($singleRow)->saveData();}} - You can execute the seeder by running: phinx seed:run
- Note:
- Seeder scripts are not track,
- when running the seeder scripts, all scripts in the seeders folder will be executed.
- If you want to run just a particularly seeder script, e.g UserData script, you can run: phinx seed:run -s UserData
- The last thing is to integration the migration into the application. We want everytime the application run it make sure it apples the migration script. There are pro and cons to apply the migration directly in production. Some prefer to apply the migration changes during CD/CI pipelines, and some others prefer to apply manually in the production to safeguard any potentially failure. In this article we will see how to integrate directly in the application. Note we don't normally integrate seeder in the application, as seeder intended for development only.
<?php
return
[
'paths' => [
'migrations' => '%%PHINX_CONFIG_DIR%%/db/migrations',
'seeds' => '%%PHINX_CONFIG_DIR%%/db/seeds'
],
'environments' => [
'default_migration_table' => 'phinxlog',
'default_environment' => 'development',
'production' => [
'adapter' => 'mysql',
'host' => 'localhost',
'name' => 'production_db',
'user' => 'root',
'pass' => '',
'port' => '3306',
'charset' => 'utf8',
],
'development' => [
'adapter' => 'mysql',
'host' => 'localhost',
'name' => 'development_db',
'user' => 'root',
'pass' => '',
'port' => '3306',
'charset' => 'utf8',
],
'testing' => [
'adapter' => 'mysql',
'host' => 'localhost',
'name' => 'testing_db',
'user' => 'root',
'pass' => '',
'port' => '3306',
'charset' => 'utf8',
]
],
'version_order' => 'creation'
];
- To integrate directly, you need to insert the migration command in the application bootstrap, which run every application start. The migration will need Phinx autoloader and Phinx configuration file, and below example is a bootstrap.php located in the root of the project
- <?phprequire_once __DIR__ . '/vendor/autoload.php'; // Path to Phinx autoloader// Determine the environment based on server variables (e.g., domain name)if ($_SERVER['SERVER_NAME'] === 'localhost') {$desiredEnvironment = 'development'; // Development environment} else {$desiredEnvironment = 'production'; // Production environment}// ... [Further logic remains the same as previous examples]// Run Phinx migrations for the determined environment$phinxCommand = __DIR__ . "/vendor/bin/phinx migrate -e $desiredEnvironment";// Capture output using `exec()`$output = [];exec($phinxCommand, $output, $returnCode);// Check if the command executed successfullyif ($returnCode === 0) {// Output captured lines (for example, print them)foreach ($output as $line) {echo $line . PHP_EOL;}} else {// Handle error if the command failsecho "Error occurred while running Phinx migrations." . PHP_EOL;}?>
- To check if this working, delete the tables in the DB
- Then, run the bootsrap.php: php bootstrap.php
- Check the status: phinx status
- Lastly check the DB, make sure all tables are created.
Note:
- The migration is important tool to share the database changes between developers working on the same project. It is also important aspect when push the DB changes from the development to the production. We have not touch on the topic fully of the migration to production or CD/CI pipeline yet.
- Phynx come with webbrowser interface, where you can see the status, migrate and rollback. Run the server using:
php -S localhost:8000 vendor/robmorgan/phinx/app/web.php
- to check the status, then just browser to http://localhost:8000
- to migrate in the browser address, type: http://localhost:8000/migrate
- to rollback in the browser address, type: http://localhost:8000/rollback
- Documentation to use phynx ca be found here:
- https://book.cakephp.org/phinx/0/en/index.html
