Tuesday 19 December 2023

PHP DB Migration with Phinx



Hi, see you again here I will write about my experience working with PHP migration scripts. To make the story short, I was involved in a project working on a PHP application project. The team is new to bitbucket, migration scripts, and CD/CI. So part of the job was to introduce these topics to them. Though PHP is not my regular tech stack, I found it pretty similar to Node/Javascript and Python stacks. npm in Node/Javascript, pip in Python, and composer in PHP.  The learning curve is very short and easy, as there is a code base to learn from. Just need a couple of days to be productive, as the same OO principles apply. In this article, I will write my experience using PHP migration scripts using Phinx. 

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)
  1. Create a project folder. eg. called MyApp
  2. Open the folder using VSCode
  3. Open a command line terminal
  4. Install phinx using composer by running: composer require robmorgan/phinx
  5. 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


  6. 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. 
  7. 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. 
  8. You can run 'phinx' command to see what command you can use. For data migrations, we will need this command
    1. init
    2. create
    3. seed:create
    4. migrate
    5. seed:run
    6. status (usefull to see what migration has been applied to DB)
  9. To initiate migration, first run: phinx init 
      • This command will create phinx.php in the root folder. 
        <?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'
        ];

      • 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. 
    1. 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);
                }
    2. 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

    3. We will also create a third migration for report table, run: phinx create ThirdScript
    4. 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
    5. 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. 
    6. 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
    7. 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.
      • <?php

        declare(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
    8. 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.
      • 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
      • <?php
            require_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 successfully
            if ($returnCode === 0) {
                // Output captured lines (for example, print them)
                foreach ($output as $line) {
                    echo $line . PHP_EOL;
                }
            } else {
                // Handle error if the command fails
                echo "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

























    Thursday 7 September 2023

    Tips: Debugging into nuget and microsoft package

    This is quick tip for debugging into nuget package and microsoft package;

    The default behaviour in Visual Studio only show the exception when running in debugging mode.

    • In Visual Studio go to Tools –> Options –> Debugging –> Symbols:


    • Disable ‘Just my Code’ and enable Source Server and Source Link Support in Tools → Options → General



    • And make sure when running the application, make sure Debug → Windows → Exception Settings, is set for Common Language Runtime Exceptions (to trap any exception thrown) 


    Sunday 9 May 2021

    Blazor framework: What I learned

    1. Introduction

    Hi, there just want to share my experiment with the Blazor framework. I have developed front-end using ASP.NET MVC and React.js before in a commercial capacity. Blazor has been there for a while before I decided to give it a try on my projects at work.

    Sunday 14 February 2021

    EF Core 5 data migration in .NET 5 : in a separate library and automatic deployment

    I have been thinking to write this topic for some time. I did a project in the past with EF data migration, but things have changed and its library dependencies have changed as well. Now, moving forward to January 2021, I will do this in  .NET 5 project and with EF Core 5 (5.0.3)

    The source code of this project can be accessed from Github.

    Sunday 7 February 2021

    NHS MIM and DMS tools: Code generation, schema and validation

    1. Introduction

    When working with the NHS projects, I need to deal with a large number of linked schemas, particularly with domains that use the NHS Messaging Implementation Manual or the NHS Domain Messaging Specification. The MIM or DMS is pretty much the same, except MIM covers multiple domains while DMS only covers just one domain. MIM was firstly introduced to cover domains like EPS, Referral (Choice and Book), GP2GP, and PDS. Later one NHS agency chose to publish specification per domain thus DMS was introduced. MIM or DMS contains specification artifacts which are the output from HL7 V3 modelings such as interaction and messaging specification within a domain or multiple domains.

    Saturday 14 November 2020

    C# Code Generator from Schema

    When you work in the back end, once in a while, you will need to generate code from XML schema. Many applications define their data model using XML schema. Particularly when working with NHS projects and working with the integration with legacy NHS services, which is based on HL7 V3 MIM (Manual Implementation manuals) or DMS (Domain Message Specifications). I need a library in C# to work with the data models. In the past, when I was still working for a primary NHS GP provider, we used a modified version of CodeXS, which is not available anymore publicly. 

    Sunday 27 October 2019

    Hackathon: Healthcare Interopen

    Here is my week in Interopen hackathon 14 -15 October 2019, and it has been a while ago now. The hackathon is a demonstration of how Healthcare IT Suppliers can interoperate with each other, particularly using FHIR data standards. There were two teams from EmisHealth, and I was represented EmisHealth from GPConnect as a technical lead for this hackathon. Our team focused on Medication Reconciliation workstream. During the course of the two days, I built the API to aggregate Medication data from different suppliers, Orient, Better Health, and Ascribe and they were consumed by the UI developed by our second team, and also consumed within the EMIS Web product.