Writing Database Migrations

One very common element of developing within the apps is having a need to change the database, so that we can save the proper information and cause our apps to function correctly. Migration Core is the way that we have to accomplish these database changes in a way that whenever someone installs an app, it will automatically run and change the database for them. Migration Core is mostly just a wrapper for Phinx, but it adds in two things: it adds in migration file functions to interact with Group Control Tables, and it also creates the fw database commands to work with the FW Installer. Each of these will be discussed in due time, but it is probably best to introduce both Migration Core Specific and Phinx concepts within this guide. We will begin our documentation with general tips which can help you wrap your head around how to use Migration Core in general, then we will get into the specifics of how to write a migration file (highlighting especially important facets of Phinx for this).

General Tips

You should have a fairly good understanding of what a database is, and the general ways in which we make it work from reading the Conceptual Introduction - Database from the training. If you are not familiar with this, it would be a very good idea to refamiliarize yourself with databases in general before attempting to move on to how to use Migration Core. The parts there that are directly applicable to Migration Core will be covered again in a little more depth, but much of that information will be assumed throughout this guide.

FW Installer Command: FW Database

One of the main things that is added by Migration Core is an improved way of handling the creation and migration of database migration files in a way that is incorporated with the FW Installer. So, every app that has Migration Core installed has access to a new FW Installer command: fw database. This command has three different permutations, which does different things, they are: fw database create, fw database migrate, and fw database rollback. While technically this is one command with three different action argument options, in practice, it is usually spoken of as three commands. In general these do three different things:

  • fw database create creates a migration file for you to edit in the server/data/migrations folder.
  • fw database migrate runs the migration forward from the current version of the database to the newest version available (by running the necessary up functions)
  • fw database rollback runs the migrations backward, usually one version (by running the necessary down functions)

It is important to note that migrate will never rollback any database migrations, and rollback will never migrate any, so it is important when determining whether you will rollback or migrate that you take into account your current version and choose your command accordingly. (so running fw database migrate 5 when you are on version 6 will do nothing, and fw database rollback 6 when you are on 5 will do nothing). For more information on this command, you can check out our API docs for this command. Understanding how to use the FW Installer command will be necessary in the rest of these tips.

When to Write a New Migration

The first idea you need to know when you are making a database change is: when to start a new migration file, and when to use the latest migration file. And the rule of thumb for Informatics is: each app release must have its own migration file, and each app release should have at most one migration file. This is because it will cause problems for installing on the live if you are already on a database version and someone made changes to a previous migration (which was already run on your system). So that means that you should begin with a new, fresh database migration file if this is the first database change within the current release, and you should edit the most recent database migration file if you have database changes that have not been released yet. Since our policy is to increase the second number of the version (rather than the third) for database migrations, we will often try to wrap several different database migration changes into the same release.

If you are not sure if the last release had a database migration or not (and the version number of the previous version is not a good enough indication), each migration file is named the timestamp followed by the number of the database migration (YYYYMMDDhhmmss_number.php). For example, Message Center's first database migration is 20200710011349_one.php was generated on July 10th 2020 at 01:13:49 AM (UTC). So this can sometimes give you a quick indication of whether you did a release since then, if you don't know off the top of your head.

How to Create a New Migration

So, when you are needing to create a new migration because it is the first in this release, all you need to do is run fw database create, and that will generate a fresh database migration file for you. Then of course, after you write your migration, you can run fw database migrate, and your changes will be made to your database.

How to Edit an Existing Migration

If, however, you are needing to edit an existing database migration, the process is a little more complicated. Before you can make changes to the database migration file, you must first run fw database rollback to make your database on the previous version. Otherwise, the migration_log table will already have the file you edited as "migrated", and will not attempt to run the up function again. Then after you have made your changes to the file, you can run fw database migrate, and it will properly add everything (including your changes).

That being said, sometimes running fw database rollback is a nuisance because any columns or tables that were removed in the down function, all the data that you had entered into those columns or tables for testing will be deleted. This can be especially problematic for a large database migration (such as the first one), and thus, one helpful tip for editing previously run database migrations is that anything unrelated to the current issue you are working on, you can comment out (from both the up and the down functions). If you do this, you will preserve the data you have saved from working on previous issues, you just need to make sure to uncomment everything before you push your changes (and especially before you release it). This is not required, but can be a way to help more easily run database migrations without deleting data.

Integrating with Group Control

The one thing that Migration Core adds to Phinx is the ability to add data to the gc_app, and gc_role tables for each app from a database migration. Without this, it would be impossible to properly set up the app in Group Control to allow users to have permissions to access the app. The various functions that are added to Phinx to accomplish these ends are described in greater detail in the documentation for the Group Control Migration Class. These are most often used in the first migration file, but they are also used at times when needing to change or add new roles in future migrations. As a side note: whenever you are editing a migration file with these functions used previously, it is highly recommended that you comment them out, otherwise, all of the Group Control configuration you have done for those apps/roles will have to be done again after you migrate.

Specific Phinx Help

With the exception of the Group Control functions and console commands linked above, everything about writing the database migration file is an exact copy of Phinx, and thus, reading over Phinx's documentation will be very helpful to you to get a full-orbed understanding of how to write a migration file. That being said, in our documentation, we will point out the fundamentals of Phinx, so that you can have a simple understanding of what is expected without having to go through all the details given within the Phinx documentation.

Phinx Migration Functions

First of all, as described in our database conceptual introduction a migration function will either have two functions up and down or one function change. These functions are run when using the fw database migrate/rollback commands as explained above. The function change is the only function that is not explained above because it is a mix of the up and the down command. When you write a change function, you write it as an up function more or less, and when running fw database migrate it will run the function as written, and for fw database rollback it will run the opposite of the function. Because of this, change cannot be used in every circumstance. Specifically, if it is impossible to predict the opposite. So here is the rules of thumb for when you can and cannot use the change function:

  • You CAN use it when you are adding new tables or new columns with foreign keys (because it is easy to know that the opposite of "add" is delete)
  • You CANNOT use it when you are removing tables or columns or changing information about columns with the changeColumn function (because it is impossible to know what the old version of the removed or changed column was before the migration).

Of course, you can always choose to use the up/down functions if you want, and in fact, many developers have decided that it is always easier to specify both up and down every time, even for simpler migrations, but that choice is ultimately up to you (several of our apps have many change migration files, others have very few). By default, fw database create makes a file with both an up and down function, but you are free, if your migration is simple enough, to change it to change if you want. When using up and down you must undo everything that you changed in the up in the down otherwise, your migrations will not be able to be migrated properly. It should be a mirror image where up takes you from your current database to where you want to go, and down takes you from your new database back to the old version exactly. If you want more information about these functions, you can view Phinx documentation about: change, up, and down.

Phinx SQL Functions

The Migration File, because it extends GroupControlMigration class, which in turn extends Phinx's default AbstractMigration, you have access to many different Phinx commands (in $this), which are defined to perform specific SQL functions. Thus, instead of writing migrations in Raw SQL, you will use the Phinx commands to actually form your database migration. While we cannot go over every option because Phinx is very flexible, we are able to point you to the more commonly used SQL functions with a brief explanation. This can help point you to some of the more detailed Phinx documentation and get you started.

The Table Object

The first thing you need to know about SQL functions is how to specify the table you are working with. In Phinx, your table must be specified with the table command BEFORE you do anything else to it. This table function must be used whether you are creating a new table, editing an existing table (by adding/removing columns), or droping a table.

The table function takes two parameters, name and options (which is an array allowing you to set the id field if it is an AID, or a primary_key field if there is no AID). If you use the id, you will not need to add the column, if you use the primary_key, the column will need to be defined below. The options parameter is only required when you are creating a table, for editing a table or dropping a table, you will not need to use it. To give a few examples, here is how the main ways a table function will be used (when creating a new table, we always use set singed to true):

  • Creating a Table with a auto-incrementing ID
$this->table('table_name', ['id' => 'tblAID', 'signed' => true])
     // adding columns to table, etc
     ->create(); // or optionally save(), either works
  • Creating a table with a primary_key
$this->table('table_name', ['id' => false, 'primary_key' => 'tblID', 'signed' => true])
     ->addColumn('tblID', 'string')
     // adding other columns to table, etc
     ->create(); // or optionally save(), either works
  • Updating a table already existing in the database
$this->table('table_name')
     // adding new columns to table, etc
     ->update(); // or optionally save(), either works
  • Dropping table from database (when dropping no need to modify specific columns)
$this->table('table_name')->drop()->save();

While these should be able to get you started, this is simply a very basic introduction, for a much more detailed exposition of the Table object, including all of the different options you have (beyond just id, primary_key, and signed), see Phinx Documentation - Working With Tables.

Column Functions

The main thing you are going to be doing when writing a database migration is adding, modifying or deleting a column. Therefore, the most common functions that you will be using are those which work with columns. There are four main functions to address that Phinx defines for working with columns: addColumn, changeColumn, renameColumn, and removeColumn.

Before talking about each of these methods individually, it is probably helpful to speak about the things which are in common between all columns: types and options. There are many different types of data that you can save to the database, which are supported by Phinx, and it is helpful to know which ones are allowed by checking the Phinx documentation here, but the most common are just regular data types (boolean, integer, string, etc). As a note, it is advisable for date fields that you use the type date or datetime rather than timestamp because timestamp is changed based on timezone (thus data is shifted for Daylight Savings Time). Options are settings for the column which help you to control attributes of the column that can be set ordinarily through SQL. Different column types may even add different options as well. For a full list checkout Phinx Documentation here, but the most common you will use for informatics are the following:

  • null is an option which determines whether the column is allowed to take null as a value. The default is true (thus you will need to specify if you wish the user not to be able to set a column to null). Columns which are required should always ensure that they cannot be null on the database.
  • default is an option which allows you to specify the default value if one is not specified when saving a row. This is most often used for boolean types or datetime types (for datetime, the default is often CURRENT_TIMESTAMP), though it can be used for any data type.
  • limit or length is used most often with strings, and with strings it changes the number of characters allowed in the strings. With integers it will change the highest integer allowed.

Now that you have a basic understanding of the different column types and column options for Phinx, and where to look for a full list of everything, the column functions are pretty simple:

  • addColumn obviously is a function that adds a column to the table, and it has three parameters: column name, column type, and options. So a non-null string of "name" would be added in the following way:
$this->table('my_table')
     ->addColumn('name', 'string', ['null' => false])
     ->save();
  • changeColumn will take the same three parameters as add column, and it is a function that allows you to change everything about the column, except for its name. So if you had previously added the previous column, you could change it to have a specific limit, and be able to be null:
$this->table('my_table')
     ->changeColumn('name', 'string', ['limit' => 64])
     ->save();
  • renameColumn is the only way to change the name of an existing column, and only takes two parameters (the old name and the new name). Thus, if you need to change both the name of the column and the type/options, you will need to use both renameColumn and changeColumn (it may need to be in separate save blocks, as that has caused issues in the past for developers). For example:
$this->table('my_table')
     ->renameColumn('name', 'title')
     ->save();
  • removeColumn is how you remove a column, and just takes one parameter - the name of the column you are seeking to delete. For example:
$this->table('my_table')
     ->removeColumn('name')
     ->save();

These column functions are fairly self explanatory, but if you need more details regarding the various column types, and the many different options for each type, make sure to checkout the Phinx Documentation - Working with Columns.

Foreign Key Functions

If you are not familiar with what a foreign key is, you need to review the database introductory concept linked in the beginning. But for implementing a foreign key into the database, Phinx defines two functions: addForeignKey and dropForeignKey. Most tables in the database will have a FK_deptID and an FK_userID, so these functions will be used extremely frequently in almost every database migration as well.

Before we discuss the arguments for these functions, it is perhaps important to note that when dealing with foreign keys, order matters. These functions only manages foreign keys on an already existing columns, thus the order you call add things matters. The following points are needed to be kept in mind when you are determining order:

  • When adding foreign keys:
    • You need to make sure that any parent tables (the table referenced by the foreign key) are defined before the child tables (the table with the foreign key). This means that if you have a very related database you will need to think carefully about the order you add your tables in.
    • You need to make sure that the column on the table exists before attempting to add a forreign key to it (usually this means having the addColumn function before the addForeignKey).
  • When dropping foreign keys:
    • If dropping whole tables, you need to make sure that all child tables are deleted BEFORE parent tables otherwise you will have foreign key constraint errors.
    • When dropping columns, you need to drop foreign key BEFORE removing the column, otherwise you will receive errors.
  • That being said, usually a helpful rule of thumb is thinking carefully about your order in the up function, and then simply doing the commands in the reverse order in the down function (that way, things are covered).

The function addForeignKey takes four parameters: column name (on the current table), reference table (you are linking to), id column name (on the reference table), and finally options. The options which are used most often are the options delete and update, which are set to settings that will tell the database what to do if the parent row which the foreign key links to is either deleted or updated. There are several options defined by Phinx, which you can see the linked documentation to hear all of those, we will only discuss two. 99% of the time you will use the CASCADE option for both delete and update, which means that if the parent row is deleted, all the child rows are deleted. Occasionally, there may be an instance in which you do not want this to be the default behavior, in which case the SET_NULL option will simply set the foreign key field to null (it must be allowed to be null on the column then). Other options are available to you, if you look at the documentation, but they are so rarely used in our apps, that they are not worth mentioning. So to put it all together, this would be an example of how to add a foreign key for the FK_deptID column from GC:

$this->table('my_table')
     // other columns for other data
     ->addColumn('FK_deptID', 'string')
     ->addForeignKey('FK_deptID', 'gc_dept', 'deptID', ['delete' => 'CASCADE', 'update' => 'CASCADE'])
     ->save();

The function dropForeignKey is very simple, and it just takes the name of the column on the table you wish to drop the foreign key for. Here is an example of dropping the foreign key added above (for example, this would be found in the down function):

$this->table('my_table')
     ->dropForeignKey('FK_deptID')
     ->removeColumn('FK_deptID')
     // other columns you are deleting
     ->save();

Note: there is no way to modify or edit a foreign key, so if you do need to change the options for a foreign key, you will need to drop the foreign key and then re-add it to the same column. Unfortunately, this will often cause issues if attempting to do this in the same query, so it is usually best to have two separate table objects, saving each one. The first drops the old foreign key, and the second adds it back.

In practice, after you get the hang of making sure you have the order right for when to call the functions for foreign keys, they are not too difficult to implement. If you want more information on working with foreign keys in Phinx, see Phinx Documentation - Working with Foreign Keys.

Raw SQL Commands

There sometimes comes a time when you need to do direct SQL statements, not covered by changing table structure information, which Phinx has provided so many helpful functions for (usually when you need to modify the data to a certain extent with an INSERT INTO or UPDATE SQL commands). It is helpful to know that in these limited occurrences, Phinx has provided a function which allows you to make a direct SQL query statement with the execute function. It takes one parameter, which is a string of raw SQL, like the following:

$this->execute("SELECT * FROM my_table");

Of the things we have spoken about, this is the least frequently used, and is fairly self explanatory, but we wanted to make sure to mention it as an option in this brief guide. For more information about the execute method, see Phinx Documentation - Executing Queries.

Other Functions

Quite obviously, if you noticed the difference between length of the documentation for Phinx and the length of the Phinx section of this page, you realize that there is far more depth and many more options with using Phinx than has been mentioned here. There are things available in Phinx, which we have used before in the apps, which are not mentioned here due to their complexity, and certainly there are aspects of writing migrations in Phinx that have never yet been used in the apps (which is not to say they never will be). Whenever you are attempting to do something more advanced than is described here, the Phinx documentation is well documented and easy to follow, so that is a wonderful place to check. The purpose of this documentation is merely to give you an introduction to the most important aspects of Phinx that you are likely to use every time you write a migration.

Example

As a practical example of using these functions, we have decided to display an example from Message Center. This is not so much so you can exactly copy it, but this is more so you can see in practice how many of the elements that we discussed above might be used in a practical situation. You can also scroll through several of the different files in the server/data/migrations of any of our apps to see many other examples of how a database migration might be written.

class Six extends GroupControlMigration {

    public function up() {
        // direct SQL statement to update some of the data in the template so that we can
        // change the temp var type from string to enum properly
       $this->execute("UPDATE `msgc_templateVariables`
                        SET `tempVar_type` = 'textfield'
                        WHERE `msgc_templateVariables`.`tempVar_type` NOT IN (
                                    'checkbox', 'number', 'textfield', 'textarea',
                                    'dropdown', 'radio','tel', 'email',
                                    'url', 'date', 'time', 'datetime')"
                    );
        //properly change the templateVariables table
        $this->table('msgc_templateVariables')
            ->changeColumn('tempVar_type', 'enum', ['values' => [
                'checkbox', 'number', 'textfield', 'textarea',
                'dropdown', 'radio','tel', 'email',
                'url', 'date', 'time', 'datetime'], 'null' => false])
            ->addColumn('tempVar_order', 'integer', ['null' => false])
            ->save();

        // add a new table called msgc_option
        $this->table('msgc_option', ['id' => 'optAID', 'signed' => true])
            ->addColumn('opt_name', 'string', ['null' => false])
            ->addColumn('opt_description', 'string', ['limit' => 1064, 'null' => true])
            ->addColumn('opt_default', 'boolean', ['default' => false, 'null' => false])
            ->addColumn('FK_opt_tempVarAID', 'integer', ['null' => false])
            ->addColumn('opt_sortKey', 'integer', ['limit' => 11, 'null' => true])
            ->addForeignKey('FK_opt_tempVarAID', 'msgc_templateVariables', 'tempVarAID',
                    ['delete' => 'CASCADE', 'update' => 'CASCADE'])
            ->save();

    }

    public function down() {
        $this->table('msgc_templateVariables')
            ->changeColumn('tempVar_type', 'string', ['null' => false])
            ->removeColumn('tempVar_order')
            ->save();
        $this->table('msgc_option')->drop()->save();
    }
}