Schema Builder

Introduction

WpMVC’s Schema class provides a database-agnostic way of manipulating tables, making it an ideal tool for WordPress developers who need to create custom database tables for their plugins. It works gracefully with WordPress’s database structure and provides an expressive API for creating and modifying tables without writing raw SQL.

Internally, WpMVC uses WordPress’s dbDelta function to ensure that your table schema is correctly synchronized during plugin installation or updates.

Creating Tables

To create a new database table, use the create method on the Schema class. The create method accepts two arguments: the first is the name of the table, while the second is a closure which receives a Blueprint object that may be used to define the new table.

Best Practice: The Setup Class

In a WpMVC plugin, table creation is typically handled within a Setup class. This class is responsible for executing schema changes during plugin activation or updates:

<?php
 
namespace MyPluginNamespace\Database;
 
defined( 'ABSPATH' ) || exit;
 
use MyPluginNamespace\WpMVC\Database\Schema\Schema;
use MyPluginNamespace\WpMVC\Database\Schema\Blueprint;
 
class Setup 
{
    public function execute() 
    {
        Schema::create( 'customers', function( Blueprint $table ) {
            $table->big_increments( 'id' );
            $table->string( 'name' );
            $table->string( 'email' )->unique();
            $table->timestamps();
        } );
    }
}

When creating the table, you may use any of the schema builder’s column methods to define the table’s columns.

Checking For Table Existence

You may easily check for the existence of a table or column using the has_table and has_column methods:

<?php
 
defined( 'ABSPATH' ) || exit;
 
use MyPluginNamespace\WpMVC\Database\Schema\Schema;
 
if ( Schema::has_table( 'customers' ) ) {
    // The "customers" table exists...
}
 
if ( Schema::has_column( 'customers', 'email' ) ) {
    // The "customers" table exists and has an "email" column...
}

Updating Tables

The alter method on the Schema class may be used to update existing tables. Like the create method, the alter method accepts the name of the table and a closure that receives a Blueprint instance:

<?php
 
defined( 'ABSPATH' ) || exit;
 
use MyPluginNamespace\WpMVC\Database\Schema\Schema;
use MyPluginNamespace\WpMVC\Database\Schema\Blueprint;
 
Schema::alter( 'customers', function( Blueprint $table ) {
    $table->string( 'phone' )->nullable();
} );

SQL Preview

Most Schema methods (create, alter, rename, drop, drop_if_exists) accept an optional boolean as the final argument. If set to true, the method will return the generated SQL string instead of executing it:

<?php
 
defined( 'ABSPATH' ) || exit;
 
use MyPluginNamespace\WpMVC\Database\Schema\Schema;
use MyPluginNamespace\WpMVC\Database\Schema\Blueprint;
 
$sql = Schema::create( 'customers', function( Blueprint $table ) {
    $table->big_increments( 'id' );
}, true );

Renaming & Dropping Tables

To rename an existing database table, use the rename method:

Schema::rename( $from, $to );

To drop an existing table, you may use the drop or drop_if_exists methods:

Schema::drop( 'customers' );
 
Schema::drop_if_exists( 'customers' );

Indexes & Constraints

The schema builder supports several types of indexes. The following example creates a new email column and specifies that its values should be unique:

$table->string( 'email' )->unique();

Alternatively, you may create the index after defining the column:

$table->string( 'email' );
 
$table->unique( 'email' );

You may even pass an array of columns to an index method to create a compound index:

$table->index( [ 'account_id', 'created_at' ] );

Primary Keys

You may also define a primary key for the table:

$table->primary( 'id' );
 
// Or for composite keys:
$table->primary( [ 'account_id', 'id' ] );

Foreign Key Constraints

WpMVC also provides support for creating foreign key constraints, which are used to force referential integrity at the database level. For example, let’s define a customer_id column on the products table that references the id column on a customers table:

<?php
 
defined( 'ABSPATH' ) || exit;
 
use MyPluginNamespace\WpMVC\Database\Schema\Schema;
use MyPluginNamespace\WpMVC\Database\Schema\Blueprint;
 
Schema::create( 'products', function( Blueprint $table ) {
    $table->big_increments( 'id' );
    $table->unsigned_big_integer( 'customer_id' );
 
    $table->foreign( 'customer_id' )
          ->references( 'id' )
          ->on( 'customers' )
          ->on_delete( 'cascade' );
} );

Dropping Indexes

To drop an index, you must specify the index’s name. By default, WpMVC assigns a reasonable name to the indexes. You can simply pass the index name to the drop_index method:

$table->drop_index( 'geo_state_index' );

Available Column Types

The schema builder blueprint offers a variety of methods that correspond to the different types of columns you can add to your database tables.

Big Integer

big_increments()

The big_increments method creates an auto-incrementing BIGINT UNSIGNED (primary key) equivalent column:

$table->big_increments( 'id' );

unsigned_big_integer()

The unsigned_big_integer method creates an UNSIGNED BIGINT equivalent column:

$table->unsigned_big_integer( 'customer_id' );

big_integer()

The big_integer method creates a BIGINT equivalent column:

$table->big_integer( 'votes' );

Strings & Text

string()

The string method creates a VARCHAR equivalent column of a given length:

$table->string( 'name', 100 );

text()

The text method creates a TEXT equivalent column:

$table->text( 'description' );

long_text()

The long_text method creates a LONGTEXT equivalent column:

$table->long_text( 'content' );

Numbers

integer()

The integer method creates an INT equivalent column:

$table->integer( 'votes' );

unsigned_integer()

The unsigned_integer method creates an UNSIGNED INT equivalent column:

$table->unsigned_integer( 'votes' );

tiny_integer()

The tiny_integer method creates a TINYINT equivalent column:

$table->tiny_integer( 'status' );

decimal()

The decimal method creates a DECIMAL equivalent column with the given precision (total digits) and scale (decimal digits):

$table->decimal( 'amount', 8, 2 );

float()

The float method creates a FLOAT equivalent column:

$table->float( 'price' );

boolean()

The boolean method creates a BOOLEAN equivalent column:

$table->boolean( 'confirmed' );

Date & Time

timestamps()

The timestamps method adds created_at and updated_at TIMESTAMP equivalent columns:

$table->timestamps();

timestamp()

The timestamp method creates a TIMESTAMP equivalent column:

$table->timestamp( 'added_on' );

date()

The date method creates a DATE equivalent column:

$table->date( 'birthday' );

datetime()

The datetime method creates a DATETIME equivalent column:

$table->datetime( 'published_at' );

Miscellaneous

enum()

The enum method creates an ENUM equivalent column with the given allowed values:

$table->enum( 'difficulty', [ 'easy', 'hard' ] );

json()

The json method creates a JSON equivalent column:

$table->json( 'options' );

In addition to the column types listed above, there are several column “modifiers” you may use while adding a column to a database table. For example, to make the column “nullable”, you may use the nullable method:

<?php
 
defined( 'ABSPATH' ) || exit;
 
use MyPluginNamespace\WpMVC\Database\Schema\Schema;
use MyPluginNamespace\WpMVC\Database\Schema\Blueprint;
 
Schema::alter( 'users', function( Blueprint $table ) {
    $table->string( 'email' )->nullable();
} );

nullable()

Allows the column to allow NULL values.

default()

Specify a “default” value for the column:

$table->string( 'status' )->default( 'draft' );

comment()

Add a comment to the column:

$table->string( 'role' )->comment( 'User role in the system' );

use_current()

Sets the default value for a timestamp column to the current timestamp:

$table->timestamp( 'created_at' )->use_current();

use_current_on_update()

Sets the column to automatically update to the current timestamp when the record is updated:

$table->timestamp( 'updated_at' )->use_current_on_update();

after()

When using MySQL, the after method may be used to specify the order of columns:

$table->string( 'address' )->after( 'email' );

Dropping Columns

To drop columns from an existing table, you may use the drop_column method on the Blueprint instance within a Schema::alter callback:

<?php
 
defined( 'ABSPATH' ) || exit;
 
use MyPluginNamespace\WpMVC\Database\Schema\Schema;
use MyPluginNamespace\WpMVC\Database\Schema\Blueprint;
 
Schema::alter( 'customers', function( Blueprint $table ) {
    $table->drop_column( 'phone' );
    
    // You may also drop multiple columns
    $table->drop_column( 'address' );
    $table->drop_column( 'social_security_number' );
} );