PHP8 - Laravel 11

Appearance

Database migrations and seeders ​

PODCAST

Warning

  • During the creation of the vinyl_shop project you already performed some steps described in this chapter
  • We've chosen to 'repeat' those steps here as well, as they do belong to the database migrations part
  • For our Vinyl Shop application, we need five database tables: users, (music) genres, records, orders and orderlines
  • Below you can find a fraction of the real database model (Some tables and columns are omitted for clarity)
  • Tables like personal_access_token, password_reset, migrations, ... are part of the Laravel and Jetstream packages and will not be discussed in this course Database model

Find the database ​

  • Laravel 11+ uses, by default, a SQLite database with the name database.sqlite in the root of the database folder
  • The default database connection is defined in the .env file:
php
DB_CONNECTION=sqlite
# DB_HOST=127.0.0.1
# DB_PORT=3306
# DB_DATABASE=laravel
# DB_USERNAME=root
# DB_PASSWORD=
  • Only the DB_CONNECTION is set to sqlite. The other database settings are commented out and are only used when you connect to a different database (e.g. MySQL)

IMPORTANT

It's not advisable to change the name of the database file database.sqlite, because it's a bit more complicated to change the database connection in Laravel.

Creating the models, migrations and seeders ​

  • Laravel represents the structure of the database tables (and the changes to it) as migrations
    • Advantages
      • Especially efficient if you code in a team
      • If you change a table, you don't have to manually implement these changes in every database instance (every developer's local database, the production database, ...), but you can adjust or migrate it automatically
    • All database migrations live inside the folder database/migrations
  • By default, Laravel (and Jetstream) have five migrations
    • 0001_01_01_000000_create_users_table.php creates three tables, related to the Users, password reset tokens, and browser sessions
    • 0001_01_01_000001_create_cache_table.php creates two tables related to caching
    • 0001_01_01_000002_create_jobs_table.php creates three tables related to jobs (we don't use jobs in this course)
    • 202x_xx_xx_xxxxxx_add_two_factor_columns_to_users_table.php for adding two-factor authentication columns to the users table
    • 202x_xx_xx_xxxxxx_create_personal_access_tokens_table.php to store access tokens (we don't use access tokens in this course)

NAMING CONVENTIONS

  • The name of a migration is always plural and snake_case and starts with a timestamp (e.g. 2024_10_01_120000_create_personal_access_tokens_table)
  • The plural name is also the name of the database table (e.g. personal_access_tokens)
  • Only the default Laravel migrations start with 0001_01_01_00000x_
  • Every new migration starts with the current date and time (e.g. 2024_10_01_120000)
  • A new migration (class) can be made with php artisan make:migration (e.g. php artisan make:migration create_genres_table), but it's more interesting to create an Eloquent model together with the migration (e.g. php artisan make:model Genre -m) and (if you need one) with a seeder (e.g. php artisan make:model Genre -ms)
    • With the second and third option, fewer mistakes against the Laravel (naming) conventions will be made
    • In Laravel, each database table needs a corresponding model class to interact (query data, insert new records, ...) with that table
    • The models are located in the app/Models folder
  • Create all necessary models with the corresponding migrations and seeders
    • You don't have to make a User model, as this model is already present by default
    • Only the Genre model and the Record model needs a seeder, the Order and Orderline model don't need a seeder
bash
php artisan make:model Genre -ms
php artisan make:model Record -ms
php artisan make:model Order -m
php artisan make:model Orderline -m

NAMING CONVENTIONS

  • The name of a model is always singular and starts with a capital letter (e.g. Record)
  • The name of the corresponding database table is always lower cased and plural (e.g. records)
  • In the remainder of this course section we only look at the tables (the migrations). The next section deals with the related models.

Modify the tables ​

  • Every migration class has two methods
    • up() for running a migration
    • down() for rolling back a migration
  • We only have to modify (extend) the up() method with the necessary columns/fields

NAMING CONVENTIONS

  • Use (lower cased) snake_case notation for the column names
  • Use id as the primary key
  • Use the model name (not the table name!) with the extension _id as a foreign key
    • E.g. user_id and not users_id

Users table ​

  • Open database/migrations/0001_01_01_000000_create_users_table.php
    • A new database table is created with the create() method (on the Schema facade). The create() method accepts two arguments: the first is the name of the table, while the second is a function (which in turn receives a Blueprint object as a parameter) that may be used to define the new table.
    • Several columns (of which the purpose will become clear later on in this course) are automatically provided by Laravel
      • The id() method call adds an auto-incremented, unsigned BIGINT column id for the primary key
        Remark: id() is a shorthand for bigIncrements('id')
      • The STRING column email must have a unique value
      • The TIMESTAMP column email_verified_at may contain the value null
      • The method timestamps() inserts two nullable TIMESTAMP columns (created_at and updated_at) that will be used by Laravel to keep track of the changes to a database row/record
    • Add a BOOLEAN column active with default value true and a BOOLEAN column admin with default value false
php
public function up(): void
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name')->collation('nocase');    
        $table->string('email')->collation('nocase')->unique();
        $table->boolean('active')->default(true);       
        $table->boolean('admin')->default(false);
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password');
        $table->rememberToken();
        $table->foreignId('current_team_id')->nullable();
        $table->string('profile_photo_path', 2048)->nullable();
        $table->timestamps();
    });
}

collation('nocase')

Don't forget the add the collation nocase to the name and email column!

  • By default, SQLite uses the BINARY collation, which means that string comparisons are case-sensitive.
    This means that A and a are considered different characters.
  • However, when you use collation('nocase'), you’re telling the database to perform case-insensitive comparisons.
    This means that A and a are considered the same character.

Genres table ​

  • Open database/migrations/yyyy_mm_dd_hhmmss_create_genres_table.php and add the necessary column
php
public function up()
{
    Schema::create('genres', function (Blueprint $table) {
        $table->id();
        $table->string('name')->collation('nocase')->unique();       
        $table->timestamps();
    });
}

Records table ​

  • Open database/migrations/yyyy_mm_dd_hhmmss_create_records_table.php and add the necessary columns and foreign key relation
    • The $table->foreignId('genre_id')->constrained() is the foreign key relation to the id of the genres table
    • The constrained() method is used to ensure that the foreign key relation is enforced
    • The onDelete('cascade') and onUpdate('cascade') methodes specifies that
      • if a row from the genres table is deleted, all the rows in the records table referencing this genre are deleted as well
      • if an id in the genres table is updated, all the corresponding foreign keys in the records table are updated as well
    • Later on, we will retrieve some additional record information (e.g. the cover image and the track list) from MusicBrainz, a music encyclopedia with lots of music metadata. Because of that, we store the MusicBrainz title id (of a record) in the column mb_id (a column of 36 characters).
php
public function up()
{
    Schema::create('records', function (Blueprint $table) {
        $table->id();
        $table->foreignId('genre_id')->constrained()->onDelete('cascade')->onUpdate('cascade');     
        $table->string('artist')->collation('nocase');
        $table->string('title')->collation('nocase');
        $table->string('mb_id', 36)->unique();
        $table->float('price', 5, 2)->default(19.99);
        $table->unsignedInteger('stock')->default(1);
        $table->timestamps();
    });
}

Orders table ​

  • Open database/migrations/yyyy_mm_dd_hhmmss_create_orders_table.php and add the necessary columns and foreign key relation
php
public function up()
{
    Schema::create('orders', function (Blueprint $table) {
        $table->id();
        $table->foreignId('user_id')->constrained()->onDelete('cascade')->onUpdate('cascade');     
        $table->float('total_price', 6, 2);
        $table->timestamps();
    });
}

Orderlines table ​

  • Open database/migrations/yyyy_mm_dd_hhmmss_create_orderlines_table.php and add the necessary columns and foreign key relation
php
public function up()
{
    Schema::create('orderlines', function (Blueprint $table) {
        $table->id();
        $table->foreignId('order_id')->constrained()->onDelete('cascade')->onUpdate('cascade');    
        $table->string('artist')->collation('nocase');
        $table->string('title')->collation('nocase');
        $table->string('mb_id', 36);
        $table->float('total_price', 6, 2);
        $table->unsignedInteger('quantity');
        $table->timestamps();
    });
}

FOREIGN KEY CONSTRAINTS

  • Foreign key constraints force referential integrity at the database level, e.g. $table->foreignId('flight_id')->constrained();
  • The constrained() method will use a default convention to determine the table and column name being referenced:
    • the prefix before _id (flight) MUST be the singular name of the table name (flights)
    • If the table name is different from the table being referenced, you can specify the table as an argument to the constrained method E.g: $table->foreignId('vlucht_id')->constrained('flights');

Migrate the database ​

  • Migrate the database with the command php artisan migrate:fresh, which results in (the creation of) the database tables according to the specifications in the migration classes

migrate vs migrate:fresh

  • The migration adds a table migrations to the database.sqlite file, in which information about the migrations is stored
  • Because we already make a migration when we created this application, we have to use the command php artisan migrate:fresh instead of php artisan migrate
  • php artisan migrate:fresh deletes all the tables (and its contents) and starts a fresh migration

Database connection in PhpStorm ​

  • It's best to connect to the database directly within PhpStorm
  • This makes it very easy to view the database tables, add/delete rows, update values, ... without installing additional software
  • Connect to the database.sqlite database in PhpStorm:
    • Double-click on the file database/database.sqlite to open the database setting
    • Click on Test Connection (The first time you connect to the database, you have to download the SQLite driver)
    • If the connection is succeeded, click on OK
      Database Connection in PhpStorm
  • Now you can explore the database tables from the Database tab on the right side of PhpStorm Explore database

Seed the database ​

  • Laravel supports different methods to insert data into a table, but the most common method is to use Seeders
  • Database seeders are classes that allow you to populate database tables with default data
  • All seed classes are stored in the database/seeders directory
  • For our Vinyl Shop application, we will insert some (fixed) users, genres, and records into the corresponding tables

Creating seeders ​

  • Create a new seeder with the command php artisan make:seeder XyzSeeder, where Xyz is the name of the model (e.g. php artisan make:seeder GenreSeeder)
  • The Genre model and the Record model already have a corresponding seeder, but the User model doesn't
  • Create the User seeders: UserSeeder
bash
php artisan make:seeder UserSeeder

UserSeeder ​

  • Open database/seeders/UserSeeder.php and insert some users inside the run() method
    • The insert() method accepts an array of associative arrays as parameter. These associative arrays (in which the column names of the table are used as keys) represent the rows inserted into the table.
    • The (Carbon) function now() is used for the created_at and email_verified_at columns
    • The password is hashed using Laravel's Hash facade
  • Don't forget to import the DB and the Hash classes at the top of the file
php
<?php

namespace Database\Seeders;

use DB;     
use Hash;
use Illuminate\Database\Console\Seeds\WithoutModelEvents;
use Illuminate\Database\Seeder;

class UserSeeder extends Seeder
{
    /**
     * Run the database seeds.
     */
    public function run(): void
    {
        DB::table('users')->insert(     
            [
                [
                    'name' => 'John Doe',
                    'email' => 'john.doe@example.com',
                    'admin' => true,
                    'password' => Hash::make('admin1234'),
                    'created_at' => now(),
                    'email_verified_at' => now()
                ],
                [
                    'name' => 'Jane Doe',
                    'email' => 'jane.doe@example.com',
                    'admin' => false,
                    'password' => Hash::make('user1234'),
                    'created_at' => now(),
                    'email_verified_at' => now()
                ]
            ]
        );

        // Add 40 dummy users inside a loop
        for ($i = 0; $i <= 40; $i++) {
            // Every 6th user, $active is false (0) else true (1)
            $active = ($i + 1) % 6 !== 0;
            DB::table('users')->insert(
                [
                    'name' => "ITF User $i",
                    'email' => "itf_user_$i@mailinator.com",
                    'password' => Hash::make("itfuser$i"),
                    'active' => $active,
                    'created_at' => now(),
                    'email_verified_at' => now()
                ]
            );
        }
    }
}

GenreSeeder ​

  • Open database/seeders/GenreSeeder.php and insert some genres
    (Copy the full list from this genres and records gist)
  • Don't forget to import the DB class at the top of the file
php
public function run(): void
{
    // Insert some genres (inside up-method)
    DB::table('genres')->insert(
        [
            ['name' => 'pop/rock',      'created_at' => now()],
            ['name' => 'punk',          'created_at' => now()],
            ['name' => 'industrial',    'created_at' => now()],
            ['name' => 'hardrock',      'created_at' => now()],
            ['name' => 'new wave',      'created_at' => now()],
            ['name' => 'dance',         'created_at' => now()],
            ['name' => 'reggae',        'created_at' => now()],
            ['name' => 'jazz',          'created_at' => now()],
            ['name' => 'dubstep',       'created_at' => now()],
            ['name' => 'blues',         'created_at' => now()],
            ['name' => 'indie rock',    'created_at' => now()],
            ['name' => 'noise',         'created_at' => now()],
            ['name' => 'electro',       'created_at' => now()],
            ['name' => 'techno',        'created_at' => now()],
            ['name' => 'folk',          'created_at' => now()],
            ['name' => 'hip hop',       'created_at' => now()],
            ['name' => 'soul',          'created_at' => now()],
        ]
    );
}

RecordSeeder ​

  • Open database/seeders/RecordSeeder.php and insert some records
    (Copy the full list from this genres and records gist
  • Don't forget to import the DB class at the top of the file
  • DON'T copy the code below because you only see 2 records. Copy and past the full list from the gist!
php
public function run(): void
{
    // Insert some records (inside up-method)
    DB::table('records')->insert(
        [
            [
                'genre_id' => 1,
                'created_at' => now(),
                'stock' => 1,
                'artist' => 'Queen',
                'title' => 'Greatest Hits',
                'mb_id' => 'fcb78d0d-8067-4b93-ae58-1e4347e20216',
                'price' => 19.99
            ],
            [
                'genre_id' => 1,
                'created_at' => now(),
                'stock' => 1,
                'artist' => 'Michael Jackson',
                'title' => 'Thriller',
                'mb_id' => 'fcb78d0d-8067-4b93-ae58-1e4347e20216',
                'price' => 19.99
            ],
            ...
        ]
    );
}

DatabaseSeeder ​

  • The DatabaseSeeder is the main entry point for the seeders
  • Here you can call all the seeders (in the right order!) that you want to run
  • Open database/seeders/DatabaseSeeder.php and call the UserSeeder, GenreSeeder, and RecordSeeder in the run() method
    (You can delete or comment out the example code in the run() method)
  • Because the records have a foreign key to the genres, you have to call the GenreSeeder before the RecordSeeder!
php
public function run(): void
{
    $this->call([       
        UserSeeder::class,
        GenreSeeder::class,
        RecordSeeder::class,
    ]);
}

Seed the database ​

  • Seed the database with the command php artisan db:seed
  • Refresh the database in PhpStorm and double-click on e.g. the users table to see the inserted users Seed database

Refresh the database ​

There are different ways to refresh the database:

  • Delete the file database/database.sqlite:
    • Run the command php artisan migrate to recreate a new database file
    • Run the command php artisan db:seed to seed the database
  • Modify one or more migrations or one or more seeders:
    • Run the command php artisan migrate:fresh to recreate the database tables
    • Run the command php artisan db:seed to seed the database
    • You can also run the command php artisan migrate:fresh --seed to do both in one go

Autocompletion for models ​

REMARK

  • The Laravel Idea plugin can also be used to autocomplete the attributes and relationships of your models
  • Go to the menu Laravel -> Generate Helper Code to (re)generate the helper file that PhpStorm understands, so it can provide accurate autocompletion (for Laravel models)
  • Run this command every time you update a table or add a new table

Laravel Idea

Backup/restore your database ​

Backup ​

If you have installed SQLite, you can easily back up your database with the following command:

bash
sqlite3 database/database.sqlite .dump > database/vinylshop_YY-MM-DD-HHMMSS.sql
  • Where:
    • The .dump command creates a text file with all the SQL commands to recreate the database
    • The > command redirects the output to a new file (e.g. vinylshop_YY-MM-DD-HHMMSS.sql)
    • The YY-MM-DD-HHMMSS part refers to the current date and time (e.g. vinylshop_2024-10-01-120000.sql)
      (Of course, you can choose another name for the backup file)
  • E.g. sqlite3 database/database.sqlite .dump > database/vinylshop_2024-10-01-120000.sql
    will generate a backup file with the name vinylshop_2024-10-01-120000.sql in the database folder Backup databases

Restore ​

  • To restore the database, you can use the following commands:
bash
rm database/database.sqlite
sqlite3 database/database.sqlite < database/vinylshop_YY-MM-DD-HHMMSS.sql

Last updated:

© 2024 - Vince Colson & Patrick Verhaert