Examples

Real-world code samples for WPZylos Migrations.

E-commerce Schema

Customers Table

use WPZylos\Framework\Migrations\Migration;

class CreateCustomersTable extends Migration
{
    public function up(): void
    {
        $this->create('myplugin_customers', [
            'id'          => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
            'wp_user_id'  => 'bigint(20) unsigned DEFAULT NULL',
            'email'       => 'varchar(255) NOT NULL',
            'first_name'  => 'varchar(255) NOT NULL',
            'last_name'   => 'varchar(255) NOT NULL',
            'phone'       => 'varchar(20) DEFAULT NULL',
            'created_at'  => 'datetime DEFAULT CURRENT_TIMESTAMP',
            'updated_at'  => 'datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
        ], [
            'PRIMARY KEY (id)',
            'UNIQUE KEY email (email)',
            'KEY wp_user_id (wp_user_id)',
        ]);
    }

    public function down(): void
    {
        $this->drop('myplugin_customers');
    }
}

Products Table

class CreateProductsTable extends Migration
{
    public function up(): void
    {
        $this->create('myplugin_products', [
            'id'          => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
            'sku'         => 'varchar(50) NOT NULL',
            'name'        => 'varchar(255) NOT NULL',
            'description' => 'text DEFAULT NULL',
            'price'       => 'decimal(10,2) NOT NULL',
            'sale_price'  => 'decimal(10,2) DEFAULT NULL',
            'stock'       => 'int(11) NOT NULL DEFAULT 0',
            'status'      => 'varchar(20) NOT NULL DEFAULT \'draft\'',
            'category_id' => 'bigint(20) unsigned DEFAULT NULL',
            'created_at'  => 'datetime DEFAULT CURRENT_TIMESTAMP',
            'updated_at'  => 'datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
            'deleted_at'  => 'datetime DEFAULT NULL',
        ], [
            'PRIMARY KEY (id)',
            'UNIQUE KEY sku (sku)',
            'KEY category_id (category_id)',
            'KEY status (status)',
        ]);
    }

    public function down(): void
    {
        $this->drop('myplugin_products');
    }
}

Orders Table

class CreateOrdersTable extends Migration
{
    public function up(): void
    {
        $this->create('myplugin_orders', [
            'id'               => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
            'customer_id'      => 'bigint(20) unsigned NOT NULL',
            'order_number'     => 'varchar(32) NOT NULL',
            'status'           => 'varchar(20) NOT NULL DEFAULT \'pending\'',
            'subtotal'         => 'decimal(12,2) NOT NULL',
            'tax'              => 'decimal(12,2) NOT NULL DEFAULT 0.00',
            'shipping'         => 'decimal(12,2) NOT NULL DEFAULT 0.00',
            'total'            => 'decimal(12,2) NOT NULL',
            'shipping_address' => 'text NOT NULL',
            'billing_address'  => 'text DEFAULT NULL',
            'notes'            => 'text DEFAULT NULL',
            'created_at'       => 'datetime DEFAULT CURRENT_TIMESTAMP',
            'updated_at'       => 'datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
        ], [
            'PRIMARY KEY (id)',
            'UNIQUE KEY order_number (order_number)',
            'KEY customer_id (customer_id)',
            'KEY status (status)',
            'KEY created_at (created_at)',
        ]);
    }

    public function down(): void
    {
        $this->drop('myplugin_orders');
    }
}

Order Items Table

class CreateOrderItemsTable extends Migration
{
    public function up(): void
    {
        $this->create('myplugin_order_items', [
            'id'           => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
            'order_id'     => 'bigint(20) unsigned NOT NULL',
            'product_id'   => 'bigint(20) unsigned NOT NULL',
            'product_name' => 'varchar(255) NOT NULL',
            'product_sku'  => 'varchar(50) NOT NULL',
            'quantity'     => 'int(11) NOT NULL',
            'unit_price'   => 'decimal(10,2) NOT NULL',
            'total'        => 'decimal(10,2) NOT NULL',
            'created_at'   => 'datetime DEFAULT CURRENT_TIMESTAMP',
        ], [
            'PRIMARY KEY (id)',
            'KEY order_id (order_id)',
            'KEY product_id (product_id)',
        ]);
    }

    public function down(): void
    {
        $this->drop('myplugin_order_items');
    }
}

Adding Columns

For table modifications, use raw SQL via $this->db->query():

class AddBrandToProducts extends Migration
{
    public function up(): void
    {
        $prefix = $this->db->wpdb()->prefix;

        $this->db->query(
            "ALTER TABLE `{$prefix}myplugin_products` ADD COLUMN `brand` varchar(100) DEFAULT NULL AFTER `name`"
        );
        $this->db->query(
            "ALTER TABLE `{$prefix}myplugin_products` ADD COLUMN `weight` int(11) DEFAULT NULL AFTER `stock`"
        );
    }

    public function down(): void
    {
        $prefix = $this->db->wpdb()->prefix;

        $this->db->query(
            "ALTER TABLE `{$prefix}myplugin_products` DROP COLUMN `brand`"
        );
        $this->db->query(
            "ALTER TABLE `{$prefix}myplugin_products` DROP COLUMN `weight`"
        );
    }
}