Getting Started with Laravel Database

Introduction

• Goal: Learn about Laravel database (Migration, Deletion)
• System: Ubuntu 16.04 (Yes it’s old)

Installation

sudo apt-get install php5.6 # Yes it's old... Might need to install something else
sudo apt install mysql-server
curl -sS https://getcomposer.org/installer | sudo php -- --install-dir=/usr/local/bin --filename=composer

• I forgot what’s the exact installation and setup for mysql
• Composer is used to create the laravel project

Start a basic Laravel

• Install, download necessary stuff and create a new folder with the project content

composer create-project laravel/laravel php-laravel-example
cd php-laravel-example
php artisan serve # Yay!

• artisan is a tool supplied with the laravel, it helps to build up the application
• Note that the one below is for use with Docker, so I’m not using it

curl -s "https://laravel.build/php-laravel-example" | bash

Connecting Laravel to MySQL

• You can create a custom user to be used with the database

CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' WITH GRANT OPTION;

• Edit the project folder’s .env file with the mysql credentials
• Run the mysql server

sudo /etc/init.d/mysql start

• And then login using this, password will be prompted

mysql -u sammy -p

Creating a Model

• Laravel includes Eloquent (ORM) and we use Model to interact with the database
• This will create app/Flight.php

php artisan make:model Flight

• If we want the instance to have soft delete (ie. when deleted it doesn’t actually get deleted from the table), we can import use Illuminate\Database\Eloquent\SoftDeletes; and put use SoftDeletes; inside the model file

Create a new Table

php artisan make:migration create_flights_table

• The above will create a file inside database/migrations
• We can edit the files to add in the table columns

    Schema::create('flights', function (Blueprint $table) {
        $table->increments('id');
        $table->string('name');
        $table->string('airline');
        $table->softDeletes(); // This is to enable usage of soft delete by creating the necessary column (deleted_at)
        $table->timestamps();
    });
  

• After creating the migration file, we can check the migration status by php artisan migrate:status then run it with php artisan migrate
• In case something is not right, we can undo the migration by php artisan migrate:rollback (Given that the migration file has enough code to roll back itself)

Add new data

• We can use the supplied php artisan tinker to “tinker” with the application
• However, we cannot add a new data like this by default since the Model by default is protected

  $flightData = array('name' => 'JAL123', 'airline' => 'Japan Airlines');
  Flight::create($flightData);
  

• By putting protected $guarded = []; inside the Model file, we can then successfully add a new row (Adding this line requires tinker to be restarted)

  >>> Flight::count()
  => 1
  

Deleting data

• If we delete the data Flight::find(1)->delete(), Flight::count() becomes 0
• The row still exists, but now the deleted_at column is filled
• We can restore is by Flight::withTrashed()->find(1)->restore()

Maintenance View

php artisan down
php artisan up

Neat!