Tag Archives: laravel

Allow SQLite to use foreign keys in Laravel 4

Because of backwards compatibility foreign key checking is by default disabled in SQLite databases. As current Laravel 4 version does not provide any elegant way of enabling that, a little hack is required to force foreign keys for SQLite connections.

Add the following code to the end of file app/start/global.php

if (DB::connection() instanceof Illuminate\Database\SQLiteConnection) {
  DB::statement(DB::raw('PRAGMA foreign_keys=1'));
}

Laravel 4 database seeding example

Database seeding is a convenient way to fill Laravel 4 application with test data, this can be useful while application developing, or testing. In provided example ‘users’ table will be seeded with test user data. Example table was previously created with Laravel Migrations which is described in this post.

First create seeding class for the table under app/database/seeds directory, in this case UserTableSeeder.php

class UserTableSeeder extends Seeder {

	public function run() {
		// delete existing db rows
		DB::table('users')->delete();
		// create new user
		User::create(array(
			'name' => 'test user 1',
			'email' => 'foo@bar.com',
			'password' => Hash::make('password')
		));
	}

}

Method run() deletes all existing users, then creates new user row with provided name, email and password (hashed with Hash::make() function). Please note that ‘User::create’ requires User model to be set up. By default User model is created in app/models, so there is no need of doing that in this particular case.

Next we need to add new seeder class to app/database/seeds/DatabaseSeeder.php

class DatabaseSeeder extends Seeder {

	/**
	 * Run the database seeds.
	 * @return void
	 */
	public function run() {
		Eloquent::unguard();
		$this->call('UserTableSeeder');
	}

}

And this is it, now simply call the following artisan command

$ php artisan db:seed

And check if users table contains test data

mysql> select * from users;
+----+-------------+-------------+--------------------------------------------------------------+----------------+---------------------+---------------------+
| id | name        | email       | password                                                     | remember_token | created_at          | updated_at          |
+----+-------------+-------------+--------------------------------------------------------------+----------------+---------------------+---------------------+
|  1 | test user 1 | foo@bar.com | $2y$10$e2hVN4Yf7.aEOHiVRmN5XORQcuIaHUDege5V9i9792Wfx8DFe03W2 | NULL           | 2014-08-10 14:11:02 | 2014-08-10 14:11:02 |
+----+-------------+-------------+--------------------------------------------------------------+----------------+---------------------+---------------------+

Laravel 4 database migrations example

Laravel 4 migrations provides a convenient way of versioning changes in application database structure. It uses Laravel Schema Builder to store database structure in php classes, this structure is then utilised to create/alter/delete database tables in database that is currently used by application. Schema Builder is database type independent, so any database supported by Laravel can be used. This makes extremely easy to change database type, and keep the same tables structure (for instance from MySQL to SQLite).

Database schema should be altered if any change in database structure has to be done. This way all developers can use the same schema version, as php files can be easily stored in version control system. One important thing to notice about migrations: always create new migration file when you want to alter existing table. Migration class that was used should not be changed.

Let’s create simple ‘users’ table with migrations. Table should contain: id, name, email, password, remember_token, created_at and updated_at fields. First thing to do is set up database connection, this should be done by altering app/config/database.php file. The most commonly MySQL will be used, this requires to set username, password, host, and database name in ‘mysql’ array. When database connection is set up go to project’s root directory, and run:

$ php artisan migrate:make create_users --create --table=users

This command will create a new migration file for users table in app/database/migrations directory (by default). File name contains current date, time, and underscored class name provided after ‘migrate:make’ (create_users). Full path to the file is in my case app/database/migrations/2014_08_10_131305_create_users.php. Edit this file to provide users table fields and details. Remember to always use artisan commands to create migration files, don’t create them manually.

CreateUsers class has 2 methods: up() and down(). Up is being called when ‘php artisan migrate’ command is run (this creates/alters table), down() is being called when ‘php artisan migrate:rollback’ is run (this rollbacks last migration, so deletes users table in this particular case).

Complete CreateUsers code:

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateUsers extends Migration {

	/**
	 * Run the migrations.
	 * @return void
	 */
	public function up() {
		Schema::create('users', function(Blueprint $table) {
			$table->engine = 'InnoDB';
			$table->increments('id');
			$table->string('name', 128)->nullable();
			$table->string('email', 256);
			$table->string('password', 256);
			// remember token is nullable by default
			$table->rememberToken();
			$table->timestamps();
		});
	}

	/**
	 * Reverse the migrations.
	 * @return void
	 */
	public function down() {
		Schema::table('users', function() {
			Schema::drop('users');
		});
	}

}

Now this class can be used to create table with structure defined in up() method, for this purpose simply run:

$ php artisan migrate

Than log in to mysql and check what tables was created (I assume that database was empty before).

mysql> show tables;
+------------------+
| Tables_in_sm2dev |
+------------------+
| migrations       |
| users            |
+------------------+

mysql> describe migrations;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| migration | varchar(255) | NO   |     | NULL    |       |
| batch     | int(11)      | NO   |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+

mysql> describe users;
+----------------+------------------+------+-----+---------------------+----------------+
| Field          | Type             | Null | Key | Default             | Extra          |
+----------------+------------------+------+-----+---------------------+----------------+
| id             | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| name           | varchar(128)     | YES  |     | NULL                |                |
| email          | varchar(256)     | NO   |     | NULL                |                |
| password       | varchar(256)     | NO   |     | NULL                |                |
| remember_token | varchar(100)     | YES  |     | NULL                |                |
| created_at     | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at     | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
+----------------+------------------+------+-----+---------------------+----------------+

Migrations table contains data internally used by Laravel to manage migrations, users table contains fields previously defined in CreateUsers class.