Introduction
Laravel makes interacting with databases extremely simple across a variety of database backends using either raw SQL, the fluent query builder, and the Eloquent ORM. Currently, Laravel supports four databases:
- MySQL
- Postgres
- SQLite
- SQL Server
Configuration
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 most 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.
SQLite Configuration
After creating a new SQLite database using a command such as
touch database/database.sqlite
, you can easily configure
your environment variables to point to this newly created database by
using the database's absolute path:
DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite
SQL Server Configuration
Laravel supports SQL Server out of the box; however, you will need to
add the connection configuration for the database to your
config/database.php
configuration file:
'sqlsrv' => [
'driver' => 'sqlsrv',
'host' => env('DB_HOST', 'localhost'),
'database' => env('DB_DATABASE', 'forge'),
'username' => env('DB_USERNAME', 'forge'),
'password' => env('DB_PASSWORD', ''),
'charset' => 'utf8',
'prefix' => '',
],
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.
You only need to place items in the read
and
write
arrays if you wish to override the values from the
main array. So, in this case, 192.168.1.1
will be used as
the host for the "read" connection, while 192.168.1.2
will
be used for the "write" connection. The database credentials, prefix,
character set, and all other options in the main mysql
array will be shared across both connections.
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();
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, you may use the select
method on
the DB
facade:
<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\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 its 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:
$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 affected will
be returned:
$deleted = DB::delete('delete from users');
Running A General Statement
Some database statements do 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 Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider
{
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
DB::listen(function ($query) {
// $query->sql
// $query->bindings
// $query->time
});
}
/**
* Register the service provider.
*
* @return void
*/
public function register()
{
//
}
}
Database Transactions
You may use the transaction
method on the
DB
facade to run a set of operations within a database
transaction. 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();
});
Handling Deadlocks
The transaction
method accepts an optional second
argument which defines the number of times a transaction should be
reattempted when a deadlock occurs. Once these attempts have been
exhausted, an exception will be thrown:
DB::transaction(function () {
DB::table('users')->update(['votes' => 1]);
DB::table('posts')->delete();
}, 5);
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();
Tip!! Using the
DB
facade's transaction methods also controls transactions for the query builder and Eloquent ORM.