Modify an existing database column in a Laravel migration

Update

The information in this post is no longer correct.

It is now possible to update columns using migrations by adding the Doctrine DBAL library to your Laravel site. https://laravel.com/docs/5.6/migrations#modifying-columns

p.s. Current Laravel is a joy to work with – I am most definitely now a fan :)

I’ve been messing around with Laravel – the ‘Clean & Classy PHP Framework’… I’m not totally sold yet, but there is a lot to like, so I am persevering.

One useful feature that Laravel includes is migrations – in essence the ability to place your database schema under version control along with your code.

Laravel handles migrations though it’s CLI ‘Artisan

Rather inexplicably (to me at least*) the migrations tool – Schema Builder does include the ability to modify columns once they have been created, however you can use use raw SQL as a workaround.

*Schemas evolve. I find myself often adding columns, tweaking existing columns regularly and dropping columns rather rarely…

Let’s build some migrations…

First things first – create the migrations table. This is used to keep track of migration within your application.

1. Open a terminal in the root of your application.

2. Create the migrations table:


php artisan migrate:install
 

3. Create an empty migrations script


php artisan migrate:make create_users
 

create_users is simply a descriptive name for your migration – you can call it anything you want…

This will create a skeleton file in /path_to_laravel/application/migrations that includes the timestamp in it’s name e.g.

2013_05_23_201939_create_users.php


class Create_Users {

	/**
	 * Make changes to the database.
	 *
	 * @return void
	 */
	public function up()
	{
		//
	}

	/**
	 * Revert the changes to the database.
	 *
	 * @return void
	 */
	public function down()
	{
		//
	}

}
 

This isn’t much use – we need to:

4. Write our migration – edit the file that we just created. Create the table and insert some test data.


class Create_Users {

	/**
	 * Make changes to the database.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::create('users',function($table){
			$table->increments('id');
			$table->string('username', 20); 
			$table->string('email', 255); 
			$table->string('password', 255); 
			$table->boolean('active');
			$table->timestamps(); 
		});
		
		DB::table('users')->insert(array(
					'username' => 'admin',
					'email' => '[email protected]',
					'password' => Hash::make('123456'),
					'active' => 1
				
		));
	}

	/**
	 * Revert the changes to the database.
	 *
	 * @return void
	 */
	public function down()
	{
		//
	}
 

5. Run the migration:


php artisan migrate
 

6. Ooops – forgotten to set a default on one of the fields.

Unfortunately you can’t change this via the Schema Builder, but we can still use another migration:


php artisan migrate:make update_users
 

This will create a file called something like 2013_05_23_202930_update_users.php

7. We need to write our migration. This needs to be actual SQL which we call with DBquery e.g.


class Modify_Users {

	/**
	 * Make changes to the database.
	 *
	 * @return void
	 */
	public function up()
	{
		DB::query("ALTER TABLE `ltest`.`users` CHANGE COLUMN `active` `active` tinyint(1) NOT NULL DEFAULT '1';");
		
		DB::table('users')->insert(array(
				'username' => 'test2',
				'email' => '[email protected]',
				'password' => Hash::make('123456'),
				
		
		));
	}

	/**
	 * Revert the changes to the database.
	 *
	 * @return void
	 */
	public function down()
	{
		//
	}

}

I’ll insert another user and see if the default for the ‘active’ column is set correctly.

8. Run the migrations again.


php artisan migrate
 

Done!

4 thoughts to “Modify an existing database column in a Laravel migration”

  1. Schemabuilder let’s you change that.

    You just issue a rollback and then, for example to add the default (I can’t format the code, but I’ve been doing this kind of migrations with artisan).

    public function up()
    {
    Schema::table(‘media_element_properties’, function(Blueprint $table)
    {
    $table->boolean(‘is_link’)->default(True);
    });
    }

    /**
    * Reverse the migrations.
    *
    * @return void
    */
    public function down()
    {
    Schema::table(‘media_element_properties’, function(Blueprint $table)
    {
    $table->dropColumn(‘is_link’);
    });
    }

    For the insert, wouldn’t it be better to use seeds ?

  2. Thanks for clearing this up, it does seem inexplicable. I thought I was retarded (probably still am), and missing something about the migrations somehow. Databases like code change all the time. Nevermind, there is still a lot to like.

  3. Actually I think this may have changed in Laravel 4 (but haven’t tried it out yet)

  4. It’s still somewhat comical that Laravel migrations don’t support a true diff-based migration option. Multiple migration files are very hard to keep track of in the early stages of development as a schema may be rapidly changing. In a waterfall development environment where the schema is decided well in advance, allowing changes only by committee and lengthy meetings, Laravel migrations are great. In the real-world of modern software development though, you’ll likely be wanting to tweak your migration files on a one-migration-file-per-table basis. The current option requires a rollback, thus deleting any basis (or production, gasp!) data. Laravel has this wrong, in my opinion. I’ve been meaning to write a migrate:diff for some time. This would first create a cumulative schema of any existing migration files then apply the differences by modifying the schema.

Comments are closed.