Database and Query Builder

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

The reference material consulted while preparing these pages is listed in the section bibliography.

This page is written against Laravel 13.x (13.34.0) on PHP 8.3 to 8.5, checked on 2026-10-05. Every query runs in the Bookshelf application against SQLite (its default connection), PostgreSQL 17.11 and MySQL 8.4.11, with the books, authors and reviews tables of the earlier pages. The PDO layer underneath, with prepared statements and transactions written by hand, is on Databases with PDO; SQL itself is on SQL.

Connections

config/database.php defines named connections; DB_CONNECTION picks the default, and a new application uses SQLite in database/database.sqlite. Laravel 13 supports MariaDB 10.3+, MySQL 5.7+, PostgreSQL 10+, SQLite 3.26+ and SQL Server 2017+. Bookshelf adds two connections for the demo containers:

<?php
// config/database.php, inside 'connections'
return [
    'pg' => [                                                   // the Bookshelf demo PostgreSQL 17
        'driver' => 'pgsql', 'host' => env('PG_HOST'), 'port' => 5432, 'database' => 'bookshelf',
        'username' => 'bookshelf', 'password' => env('PG_PASS'), 'charset' => 'utf8', 'prefix' => '',
        'search_path' => 'public', 'sslmode' => 'prefer',
    ],

    'my' => [                                                   // the Bookshelf demo MySQL 8.4
        'driver' => 'mysql', 'host' => env('MY_HOST'), 'port' => 3306, 'database' => 'bookshelf',
        'username' => 'bookshelf', 'password' => env('MY_PASS'), 'charset' => 'utf8mb4',
        'collation' => 'utf8mb4_unicode_ci', 'prefix' => '', 'strict' => true, 'engine' => null,
    ],
];

Source: Laravel — Database Configuration

DB::connection('pg') selects a connection; models choose one with a $connection property. Two production options need no code changes:

  • Read and write connections: 'read' => ['host' => [...]], 'write' => ['host' => [...]] send SELECTs to replicas and everything else to the primary. 'sticky' => true makes reads after a write in the same request use the primary, so a user sees the row they just saved despite replication lag.

  • Pooled PostgreSQL (Laravel 13): with 'pooled' => true and a 'direct' block, application queries go through a transaction-mode pooler such as PgBouncer with emulated prepares, while migrations, db:show and schema dumps use the direct connection.

php artisan db:show, db:table and db:monitor inspect a connection; php artisan db opens its command-line client:

$ php artisan db:table books --database=pg
  public.books ...............................................................
  Columns .................................................................. 9
  Size .............................................................. 48.00 KB
  Column ................................................................ Type
  id int8, autoincrement ............ nextval('books_id_seq'::regclass) bigint
  author_id int8 ...................................................... bigint
  title varchar ....................................... character varying(255)
  isbn varchar ......................................... character varying(17)
  published_year int2, nullable ..................................... smallint
  cover_path varchar, nullable ........................ character varying(255)
  created_at timestamp, nullable .............. timestamp(0) without time zone
  updated_at timestamp, nullable .............. timestamp(0) without time zone
  meta json, nullable ................................................... json
  Index ......................................................................
  books_isbn_unique isbn ....................................... btree, unique
  books_pkey id ............................................... btree, primary
  Foreign Key .......................................... On Update / On Delete
  books_author_id_foreign author_id references id on authors  no action / cascade
$ php artisan db:monitor --databases=pg,my --max=100
  Database name .................................................. Connections
  pg .................................................................. [6] OK
  my .................................................................. [1] OK

Source: Laravel — Inspecting Your Databases and Monitoring Your Databases

db:monitor dispatches a DatabaseBusy event when a connection exceeds --max, which a listener can turn into an alert; scheduled every minute it is a cheap guard against connection exhaustion.

Raw queries and bindings

DB::select(), insert(), update(), delete() and statement() run SQL with ? or named bindings, which PDO sends separately from the SQL. The builder methods ending in Raw (selectRaw, whereRaw, orderByRaw, DB::raw()) insert their string as SQL:

<?php
use Illuminate\Support\Facades\DB;

$db = DB::connection('pg');

// 1. raw expressions: bindings vs interpolation
$sort = "published_year; drop table reviews; --";               // attacker-controlled "sort" parameter
try {
    $db->table('books')->orderByRaw($sort)->get();               // orderByRaw passes the string through
} catch (Illuminate\Database\QueryException $e) {
    echo 'orderByRaw: ', substr($e->getPrevious()->getMessage(), 0, 90), PHP_EOL;
}
$col = in_array(request('sort', 'title'), ['title', 'published_year'], true) ? request('sort', 'title') : 'title';
echo 'allow-listed: ', $db->table('books')->orderBy($col)->toRawSql(), PHP_EOL;
$title = "Kindred' or '1'='1";
echo 'bound: ', json_encode($db->select('select title from books where title = ?', [$title])), PHP_EOL;
echo 'raw with bindings: ', $db->table('books')->whereRaw('lower(title) = lower(?)', ['KINDRED'])->toRawSql(), PHP_EOL;
orderByRaw: SQLSTATE[42601]: Syntax error: 7 ERROR: cannot insert multiple commands into a prepared s…
allow-listed: select * from "books" order by "title" asc
bound: []
raw with bindings: select * from "books" where lower(title) = lower('KINDRED')

Source: Laravel — Raw Expressions

The stacked drop table failed only because PostgreSQL refuses several statements in one prepared statement. The injection itself worked, and a single expression is enough to read other tables one yes/no question at a time:

<?php
use Illuminate\Support\Facades\DB;

$db = DB::connection('pg');
// a "sort" parameter that asks a yes/no question about another table
$probe = fn (string $prefix) => "(case when (select count(*) from users where email like '{$prefix}%') > 0 then title else isbn end)";
echo 'a%: ', json_encode($db->table('books')->orderByRaw($probe('a'))->pluck('title')), PHP_EOL;
echo 'z%: ', json_encode($db->table('books')->orderByRaw($probe('z'))->pluck('title')), PHP_EOL;
a%: ["Kindred","Parable of the Sower","The Dispossessed"]
z%: ["The Dispossessed","Kindred","Parable of the Sower"]

Source: Laravel — orderByRaw

The order of the result reveals whether some user’s e-mail starts with a (it does: ada@example.com). Column names, sort directions and other identifiers cannot be bound, so they must come from an allow-list, as $col does; values go in bindings, which every Raw method accepts as its second argument. orderBy($column) quotes the column name but still trusts it to be a column. The OWASP side of this is on Web Security Fundamentals.

Listening to queries and raw SQL

DB::listen() receives every executed query with its bindings and time; toRawSql() (and dumpRawSql(), ddRawSql()) returns a builder’s SQL with the bindings substituted, for debugging only:

<?php
use Illuminate\Support\Facades\DB;

$db = DB::connection('pg');
$log = [];
$db->listen(function ($query) use (&$log) { $log[] = sprintf('%.2f ms %s', $query->time, $query->toRawSql()); });
$db->table('books')->where('published_year', '>', 1980)->count();
echo $log[0], PHP_EOL;
// 0.66 ms select count(*) as "aggregate" from "books" where "published_year" > 1980

Source: Laravel — Listening for Query Events

DB::whenQueryingForLongerThan(500, fn ($connection, $event) => ...) alerts when a request’s total query time exceeds a budget. Query logging for performance work, with Telescope, Debugbar and EXPLAIN, is on Query Performance and N+1.

The query builder

DB::table('books') starts a builder; get() returns a collection of stdClass rows, first(), value(), pluck(), count(), exists() and the other aggregates return single values. One script, run on PostgreSQL:

<?php
use Illuminate\Support\Facades\DB;

$db = DB::connection('pg');

// 1. joins, aggregates, grouping
$perAuthor = $db->table('books')
    ->join('authors', 'authors.id', '=', 'books.author_id')
    ->leftJoin('reviews', 'reviews.book_id', '=', 'books.id')
    ->whereIn('authors.name', ['Octavia E. Butler', 'Ursula K. Le Guin'])
    ->groupBy('authors.name')
    ->orderBy('authors.name')
    ->select('authors.name', DB::raw('count(distinct books.id) as books'), DB::raw('count(reviews.id) as reviews'))
    ->get();
echo json_encode($perAuthor), PHP_EOL;

// 2. JSON where clauses
echo json_encode($db->table('books')->whereJsonContains('meta->formats', 'ebook')->pluck('title')), PHP_EOL;
echo json_encode($db->table('books')->where('meta->awards->hugo', true)->pluck('title')), PHP_EOL;
echo $db->table('books')->where('meta->awards->hugo', true)->toRawSql(), PHP_EOL;

// 3. logical grouping and when()
$q = $db->table('books')
    ->where('published_year', '<', 1990)
    ->where(fn ($w) => $w->where('title', 'like', 'The %')->orWhere('isbn', 'like', '978-0-8070%'))
    ->when(request('sort') === 'year', fn ($w) => $w->orderBy('published_year'), fn ($w) => $w->orderBy('title'));
echo $q->toRawSql(), PHP_EOL, json_encode($q->pluck('title')), PHP_EOL;

// 4. subquery select and whereExists
$latest = $db->table('reviews')->select('created_at')->whereColumn('reviews.book_id', 'books.id')->latest()->limit(1);
echo json_encode($db->table('books')->select('title')->selectSub($latest, 'last_review')->whereExists(
    fn ($e) => $e->select(DB::raw(1))->from('reviews')->whereColumn('reviews.book_id', 'books.id')
)->get()), PHP_EOL;

// 5. full text (PostgreSQL to_tsvector / plainto_tsquery)
echo json_encode($db->table('books')->whereFullText('title', 'sower')->pluck('title')), ' ', $db->table('books')->whereFullText('title', 'sower')->toRawSql(), PHP_EOL;

// 6. union
$old = $db->table('books')->select('title')->where('published_year', '<', 1975);
echo json_encode($db->table('books')->select('title')->where('title', 'Kindred')->union($old)->pluck('title')), PHP_EOL;
[{"name":"Octavia E. Butler","books":2,"reviews":1},{"name":"Ursula K. Le Guin","books":1,"reviews":0}]
["Kindred"]
["The Dispossessed"]
select * from "books" where ("meta"->'awards'->'hugo')::jsonb = 'true'::jsonb
select * from "books" where "published_year" < 1990 and ("title"::text like 'The %' or "isbn"::text like '978-0-8070%') order by "title" asc
["Kindred","The Dispossessed"]
[{"title":"Kindred","last_review":"2026-10-05 20:46:18"}]
["Parable of the Sower"] select * from "books" where (to_tsvector('english', "title")) @@ plainto_tsquery('english', 'sower')
["Kindred","The Dispossessed"]

Source: Laravel — Joins, JSON Where Clauses, Logical Grouping and Full Text Where Clauses

  • The closure in where(fn ...) produced the parentheses around the or; without it, a and b or c would match every ISBN starting 978-0-8070 regardless of year. Any orWhere next to other conditions belongs in a group.

  • -> paths compile per driver. The same JSON query on MySQL:

select * from `books` where json_extract(`meta`, '$."awards"."hugo"') = true
["The Dispossessed"]
SQLSTATE[HY000]: General error: 1191 Can't find FULLTEXT index matching the column list (Connection: my, …

Source: Laravel — Full Text Where Clauses

whereFullText works on PostgreSQL without an index (slowly, a sequential scan over to_tsvector()), but MySQL and MariaDB refuse it unless the columns have a fullText() index from a migration. Vector similarity (whereVectorSimilarTo(), PostgreSQL with pgvector and MariaDB 11.7+) is covered on Redis, MongoDB and Search.

Other families worth knowing: whereBetween, whereNull, whereIn with a subquery, whereColumn, whereAny/whereAll/whereNone (one value against several columns), whereDate/whereYear, having, latest(), inRandomOrder(), limit/offset, lateral joins (joinLateral) on PostgreSQL and MySQL 8, and reusable query pieces with tap() or query pipes.

Writing: inserts, upserts, updates and deletes

<?php
use Illuminate\Support\Facades\DB;

$db = DB::connection('pg');

$n = $db->table('authors')->upsert(
    [['id' => 1, 'name' => 'Octavia E. Butler'], ['id' => 99, 'name' => 'N. K. Jemisin']],
    uniqueBy: ['id'], update: ['name'],
);
echo "upsert affected $n; authors now ", $db->table('authors')->count(), PHP_EOL;
echo 'insertOrIgnore: ', $db->table('authors')->insertOrIgnore([['id' => 99, 'name' => 'duplicate']]), PHP_EOL;
$db->table('reviews')->where('id', 1)->increment('rating', 0, ['body' => 'Unforgettable. Read it twice.']);
echo json_encode($db->table('reviews')->where('id', 1)->first(['rating', 'body'])), PHP_EOL;
upsert affected 2; authors now 3
insertOrIgnore: 0
{"rating":5,"body":"Unforgettable. Read it twice."}

Source: Laravel — Upserts and Increment and Decrement

upsert() compiles to insert ... on conflict (id) do update on PostgreSQL and SQLite and on duplicate key update on MySQL; the uniqueBy columns need a primary or unique index (MySQL ignores the argument and uses whichever unique index conflicts). insertOrIgnore() swallows duplicate-key errors, and on MySQL other errors too, so use it only when silently skipping rows is acceptable. increment() and decrement() update atomically in SQL (set rating = rating + 1), unlike read-modify-write in PHP. update() and delete() without a where affect every row; the builder does not ask.

Transactions, deadlocks and locking

DB::transaction(fn, attempts: 3) commits when the closure returns, rolls back when it throws, and retries the whole closure when the database reports a deadlock. Two processes that lock the same two rows in opposite order produce one:

<?php
use Illuminate\Support\Facades\DB;

$db = DB::connection('pg');
[$first, $second] = getenv('ORDER') === 'ab' ? ['Kindred', 'The Dispossessed'] : ['The Dispossessed', 'Kindred'];
$attempt = 0;
$db->transaction(function () use ($db, $first, $second, &$attempt) {
    $attempt++;
    $db->table('books')->where('title', $first)->lockForUpdate()->first();
    usleep(500_000);                                          // both transactions now hold one row each
    $db->table('books')->where('title', $second)->lockForUpdate()->first();
    echo getenv('ORDER'), " attempt $attempt locked both rows", PHP_EOL;
}, attempts: 3);
$ ORDER=ab php artisan tinker --execute "$(cat deadlock.php)" & ORDER=ba php artisan tinker --execute "$(cat deadlock.php)"
ab attempt 1 locked both rows
ba attempt 2 locked both rows

# PostgreSQL log
ERROR:  deadlock detected

Source: Laravel — Handling Deadlocks and Pessimistic Locking

PostgreSQL chose ba as the victim and rolled it back; Laravel recognised the deadlock and ran the closure again, which then succeeded. The retry repeats everything in the closure, so side effects such as sending an e-mail or calling an API belong after the transaction (or in DB::afterCommit() callbacks and queued jobs marked afterCommit). Locking rows in a consistent order (by ID) avoids most deadlocks in the first place.

lockForUpdate() compiles to select ... for update and sharedLock() to for share on PostgreSQL (lock in share mode on MySQL); both only make sense inside a transaction and on a database that supports row locks, which SQLite does not. Locking, isolation levels and their anomalies in general are on SQL.

Chunking and lazy iteration

Large result sets are processed in pieces. chunk() pages with limit/offset, which breaks when the callback changes the column being filtered on:

<?php
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

$db = DB::connection('pg');
Schema::connection('pg')->dropIfExists('loans');
Schema::connection('pg')->create('loans', function ($t) { $t->id(); $t->boolean('overdue_notice_sent')->default(false); });
$reset = function () use ($db) {
    $db->table('loans')->truncate();
    $db->table('loans')->insert(array_fill(0, 10, ['overdue_notice_sent' => false]));
};

$reset();
$db->table('loans')->where('overdue_notice_sent', false)->orderBy('id')->chunk(3, function ($loans) use ($db) {
    $db->table('loans')->whereIn('id', $loans->pluck('id'))->update(['overdue_notice_sent' => true]);
});
echo 'chunk():     ', $db->table('loans')->where('overdue_notice_sent', false)->count(), ' of 10 left unsent', PHP_EOL;

$reset();
$db->table('loans')->where('overdue_notice_sent', false)->chunkById(3, function ($loans) use ($db) {
    $db->table('loans')->whereIn('id', $loans->pluck('id'))->update(['overdue_notice_sent' => true]);
});
echo 'chunkById(): ', $db->table('loans')->where('overdue_notice_sent', false)->count(), ' of 10 left unsent', PHP_EOL;

// memory: get() versus lazy() over 50 000 rows
$db->table('loans')->truncate();
foreach (array_chunk(array_fill(0, 50_000, ['overdue_notice_sent' => false]), 5000) as $rows) { $db->table('loans')->insert($rows); }
gc_collect_cycles(); $base = memory_get_usage();
$all = $db->table('loans')->get(); $n = $all->count();
printf("get():  %d rows, %.1f MB held%s", $n, (memory_get_usage() - $base) / 1048576, PHP_EOL); unset($all); gc_collect_cycles();
$base = memory_get_usage(); $peak = 0; $n = 0;
foreach ($db->table('loans')->lazyById(1000) as $loan) { $n++; $peak = max($peak, memory_get_usage() - $base); }
printf("lazyById(): %d rows, %.1f MB peak%s", $n, $peak / 1048576, PHP_EOL);
Schema::connection('pg')->drop('loans');
chunk():     4 of 10 left unsent
chunkById(): 0 of 10 left unsent
get():  50000 rows, 21.2 MB held
lazyById(): 50000 rows, 0.5 MB peak

Source: Laravel — Chunking Results and Streaming Results Lazily

After the first chunk updated three rows, the next offset 3 skipped three rows that still matched, and so on: four loans never got their notice. chunkById() pages by where id > last_id instead, so updates do not shift the window. lazy() and lazyById() return a LazyCollection that fetches chunks as it is iterated, keeping memory flat (0.5 MB for 50 000 rows against 21 MB for get()). cursor() streams one row at a time with a single query, but PDO’s buffered mode on MySQL still loads the whole result into the driver’s memory.

Where older books and tutorials are outdated

  • DB::select("... where title = '$title'") with interpolated variables (Kromann ch. 30, Nixon ch. 10) is SQL injection; Laravel’s bindings fix it, but only for values, not for the strings passed to *Raw methods.

  • chunk() for jobs that update the rows they select (Stauffer ch. 5) skips rows; use chunkById() or lazyById().

  • DB::table(...)->toSql() and pasting bindings by hand is replaced by toRawSql(), dumpRawSql() and ddRawSql() (Laravel 10).

  • MySQL as the default database changed in Laravel 11: new applications use SQLite until configured otherwise.

  • Transactions without retries in examples ignore deadlocks; the attempts argument exists for that.

References