Dev Notes

Software Development Resources by David Egan.

Laravel Database Management


Laravel, MariaDB, MySQL
David Egan

This article focuses on Laravel 5.4 development in a Ubuntu 16.04 desktop environment. It is assumed that a suitable database has already been created.

Create a Database Table

The Laravel way involves creating and running a suitable “migration”. Migrations allow the app to create a new table or modify existing tables. Migrations are very powerful - amongst other things, they enable:

  • Version control of the database
  • Database modifications to be easily rolled-back

Create Migration

To create a migration:

php artisan make:migration create_users_table

This creates a new file in the database/migrations directory. The filename is based on the option passed to php artisan make:migration and a timestamp - for example: database/migrations/2017_05_29_094813_create_users_table.php.

If the migration is to create a new table, you can specify the table name by passing the create option to make:migration command:

php artisan make:migration create_users_table --create=users

This will stub out the migration file with the specified table. It’s probably a good idea to name the migration descriptively in the imperative case - for example “create_xx_table” or “adding_excerpt_to_posts_table”.

The following command sets up a migration that will create a database table called “stages”:

php artisan make:migration create_stages_table --table=stages

Once the migration has been created, it needs to be run to actually create the database table:

php artisan migrate

In this case, the migration class will be in database/migrations/2017_05_20_112319_create_stages_table.php.

Before running the migration and creating the new table, you’ll probably want to add some columns. You can do this by editing the up() method on the newly created migration. For example, to add a simple title column:

<?php
public function up()
    {
        Schema::create('stages', function (Blueprint $table) {
            $table->increments('id');
            $table->string('title');
            $table->timestamps();
        });
    }

NOTE: you need to ensure that the additional table methods are added BEFORE the timestamps() method or they won’t be applied.

Run or Rollback Migration

To apply the changes to the database, you need to run the migration:

php artisan migrate

To rollback a migration:

php artisan migrate:rollback

See: https://laravel.com/docs/5.4/migrations#generating-migrations

Rollback Problems

When attempting a rollback for the first time you may experience this error:

PHP Fatal error:  Uncaught UnexpectedValueException:
The stream or file "/var/www/html/test-laravel/storage/logs/laravel.log"
could not be opened: failed to open stream:
Permission denied in /var/www/html/test-laravel/vendor
/monolog/monolog/src/Monolog/Handler/StreamHandler.php:107

This occurs because you’re running the rollback command as your current user. The storage directory will be owned by the server user (www-data in the case of Ubuntu), and will probably belong to your main user’s group (depending upon how you assigned permissions when you first installed the app).

The fix is simple - chmod the log file to give your user write permissions. The file will probably have -rw--r--r-- permissions, so change these to 664:

sudo chmod 664 /var/www/html/test-laravel/storage/logs/laravel.log

Seeding a Table

Laravel 5.4 ships with the Faker Factory class included, which can be used to “seed” the database.

Create a seeder stub for the ‘stages’ table:

php artisan make:seeder StagesTableSeeder

This stubs out a StagesTableSeeder class in database/seeds/StagesTableSeeder.php.

Example Seeder:

<?php

use Illuminate\Database\Seeder;

class UsersTableSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        factory(App\User::class, 50)->create();
    }
}

Defining Model Factories

The method above uses the factory() helper function. This references a Model Factory that you can create in database/factories. You could add all factory logic in database/factories/ModelFactory.php - but it’s easier to keep code organised if you duplicate this file and add factory logic for your models within appropriately named files. For example, database/factories/PostFactory.php:

<?php

/*
|--------------------------------------------------------------------------
| Model Factory for Posts
|--------------------------------------------------------------------------
|
| Adds a random user_id to the dummy posts.
|
*/

/** @var \Illuminate\Database\Eloquent\Factory $factory */
$factory->define(App\Post::class, function (Faker\Generator $faker) {

    $userID = App\User::inRandomOrder()->first()->id;

    return [
        'title' => $faker->sentence(),
        'body' => $faker->text(60),
        'excerpt' => $faker->text(30),
        'user_id' => $userID,
    ];
});

You can call the new seeder from the run() method in seeds/DatabaseSeeder.php:

<?php

use Illuminate\Database\Seeder;

class DatabaseSeeder extends Seeder
{
    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        $this->call(UsersTableSeeder::class);
        $this->call(PostsTableSeeder::class);
    }
}

This helps to keep seeder classes nice and neat.

Run the seeders:

php artisan db:seed

Rollback all database migrations and re-seed:

php artisan migrate:refresh --seed

comments powered by Disqus