Introduction
Laravel makes connecting with databases and running queries extremely simple across a variety of database back-ends using either raw SQL, the fluent query builder, and the Eloquent ORM. Currently, Laravel supports four database systems:
- MySQL
- Postgres
- SQLite
- SQL Server
Configuration
Laravel makes connecting with databases and running
queries extremely simple. The database configuration for
your application is located at
config/database.php
. In this file you may
define all of your database connections, as well as
specify which connection should be used by default.
Examples for all of the supported database systems are
provided in this file.
By default, Laravel's sample environment configuration is ready to use with Laravel Homestead, which is a convenient virtual machine for doing Laravel development on your local machine. Of course, you are free to modify this configuration as needed for your local database.
Read / Write Connections
Sometimes you may wish to use one database connection for SELECT statements, and another for INSERT, UPDATE, and DELETE statements. Laravel makes this a breeze, and the proper connections will always be used whether you are using raw queries, the query builder, or the Eloquent ORM.
To see how read / write connections should be configured, let's look at this example:
'mysql' => [
'read' => [
'host' => '192.168.1.1',
],
'write' => [
'host' => '196.168.1.2'
],
'driver' => 'mysql',
'database' => 'database',
'username' => 'root',
'password' => '',
'charset' => 'utf8',
'collation' => 'utf8_unicode_ci',
'prefix' => '',
],
Note that two keys have been added to the configuration
array: read
and write
. Both of
these keys have array values containing a single key:
host
. The rest of the database options for
the read
and write
connections
will be merged from the main mysql
array.
So, we only need to place items in the read
and write
arrays if we wish to override the
values in the main array. So, in this case,
192.168.1.1
will be used as the
"read" connection, while
192.168.1.2
will be used as the
"write" connection. The database credentials,
prefix, character set, and all other options in the main
mysql
array will be shared across both
connections.
Running Raw SQL Queries
Once you have configured your database connection, you
may run queries using the DB
facade. The
DB
facade provides methods for each type of
query: select
, update
,
insert
, delete
, and
statement
.
Running A Select Query
To run a basic query, we can use the select
method on the DB
facade:
<?php
namespace App\Http\Controllers;
use DB;
use App\Http\Controllers\Controller;
class UserController extends Controller
{
/**
* Show a list of all of the application's users.
*
* @return Response
*/
public function index()
{
$users = DB::select('select * from users where active = ?', [1]);
return view('user.index', ['users' => $users]);
}
}
The first argument passed to the select
method is the raw SQL query, while the second argument
is any parameter bindings that need to be bound to the
query. Typically, these are the values of the
where
clause constraints. Parameter binding
provides protection against SQL injection.
The select
method will always return an
array
of results. Each result within the
array will be a PHP stdClass
object,
allowing you to access the values of the results:
foreach ($users as $user) {
echo $user->name;
}
Using Named Bindings
Instead of using ?
to represent your
parameter bindings, you may execute a query using named
bindings:
$results = DB::select('select * from users where id = :id', ['id' => 1]);
Running An Insert Statement
To execute an insert
statement, you may use
the insert
method on the DB
facade. Like select
, this method takes the
raw SQL query as its first argument, and bindings as the
second argument:
DB::insert('insert into users (id, name) values (?, ?)', [1, 'Dayle']);
Running An Update Statement
The update
method should be used to update
existing records in the database. The number of rows
affected by the statement will be returned by the
method:
$affected = DB::update('update users set votes = 100 where name = ?', ['John']);
Running A Delete Statement
The delete
method should be used to delete
records from the database. Like update
, the
number of rows deleted will be returned:
$deleted = DB::delete('delete from users');
Running A General Statement
Some database statements should not return any value. For
these types of operations, you may use the
statement
method on the DB
facade:
DB::statement('drop table users');
Listening For Query Events
If you would like to receive each SQL query executed by
your application, you may use the listen
method. This method is useful for logging queries or
debugging. You may register your query listener in a service provider:
<?php
namespace App\Providers;
use DB;
use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider
{
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
DB::listen(function($sql, $bindings, $time) {
//
});
}
/**
* Register the service provider.
*
* @return void
*/
public function register()
{
//
}
}
Database Transactions
To run a set of operations within a database transaction,
you may use the transaction
method on the
DB
facade. If an exception is thrown within
the transaction Closure
, the transaction
will automatically be rolled back. If the
Closure
executes successfully, the
transaction will automatically be committed. You don't
need to worry about manually rolling back or committing
while using the transaction
method:
DB::transaction(function () {
DB::table('users')->update(['votes' => 1]);
DB::table('posts')->delete();
});
Manually Using Transactions
If you would like to begin a transaction manually and
have complete control over rollbacks and commits, you
may use the beginTransaction
method on the
DB
facade:
DB::beginTransaction();
You can rollback the transaction via the
rollBack
method:
DB::rollBack();
Lastly, you can commit a transaction via the
commit
method:
DB::commit();
Note: Using the
DB
facade's transaction methods also controls transactions for the query builder and Eloquent ORM.
Using Multiple Database Connections
When using multiple connections, you may access each
connection via the connection
method on the
DB
facade. 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(...);
You may also access the raw, underlying PDO instance
using the getPdo
method on a connection
instance:
$pdo = DB::connection()->getPdo();