Skip to content
Tutorials Center
My learning

Topics

Lesson 21 of 67 ยท Databases and Migrations

Database Configuration and the Query Builder

Configure Laravel's database connections, run raw queries with bindings, build fluent queries with where, join and groupBy, chunk results and use transactions.

Nearly every Laravel application stores data, and the framework gives you three layers for talking to a database. At the bottom are raw queries with bound parameters. In the middle is the query builder, a fluent PHP interface that writes SQL for you. On top sits Eloquent, the ORM you will meet in Eloquent models. Each layer is built on the one below, so learning the query builder first makes Eloquent far easier to understand.

In this lesson you will configure a connection, run queries safely, compose them with the builder, process big tables in chunks and wrap related changes in transactions. If you have used PDO before (PDO basics, prepared statements), you already know what Laravel does underneath.

Configuring a connection

Connections are defined in config/database.php, and the one in use is chosen by DB_CONNECTION in .env. A new Laravel 13 project starts on SQLite, which needs no server: the database is one file, database/database.sqlite.

INI
DB_CONNECTION=sqlite

To use MySQL, MariaDB or PostgreSQL instead, switch the driver and add the details:

INI
DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=blog
DB_USERNAME=root
DB_PASSWORD=

Laravel supports SQLite, MySQL, MariaDB, PostgreSQL and SQL Server out of the box. Your queries stay the same; the framework adapts the SQL grammar for each. Remember that env() belongs only inside config files (configuration and environments); application code reads values with config('database.default').

You can define several connections and pick one per query with DB::connection('reporting'), useful for a read replica or a legacy database.

Raw queries with bindings

The DB facade runs SQL directly. Always put values in bindings, never inside the string:

PHP
use Illuminate\Support\Facades\DB;

$users = DB::select('select * from users where active = ?', [1]);

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

DB::insert('insert into users (name, email) values (?, ?)', ['Ada', '[email protected]']);

$changed = DB::update('update users set active = 0 where last_login < ?', [$cutoff]);
$removed = DB::delete('delete from sessions where expires_at < ?', [$now]);

DB::statement('drop table temp_report');   // anything without a result

select returns an array of plain stdClass objects. update and delete return the number of affected rows. Bindings are sent to the database separately from the SQL text, so a visitor's input can never change the meaning of the query. That is the whole defense against SQL injection, and it is why "where name = '$name'" is never acceptable (see security in Laravel).

The fluent query builder

DB::table('users') starts a builder. Each method adds a piece of SQL and returns the builder, so you chain them and finish with a method that runs the query.

PHP
$posts = DB::table('posts')
    ->where('published', true)
    ->where('views', '>=', 100)
    ->orderByDesc('published_at')
    ->limit(10)
    ->get();                       // a Collection of stdClass rows

The finishing methods are the ones that hit the database:

Method Returns
get() All matching rows as a collection
first() The first row or null
find($id) One row by primary key
value('title') A single column of the first row
pluck('title', 'id') A collection of one column, optionally keyed
count(), sum(), avg(), min(), max() An aggregate
exists() / doesntExist() A boolean

Where clauses

PHP
DB::table('users')
    ->where('country', 'DE')
    ->orWhere('country', 'AT')
    ->whereIn('plan', ['pro', 'team'])
    ->whereBetween('age', [18, 65])
    ->whereNull('deleted_at')
    ->whereColumn('updated_at', '>', 'created_at');

// Parentheses: (role = 'admin' OR role = 'editor') AND active = 1
DB::table('users')
    ->where(function ($query) {
        $query->where('role', 'admin')->orWhere('role', 'editor');
    })
    ->where('active', 1);

Use the closure form whenever you mix AND and OR; chaining orWhere without grouping is a classic source of wrong results.

Joins, grouping and ordering

PHP
$totals = DB::table('orders')
    ->join('customers', 'customers.id', '=', 'orders.customer_id')
    ->select('customers.name', DB::raw('count(*) as orders'), DB::raw('sum(orders.total) as spent'))
    ->groupBy('customers.id', 'customers.name')
    ->having('spent', '>', 500)
    ->orderByDesc('spent')
    ->get();

join is an inner join; leftJoin and rightJoin keep unmatched rows. groupBy and having work as in SQL (SQL tutorial explains why).

Writing data

PHP
DB::table('users')->insert(['name' => 'Ada', 'email' => '[email protected]']);
$id = DB::table('users')->insertGetId(['name' => 'Grace', 'email' => '[email protected]']);

DB::table('users')->where('id', $id)->update(['active' => false]);
DB::table('posts')->where('id', 3)->increment('views');
DB::table('users')->where('active', false)->delete();

DB::table('flights')->upsert(
    [['code' => 'LH1', 'price' => 99]],
    ['code'],              // columns that identify a row
    ['price']              // columns to update if it exists
);

Passing an array of arrays to insert() inserts many rows in one statement. Careful: update() and delete() without where() change every row.

Behind the scenes, the builder collects clauses and bindings separately, then compiles them. The stand-in below does the same on a real SQLite database, so you can see the SQL it produces.

PHP A fluent query builder on PDO SQLite

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

The real builder supports far more (nested groups, joins, subqueries, many grammars), but the shape is identical: collect clauses and bindings, compile once, execute with bound values. Calling ->toSql() or ->dump() on a real builder shows the same kind of output while you debug.

Processing large tables

Loading a million rows with get() exhausts memory. Process them in batches instead:

PHP
DB::table('users')->orderBy('id')->chunk(500, function ($users) {
    foreach ($users as $user) {
        // handle one user
    }
});

DB::table('users')->where('active', 1)->chunkById(500, function ($users) {
    // safe when you update the rows you are chunking
});

foreach (DB::table('logs')->lazy() as $log) {
    // one row at a time, streamed in small chunks
}

Prefer chunkById when your callback changes the column you filter on: plain chunk paginates with offsets, so updating rows in the loop can make it skip some. lazy() and lazyById() return a lazy collection (see collections) and keep code readable.

Transactions

A transaction makes several changes succeed or fail together. The closure form commits when the closure returns and rolls back if it throws:

PHP
DB::transaction(function () use ($from, $to, $amount) {
    DB::table('accounts')->where('id', $from)->decrement('balance', $amount);
    DB::table('accounts')->where('id', $to)->increment('balance', $amount);
});

A second argument, DB::transaction($callback, 3), retries the whole closure up to three times when the database reports a deadlock. For manual control there are DB::beginTransaction(), DB::commit() and DB::rollBack(). Keep transactions short, and do not call slow external services inside one.

Raw expressions, safely

Sometimes you need SQL the builder has no method for. The *Raw methods accept a SQL fragment, and each takes bindings for values:

PHP
DB::table('orders')
    ->selectRaw('customer_id, sum(total * ?) as taxed', [1.2])
    ->whereRaw('total > ?', [100])
    ->orderByRaw('field(status, ?, ?)', ['paid', 'pending'])
    ->groupBy('customer_id')
    ->get();

Raw fragments are inserted into the query exactly as written, so they are the one place where injection can sneak in. Rules of thumb: bind every value, never concatenate request input, and if a column or direction name comes from the user, compare it against a fixed allow-list first.

The second stand-in shows a transaction rolling back and the difference between a bound value and a concatenated one.

PHP Transactions and bound values

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What is the default database connection in a new Laravel 13 project?

    New projects start with SQLite so you can run migrations and write queries without installing a database server. You switch with DB_CONNECTION in .env.
  2. 2. Why should you write DB::select('... where id = ?', [$id]) instead of building the SQL with "... where id = $id"?

    The database receives the SQL structure and the values separately, which removes the opening for SQL injection.
  3. 3. You loop over chunk(500, ...) and, inside the callback, update the column you filter on. Which method is safer?

    chunkById paginates by the primary key instead of by offset, so changing the rows you iterate over does not make it skip any.
  4. 4. What does DB::transaction(function () { ... }) do if the closure throws an exception?

    The closure form commits on success and rolls back on any exception, so related writes succeed or fail together.
  5. 5. A whereRaw fragment needs a value from the request. What is the right way?

    Raw methods accept a bindings array for exactly this reason. Concatenation reintroduces SQL injection.

Summary

  • config/database.php and .env choose the connection; Laravel 13 starts on SQLite and also supports MySQL, MariaDB, PostgreSQL and SQL Server.
  • DB::select, insert, update, delete and statement run raw SQL; always pass values as bindings.
  • DB::table() starts the fluent builder: where, orWhere (group with a closure), whereIn, join, groupBy, having, orderBy, limit, then get, first, value, pluck, count or another aggregate.
  • Write with insert, insertGetId, update, increment, delete and upsert; never run update or delete without a where unless you mean every row.
  • Use chunk, chunkById or lazy for big tables, and DB::transaction() for changes that must succeed together.
  • Raw expressions are the one injection risk in the builder: bind values and allow-list identifiers.

Next: Migrations