Generate Laravel migrations from an existing database

One of the common challenges when migrating a legacy PHP application to Laravel is creating database migrations based on the existing database.

Depending on the size of the database, it can become an exhausting task.
I had to do it a few times, but recently I stumbled upon a database with over a hundred tables.

As a programmer, we don’t have the patience to do such a task,
and we shouldn’t.
The first thought is how to automate it.
With that in mind, I searched for an existing solution, found some packages,
and picked one by kitloong,
the Laravel migration generator package.

Practical example using an existing database structure

Creating the tables

CREATE TABLE permissions
(
    id bigint unsigned auto_increment primary key,
    name varchar(255) not null,
    guard_name varchar(255) not null,
    created_at timestamp    null,
    updated_at timestamp    null,
    constraint permissions_name_guard_name_unique
    unique (name, guard_name)
)
collate = utf8_unicode_ci;

CREATE TABLE roles
(
    id bigint unsigned auto_increment primary key,
    team_id bigint unsigned null,
    name varchar(255) not null,
    guard_name varchar(255) not null,
    created_at timestamp null,
    updated_at timestamp null,
    constraint roles_team_id_name_guard_name_unique
    unique (team_id, name, guard_name)
)
collate = utf8_unicode_ci;

CREATE TABLE role_has_permissions
(
    permission_id bigint unsigned not null,
    role_id bigint unsigned not null,
    primary key (permission_id, role_id),
    constraint role_has_permissions_permission_id_foreign
    foreign key (permission_id) references permissions (id)
    on delete cascade,
    constraint role_has_permissions_role_id_foreign
    foreign key (role_id) references roles (id)
    on delete cascade
)
collate = utf8_unicode_ci;

CREATE INDEX roles_team_foreign_key_index on roles (team_id);

Installing the package

composer require --dev kitloong/laravel-migrations-generator

Running the package command that does the magic

You can specify or ignore the tables you want using --tables= or --ignore= respectively.

Below is the command I ran for the tables we created above.
To run for all the tables, don’t add any additional filters.

php artisan migrate:generate --tables="roles,permissions,role_permissions"

Command output

Using connection: mysql

Generating migrations for: permissions,role_has_permissions,roles

Do you want to log these migrations in the migrations table? (yes/no) [yes]:
> yes

Setting up Tables and Index migrations.
Created: /var/www/html/database/migrations/2023_06_08_132125_create_permissions_table.php
Created: /var/www/html/database/migrations/2023_06_08_132125_create_role_has_permissions_table.php
Created: /var/www/html/database/migrations/2023_06_08_132125_create_roles_table.php

Setting up Views migrations.

Setting up Stored Procedures migrations.

Setting up Foreign Key migrations.
Created: /var/www/html/database/migrations/2023_06_08_132128_add_foreign_keys_to_role_has_permissions_table.php

Finished!

Checking the migration files

Permissions table: 2023_06_08_132125_create_permissions_table.php

...

Schema::create('roles', function (Blueprint $table) {
    $table->bigIncrements('id');

    $table->unsignedBigInteger('team_id')
        ->nullable()
        ->index('roles_team_foreign_key_index');

    $table->string('name');
    $table->string('guard_name');
    $table->timestamps();

    $table->unique(['team_id', 'name', 'guard_name']);
});

...

Roles table: 2023_06_08_132125_create_role_has_permissions_table.php

...

Schema::create('roles', function (Blueprint $table) {
    $table->bigIncrements('id');

    $table->unsignedBigInteger('team_id')
        ->nullable()
        ->index('roles_team_foreign_key_index');

    $table->string('name');
    $table->string('guard_name');
    $table->timestamps();

    $table->unique(['team_id', 'name', 'guard_name']);
});

...

Pivot table: 2023_06_08_132125_create_roles_table.php

...

Schema::create('role_has_permissions', function (Blueprint $table) {
    $table->unsignedBigInteger('permission_id');

    $table->unsignedBigInteger('role_id')
        ->index('role_has_permissions_role_id_foreign');

    $table->primary(['permission_id', 'role_id']);
});

...

Add foreign key to the pivot table: 2023_06_08_132128_add_foreign_keys_to_role_has_permissions_table.php

...

Schema::table('role_has_permissions', function (Blueprint $table) {
    $table->foreign(['permission_id'])
        ->references(['id'])
        ->on('permissions')
        ->onUpdate('NO ACTION')
        ->onDelete('CASCADE');

    $table->foreign(['role_id'])
        ->references(['id'])
        ->on('roles')
        ->onUpdate('NO ACTION')
        ->onDelete('CASCADE');
});

...

This is just one of the challenges when migrating a legacy PHP application to Laravel.

The following post will be about password hashing algorithm incompatibility.

Join the discussion on Twitter.