Laravel and Databases

Databases are an essential part of almost all web applications. Fortunately, Laravel makes it easy to connect with databases, and the Eloquent Object Relational Mapper (ORM) simplifies the process of interacting with the database. Laravel support several databases, including MySQL, PostgreSQL, SQLite, SQL Server.

Configuration

Laravel simplifies the connection to a database by including the connection out of the box. The configuration setting for the database can be viewed and updated in the environment configuration file.

To update the database settings, open the .env file in the root directory of your Laravel project, and then find and update the following lines with your specific database settings:

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

NOTE

If you do not see a .env, look for a .env.example file. Then remain it be .env.

Defining Models

Before we can start using a database, a model must be defined. Models are stored in the app/Models directory of the Laravel project, and while it is possible to create these files manually, the easiest way is to use the artisan make:model command:

php artisan make:model Movie

This command will create the file Movie.php. It would look something like this:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Movie extends Model
{
    //
}

Model Conventions

Laravel has several conventions when it comes to the table and column names as well as expected columns. For example, Laravel will assume the Category model will work with the categories database table.

It is possible to override these conventions by adding variables to the Model class. For example, the primary key of a table is not the id column as Laravel assumes, then the $primaryKey variable can be added with the correct column name.

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Movie extends Model
{
    protected $primaryKey = 'movie_id';
}

Laravel Eloquent

Eloquent is an interface built on PDO and is used to interact with a database. Eloquent includes methods to retrieve and manipulate data in a database table using the corresponding model.

all

The all method is used to retrieve all rows from a table as a Collection, which is an array-like object.

// retrieve all movies
$movies = Movie::all();

find

The find method can retrieve a single row from a table by its primary key.

// retreive a movie where movie_id = 1
$movie = Movie::find(1);

where

The where method can be used to retrieve multiple rows filtered by a specific condition. When using the where method, the get method must also be used.

// retrieve all movies with the word "the" in the title
$movies = Movie::where('movie_title', 'like', "%the %")->get();

save

Eloquent makes it easy to insert and update data in a database. To insert a new record into the database, you should instantiate a new model instance and set attributes on the model. Then, call the save method on the model instance.

// insert a new movie 
$movie = new Movie;
$movie->movie_title = request('movie_title');
$movie->save();

A similar process is done for updating data. However, instead of creating a new model instance, an existing model is retrieved using the find method.

// update an existing movie
$movie = Movie::find(1);
$movie->movie_title = request('movie_title');
$movie->save();

delete

Deleting a row in a table is achieved using the delete method.

// delete a movie
$movie = Movie::find(1);
$movie->delete();