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!