Laravel 101 - Database

In this article, we will look at how to interact with databases in Laravel. Since it all becomes a bit abstract without an example, we will be creating a database to store our news articles.

Configuration

You configure connections to all your databases inside the config/database.php file. Laravel 5 supports four database systems: SQLite, MySQL, PostgreSQL and SQL Server. The configuration for all four has already been included in the config/database.php file.

Set the default key in the returned array in config/database.php to one of the four values - sqlite, mysql, pgsql or sqlsrv.

By default, the host, database, username, and password keys in the respective database connection are obtained from the DB_HOST, DB_DATABASE, DB_USERNAME and DB_PASSWORD variables defined inside the .env file, respectively. So to change these database settings, you shouldn't touch the config/database.php file at all, just change the values of the variables in .env.

Creating the Database

You probably want to create a new database to house the data for your application. So first we must create a new database; we also need to create a new user and grant him/her full permissions to that database.

We will use MySQL since that is the default. We will also use the default database settings, but you should change it when deploying to production.

DB_HOST=localhost
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret
Create new user

Enter the MySQL command-line tool

$ mysql -u root -p

Then create a new user according to values set in .env

mysql > CREATE USER 'homestead'@'localhost' IDENTIFIED BY 'secret';

And to check it has been created, run

mysql > SELECT User,Host FROM mysql.user;

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | 127.0.0.1 |
| root             | ::1       |
| debian-sys-maint | localhost |
| homestead        | localhost |
| root             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
Create new database

First, create a database named homestead

mysql> create database homestead;

Next, assign full permissions of the database to the new user.

mysql> grant all on homestead.* to 'homestead'@'localhost';

So we have created a new user, next, we create the tables.

Migration

Traditionally, you'd invoke the mysql command line tool and create the tables schema you need for your application manually, but this means it cannot be version controlled, and is prone to mistakes.

Migrations resolves this issue by storing the schema in migration files which can be version controlled. You can then create the tables from the migration file using a single command provided by artisan.

The Migration File

We will be using artisan's make:migration command to create our migration file. Let's see how that works.

$ php artisan help make:migration
Usage:
 make:migration [--create[="..."]] [--table[="..."]] name

Arguments:
 name                  The name of the migration

Options:
 --create              The table to be created.
 --table               The table to migrate.
 --help (-h)           Display this help message
 --quiet (-q)          Do not output any message
 --verbose (-v|vv|vvv) Increase the verbosity of messages: 1 for normal output, 2 for more verbose output and 3 for debug
 --version (-V)        Display this application version
 --ansi                Force ANSI output
 --no-ansi             Disable ANSI output
 --no-interaction (-n) Do not ask any interactive question
 --env                 The environment the command should run under.

As you can see, it requires a name argument; we will also use the --create to help us create a new table.

$ php artisan make:migration create_articles_table --create=articles
Created Migration: 2015_04_17_100504_create_articles_table

You can now find the migration file inside the database/migrations/ directory

2015_04_17_100504_create_articles_table.php

Notice Laravel automatically prepended a timestamp to the front of the file. This will determine the order of the migration - earlier is first.

If we look inside the migration file, this is what we'll find:

<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateArticlesTable extends Migration {
        public function up()
        {
                Schema::create('articles', function(Blueprint $table)
                {
                        $table->increments('id');
                        $table->timestamps();
                });
        }
        public function down()
        {
                Schema::drop('articles');
        }

}

Because we specified create=articless when running make:migration, when we run the migration, it will create a new table for us, using the schema defined in the Schema::create() method.

The $table->increments command adds an auto-incrementing ID as the primary key column, and the $table->timestamps() command adds the created_at and updated_at columns to the table.

We'd want to add additional fields to the table, such as title, body, published and author. So we simply add the columns using the Schema Builder.

The title column will be of type VARCHAR, body will be of type TEXT. We are storing the published field as an UNIX timestamp and thus require a column of type INTEGER. We will also be storing the ID's of the authors in the author column. Since there won't be many authors, we can use SMALLINT as the column type.

Schema::create('articles', function(Blueprint $table)
{
    $table->increments('id');
    $table->string('title');
    $table->text('body');
    $table->integer('published');
    $table->smallInteger('author');
    $table->timestamps();
});

See the documentation for all valid column types.

Running the Migration

Continue making all the necessary migration files. Once you're done, you can run the migration at the application root directory.

$ php artisan migrate
Migrated: 2015_04_17_100504_create_articles_table

If running the migration throws a [PDOException] could not find driver driver, that means the php-mysql extension is not installed, and you'd need to run something like apt-get install php5-mysql

We can then check that the table is created correctly.

mysql> use homestead;
mysql> describe articles;
+------------+------------------+------+-----+---------------------+----------------+
| Field      | Type             | Null | Key | Default             | Extra          |
+------------+------------------+------+-----+---------------------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| title      | varchar(255)     | NO   |     | NULL                |                |
| body       | text             | NO   |     | NULL                |                |
| published  | int(11)          | NO   |     | NULL                |                |
| author     | smallint(6)      | NO   |     | NULL                |                |
| created_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
+------------+------------------+------+-----+---------------------+----------------+

Database Seeding

Up til this point, we have created a schema but no content. To continue developing our application, we need dummy data to work from; so we should be glad that Laravel provides this functionality out-of-the-box.

Inside the database/seeds directory, you'll find a DatabaseSeeder.php file containing the DatabaseSeeder class.

Later on, we will use the db:seed command, which will invoke the run() method in DatabaseSeeder and populate our database.

The default DatabaseSeeder.php file:

use Illuminate\Database\Seeder;
use Illuminate\Database\Eloquent\Model;

class DatabaseSeeder extends Seeder {
        public function run()
        {
                Model::unguard();
                // $this->call('UserTableSeeder');
        }

}

The Model::unguard() method allows for mass assignment.

Next, we will create a ArticlesTableSeeder class by copying the DatabaseSeeder class. We will also add $this->call('ArticlesTableSeeder'); inside of the DatabaseSeeder.run() function so that it'll invoke the ArticlesTableSeeder.run() method when ran. In effect, we are using the DatabaseSeeder.run() method as a caller for all our other classes.

use Illuminate\Database\Seeder;
use Illuminate\Database\Eloquent\Model;

class DatabaseSeeder extends Seeder {
        public function run()
        {
                Model::unguard()
                $this->call('ArticlesTableSeeder');
        }
}

class ArticlesTableSeeder extends Seeder {
        public function run()
        {       
            DB::table('articles')->insert([
                'title' => 'First Blog Post'
                , 'body' => 'Something interesting to say!'
                , 'published' => '1429860898'
                , 'author' => 1
            ]);
        }
}
Faker

We passed an array of key-value pairs into the DB::table('articles')->insert() method for each article we want to create. But this can quickly gets labourious. What we need is something that will create dummy data. This is where Faker comes in.

Faker is a PHP library that generates fake data for you. To install faker, simply add it to the require-dev property of composer.json.

...
"require-dev": {
    "phpunit/phpunit": "~4.0",
    "phpspec/phpspec": "~2.1",
    "fzaninotto/faker": "1.4.*"
},
...

Then run composer update followed by composer install

This provides you with a Faker\Factory::create() method which will initiate a faker generator, where you can then generate data from.

class ArticlesTableSeeder extends Seeder {
  public function run()
  {
    $faker = Faker\Factory::create();
    DB::table('articles')->delete();
    $articles = [];
    for ($i = 0; $i < 100; $i++)
    {
      $article_dt = $faker->dateTime('now');
      array_push($articles, [
        'title' => $faker->sentence
        , 'body' => $faker->realText
        , 'published' => $faker->unixTime()
        , 'author' => $faker->numberBetween(1, 10)
        , 'created_at' => $article_dt
        , 'updated_at' => $article_dt
      ]);
    }
    DB::table('articles')->insert($articles);
  }
}

Next, run

$ php artisan db:seed
Seeded: ArticlesTableSeeder

This will seed 100 rows of data into your MySQL database. We can check by going into the MySQL CLI directly.

> select count(*) from articles;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.04 sec)

mysql> select * from articles limit 5;
+----+---------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------+--------+---------------------+---------------------+
| id | title         | body               | published | author | created_at          | updated_at          |
+----+---------------+--------------------+-----------------+--------+---------------------+---------------------+
|  1 | Libero itaque | I think it ...     | 695739128  |     9 | 1983-05-28 14:15:52 | 1983-05-28 14:15:52 |
|  2 | Eligendi quis | D,' she added...   | 1197710479 |     9 | 1978-03-02 16:43:47 | 1978-03-02 16:43:47 |
|  3 | Sint quasi se | Cat again, sit...  | 1367752259 |     9 | 2004-09-27 12:15:31 | 2004-09-27 12:15:31 |
|  4 | Quia illo tem | Then turn pale...  | 621507102  |     1 | 2007-10-11 18:08:33 | 2007-10-11 18:08:33 |
|  5 | Error quidem  | Queen was to eat...| 562820839  |     5 | 2011-03-21 23:35:27 | 2011-03-21 23:35:27 |
+----+---------------+--------------------+-----------------+--------+---------------------+---------------------+
5 rows in set (0.01 sec)

Because we had DB::table('articles')->delete();, each time the seed is ran the old data is deleted. An alternative to this is to use migrate:refresh --seed which will reset and re-run all migrations, along with seeding your data.

Table(s) Seeding

If you just want to seed one table, or a set of tables, and not the entire database, you can run db:seed with the --class flag, which will only invoke the run() method within that class.

$ php artisan db:seed --class=ArticlesTableSeeder

Eloquent ORM

Above, we created an array of arrays containing article data, and then inserted directly into the database using the Query Builder syntax.

However, in Laravel, we can use something called the Eloquent ORM, which is a different way for us to interact with our database. It requires us to define data Models which maps to a corresponding table. So instead of directly querying our table, we would run our Model's methods instead.

The benefits of using Eloquent is too much to get into right now, but they will be covered in the next post.

So, let's first rollback what we've done previously so we start clean.

php artisan migrate:rollback

This will rollback our last migration. Querying the database now shows our table is gone.

mysql> show tables;
+---------------------+
| Tables_in_homestead |
+---------------------+
| migrations          |
+---------------------+
1 row in set (0.01 sec)

Now, instead of make:migration, we run make:model, which actually creates a migration file for us automatically.

$ php artisan make:model Article
Model created successfully.
Created Migration: 2015_04_24_075432_create_articles_table

Because we don't want two duplicate tables, and since we already configured our old migration file, we need to remove the new migration file we just created.

Also look inside app/, you'll find a newly-created file called Article.php, which defines the Article class.

<?php namespace App;

use Illuminate\Database\Eloquent\Model;

class Article extends Model {

}

And because it extends Model, we can use the Model's methods, one of which is create(), to write a new row into our table.

Now, in our data seeder, we need to import our namespace so Laravel knows which Article class we are talkin about.

use App\Article;

Then, we use the Article::create() method to add articles to our database.

class ArticlesTableSeeder extends Seeder {
  public function run()
  {
    $faker = Faker\Factory::create();
    DB::table('articles')->delete();
    for ($i = 0; $i < 100; $i++)
    {
      $user_dt = $faker->dateTime('now');
      Article::create([
        'title' => $faker->sentence
        , 'body' => $faker->realText
        , 'published' => $faker->unixTime()
        , 'author' => $faker->numberBetween(1, 10)
        , 'created_at' => $user_dt
        , 'updated_at' => $user_dt
      ]);
    }
  }
}

We can now run php artisan db:seed

$ php artisan db:seed
Seeded: ArticlesTableSeeder

And indeed it has been inserted, as before.

mysql> select count(*) from articles;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

If you get the following error, you must ensure that Model::unguard() is included inside your run() method.

[Illuminate\Database\Eloquent\MassAssignmentException]    
title

Summary

In this post, I have shown you how to interact with your database at the basic level, using both Query Builder syntax and using Eloquent ORM. Eloquent is a giant topic, and many of the features and reasonings behind using it was skipped to keep this post managable. I will have an in-depth coverage of Eloquent in my next article.

comments powered by Disqus