Introduction to Migrations

In Laravel and in Gateway a “migration” refers to the code that runs to do database updates. In Gateway we’ve adapted the concept to work well with the WordPress database. Gateway uses dbDelta() a function available in WP core to run raw SQL. The SQL we run creates, updates or drops database tables. It is of course very important that we have a high degree of accuracy when making database table changes which is why most of the time we rely on automation for this SQL creation rather than manually writing it.

Note that although AI can write SQL at rapid speed all day long, we don’t recommend relying on AI to write your migrations. Instead rely on our automations which for the most get it right nearly 100% of the time.

The way the automated migrations work in Gateway is that they are handled at the collection level. Each collection is an Eloquent model underneath the hood. Which means it fits into the approach of one model has one table. Each collection has a single database table that stores it’s records. If you have an Events collection, you probably have a wp_events table unless you’ve customized the prefix or the table name. Same concept for a tickets table, you will have a wp_tickets table for this collection unless customized.

When the migration file is being created by Gateway it’s main input is the $fields property on the collection file. For our Events collection example, it is going to read Events.$fields and loop over all the fields. As it does this it will identify the field.type, pair this to a database column that fits that field type, and in this manner it builds up the bulk of the data in a format it needs to craft SQL for the update. Before doing that however there is another step, the migration automation needs to also establish the correct opening and closing lines such as the “create” or “update” line, and in closing it considers things like which index keys to set.

Let’s keep it real, migrations are neat but this is the most tedious side effect of choosing to build a system around custom database tables. Frankly it’s a pain, and it’s always an issue which is why we want to be on top of it at all times. Get in the habit of working with migrations proactively, ensuring your tables are always updated. Each time you change the $fields in a collection, visit the listing for that collection in WP Admin > Gateway > Collections > Registered Collections and click the “Run Migration” button. This will do both the build migration and run it processes in sequence.

What is our goal in terms of working with migrations?

1. Create the table for a new collection with accurate columns that match the $fields defined.
2. Update columns including removal of cols that are no longer needed after a field was removed.
3. Maintain data and avoid losing existing data. Note that database changes like this should only be run on local or staging development sites, until you are ready to make a single deployment to production which you can do either with the Gateway tooling, or manually using a SQL update file.
4. Make column types that are rational and reasonably optimized for the field types in the collection.
5. Add reasonable indexing for the field types included in the collection.
6. Accurately run migrations after each change to fields to avoid stale tables that won’t be able to store records accurately.

Below is an example of the PHP migration file for Attendee a collection that relates to the Events in an Events extension. This collection stores records where each row represents one attendee to a given event.

<?php

namespace Events\Database;

// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Database Migration: AttendeeMigration
 *
 * This class creates the database table for the collection.
 * Auto-generated by Gateway Builder.
 */
class AttendeeMigration
{
    /**
     * Create or update the database table
     *
     * This method uses WordPress's dbDelta() function which safely
     * handles both table creation and updates.
     */
    public static function create()
    {
        global $wpdb;

        $table_name = $wpdb->prefix . 'attendee';
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            event_id BIGINT UNSIGNED NOT NULL,
            title VARCHAR(255) NULL,
            label VARCHAR(255) NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY event_id (event_id)
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }
}

<?php

namespace Events\Database;

// Exit if accessed directly
if (!defined('ABSPATH')) {
    exit;
}

/**
 * Database Migration: AttendeeMigration
 *
 * This class creates the database table for the collection.
 * Auto-generated by Gateway Builder.
 */
class AttendeeMigration
{
    /**
     * Create or update the database table
     *
     * This method uses WordPress's dbDelta() function which safely
     * handles both table creation and updates.
     */
    public static function create()
    {
        global $wpdb;

        $table_name = $wpdb->prefix . 'attendee';
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $table_name (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            event_id BIGINT UNSIGNED NOT NULL,
            title VARCHAR(255) NULL,
            label VARCHAR(255) NULL,
            created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY event_id (event_id)
        ) $charset_collate;";

        require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
        dbDelta($sql);
    }
}

This code was generated by Gateway using its built-in Migration Automation. No need to labor over making these type of files. Would you write it any differently if you were writing it by hand?

Running Migrations on Version Change

One of the most vital concepts in extension building is that we should ship our migrations as code, so they don’t just live in the site where we’re building. You’ll often (should) be building your Gateway extensions on a local or staging development site. Later you will want the features to work on a production site. In order to get from point A to B without too much pain we want you to ensure your migrations live right alongside the collections they are made for. To achieve this we want to take the generated migration files and copy them into our extension. If you’re building with Raptor Editor you won’t need to do this step because the editor automatically creates collections as a pair of files, a collection file and it’s corresponding migration file. For code-first approach however, get in the habit of visiting WP Admin > Gateway > Collections > Registered Collections and click the “Generate Migration” button. Then copy paste the code into the correctly named and placed migration file in your project. Check this into your repo.

Once your migrations live alongside your collections you are halfway there. We just need to make sure that your migrations will run when expected. The way this will work if you’ve used a boilerplate or started your project using Raptor Editor, is when the site detects a higher version of your extension it will run any scheduled migrations for the current version or any versions skipped. Note that this key feature might be missing if you rolled your own main plugin file. See how this is done in the boilerplates at https://github.com/arcwordpress/gateway-extension-boilerplates if you prefer to continue to build from scratch.