Installation

The installation of this library can be done with Composer:

composer require aplus/database-extra

Introduction

The Database Extra library provides tools for working with database migrations and seeding.

Migrations

Migrations works like a database versioning.

With them, you can manage schemas and tables. Up and down for a given version.

The migration files are according to the template below:

use Framework\Database\Extra\Migration;

return new class() extends Migration
{
    public function up() : void
    {
        //
    }

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

In the up method, the creation and modification of schemas and tables must be performed.
While in the down method, the opposite way is carried out, undoing the modifications carried out in the up method.

The file names must follow an order, in which the migrations will go up and down.

You can use the date and time as a prefix and a brief description. For example:

2022_05_26_213000_create_table_users.php

Another way is simply numbering the start of the migration file. For example:

100_create_table_users.php

The important thing is that each migration system has its own standard.

In the examples that we will see, we will use a number as a prefix and a brief description.

Inside the migration directory, we create the 100_create_table_users.php file:

migrations/100createtableusers.php

use Framework\Database\Definition\Table\TableDefinition;
use Framework\Database\Extra\Migration;

return new class() extends Migration
{
    protected string $table = 'Users';

    public function up() : void
    {
        $this->getDatabase()->createTable($this->table)
            ->definition(function (TableDefinition $def) {
                $def->column('id')->int()->primaryKey()->autoIncrement();
                $def->column('name')->varchar(32);
                $def->column('birthday')->date();
            })->run();
    }

    public function down() : void
    {
        $this->getDatabase()->dropTable($this->table)->ifExists()->run();
    }
};

This file creates the Users table in the up method and drop it in the down method.

The file below creates the Posts table, which will run after 100createtableusers, due to it starting with the number 200.

migrations/200createtableposts.php

use Framework\Database\Definition\Table\TableDefinition;
use Framework\Database\Extra\Migration;

return new class() extends Migration
{
    protected string $table = 'Posts';

    public function up() : void
    {
        $this->getDatabase()->createTable($this->table)
            ->definition(function (TableDefinition $def) {
                $def->column('id')->int()->primaryKey()->autoIncrement();
                $def->column('userId')->int()->null();
                $def->column('title')->varchar(128);
                $def->column('contents')->text();
                $def->column('createdAt')->timestamp();
                $def->index()
                    ->foreignKey('userId')
                    ->references('Users', 'id')
                    ->onDelete('SET NULL')
                    ->onUpdate('CASCADE');
            })->run();
    }

    public function down() : void
    {
        $this->getDatabase()->dropTable($this->table)->ifExists()->run();
    }
};

In the next example, let's change the Users table, adding two columns to it on the way up and removing them on the way down:

migrations/300altertableusers.php

use Framework\Database\Definition\Table\TableDefinition;
use Framework\Database\Extra\Migration;

return new class() extends Migration
{
    protected string $table = 'Users';

    public function up() : void
    {
        $this->getDatabase()->alterTable($this->table)
            ->add(function (TableDefinition $def) {
                $def->column('email')->varchar(255)->after('id');
                $def->column('password')->varchar(255);
            })->run();
    }

    public function down() : void
    {
        $this->getDatabase()->alterTable($this->table)
            ->dropColumnIfExists('email')
            ->dropColumnIfExists('password')
            ->run();
    }
};

The files must return an instance of the Framework\Database\Extra\Migration class to run in a Migrator.

use Framework\Database\Database;
use Framework\Database\Extra\Migrator;

$database = new Database('root', 'password', 'app');

$directories = [
    __DIR__ . '/migrations',
];

$migrator = new Migrator($database, $directories);

foreach($migrator->migrateUp() as $name) {
    echo $name . PHP_EOL;
}

foreach($migrator->migrateDown() as $name) {
    echo $name . PHP_EOL;
}

foreach($migrator->migrateDown(3) as $name) {
    echo $name . PHP_EOL;
}

foreach($migrator->migrateTo('2022_05_26_123000') as $name) {
    echo $name . PHP_EOL;
}

use Framework\Database\Extra\Seeder;

class SeederName extends Seeder
{
    public function run() : void
    {
        //
    }
}

use Framework\Database\Extra\Seeder;

class UsersSeeder extends Seeder
{
    public function run() : void
    {
        $this->getDatabase()->insert('Users')
            ->columns('name', 'birthday', 'email', 'password')
            ->values([
                [
                    'Seiya',
                    '2009-12-01',
                    '[email protected]',
                    password_hash('password', PASSWORD_DEFAULT),
                ],
                [
                    'Shiryu',
                    '2008-10-04',
                    '[email protected]',
                    password_hash('password', PASSWORD_DEFAULT),
                ],
                [
                    'Hyoga',
                    '2008-01-23',
                    '[email protected]',
                    password_hash('password', PASSWORD_DEFAULT),
                ],
                [
                    'Shun',
                    '2009-09-09',
                    '[email protected]',
                    password_hash('password', PASSWORD_DEFAULT),
                ],
                [
                    'Ikki',
                    '2007-08-15',
                    '[email protected]',
                    password_hash('password', PASSWORD_DEFAULT),
                ],
            ])->run();
    }
}

use Framework\Database\Extra\Seeder;

class DatabaseSeeder extends Seeder
{
    public function run() : void
    {
        $this->call([
            UsersSeeder::class,
            PostsSeeder::class,
        ]);
    }
}

use Framework\Database\Database;

$database = new Database('root', 'password', 'app');

$seeder = new DatabaseSeeder($database);
$seeder->run();