Laravel DB Facade Cheat Sheet: Quick Reference for Database Operations

Basic Usage

DB::connection('connection_name'); // run a specific connection
$results = DB::select('select * from users where id = ?', [1]);
$results = DB::select('select * from users where id = :id', ['id' => 1]);
// run raw statement
DB::statement('drop table users');
// listen to query events
DB::listen(function ($sql, $bindings, $time) { /* code_here */ });
// transactions
DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);
    DB::table('posts')->delete();
});
DB::beginTransaction();
DB::rollBack();
DB::commit();
// get table prefix
DB::getTablePrefix();

Query Builder

// get all rows
DB::table('name')->get();
// chunk results
DB::table('users')->chunk(100, function ($users) {
    foreach ($users as $user) {
        // ...
    }
});
// first row
$user = DB::table('users')->where('name', 'John')->first();
DB::table('name')->first();
// pluck single column
$name = DB::table('users')->where('name', 'John')->pluck('name');
DB::table('name')->pluck('column');
// lists (legacy)
$roles = DB::table('roles')->lists('title');
$roles = DB::table('roles')->lists('title', 'name');
// select specific columns
$users = DB::table('users')->select('name', 'email')->get();
$users = DB::table('users')->distinct()->get();
$users = DB::table('users')->select('name as user_name')->get();
// add select to existing query
$query = DB::table('users')->select('name');
$users = $query->addSelect('age')->get();
// where operators
$users = DB::table('users')->where('votes', '>', 100)->get();
$users = DB::table('users')
    ->where('votes', '>', 100)
    ->orWhere('name', 'John')
    ->get();
$users = DB::table('users')->whereBetween('votes', [1, 100])->get();
$users = DB::table('users')->whereNotBetween('votes', [1, 100])->get();
$users = DB::table('users')->whereIn('id', [1, 2, 3])->get();
$users = DB::table('users')->whereNotIn('id', [1, 2, 3])->get();
$users = DB::table('users')->whereNull('updated_at')->get();
DB::table('name')->whereNotNull('column')->get();
// dynamic where shortcuts
$admin = DB::table('users')->whereId(1)->first();
$john = DB::table('users')->whereIdAndEmail(2, '[email protected]')->first();
$jane = DB::table('users')->whereNameOrAge('Jane', 22)->first();
// order, group, having
$users = DB::table('users')
    ->orderBy('name', 'desc')
    ->groupBy('count')
    ->having('count', '>', 100)
    ->get();
DB::table('name')->orderBy('column')->get();
DB::table('name')->orderBy('column', 'desc')->get();
DB::table('name')->having('count', '>', 100)->get();
// offset & limit
$users = DB::table('users')->skip(10)->take(5)->get();

Joins

// basic join
DB::table('users')
    ->join('contacts', 'users.id', '=', 'contacts.user_id')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->select('users.id', 'contacts.phone', 'orders.price')
    ->get();
// left join
DB::table('users')
    ->leftJoin('posts', 'users.id', '=', 'posts.user_id')
    ->get();
// complex where with closure
DB::table('users')
    ->where('name', '=', 'John')
    ->orWhere(function ($query) {
        $query->where('votes', '>', 100)
              ->where('title', '<>', 'Admin');
    })
    ->get();

Aggregates

$users = DB::table('users')->count();
$price = DB::table('orders')->max('price');
$price = DB::table('orders')->min('price');
$price = DB::table('orders')->avg('price');
$total = DB::table('users')->sum('votes');
// raw select with group
$users = DB::table('users')
    ->select(DB::raw('count(*) as user_count, status'))
    ->where('status', '<>', 1)
    ->groupBy('status')
    ->get();
// raw statements
DB::select('select * from users where id = ?', array('value'));
DB::insert('insert into foo set bar=2');
DB::update('update foo set bar=2');
DB::delete('delete from bar');
DB::statement('update foo set bar=2');
DB::table('name')->select(DB::raw('count(*) as count, column2'))->get();

Inserts / Updates / Deletes / Unions / Pessimistic Locking

// insert
DB::table('users')->insert([
    ['email' => '[email protected]', 'votes' => 0]
]);
$id = DB::table('users')->insertGetId([
    ['email' => '[email protected]', 'votes' => 0]
]);
DB::table('users')->insert([
    ['email' => '[email protected]', 'votes' => 0],
    ['email' => '[email protected]', 'votes' => 0]
]);
// update
DB::table('users')
    ->where('id', 1)
    ->update(['votes' => 1]);
DB::table('users')->increment('votes');
DB::table('users')->increment('votes', 5);
DB::table('users')->decrement('votes');
DB::table('users')->decrement('votes', 5);
DB::table('users')->increment('votes', 1, ['name' => 'John']);
// delete
DB::table('users')->where('votes', '<', 100)->delete();
DB::table('users')->delete();
DB::table('users')->truncate();
// unions
$first = DB::table('users')->whereNull('first_name');
$users = DB::table('users')->whereNull('last_name')->union($first)->get();
// pessimistic locks
DB::table('users')
    ->where('votes', '>', 100)
    ->sharedLock()
    ->get();
DB::table('users')
    ->where('votes', '>', 100)
    ->lockForUpdate()
    ->get();