Laravel In a Nutshell - Database & Migrations (#5)

Laravel supports five different database types - MariaDB, MySQL, PostgreSQL, SQLite, SQL Server. In this guide, we’ll use MySQL.

Our goal will be to set up a database to manage our products and categories info that was previously hard-coded in an array:

$productsByCategory = [
    'health' => [
        'Band-Aids',
        'Johnson’s Baby Powder',
        'Tylenol'
    ],
    'tech' => [
        'GoPro Action Camera',
        'FitBit Fitness Watch',
        'Nintendo Switch'
    ],
    'books' => [
        'The Martian',
        'The Great Gatsby',
        'Joy Luck Club'
    ]
];

Here’s the structure of the resulting tables we’ll create:

To get started, you need a local database server so follow the appropriate instructions below...

Windows Users

Windows users working with Laragon (as set up in Part 1 of this series) don’t need to install any additional software because Laragon comes with a MySQL database service. Just confirm that your MySQL service is running along with your Apache web server.

With MySQL running, you can skip down to configuring the database in your Laravel app...

Mac Users

Mac users working with Herd (as set up in Part 1 of this series) can download the free program DBngin to run a MySQL database service.

Once downloaded, click the + icon to add a new service. Choose MySQL as the service type and give it a name (e.g. MySQL). Leave all the other options as their defaults and click Create.

Once your new MySQL service is added click Start. A green circle will indicate the service is running:

Configure database in your Laravel app

Next we need to configure our application to connect to the database services we set up above.

To do this, open your application’s environment file, .env located in the root of your project and find the settings prefixed with DB_:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel
DB_USERNAME=root
DB_PASSWORD=

To work with DBngin and Laragon’s MySQL databases, you can leave most of these values as the default. The only thing you should change is DB_DATABASE - set this to the name you want to use for your database. In my example, I’ll change it to demo to match the name of my application.

With your configs set, run the command following command, which should prompt you to create your new database:

> php artisan migrate

Expected output:

   WARN  The database 'demo' does not exist on the 'mysql' connection.

 ┌ Would you like to create it? ────────────────────────────────┐
 │ ● Yes / ○ No                                                 │
 └──────────────────────────────────────────────────────────────┘

Choose yes and the migration will complete creating your new database as well as the default tables that come with every Laravel application:

  INFO  Preparing database.

  Creating migration table .......................................................... 48ms DONE

   INFO  Running migrations.

  2014_10_12_000000_create_users_table .............................................. 20ms DONE
  2014_10_12_100000_create_password_reset_tokens_table .............................. 13ms DONE
  2019_08_19_000000_create_failed_jobs_table ......................................... 9ms DONE
  2019_12_14_000001_create_personal_access_tokens_table ............................. 14ms DONE

With the above steps complete, your database is now set up and because the migrations were successful, you know your application is configured correctly to connect to the database.

Migrations

The above step invoked Laravel’s migration system. Migrations are files within your application that describe the structure of database tables your application uses. Having migrations is useful because it allows you to quickly set up your application’s database in different contexts (e.g. on a collaborator’s system, on a production server, etc.).

Migrations are found in the directory database/migrations. Exploring this directory you’ll see that Laravel comes with 4 default migrations. To learn how migrations work, let’s study the first default migration, 2014_10_12_000000_create_users_table.php:

<?php

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

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        Schema::create('users', function (Blueprint $table) {
            $table->id();
            $table->string('name');
            $table->string('email')->unique();
            $table->timestamp('email_verified_at')->nullable();
            $table->string('password');
            $table->rememberToken();
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        Schema::dropIfExists('users');
    }
};

Each migration contains an up method that describes changes that are to be made to the database, and a down method that would undo those changes.

Within the up method you can write code to create or alter database tables. This is done using the Schema class where you have access to many methods for constructing different column types in your table.

Create a migration

Let’s create migrations to set up some new tables for our application. We’ll start with a categories table, followed by a products table.

To create a new migration for a categories table run this command:

> php artisan make:migration create_categories_table

In the resulting file (/database/migrations/TIMESTAMP_create_categories_table.php), update your up method to look like this:

public function up(): void
{
    Schema::create('categories', function (Blueprint $table) {
        # Typically, every table has this id field which will act as a unique identifier for every row in the table
        $table->id(); 

        # The timestamps method adds two fields: created_at and updated_at. These fields are used to track when rows in the table are created and updated.
        $table->timestamps(); 

        # This will create a field called `name` that will expect string data. 
        # We will store the category name in this field
        $table->string('name'); 
    });
}

You can leave the down method as written:

public function down(): void
{
    Schema::dropIfExists('categories');
}

Next, create another migration for a products table:

> php artisan make:migration create_products_table

In the resulting file, update your up method to look like this:

public function up(): void
{
    Schema::create('products', function (Blueprint $table) {
        $table->id(); 
        $table->timestamps(); 

        # This field will store the product name
        $table->string('name'); 

        # Create a field called `category_id` to store the id of the category associated with a given product. Because this field will be a foreign key, we need to specify it’s unsigned.
        $table->bigInteger('category_id')->unsigned();

        # Specify that the category_id field is a foreign key that connects to the `id` field in the `categories` table
        $table->foreign('category_id')->references('id')->on('categories');
    });
}

You can leave the down method as written:

public function down(): void
{
    Schema::dropIfExists('products');
}

Running migrations

With your new migrations set up, run them with this command:

> php artisan migrate

Example output:

> php artisan migrate

   INFO  Running migrations.

  2023_09_13_200835_create_categories_table ............................. 13ms DONE
  2023_09_13_201447_create_products_table ............................... 19ms DONE

Behind the scenes, this is the resulting structure of our categories and products table:

Conclusion

In the next part of this guide, we’ll look at how we can write features in our application that will interact with the tables generated in the above steps.