This tutorial explains Laravel Database handling, Migrations, Seeding, Raw SQL Queries, Eloquent Models, Eloquent Relationships, Artisan and Tinker:
In the previous tutorial of Laravel Tutorial Series, we learned about the architecture, installation, and components of the Laravel Framework. We have seen the Laravel PHP Project Structure in detail.
In this tutorial, we will cover the Laravel Database, Migrations, Seeding, Running Raw SQL Queries, Eloquent Models, Eloquent Relationships, Artisan, and Tinker. We have used Laravel version 7 in all the examples.
Table of Contents:
Laravel Database Interaction
The Laravel Framework interacts with databases using raw SQL, the fluent query builder, and the Eloquent ORM. It supports the following four databases.
- MySQL version 5.6+
- PostgreSQL (Postgres) version 9.4+
- SQLite version 3.8.8+
- SQL Server version 2017+
Steps to connect your Project to MySQL Database:
Step #1: Create a new database named db_ academy (you can use any suitable name) via phpMyadmin or HeidiSQL.
Step #2: Create a new project named academy (you can use any suitable name).
Note: Please refer to our previous tutorial, Laravel Tutorial for Beginners 1, if you don’t know how to install Laravel and create a new project.
Step #3: Open the project in an IDE and open the .env file. Change the value of DB_DATABASE (database name) to db_ academy (you can use any suitable name). Both the database name and the value of the DB_DATABASE in the .env file must be the same.
Note: You may need to change other database parameters in the .env file such as DB_USERNAME, DB_PASSWOD, etc depending on your database configuration.
The following screenshot shows the database configuration in the .env file.
Laravel Migrations
Laravel Migration is a special feature used to create a database table. By default, the xxxx_xx_xx_xxxxxx _create_users_table.php file and the xxxx_xx_xx_xxxxxx _create_failed_jobs_table.php file are included.
Structure of a Laravel Migration
Laravel Migration class uses the following two methods:
- The up() method: This method is used to create a new table, column, or index in the database.
- The down() method: This method is used to drop an existing table, column, or index in the database. This method is the opposite method of the up() method.
Creating a Laravel Migration
Run the following command in the command prompt to create a migration called create_tutors_table.
php artisan make:migrationcreate_tutors_table
The execution of the above command will create a file named xxxx_xx_xx_xxxxxx_create_tutors_table.php in the database/migrations directory.
Open the xxxx_xx_xx_xxxxxx_create_tutors_table.php file and modify the existing code as shown below.
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreateTutorsTable extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('tutors', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('subject');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('tutors');
}
}
Running Laravel Migrations
Run the following command in the command prompt to run all the available migrations.
php artisan migrate
The above command will create the relevant tables in the database as shown below.
Rolling Back Migrations
#1) Rolling Back the last batch of Migrations
Run the following command in the command prompt to roll back the last batch of migrations (it may include single or multiple migrations).
php artisan migrate:rollback
#2) Rolling Back Migrations providing the step
Run the following command in the command prompt to roll back the last two migrations.
php artisan migrate:rollback --step=2
#3) Rolling Back All the Migrations of the application
Run the following command in the command prompt to roll back all the migrations of the application.
php artisan migrate:reset
#4) Rolling Back and Migrate using a single command
Run the following command in the command prompt to roll back and migrate using a single command.
php artisan migrate:refresh
#5) Rolling Back and Migrate limited no. of Migrations
Run the following command in the command prompt to roll back and migrate the last two migrations using a single command.
php artisan migrate:refresh --step=2
#6) Drop All Tables and Migrate
Run the following command in the command prompt to drop all tables and migrate.
php artisan migrate:fresh
Laravel Seeding
When you develop an application, you need data to test the application. Seeding is used to insert test data into the database.
Creating a Seeder
Step 1: Run the following command in the command prompt to create a seeder called UserSeeder.
php artisan make:seederUserSeeder
This command will create a file named UserSeeder.php in the database/seeds directory.
Step 2: Open the UserSeeder.php file and modify the existing code as shown below.
<?php
use Illuminate\Database\Seeder;
class UserSeeder extends Seeder
{
/**
* Run the database seeds.
*
* @return void
*/
public function run()
{
DB::table('users')->insert([
'name' => Str::random(5),
'email' => Str::random(5).'@user.com',
'password' => Hash::make('password'),
]);
}
}
Step 3: Open the DatabaseSeeder.php file in the database/seeds directory and modify the existing code as shown below.
<?php
use Illuminate\Database\Seeder;
class DatabaseSeeder extends Seeder
{
/**
* Seed the application's database.
*
* @return void
*/
public function run()
{
$this->call(UserSeeder::class);
}
}
Note: The DatabaseSeeder class is used to call other seed classes.
Running Seeders
Step 1: Run the following command in the command prompt to regenerate Composer’s autoloader.
composer dump-autoload
Step 2: Run the following command in the command prompt to run the DatabaseSeeder class.
php artisan db:seed
Instead of running the above command, you may run the following command in the command prompt to run the UserSeeder class.
php artisan db:seed --class= UserSeeder
Note: Run the following command in the command prompt to drop all tables and re-run all migrations.
php artisan migrate:fresh --seed
Step 3: Verify the inserted data with the database.
The database will display output similar as below:
Running Raw SQL Queries
Running an Insert Query
Step 1: Add the following code segment in the routes/web.php file to insert a new record to the tutors table.
Route::get('/insert', function () {
DB::insert('insert into tutors(name,subject) values(?,?)',['Mr John','Computer Science']);
});
Step 2: Visit the URL: http://academy.test/insert
Step 3: Verify the inserted data with the database.
The database will display output similar to the following screenshot.
Running a Select Query
Step 1: Add the following code segment in the routes/web.php file to retrieve data from the tutors table which is having an id equals to 2.
Route::get('/select',function(){
$tutors=DB::select('select * from tutors where id=?',[1]);
foreach($tutors as $tutor)
{
echo $tutor->name." is teaching ".$tutor->;subject;
}
});
Step 2: Visit the URL: http:/academy.test/select
Step 3: The browser will display output similar to the following screenshot.
Running an Update Query
Step 1: Add the following code segment in the routes/web.php file to update the record in the tutors table which is having an id equals to 1.
Route::get('/update', function(){
$tutors=DB::update('update tutors set name="Mr. Brown" where id=?',[1]);
return $tutors;
});
Step 2: Visit the following URL: http://academy.test/update
Step 3: Verify the updated data with the database.
The database will display output similar to the below image:
Running a Delete Query
Step 1: Add the following code segment in the routes/web.php file to delete the record in the tutors table which is having an id equals to 1.
Route::get('/delete',function(){
$tutors=DB::delete('delete from tutors where id=?',[1]);
return $tutors;
});
Step 2: Visit the URL: http://academy.test/delete
Step 3: Verify the deleted data with the database.
The database will display output similar to the below image:
Laravel Eloquent Models
In MVC architecture, the character M stands for Model. A model handles the data used by the application. All models are stored in the app directory. The User model (User.php) is the default model. Each table of the database may have a corresponding Eloquent model. For example, Tutor model stores data in tutors table.
Note: The plural name of the class will be used as the table name unless specified otherwise.
Creating an Eloquent Model
Run the following command in the command prompt to create a model named Tutor.
php artisan make:model Tutor
This command will create a file named Tutor.php in the app directory.
Instead of the above command, you can run the following command in the command prompt to create a database migration when creating the model.
php artisan make:modelTutor --migration
Or
php artisan make:modelTutor -m
Both of the above commands will provide the same output.
Insert Data
Step 1: Add the following code segment in the routes/web.php file to insert a new record to the tutors table.
Route::get('/insert-data',function(){
$tutor=new Tutor;
$tutor->name='Mr George';
$tutor->subject='Mathematics';
$tutor->save();
});
Note: Put the trait “use App\Tutor” in the routes/web.php file as shown below.
<?php use Illuminate\Support\Facades\Route; use App\Tutor;
Step 2: Visit the following URL: http://academy.test/insert-data
Step 3: Verify the inserted data with the database.
The database will display output similar to the below image:
Find Data
Step 1: Add the following code segment in the routes/web.php file to find/retrieve the record in the tutors table which is having an id equals to 2.
Route::get('/find-data',function(){
$tutor=Tutor::find(2);
return $tutor->name;
});
Step 2: Visit the following URL: http://academy.test/find-data
Step 3: The browser will display output similar to the below image:
Update Data
Step 1: Add the following code segment in the routes/web.php file to update the record in the tutors table which is having an id equals to 2.
Route::get('/update-data',function(){
$tutor=Tutor::find(2);
$tutor->name='Ms Georgina';
$tutor->subject='Computer Science';
$tutor->save();
});
or
Route::get('/update-data',function(){
Tutor::where('id',2)->
update(['name'=>'Ms Georgina','subject'=>'Computer Science']);
});
Step 2: Visit the following URL: http://academy.test/update-data
Step 3: Verify the updated data with the database.
The database will display output similar to the below image:
Delete Data
Step 1: Add the following code segment in the routes/web.php file to delete the record in the tutors table which is having an id equals to 2.
Route::get('/delete-data',function(){
$tutor=Tutor::find(2);
$tutor->delete();
});
Step 2: Visit the following URL: http://academy.test/delete-data
Step 3: Verify the deleted data with the database.
The database will display output similar to the below image:
Soft Delete Data
Instead of deleting data from the database, soft delete sets an attribute called deleted_at on the model and insert it into the database table.
Use the following trait to enable soft delete.
Illuminate\Database\Eloquent\SoftDeletes
The following code segment can be used to soft delete data.
Route::get('/soft-delete-data',function(){
Tutor::find(1)->delete();
});
Get Soft Delete Data
The withTrashed() method can be used to get soft delete data as shown below.
Route::get('/read-soft-delete-data',function(){
$tutor=Tutor::withTrashed()->where('id',1)->get();
return $tutor;
});
Restore Soft Delete Data
The restore() method can be used to restore soft delete data as shown below.
Route::get('/restore-data',function(){
Tutor::withTrashed()->where('id',1)->restore();
});
Delete Data Permanently
The forceDelete() method can be used to delete data permanently as shown below.
Route::get('/force-delete-data',function(){
Tutor::onlyTrashed()->forceDelete();
});
Laravel Eloquent Relationships
An eloquent relationship helps to link database tables easily.
Eloquent Relationship Types
The following list shows the different types of eloquent relationships:
- One-to-one relationship
- One-to-many relationship
- Many-to-many relationship
- Has-one-through relationship
- Has-many-through relationship
- One-to-one (polymorphic) relationship
- One-to-many (polymorphic) relationship
- Many-to-many (polymorphic) relationship
#1) One-to-One Relationship
Description: It is the one-to-one relationship between the columns of different tables.
One-to-one relationship example:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Author extends Model
{
/**
* Get the book record associated with the author.
*/
public function book()
{
return $this->hasOne('App\Book);
}
}
One-to-one relationship (inverse) example:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Book extends Model
{
/**
* Get the author that owns the book.
*/
public function author()
{
return $this->belongsTo('App\ Author);
}
}
#2) One-to-Many Relationship
Description: In this type of relationship, one model owns any number of other models.
One-to-many relationship example:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Tutorial extends Model
{
/**
* Get the reviews for the tutorial.
*/
public function reviews()
{
return $this->hasMany('App\Review');
}
}
One-to-many relationship (inverse) example:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Review extends Model
{
/**
* Get the tutorial that owns the review.
*/
public function tutorial()
{
return $this->belongsTo('App\Tutorial');
}
}
#3) Many-to-Many Relationship
Description: It is a more complicated relationship than one-to-one & one-to-many relationships, and you need to create a pivot table to define such a relationship.
Many-to-many relationship example:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Author extends Model
{
/**
* The books that belong to the author.
*/
public function books()
{
return $this->belongsToMany('App\Book);
}
}
Many-to-many (inverse) relationship Example:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Book extends Model
{
/**
* The authors that belong to the book.
*/
public function authors()
{
return $this->belongsToMany('App\Author);
}
}
#4) Has-One-Through Relationship
Description: It connects models through a single intermediate relation.
Has-one-through relationship example:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Mechanic extends Model
{
/**
* Get the car's owner.
*/
public function carOwner()
{
return $this->hasOneThrough('App\Owner', 'App\Car');
}
}
#5) Has-Many-Through Relationship
Description: It allows access to distant or intermediate relations in an easy method.
Has-many-through relationship example:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Country extends Model
{
/**
* Get all of the books for the country.
*/
public function books()
{
return $this->hasManyThrough('App\Book, 'App\Author);
}
}
#6) One-to-One (Polymorphic) Relationship
Description: It is similar to a one-to-one relationship, but the target model can belong to more than one model type on a single association.
One-to-one (polymorphic) relationship example:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Image extends Model
{
/**
* Get the owning imageable model.
*/
public function imageable()
{
return $this->morphTo();
}
}
class Article extends Model
{
/**
* Get the article's image.
*/
public function image()
{
return $this->morphOne('App\Image', 'imageable');
}
}
class Author extends Model
{
/**
* Get the author's image.
*/
public function image()
{
return $this->morphOne('App\Image', 'imageable');
}
#7) One-to-Many (Polymorphic) Relationship
Description: It is similar to a one-to-many relationship but the target model can belong to more than one model type on a single association.
One-to-many (polymorphic) relationship example:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Comment extends Model
{
/**
* Get the owning commentable model.
*/
public function commentable()
{
return $this->morphTo();
}
}
class Article extends Model
{
/**
* Get all of the article's comments.
*/
public function comments()
{
return $this->morphMany('App\Comment', 'commentable');
}
}
class Audio extends Model
{
/**
* Get all of the audio’s comments.
*/
public function comments()
{
return $this->morphMany('App\Comment', 'commentable');
}
}
#8) Many-to-Many (Polymorphic) Relationship
Description: In this type of relationship, the target model has unique records that can share among the other models.
Many-to-many (polymorphic) relationship example:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Article extends Model
{
/**
* Get all of the comments for the article.
*/
public function comments()
{
return $this->morphToMany('App\Comment, commentable);
}
}
Laravel Artisan
In the previous tutorial, we have learned that Artisan is a command-line interface (CLI).
Artisan Commands
Run the following command in the command prompt to view all the available Artisan commands.
php artisan
The following screenshot shows the output of the above command.
The following table shows some of the important artisan commands with their respective functionalities, which are taken from the output of the above command (php artisan).
| Command | functionality |
|---|---|
| help | Displays help for a command |
| list | Lists commands |
| migrate | Run the database migrations |
| test | Run the application tests |
| tinker | Interact with your application |
| auth:clear-resets | Flush expired password reset tokens |
| cache:clear | Flush the application cache |
| db:seed | Seed the database with records |
| event:list | List the application's events and listeners |
| key:generate | Set the application key |
| make:channel | Create a new channel class |
| make:component | Create a new view component class |
| make:controller | Create a new controller class |
| make:event | Create a new event class |
| make:factory | Create a new model factory |
| make:listener | Create a new event listener class |
| make:mail | Create a new email class |
| make:middleware | Create a new middleware class |
| make:migration | Create a new migration file |
| make:model | Create a new Eloquent model class |
| make:notification | Create a new notification class |
| make:observer | Create a new observer class |
| make:policy | Create a new policy class |
| make:provider | Create a new service provider class |
| make:resource | Create a new resource |
| make:seeder | Create a new seeder class |
| make:test | Create a new test class |
| migrate:fresh | Drop all tables and re-run all migrations |
| migrate:refresh | Reset and re-run all migrations |
| migrate:reset | Rollback all database migrations |
| migrate:rollback | Rollback the last database migration |
| notifications:table | Create a migration for the notifications table |
| route:list | List all registered routes |
| storage:link | Create the symbolic links configured for the application |
Laravel Tinker
Tinker is a REPL (READ-EVAL-PRINT-LOOP) which allows you to interact with the application on the command-line. Tinker interacts with a database without registering routes.
Tinker use with Artisan, and it includes by default.
Enabling Tinker Environment
Run the following command in the command prompt to enable the Tinker environment.
php artisan tinker
The following screenshot shows the output of the above command.
Insert Data with Tinker
Run the following commands in the command prompt to insert a new record to the users table.
$user = new App\User; $user->name = 'User A'; $user->email = 'usera@user.com'; $user->password = 'user123'; $user->save();
The database will display output similar to the following screenshot.
Find Data with Tinker
Run the following command in the command prompt to find/retrieve the record which is having an id equal to 2 in the users table.
$user = App\User::find(2);
The command prompt will display output similar to the following screenshot.
Update Data with Tinker
Run the following command in the command prompt to update the record which is having an id equal to 2 in the users table.
$user = App\User::find(2); $user->name = 'User B'; $user->email = 'userb@user.com'; $user->save();
The database will display output similar to the following screenshot.
Delete Data with Tinker
Run the following command in the command prompt to delete the record which is having an id equal to 2 in the users table.
$user = App\User::find(2); $user->delete();
The database will display output similar to the following screenshot.
Conclusion
The Laravel Framework interacts with the databases using raw SQL, the fluent query builder, and the Eloquent ORM. A model handles the data used by the application. An eloquent relationship helps to link database tables easily.
Migration is a special feature used to create a database table. Seeding is used to insert test data to the database that is very helpful for the developer as the developer needs data to test the application when developing. Tinker interacts with a database without registering routes.
We hope you found this tutorial helpful! In the next tutorial, we will discuss forms, file uploading, authentication, sending emails, sessions, etc.
Happy learning!
<< PREV Tutorial | NEXT Tutorial>>