# Database Migrations

Migrations add versioning to database changes over time. The migration system implemented in Phenix is based on Phinx (opens new window), offering simplicity and maintainability. Migrations are stored in the database/migrations folder and extend the Phenix\Database\Migration class.

# Writing Migrations

To create a migration, run the following Phenix command:

php phenix make:migration CreateUserTable

The name of the migration must be in camel case style. The class will be created with the name in camel case style, but the file will be created with a name composed of two parts: the date and time, and the name of the migration in snake case style. For example, 20231006144210_create_users_table.

Version (time): 20231006144210
File name: create_users_table
Class name: CreateUserTable

<?php

declare(strict_types=1);

use Phenix\Database\Migration;

class CreateUsersTable extends Migration
{
    public function up(): void
    {
        // ..
    }

    public function down(): void
    {
        // ..
    }
}

The up method is used to create and modify tables, add indexes, and perform other operations, while the down method should reverse the operations performed by the up method.

# Running Migrations

To run the migrations, use the migrate command:

php phenix migrate

# Rolling Back Migrations

The rollback command reverts the last migration, or optionally up to a specific version:

php phenix migrate:rollback

To roll back to a specific version, use the -t option:

php phenix migrate:rollback -t 20231006144210

# Tables

The table method prepares the table to be created. The first argument is the name of the table, and the second argument is an associative array of options.

public function up(): void
{
    $table = $this->table("users");
    $table->addColumn('name', 'string', ['limit' => 100]);
    $table->addColumn('email', 'string', ['limit' => 100]);
    $table->create();
}

Important: The id column is not defined because it is automatically created as the primary key. To specify a different primary key, use the primary_key option.

# Table Options

Option	Values
primary_key	`array`, `string`, `false`
engine	Defaults to `InnoDB`
collation	Defaults to `utf8mb4_unicode_ci`
signed	Defaults to `false`

# Checking if Table Exists

The hasTable method verifies the existence of a table in the database.

public function up(): void
{
    if (! $this->hasTable('users')) {
        // code
    }
}

# Dropping Tables

To drop a table, use the drop method and invoke the save method.

$this->table('users')->drop()->save();

# Renaming Tables

To rename tables, use the rename method and invoke the update method to apply the change.

$this->table('users')
    ->rename('legacy_users')
    ->update();

# Columns

The addColumn method adds columns to the table being created, specifying the data type and additional configuration options.

public function up(): void
{
    $table = $this->table("users");
    $table->addColumn('name', 'string', ['limit' => 100]);
    $table->create();
}

# Column Types

Supported data types for columns:

binary
boolean
char
date
datetime
decimal
float
double
smallinteger
integer
biginteger
string
text
time
timestamp
uuid

The MySQL adapter also supports enum, set, blob, tinyblob, mediumblob, longblob, bit, and json column types (json in MySQL 5.7 and above). When providing a limit value and using binary, varbinary, or blob and its subtypes, the retained column type will be based on the required length (see Limit Option and MySQL for details).

The Postgres adapter supports interval, json, jsonb, uuid, cidr, inet, and macaddr column types (PostgreSQL 9.3 and above).

# Column Options

For any column type:

Option	Description
limit	Set maximum length for strings, also hints column types in adapters (see note below)
length	Alias for `limit`
default	Set default value or action
null	Allow `NULL` values, defaults to false if `identity` option is set to true, else defaults to true
after	Specify the column that a new column should be placed after, or use `\Phinx\Db\Adapter\MysqlAdapter::FIRST` to place the column at the start of the table (only applies to MySQL)
comment	Set a text comment on the column

For decimal columns:

Option	Description
precision	Combine with `scale` to set decimal accuracy
scale	Combine with `precision` to set decimal accuracy
signed	Enable or disable the `unsigned` option (only applies to MySQL)

For enum and set columns:

Option	Description
values	Can be a comma-separated list or an array of values

For smallinteger, integer, and biginteger columns:

Option	Description
identity	Enable or disable automatic incrementing
signed	Enable or disable the `unsigned` option (only applies to MySQL)

For Postgres, when using identity, it will utilize the serial type appropriate for the integer size, so that smallinteger will give you smallserial, integer gives serial, and biginteger gives bigserial.

For timestamp columns:

Option	Description
default	Set default value (use with `CURRENT_TIMESTAMP`)
update	Set an action to be triggered when the row is updated (use with `CURRENT_TIMESTAMP`) (only applies to MySQL)
timezone	Enable or disable the `with time zone` option for `time` and `timestamp` columns (only applies to Postgres)

For boolean columns:

Option	Description
signed	Enable or disable the `unsigned` option (only applies to MySQL)

For string and text columns:

Option	Description
collation	Set collation that differs from table defaults (only applies to MySQL)
encoding	Set character set that differs from table defaults (only applies to MySQL)

For foreign key definitions:

Option	Description
update	Set an action to be triggered when the row is updated
delete	Set an action to be triggered when the row is deleted
constraint	Set a name to be used by the foreign key constraint

# Checking if Column Exists

$column = $table->hasColumn('username');

if ($column) {
    // code
}

# Renaming a Column

$this->table('users')
    ->renameColumn('nickname', 'username')
    ->save();

# Adding Column After Another

$table = $this->table('users');
$table->addColumn('city', 'string', ['after' => 'email']);
$table->update();

# Dropping a Column

$table = $this->table('users');
$table->removeColumn('nickname')->save();

# Indexes

Indexes are used to improve the performance of database queries by speeding up data retrieval. They serve as data structures that provide fast access to specific rows within database tables. By creating an index on one or more columns, the database can quickly locate the desired data without having to scan the entire table, resulting in significant query optimization.

$table = $this->table("users");
$table->addColumn('name', 'string', ['limit' => 100]);
$table->addColumn('email', 'string', ['limit' => 100]);
$table->addIndex(['email']);
$table->create();

More about indexes: Indexes in Depth (opens new window).

# Foreign Keys

To add foreign keys, use the addForeignKey method. The first argument is the name of the foreign key, the second argument is the name of the related table, the third argument is the column that refers to the foreign key, and the fourth argument is the configuration options.

$table = $this->table("posts");
$table->addColumn('title', 'string', ['limit' => 100]);
$table->addForeignKey('user_id', 'users', 'id', ['delete'=> 'SET_NULL', 'update'=> 'NO_ACTION']);
$table->create();

More about foreign keys: Foreign Keys in Depth (opens new window).

← Validation Database Seeders →