Connecting with a database

As we saw previously, the controllers are activated by the routes and transmit information between the model/database and the view. In the preceding example, we used static content inside the view, but in larger applications, we will almost always have content coming from a database, or generated within the controller and passed to the view.

In the next example, we will see how to do this.

Setting up the database inside a Docker container

It's now time to configure our database. If you use Homestead, you probably have your database connection configured and working well. To check, open your Terminal and type the following command:

php artisan tinker
DB::connection()->getPdo();

If everything goes well, you will see the following message:

Database connection message

For this example, however, we are using Docker, and we need to do some configuration to accomplish this task:

1. Inside of the root project, open the .env file and look at line 8 (the database connection), which looks as follows:

 DB_CONNECTION=mysql
 DB_HOST=127.0.0.1
 DB_PORT=3306
 DB_DATABASE=homestead
 DB_USERNAME=homestead
 DB_PASSWORD=secret

Now, replace the preceding code with the following lines:

 DB_CONNECTION=mysql
 DB_HOST=mysql
 DB_PORT=3306
 DB_DATABASE=laravel-angular-book
 DB_USERNAME=laravel-angular-book
 DB_PASSWORD=123456

Note that we need to change a bit to get the Docker MySQL container directions; if you don't remember what you chose in the PHPDocker.io generator, you can copy it from the container configuration.

2. Open docker-compose.yml at the root directory.
3. Copy the environment variables from the MySQL container setup:

mysql:
  image: mysql:8.0
  entrypoint: ['/entrypoint.sh', '--character-set-server=utf8', '--
  collation-server=utf8_general_ci']
  container_name: larahell-mysql
  working_dir: /application
  volumes:
    - .:/application
  environment:
    - MYSQL_ROOT_PASSWORD=larahell
    - MYSQL_DATABASE=larahell-angular-book
    - MYSQL_USER=larahell-user
    - MYSQL_PASSWORD=123456
  ports:
    - "8083:3306"

Now, it's time to test our connection.

4. In your Terminal window, type the following command:

docker-compose exec php-fpm bash

5. Finally, let's check our connection; type the following command:

php artisan tinker
DB::connection()->getPdo();

You should see the same message as the previous screenshot. Then, you will have everything you need to go ahead with the example.

Creating a migrations file and database seed

Migration files are very common in some MVC frameworks, such as Rails, Django, and, of course, Laravel. It is through this type of file that we can keep our database consistent with our application, since we cannot versioning the database schemes . Migration files help us to store each change in our database, so that we can version these files and keep the project consistent.

Database seeds serve to populate the tables of a database with an initial batch of records; this is extremely useful when we are developing web applications from the beginning. The data of the initial load can be varied, from tables of users to administration objects such as passwords and tokens, and everything else that we require.

Let's look at how we can create a migration file for the Bands model in Laravel:

1. Open your Terminal window and type the following command:

php artisan make:migration create_bands_table

2. Open the database/migrations folder, and you will see a file called<timestamp>create_bands_table.php.
3. Open this file and paste the following code inside public function up():

Schema::create('bands', function (Blueprint $table) {
   $table->increments('id');
   $table->string('name');
   $table->string('description');
   $table->timestamps();
});

4. Paste the following code inside public function down():

Schema::dropIfExists('bands');

5. The final result will be the following code:

<?php
use Illuminate\Support\Facades\Schema;
 use Illuminate\Database\Schema\Blueprint;
 use Illuminate\Database\Migrations\Migration;
class CreateBandsTable extends Migration
 {
     /**
     * Run the migrations.
     *
     * @return void
    */
     public function up()
     {
         Schema::create('bands', function (Blueprint $table) {
         $table->increments('id');
         $table->string('name');
         $table->string('description');
         $table->timestamps();
         });
     }
    /**
     * Reverse the migrations.
     *
     * @return void
     */
     public function down()
     {
         Schema::dropIfExists('bands');
     }
 }

6. Inside of the database/factories folder, open the ModalFactory.php file and add the following code, right after the User Factory. Note that we are using a PHP library called faker inside a factory function, in order to generate some data:

$factory->define(App\Band::class, function (Faker\Generator $faker) {
return [
 'name' => $faker->word,
 'description' => $faker->sentence
 ];
 });

7. Go back to your Terminal window and create a database seed. To do this, type the following command:

php artisan make:seeder BandsTableSeeder

8. In the database/seeds folder, open the BandsTableSeeder.php file and type the following code, inside public function run():

factory(App\Band::class,5)->create()->each(function ($p) {
 $p->save();
 });

9. Now, in the database/seeds folder, open the DatabaseSeeder.php file and add the following code, inside public function run():

$this->call(BandsTableSeeder::class);

You can read more about Faker PHP at https://github.com/fzaninotto/Faker.

Before we go any further , we need to do a small refactoring on the Band model.

10. Inside of the app root, open the Band.php file and add the following code, inside the Band class:

protected $fillable = ['name','description'];

11. Go back to your Terminal and type the following command:

php artisan migrate

After the command, you will see the following message in the Terminal window:

 Migration table created successfully.

The preceding command was just to populate the database with our seed.

12. Go back to your Terminal and type the following command:

php artisan db:seed

We now have five items ready to use in our database.

Let's check whether everything will go smoothly.

13. Inside of your Terminal, to exit php-fpm container, type the following command:

exit

14. Now, in the application root folder, type the following command in your Terminal:

docker-compose exec mysql mysql -ularavel-angular-book -p123456

The preceding command will give you access to the MySQL console inside mysql Docker container, almost exactly the same as how we gained access to php-fpm container.

15. Inside of the Terminal, type the following command to see all of the databases:

show databases;

As you can see, we have two tables: information_schema and laravel-angular-book.

16. Let's access the laravel-angular-book table; type the following command:

use laravel-angular-book;

17. And now, let's check our tables, as follows:

show tables;

18. Now, let's SELECT all records from the bands tables:

SELECT * from bands;

We will see something similar to the following screenshot:

Database bands table

19. Now, exit the MySQL console with the following command:

exit

Using the resource flag to create CRUD methods

Let's see another feature of the Artisan CLI, creating all of the Create, Read, Update, and Delete (CRUD) operations using a single command.

First, in the app/Http/Controllers folder, delete the BandController.php file:

1. Open your Terminal window and type the following command:

php artisan make:controller BandController --resource

This action will create the same file again, but now, it includes the CRUD operations, as shown in the following code:

<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
class BandController extends Controller
 {
     /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
     public function index()
     {
         //
     }
    /**
     * Show the form for creating a new resource.
     *
     * @return \Illuminate\Http\Response
     */
     public function create()
     {
         //
     }
    /**
     * Store a newly created resource in storage.
     *
     * @param \Illuminate\Http\Request $request
     * @return \Illuminate\Http\Response
     */
     public function store(Request $request)
     {
         //
     }
    /**
     * Display the specified resource.
     *
     * @param int $id
     * @return \Illuminate\Http\Response
     */
     public function show($id)
     {
         //
     }
    /**
     * Show the form for editing the specified resource.
     *
     * @param int $id
     * @return \Illuminate\Http\Response
     */
     public function edit($id)
     {
         //
     }
    /**
     * Update the specified resource in storage.
     *
     * @param \Illuminate\Http\Request $request
     * @param int $id
     * @return \Illuminate\Http\Response
     */
     public function update(Request $request, $id)
     {
         //
     }
    /**
     * Remove the specified resource from storage.
     *
     * @param int $id
     * @return \Illuminate\Http\Response
     */
     public function destroy($id)
     {
         //
     }
 }

For this example, we will write only two methods: one to list all of the records, and another to get a specific record. Don't worry about the other methods; we will cover all of the methods in the upcoming chapters.

2. Let's edit public function index() and add the following code:

$bands = Band::all();
 return $bands;

3. Now, edit public function show() and add the following code:

$band = Band::find($id);
 return view('bands.show', array('band' => $band));

4. Add the following line, right after App\Http\Requests:

use App\Band;

5. Update the routes.php file, inside the routes folder, to the following code:

Route::get('/', function () {
 return view('welcome');
 });
Route::resource('bands', 'BandController');

6. Open your browser and go to http://localhost:8081/bands, where you will see the following content:

[{
  "id": 1,
  "name": "porro",
  "description": "Minus sapiente ut libero explicabo et voluptas harum.",
  "created_at": "2018-03-02 19:20:58",
  "updated_at": "2018-03-02 19:20:58"}
...]

Don't worry if your data is different from the previous code; this is due to Faker generating random data. Note that we are returning a JSON directly to the browser, instead of returning the data to the view. This is a very important feature of Laravel; it serializes and deserializes data, by default.

Creating the Blade template engine

Now, it's time to create another view component. This time, we will use the Blade template engine to show some records from our database. Let's look at what the official documentation says about Blade:

Blade is the simple, yet powerful, templating engine provided with Laravel. Unlike other popular PHP templating engines, Blade does not restrict you from using plain PHP code in your views. All Blade views are compiled into plain PHP code and cached until they are modified, meaning Blade adds essentially zero overhead to your application.

Now, it's time to see this behavior in action:

1. Go back to the code editor and create another folder inside resources/views, called bands.
2. Create a file, show.blade.php, inside resources/views/bands, and place the following code in it:

<h1>Band {{ $band->id }}</h1>
<ul>
<li>band: {{ $band->name }}</li>
<li>description: {{ $band->description }}</li>
</ul>

You can find out more about Blade at https://laravel.com/docs/5.2/blade.

3. Open your browser to http://localhost:8081/bands/1. You will see the template in action, with results similar to the following:

View of the template engine

Note that here, we are using the Blade template engine to show a record from our database. Now, let's create another view to render all of the records.

4. Create another file, called index.blade.php, inside resources/views/bands, and place the following code in it:

@foreach ($bands as $band)
<h1>Band id: {{ $band->id }}</h1>
<h2>Band name: {{ $band->name }}</h2>
<p>Band Description: {{ $band->description }}</p>
@endforeach

5. Go back to your browser and visit http://localhost:8081/bands/, where you will see a result similar to the following:

View template engine