Modify an existing database column in a Laravel migration

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
 
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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
 
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 Responses to “Modify an existing database column in a Laravel migration”

  1. Jorge Vazquez

    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. David Barrington

    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. Flipflops

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

  4. Arno

    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.

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>