Database: Getting Started

Introduction

Laravel supports four database systems:

• MySQL
• Postgres
• SQLite
• SQL Server

Configuration

The database configuration for your application is located at: config/database.php

Of course, you are free to modify this configuration as needed for your local database.

Read / Write Connections

'mysql' => [
    'read' => [
        'host' => '192.168.1.1',
    ],
    'write' => [
        'host' => '196.168.1.2'
    ],
    'driver'    => 'mysql',
    'database'  => 'database',
    'username'  => 'root',
    'password'  => '',
    'prefix'    => '',
],

Running Raw

SQL Queries

Running A Select Query

class UserController extends Controller
{
    public function index()
    {
        $users = DB::select('select * from users where active = ?', [1]);

        return view('user.index', ['users' => $users]);
    }
}

Using Named Bindings

$results = DB::select(
   'select * from users where id = :id', ['id' => 1]
);

Running An Insert Statement

DB::insert(
   'insert into users (id, name) values (?, ?)', [1, 'Dayle']
);

Running An Update Statement

$affected = DB::update(
   'update users set votes = 100 where name = ?', ['John']
);

Running A Delete Statement

$deleted = DB::delete('delete from users');

Running A General Statement

DB::statement('drop table users');

Database Transactions

DB::transaction(function () {
    
    DB::table('users')
       ->update(['votes' => 1]);

    
    DB::table('posts')
       ->delete();

});

Manually Using Transactions

DB::beginTransaction();

DB::rollBack();

DB::commit();

Using Multiple Database Connections

The name passed to the connection method should correspond to one of the connections listed in your config/database.php 
configuration file:

$users = DB::connection('foo')->select(...);

To get PDO instance:

$pdo = DB::connection()->getPdo();

Query Builder

Retrieving All Rows From A Table

class UserController extends Controller
{
    public function index()
    {
        $users = DB::table('users')->get();

        return view('user.index', ['users' => $users]);
    }
}

foreach ($users as $user) {
    echo $user->name;
}

A Single Row / Column From A Table

$user = DB::table('users')->where('name', 'John')->first();

echo $user->name;

$email = DB::table('users')->where('name', 'John')
            ->value('email');

Chunking Results From A Table

DB::table('users')->chunk(100, function($users) {
    foreach ($users as $user) {
        //
    }
});

DB::table('users')->chunk(100, function($users) {
    // Process the records...

    return false;
});

Retrieving A List Of Column Values

$titles = DB::table('roles')->lists('title');

foreach ($titles as $title) {
    echo $title;
}

$roles = DB::table('roles')->lists('title', 'name');

foreach ($roles as $name => $title) {
    echo $title;
}

Aggregates

$users = DB::table('users')->count();

$price = DB::table('orders')->max('price');

$price = DB::table('orders')
                ->where('finalized', 1)
                ->avg('price');

Selects

Specifying A Select Clause

$users = DB::table('users')
            ->select('name', 'email as user_email')->get();

$users = DB::table('users')->distinct()->get();

$query = DB::table('users')->select('name');

$users = $query->addSelect('age')->get();

Raw Expressions

$users = DB::table('users')
            ->select(DB::raw('count(*) as user_count, status'))
            ->where('status', '<>', 1)
            ->groupBy('status')
            ->get();

Joins

Inner Join Statement

$users = DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.*', 'contacts.phone', 'orders.price')
            ->get();

Left Join Statement

$users = DB::table('users')
            ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
            ->get();

Advanced Join Statements

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')
                    ->orOn(...);
        })
        ->get();

DB::table('users')
        ->join('contacts', function ($join) {
            $join->on('users.id', '=', 'contacts.user_id')
                 ->where('contacts.user_id', '>', 5);
        })
        ->get();

Where Clauses

Simple Where Clauses

$users = DB::table('users')->where('votes', '=', 100)->get();

$users = DB::table('users')->where('votes', 100)->get();

$users = DB::table('users')
                ->where('name', 'like', 'T%')
                ->get();

Or Statements

$users = DB::table('users')
                    ->where('votes', '>', 100)
                    ->orWhere('name', 'John')
                    ->get();

Advanced Where Clauses

Parameter Grouping

DB::table('users')
            ->where('name', '=', 'John')
            ->orWhere(function ($query) {
                $query->where('votes', '>', 100)
                      ->where('title', '<>', 'Admin');
            })
            ->get();

Ordering, Grouping, Limit, & Offset

orderBy

$users = DB::table('users')
                ->orderBy('name', 'desc')
                ->get();

groupBy / having / havingRaw

$users = DB::table('users')
                ->groupBy('account_id')
                ->having('account_id', '>', 100)
                ->get();

Inserts

DB::table('users')->insert(
    ['email' => 'john@example.com', 'votes' => 0]
);

DB::table('users')->insert([
    ['email' => 'taylor@example.com', 'votes' => 0],
    ['email' => 'dayle@example.com', 'votes' => 0]
]);

Auto-Incrementing IDs

If the table has an auto-incrementing id, use the insertGetId method to insert a record and then retrieve the ID:

$id = DB::table('users')->insertGetId(
    ['email' => 'john@example.com', 'votes' => 0]
);