PHP Database Migrations Cookbook

In this repository I will collect the most common recipes for working with database migrations.

Recipes are given for the following libraries/frameworks:

• Phinx - Documentation - Package
• Laravel Database Migrations

All recipes are accompanied by examples of native MySQL queries.

* Check your database server connection and user permissions executing queries.

Table of Contents

• Schema
  • Creating a scheme
  • Dropping a database
• Tables
  • Creating a table
  • Renaming a table
  • Dropping a table
• Columns
  • Adding an INTEGER column
  • Adding a TINYINT column
  • Adding a DECIMAL column
  • Adding an ENUM column
  • Adding a BOOLEAN column
  • Specifying a column length
  • Specifying a default value for a column
  • Working with nullable columns
  • Specifying a comment for a column
  • Working with signed columns
  • Specifying a column character set
  • Specifying a column collation
• TODO

Schema

Creating a database

• check if the database is exists
• set charset
• set collation

MySQL

CREATE DATABASE IF NOT EXISTS `cookbook` DEFAULT CHARACTER SET `utf8mb4` COLLATE `utf8mb4_unicode_ci`

Phinx

if (!$this->getAdapter()->hasDatabase('cookbook')) {
    $this->createDatabase('cookbook', [
        'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci',
    ]);
}

Laravel

Create database that specified in configuration file (config/database.php) (using default connection (DB_CONNECTION))

php artisan migrate:install

With specific connection

php artisan migrate:install --database="pgsql"

Using migration

use Illuminate\Support\Facades\Schema;

Schema::connection('mysql')
    ->getConnection()
    ->getDoctrineSchemaManager()
    ->createDatabase('cookbook');

Dropping a database

MySQL

DROP DATABASE IF EXISTS `cookbook`

Phinx

if ($this->getAdapter()->hasDatabase('cookbook')) {
    $this->dropDatabase('cookbook');
}

Laravel

use Illuminate\Support\Facades\Schema;

Schema::connection('mysql')
    ->getConnection()
    ->getDoctrineSchemaManager()
    ->dropDatabase('cookbook');

Tables

Creating a table

• check if the table is exists
• add a primary column
• specify engine
• set charset
• set collation
• add a comment

MySQL

CREATE TABLE IF NOT EXISTS `posts`
(
    `id` int unsigned auto_increment primary key
)
    ENGINE = InnoDB
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci

Phinx

use Phinx\Db\Table\Table;

if (!$this->hasTable('posts')) {
    $this->getAdapter()->createTable(
        new Table('posts', [
            'engine' => 'InnoDB',
            'collation' => 'utf8mb4_unicode_ci',
            'comment' => 'Table short description',
            // Primary key options
            'id' => 'id', // Primary key name
            'signed' => false,
        ])
    );
}

Laravel

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;

if (!Schema::hasTable('posts')) {
    Schema::create('posts', function (Blueprint $table) {
        $table->engine = 'InnoDB';
        $table->charset = 'utf8mb4';
        $table->collation = 'utf8mb4_unicode_ci';

        $table->integerIncrements('id');
    });

    DB::statement("ALTER TABLE `posts` comment 'Table description'");
}

Renaming a table

MySQL

ALTER TABLE `posts` RENAME `articles`;

Phinx

$this->getAdapter()->renameTable('posts', 'articles');

Laravel

use Illuminate\Support\Facades\Schema;

Schema::rename('posts', 'articles');

Dropping a table

MySQL

DROP TABLE IF EXISTS `articles`

Phinx

if ($this->hasTable('articles')) {
    $this->table('articles')
        ->drop()
        ->save();
}

Laravel

use Illuminate\Support\Facades\Schema;

Schema::dropIfExists('articles');

Columns

Adding an INTEGER column

MySQL

ALTER TABLE `posts` ADD COLUMN `likes` INTEGER

Phinx

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Db\Table\Column;

$this->table('posts')
    ->addColumn(
        (new Column())
            ->setName('likes')
            ->setType(MysqlAdapter::PHINX_TYPE_INTEGER)
    )
    ->update();

Laravel

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::table('posts', function (Blueprint $table) {
    $table->integer('likes');
});

Adding a TINYINT column

MySQL

ALTER TABLE `posts` ADD COLUMN `type` TINYINT

Phinx

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Db\Table\Column;

$this->table('posts')
    ->addColumn(
        (new Column())
            ->setName('type')
            ->setType(MysqlAdapter::PHINX_TYPE_BOOLEAN)
    )
    ->update();

Laravel

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::table('posts', function (Blueprint $table) {
    $table->tinyInteger('type');
});

Adding a DECIMAL column

MySQL

ALTER TABLE `products` ADD COLUMN `price` DECIMAL(10, 2)

Phinx

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Db\Table\Column;

$this->table('products')
    ->addColumn(
        (new Column())
            ->setName('price')
            ->setType(MysqlAdapter::PHINX_TYPE_DECIMAL)
            ->setPrecisionAndScale(10, 2)
    )
    ->update();

Laravel

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::table('products', function (Blueprint $table) {
    $table->decimal('price', 10, 2);
});

Adding an ENUM column

MySQL

ALTER TABLE `posts` ADD COLUMN `status` ENUM("draft", "publish", "private", "trash")

Phinx

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Db\Table\Column;

$this->table('posts')
    ->addColumn(
        (new Column())
            ->setName('status')
            ->setType(MysqlAdapter::PHINX_TYPE_ENUM)
            ->setValues([
                'draft', 'publish', 'private', 'trash',
            ])
    )
    ->update();

Laravel

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::table('posts', function (Blueprint $table) {
    $table->enum('status', [
        'draft', 'publish', 'private', 'trash',
    ]);
});

Adding a BOOLEAN column

MySQL

ALTER TABLE `posts` ADD COLUMN `active` TINYINT(1)

Phinx

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Db\Table\Column;

$this->table('posts')
    ->addColumn(
        (new Column())
            ->setName('active')
            ->setType(MysqlAdapter::PHINX_TYPE_BOOLEAN)
    )
    ->update();

Laravel

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::table('posts', function (Blueprint $table) {
    $table->boolean('active');
});

Specifying a column length

MySQL

ALTER TABLE `posts` ADD COLUMN `name` VARCHAR(100)

Phinx

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Db\Table\Column;

$this->table('posts')
    ->addColumn(
        (new Column())
            ->setName('name')
            ->setType(MysqlAdapter::PHINX_TYPE_STRING)
            ->setLimit(100)
    )
    ->update();

Laravel

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::table('posts', function (Blueprint $table) {
    $table->string('name', 100);
});

Specifying a default value for a column

MySQL

ALTER TABLE `posts` ADD COLUMN `likes` INTEGER DEFAULT 0

Phinx

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Db\Table\Column;

$this->table('posts')
    ->addColumn(
        (new Column())
            ->setName('likes')
            ->setType(MysqlAdapter::PHINX_TYPE_INTEGER)
            ->setDefault(0)
    )
    ->update();

Laravel

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::table('posts', function (Blueprint $table) {
    $table->integer('likes')
        ->default(0);
});

Working with nullable columns

MySQL

ALTER TABLE `posts` ADD COLUMN `likes` INTEGER NOT NULL;
ALTER TABLE `posts` ADD COLUMN `published_at` TIMESTAMP NULL;

Phinx

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Db\Table\Column;

$this->table('posts')
    ->addColumn(
        (new Column())
            ->setName('likes')
            ->setType(MysqlAdapter::PHINX_TYPE_INTEGER)
            ->setDefault(0)
            ->setNull(false) // NOT NULL
    )->addColumn(
        (new Column())
            ->setName('published_at')
            ->setType(MysqlAdapter::PHINX_TYPE_TIMESTAMP)
            ->setNull(true) // NULL
    )
    ->update();

Laravel

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::table('posts', function (Blueprint $table) {
    $table->integer('likes')
        ->nullable(false);

    $table->integer('published_at')
        ->nullable(true);
        // or just ->nullable();
});

Specifying a comment for a column

MySQL

ALTER TABLE `posts` ADD COLUMN `likes` INTEGER COMMENT 'Column comment';

Phinx

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Db\Table\Column;

$this->table('posts')
    ->addColumn(
        (new Column())
            ->setName('likes')
            ->setType(MysqlAdapter::PHINX_TYPE_INTEGER)
            ->setComment('Column comment')
    )
    ->update();

Laravel

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::table('posts', function (Blueprint $table) {
    $table->integer('likes')
        ->comment('Column comment');
});

Working with signed columns

MySQL

ALTER TABLE `posts` ADD COLUMN `likes` INTEGER UNSIGNED;
ALTER TABLE `posts` ADD COLUMN `rating` TINYINT SIGNED;

Phinx

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Db\Table\Column;

$this->table('posts')
    ->addColumn(
        (new Column())
            ->setName('likes')
            ->setType(MysqlAdapter::PHINX_TYPE_INTEGER)
            ->setSigned(false) // UNSIGNED
    )->addColumn(
        (new Column())
            ->setName('rating')
            ->setType(MysqlAdapter::PHINX_TYPE_BOOLEAN)
            ->setSigned(true) // SIGNED
    )
    ->update();

Laravel

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::table('posts', function (Blueprint $table) {
    $table->integer('likes')
        ->unsigned();

    // The third argument must be with "true" boolean value
    $table->integer('rating', false, true);
});

Specifying a column character set

MySQL

ALTER TABLE `posts` ADD COLUMN `content` TEXT CHARACTER SET 'utf8mb4'

Phinx

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Db\Table\Column;

$this->table('posts')
    ->addColumn(
        (new Column())
            ->setName('content')
            ->setType(MysqlAdapter::PHINX_TYPE_TEXT)
            ->setEncoding('utf8mb4')
    )
    ->update();

Laravel

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::table('posts', function (Blueprint $table) {
    $table->text('content')
        ->charset('utf8mb4');
});

Specifying a column collation

MySQL

ALTER TABLE `posts` ADD COLUMN `content` TEXT COLLATE 'utf8mb4_unicode_ci'

Phinx

use Phinx\Db\Adapter\MysqlAdapter;
use Phinx\Db\Table\Column;

$this->table('posts')
    ->addColumn(
        (new Column())
            ->setName('content')
            ->setType(MysqlAdapter::PHINX_TYPE_TEXT)
            ->setCollation('utf8mb4_unicode_ci')
    )
    ->update();

Laravel

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;

Schema::table('posts', function (Blueprint $table) {
    $table->text('content')
        ->collation('utf8mb4_unicode_ci');
});

TODO

• Indices
• Foreign Keys
• Additional manipulation with schema|table|column