Web Applications in PHP

Appearance

Database migrations ​

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 orderlinesDatabase model

Find the database ​

  • All databases can be found on http://phpmyadmin.test (login with homestead/secret)
  • Because we added the vinylshop database to our Homestead.yaml file, the database is automatically created on vagrant up
  • You should already have executed the php artisan migrate command to create the default database tables for our application

Overview databases

Database backups ​

  • Line 6: all databases are automatically backed up all databases when the vagrant destroy command is executed
  • Line 11: the vinylshop database is automatically created when the vagrant up --provision command is executed
    bash
    ---
ip: "192.168.56.56"
memory: 2048
cpus: 2
provider: virtualbox
backup: true

...
databases:
  - homestead
  - vinylshop
    ---
ip: "192.168.56.56"
memory: 2048
cpus: 2
provider: virtualbox
backup: true

...
databases:
  - homestead
  - vinylshop
  • Once configured, Homestead will export your databases to the C:\vagrant\homestead.backup\mysql_backup directory when the vagrant destroy command is executed
    Backup databases

Configure the database in .env file ​

  • Open the file .env and look at the database settings
php
DB_CONNECTION=mysql
DB_HOST=localhost           # change to localhost, try 127.0.0.1 or 192.168.56.56 if localhost doesn't work     
DB_PORT=2200                # change to 2200 (see remarks)
DB_DATABASE=vinylshop       # change to vinylshop
DB_USERNAME=homestead       # change to homestead
DB_PASSWORD=secret          # change to secret
DB_CONNECTION=mysql
DB_HOST=localhost           # change to localhost, try 127.0.0.1 or 192.168.56.56 if localhost doesn't work     
DB_PORT=2200                # change to 2200 (see remarks)
DB_DATABASE=vinylshop       # change to vinylshop
DB_USERNAME=homestead       # change to homestead
DB_PASSWORD=secret          # change to secret

REMARKS

  • If you get an error when connecting to the database, check if port 2200 is in use by another application
    • open laravel.bat and choose option 4 to check all ports
    • Look for the line 3306 (guest) => 2200 (host)
    • If port 2200 is already in use, you see another port number (e.g. 2201)
    • If so, change the value of DB_PORT in the .env file to the port number you found in the previous step
      MySql port

Creating the models and migrations ​

  • 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 six migrations
    • 2014_10_12_000000_create_users_table.php for the users table
    • 2014_10_12_100000_create_password_resets_table.php for resetting your login password (used later in this course)
    • 2014_10_12_200000_add_two_factor_columns_to_users_table.php for Two-Factor Authentication (we don't use 2FA in this course)
    • 2019_08_19_000000_create_failed_jobs_table.php for failed jobs (we don't use jobs in this course)
    • 2019_12_14_000001_create_personal_access_tokens_table.php to store access tokens (we don't use access tokens in this course)
    • 202x_xx_xx_xxxxxx_create_sessions_table.php to store how many sessions are active by a user
  • 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)
    • With the second option, less 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 root of the app folder
  • Create all necessary models with the corresponding migrations (with the flag -m or --migration)
    • You don't have to make a User model, as this model is already present by default
bash
php artisan make:model Genre -m
php artisan make:model Record -m
php artisan make:model Order -m
php artisan make:model Orderline -m
php artisan make:model Genre -m
php artisan make:model Record -m
php artisan make:model Order -m
php artisan make:model Orderline -m

WARNING

  • Pay special attention to the links between the database tables in our database, as they determine the order of creating the models/migrations
    • A record belongs to (has a) a certain genre. So FIRST create the Genre model (and migration) and THEN create the Record model (and migration), otherwise you can't link the foreign key (see below)
    • Likewise for the Orderline model (and migration), which has to be created AFTER the Order model (and migration)

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/2014_10_12_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');
        $table->string('email')->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();
    });
}
public function up(): void
{
    Schema::create('users', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->string('email')->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();
    });
}

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')->unique();       
        $table->timestamps();
    });
}
public function up()
{
    Schema::create('genres', function (Blueprint $table) {
        $table->id();
        $table->string('name')->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');
        $table->string('title');
        $table->string('mb_id', 36)->unique();
        $table->float('price', 5, 2)->default(19.99);
        $table->unsignedInteger('stock')->default(1);
        $table->timestamps();
    });
}
public function up()
{
    Schema::create('records', function (Blueprint $table) {
        $table->id();
        $table->foreignId('genre_id')->constrained()->onDelete('cascade')->onUpdate('cascade');     
        $table->string('artist');
        $table->string('title');
        $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();
    });
}
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');
        $table->string('title');
        $table->string('mb_id', 36)->unique();
        $table->float('total_price', 6, 2);
        $table->unsignedInteger('quantity');
        $table->timestamps();
    });
}
public function up()
{
    Schema::create('orderlines', function (Blueprint $table) {
        $table->id();
        $table->foreignId('order_id')->constrained()->onDelete('cascade')->onUpdate('cascade');    
        $table->string('artist');
        $table->string('title');
        $table->string('mb_id', 36)->unique();
        $table->float('total_price', 6, 2);
        $table->unsignedInteger('quantity');
        $table->timestamps();
    });
}

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 database

REMARKS

  • The migration adds a table migrations to your database vinlyshop, 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

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');

Add fixed data to the tables ​

  • Laravel supports different methods to insert data into a table
  • If the data is fixed, you can insert table rows within the (up() method of the) migration

Users table ​

  • Open database/migrations/yyyy_mm_dd_hhmmss_create_users_table.php and insert some users
    • Insert (inside the up-function!!!) some users using the insert() method (from Laravel's Query Builder). 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
php
public function up()
{
    Schema::create('users', function (Blueprint $table) {
        ...
    });
    
    // Insert some users (inside the up-function!)      
    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()
            ]
        );
    }
}
public function up()
{
    Schema::create('users', function (Blueprint $table) {
        ...
    });
    
    // Insert some users (inside the up-function!)      
    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()
            ]
        );
    }
}

EXERCISE ​

  • Replace the name and email of the first user with your credentials, or add an additional (admin) user with your credentials

Genres table ​

  • Open database/migrations/yyyy_mm_dd_hhmmss_create_genres_table.php and insert some genres
    (Copy the full list from this genres and records gist)
php
public function up()
{
    Schema::create('genres', function (Blueprint $table) {
        ...
    });
    
    // Insert some genres (inside up-function, after create-method)     
    DB::table('genres')->insert(
        [
            ['name' => 'pop/rock', 'created_at' => now()],
            ...
        ]
    );
}
public function up()
{
    Schema::create('genres', function (Blueprint $table) {
        ...
    });
    
    // Insert some genres (inside up-function, after create-method)     
    DB::table('genres')->insert(
        [
            ['name' => 'pop/rock', 'created_at' => now()],
            ...
        ]
    );
}

Records table ​

  • Open dagitabase/migrations/yyyy_mm_dd_hhmmss_create_records_table.php and insert some records
    (Copy the full list from this genres and records gist)
php
public function up()
{
    Schema::create('records', function (Blueprint $table) {
        ...
    });
    
    // Insert some records (inside up-function, after create-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
            ],
            ...
         ]
    );
}
public function up()
{
    Schema::create('records', function (Blueprint $table) {
        ...
    });
    
    // Insert some records (inside up-function, after create-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
            ],
            ...
         ]
    );
}

Migrate the database ​

  • Replace the old version of the database with php artisan migrate:fresh
    Migrate database

TIP : Using Seeding and Factories for Data Population

When populating your database, consider using Seeders and Factories as a recommended alternative to adding data directly in migrations as we have done previously. The logic/code is similar but grants several benefits.

Benefits of Seeders and Factories: ​

  1. Data Separation: Seeders keep your data separate from schema definitions, making it easier to manage and update data independently.

  2. Reproducibility: Seeders help recreate the initial database state reliably, which is valuable in team environments and setting up new development instances.

  3. Consistency and Efficiency: Factories enable the generation of consistent, realistic, or random data for testing and development tasks more efficiently.

Timezone ​

  • Inspect the table genres in phpMyAdmin
  • Look at the dates in the created_at column
  • If there is a time difference between the time in this column and the time on your clock, you have to set the default timezone of your app to your local timezone, as explained in Config -> Laravel project -> Start a new project -> Update timezone
  • After you adjusted the timezone, you can run the migration again (php artisan migrate:fresh)

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

Database connection in PhpStorm (optional) ​

  • It's also possible to connect to the database directly within PhpStorm
  • This makes it very easy to view the database tables, add/delete rows, update values, ... without opening PhpMyAdmin
  • Connect to the vinylshop database in PhpStorm:
    • Go to the menu View -> Tool Windows -> Database
    • Click, in the top left corner, on the plus sign and choose Data Sources -> MySQL
      1. Use the same credentials as in the .env file
        Name: vinylshop
        Host: localhost
        Port: 2200 (= same value as the value of DB_PORT in the .env file)
        User: homestead
        User: secret
      2. Click on Test Connection
      3. If the connection is OK, select vinylshop from the Database dropdown list and click on OK
        Database Connection in PhpStorm
  • Now you can explore the database by double-clicking on one of the tables
    Explore database

Last updated:

© 2023 - Vince Colson, Jordi De Roeck & Patrick Verhaert