Master Laravel Database Connections: Configuration, Read/Write Splitting, and Transactions

Supported Databases

Laravel can interact with four database back‑ends using raw SQL, the query builder, or Eloquent ORM: MySQL, Postgres, SQLite, and SQL Server.

Configuration

The database configuration file is config/database.php. It defines all connections and the default connection. By default Laravel uses the Homestead virtual machine, which you can adjust to match your local database setup.

SQLite configuration

Create a SQLite file, for example with touch database/database.sqlite, then set the absolute path in the environment variables:

DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite

Read/Write connections

Laravel allows you to route SELECT statements to a read host and INSERT/UPDATE/DELETE statements to a write host. Add read, write, and optional sticky keys to the connection array:

'mysql' => [
    'read' => [
        'host' => '192.168.1.1',
    ],
    'write' => [
        'host' => '192.168.1.2',
    ],
    'sticky' => true,
    'driver' => 'mysql',
    'database' => 'database',
    'username' => 'root',
    'password' => '',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
],

When sticky is true, any reads performed after a write in the same request will use the write connection, ensuring immediate visibility of newly written data.

Multiple database connections

Access a named connection via the DB::connection('name') method:

$users = DB::connection('foo')->select('SELECT * FROM users');

You can also retrieve the underlying PDO instance with DB::connection()->getPdo().

Running native SQL queries

Select queries

Use DB::select($sql, $bindings). The method returns an array of StdClass objects:

use Illuminate\Support\Facades\DB;

$users = DB::select('select * from users where active = ?', [1]);
foreach ($users as $user) {
    echo $user->name;
}

Named bindings

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

Insert statements

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

Update statements

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

Delete statements

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

Statements without results

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

Listening to query events

Register a listener in a service provider to capture each executed query:

use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider {
    public function boot() {
        DB::listen(function ($query) {
            // $query->sql
            // $query->bindings
            // $query->time
        });
    }
}

Database transactions

Automatic transaction handling

Wrap a set of operations in DB::transaction(function () { ... }). The transaction is committed if the closure completes without exception; otherwise it is rolled back.

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

Deadlock retries

The transaction method accepts an optional second argument specifying the number of retry attempts when a deadlock occurs:

DB::transaction(function () {
    DB::table('users')->update(['votes' => 1]);
    DB::table('posts')->delete();
}, 5);

Manual transaction control

Start a transaction with DB::beginTransaction(), roll back with DB::rollBack(), and commit with DB::commit() for full control.

DB::beginTransaction();
// ... perform queries ...
DB::rollBack(); // or DB::commit();