From CodeIgniter to Laravel | part 4: managing databases

UPDATE: I have re-written this article for the new Laravel 4. You’ll find the updated article over at Geek & Dummy.

Contents


AVZ DatabaseFor almost all my previous web design, I’ve used phpMyAdmin to administer the databases. I speak SQL, so that has never been a big deal. But Laravel comes with some excellent tools for administering your databases more intelligently and (most importantly!) with less effort. Migrations offer version control for your application’s database. For each version change, you create a “migration” which provides details on the changes to make and how to roll back those changes. Once you’ve got the hang of it, I reckon you’ll barely touch phpMyAdmin again.

Configuration

So let’s assume that I’m creating a new website about ducks. When I created the virtual host, Virtualmin also created my “ducks” database. I’m going to create a MySQL user with the same name, with full permission to access the new database. Here’s how I do that from a root SSH login:

echo "GRANT ALL ON ducks.* TO 'ducks'@localhost IDENTIFIED BY 'newpassword';" | mysql -p
Enter password:[root MySQL password]

This creates a new MySQL user, “ducks” and gives it all privileges associated to the database in question. Next we need to tell Laravel about these credentials. The important lines in the file application/config/database.php are:

<?php
return array(

//...

	'default' => 'mysql',

	'connections' => array(

//...

		'mysql' => array(
			'driver'   => 'mysql',
			'host'     => '127.0.0.1',
			'database' => 'ducks',
			'username' => 'ducks',
			'password' => 'newpassword',
			'charset'  => 'utf8',
			'prefix'   => '',
		),

//...

	),

//...

);

Initialise Migrations

The migration environment must be initialised for this application. We do this using Laravel’s command line interface, Artisan. From an SSH login:

php artisan migrate:install
Migration table created successfully.

This creates a new table, laravel_migrations, which will be used to track changes to your database schema (i.e. structure), going forwards.

My ducks application will have a single table to start with, called “ducks” [Note: it is significant that we’re using a plural word here; I recommend you follow suit]. This table does not yet exist; we will create it using a migration. To kick this off, use the following Artisan command:

php artisan migrate:make create_ducks_table
Great! New migration created!

This will create a new file named something like “2013_04_15_085356_create_ducks_table.php”. If, like me, you’re developing remotely, you’ll need to pull this new file into your development environment. In NetBeans, for example, right-click the migrations folder, click “download” and follow the wizard.

You can deduce from the naming of the file that migrations are effectively time-stamped. This is where the life of your applications database begins. The migrations file will look a bit like this:

<?php

class Create_Ducks_Table {

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

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

}&#91;/php&#93;

As you can probably guess, in the "up" function, you enter the code necessary to create the new table (to move "up" a migration) and in the "down" function, you do the reverse (to move "down" or to roll back a migration).

<h1>Create first table</h1>

Your first migration will probably be to create a table (unless you have already created or imported tables via some other method).  Naturally, Laravel has a class for this purpose, the <a href="http://laravel.com/docs/database/schema" target="_blank">Schema class</a>.  Here's how you can use it, in your newly-created migrations php file:

[php]<?php

class Create_Ducks_Table {

	/**
	 * Make changes to the database.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::create('ducks', function($table) {
				$table->increments('id');              // auto-incrementing primary key
				$table->string('name', 255);           // varchar field; length 255 characters
				$table->date('birthdate')->nullable(); // can be empty
				$table->boolean('can_fly')->default(TRUE);
				$table->integer('children')->unsigned();
				$table->text('biography');
				$table->timestamps(); // special created_at and updated_at timestamp fields
		});	
	}

	/**
	 * Revert the changes to the database.
	 *
	 * @return void
	 */
	public function down()
	{
		Schema::drop('ducks');
	}

}

To run the migration (i.e. to create the table), do the following at your SSH login:

php artisan migrate
Migrated: application/2013_04_15_085356_create_ducks_table

That creates the table, as described. And if you need to roll back:

php artisan migrate:rollback
Rolled back: application/2013_04_15_085356_create_ducks_table

This removes the table.

By examining the Schema class documentation, you’ll see how you can use future migrations to add or remove fields, create indexes, etc. In my next tutorial, I’ll have a look at using databases in your application.

AVZ Database image copyright © adesigna, licensed under Creative Commons. Used with permission.

3 Replies to “From CodeIgniter to Laravel | part 4: managing databases”

  1. Looking forward to your next tutorial on “using databases in your application”. It also looks like we are really close to Laravel 4 so wondering if any of this info will change at all?

    Thanks for your work so far!

    — Dave

    1. Thanks Dave! I’m not far off tutorial 5, but with L4 due out imminently (and it’s a big improvement, by the looks of it), I’m not sure whether to refresh the whole series.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.