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.



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.

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)
        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 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) {

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


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', '=', '')
      ->join('user_project', '', '=', 'user_project.project_id')
      ->join('user', 'user_project.user_id', '=', '')
      ->where('', $this->id);

Learn Something New Everyday,
Connect With The Best Developers!

Sign Up Now!

& 500k+ others use Hashnode actively.

Oliver Russell's photo

In case if you are using an older MySQL version and want to use artisan command to create table in laravel , you must define default string length in the AppServiceProvider.php file

Emil Moe's photo

I'm not sure if the last accounts() method can be done in a fancier way?

Also feel free to give me some feedback on my article :-) I'm not very experienced in it except for writing notes for myself, but would like to share from my knowledge, although this one might be very specific of course.

Ian Edington's photo

Software Engineer | Entrepreneur

I'm running into the same problem. I'm running into the issue where depending on the information in the table I want to create different records in the second table. Do you have any insight on how to do that?

Emil Moe's photo

Software Engineer & Consultant

Hi Ian Thanks for reaching out. I'm not sure I understand your question right. You can't just use statements as in my article?

Oliver Russell's photo

Laravel migration table creation is really easy now with PHP artisan commands. A simple app can be created in minutes now with this.