Laravel migration with database changes

Sometimes you might need to make changes to your production application which requires a restructure in your database layout.

In a project I'm currently working on where users can create projects and register spent hours on that project, a client recently asked me if they could have sort of accounts per project in order to keep track of what their employees spend their time during.

This means at that time my tables would exist of

• user
• record
• project

Before this when a user registered he would create a new record in a record table which then pointed at both the user and the project.

Consider the record table:

id | user_id | project_id | duration

In order to add accounts we first need to migrate a new account table as we need to store something new, but also need to alter the record table.

php artisan make:migration create_account_table --create=account
php artisan make:migration alter_record_table_account --table=record

The account migration is straightforward creating a new table. I have more columns in my code, but for simplicity I will stick with just a few here in the example.

create_account_table:

$table->increments('id');
$table->timestamps();
$table->integer('project_id')->unsigned();
$table->string('name');
$table->foreign('project_id')->references('id')->on('project');

The tricky part comes when we want to migrate the record table, as information was already stored in this table.

As for anything, it's easier to build it in steps:

• Create new columns
• Migrate old data to the new structure
• Drop obsolete columns
• Migrate

Create new columns

From here on the work will be on the record table in the alter_record_table migration.

Since I can't fill in all the information just yet, I'm gonna create the new account_id column with a nullable option. I will later change this to not null when data has been filed.

$table->integer('account_id')->unsigned()->nullable()->default(null);
$table->foreign('account_id')->references('id')->on('account');

Migrate old data to the new structure

In order to fill the account_id column I need some accounts, I tend to use raw queries in my migrations so they don't rely on any application architecture. It's especially important if you later modify your models and redeploy the whole application, so the migrations will still be working.

First I will create default accounts for any project:

$projects = DB::Select(DB::Raw('SELECT project_id FROM record GROUP BY project_id'));

foreach ($projects as $project)
{
    DB::Statement('
        INSERT INTO account (created_at, updated_at, name, project_id)
        VALUES (NOW(), NOW(), "Migrated account", '. $project->project_id .')
    ');
}

Since all projects has exactly one account now, it's very easy to apply that account id to the record based on project_id:

DB::Statement('UPDATE record SET account_id = (SELECT account.id FROM account WHERE project_id = record.project_id)');

Drop obsolete columns

And now the project_id column has become obsolete, so that can be removed. Also the account_id should not be nullable anymore:

Schema::table('record', function (Blueprint $table) {
    $table->dropForeign('record_project_id_foreign');
    $table->dropColumn('project_id');
    $table->integer('account_id')->unsigned()->change();
});

Migrate

Your migration is now completely automised and ready to be deployed, run the migrate command and test that no errors occurred along.

php artisan migrate

Relations

On a last note, if you still want to relate an user object to all related projects, it can be done by adding this method to the user class:

public function accounts()
{
    return Account::join('project', 'account.project_id', '=', 'project.id')
      ->join('user_project', 'project.id', '=', 'user_project.project_id')
      ->join('user', 'user_project.user_id', '=', 'user.id')
      ->where('user.id', $this->id);
}