Introduction
Almost every modern web application interacts with a database. Laravel makes interacting with databases extremely simple across a variety of supported databases using raw SQL, a fluent query builder, and the Eloquent ORM. Currently, Laravel provides first-party support for five databases:
- MariaDB 10.2+ (Version Policy)
- MySQL 5.7+ (Version Policy)
- PostgreSQL 9.6+ (Version Policy)
- SQLite 3.8.8+
- SQL Server 2017+ (Version Policy)
Configuration
The configuration for Laravel's database services is located in your
application's config/database.php
configuration file. In
this file, you may define all of your database connections, as well as
specify which connection should be used by default. Most of the
configuration options within this file are driven by the values of your
application's environment variables. Examples for most of Laravel's
supported database systems are provided in this file.
By default, Laravel's sample environment configuration is ready to use with Laravel Sail, which is a Docker configuration for developing Laravel applications on your local machine. However, you are free to modify your database configuration as needed for your local database.
SQLite Configuration
SQLite databases are contained within a single file on your
filesystem. You can create a new SQLite database using the
touch
command in your terminal:
touch database/database.sqlite
. After the database has been
created, you may easily configure your environment variables to point to
this database by placing the absolute path to the database in the
DB_DATABASE
environment variable:
DB_CONNECTION=sqlite
DB_DATABASE=/absolute/path/to/database.sqlite
To enable foreign key constraints for SQLite connections, you should
set the DB_FOREIGN_KEYS
environment variable to
true
:
DB_FOREIGN_KEYS=true
Microsoft SQL Server Configuration
To use a Microsoft SQL Server database, you should ensure that you
have the sqlsrv
and pdo_sqlsrv
PHP extensions
installed as well as any dependencies they may require such as the
Microsoft SQL ODBC driver.
Configuration Using URLs
Typically, database connections are configured using multiple
configuration values such as host
, database
,
username
, password
, etc. Each of these
configuration values has its own corresponding environment variable.
This means that when configuring your database connection information on
a production server, you need to manage several environment
variables.
Some managed database providers such as AWS and Heroku provide a single database "URL" that contains all of the connection information for the database in a single string. An example database URL may look something like the following:
mysql://root:password@127.0.0.1/forge?charset=UTF-8
These URLs typically follow a standard schema convention:
driver://username:password@host:port/database?options
For convenience, Laravel supports these URLs as an alternative to
configuring your database with multiple configuration options. If the
url
(or corresponding DATABASE_URL
environment
variable) configuration option is present, it will be used to extract
the database connection and credential information.
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',
'196.168.1.2',
],
],
'write' => [
'host' => [
'196.168.1.3',
],
],
'sticky' => true,
'driver' => 'mysql',
'database' => 'database',
'username' => 'root',
'password' => '',
'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',
'prefix' => '',
],
Note that three keys have been added to the configuration array:
read
, write
and sticky
. The
read
and write
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
configuration array.
You only need to place items in the read
and
write
arrays if you wish to override the values from the
main mysql
array. So, in this case,
192.168.1.1
will be used as the host for the "read"
connection, while 192.168.1.3
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. When multiple values exist in the host
configuration array, a database host will be randomly chosen for each
request.
The sticky
Option
The sticky
option is an optional value that can
be used to allow the immediate reading of records that have been written
to the database during the current request cycle. If the
sticky
option is enabled and a "write" operation has been
performed against the database during the current request cycle, any
further "read" operations will use the "write" connection. This ensures
that any data written during the request cycle can be immediately read
back from the database during that same request. It is up to you to
decide if this is the desired behavior for your application.
Running 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 SELECT query, you may use the select
method on the DB
facade:
<?php
namespace App\Http\Controllers;
use App\Http\Controllers\Controller;
use Illuminate\Support\Facades\DB;
class UserController extends Controller
{
/**
* Show a list of all of the application's users.
*
* @return \Illuminate\Http\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
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 representing a record from the
database:
use Illuminate\Support\Facades\DB;
$users = DB::select('select * from users');
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 accepts the SQL query as its first
argument and bindings as its second argument:
use Illuminate\Support\Facades\DB;
DB::insert('insert into users (id, name) values (?, ?)', [1, 'Marc']);
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 is
returned by the method:
use Illuminate\Support\Facades\DB;
$affected = DB::update(
'update users set votes = 100 where name = ?',
['Anita']
);
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 by the method:
use Illuminate\Support\Facades\DB;
$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');
Running An Unprepared Statement
Sometimes you may want to execute an SQL statement without binding
any values. You may use the DB
facade's
unprepared
method to accomplish this:
DB::unprepared('update users set votes = 100 where name = "Dries"');
Note: Since unprepared statements do not bind parameters, they may be vulnerable to SQL injection. You should never allow user controlled values within an unprepared statement.
Implicit Commits
When using the DB
facade's statement
and
unprepared
methods within transactions you must be careful
to avoid statements that cause implicit
commits. These statements will cause the database engine to
indirectly commit the entire transaction, leaving Laravel unaware of the
database's transaction level. An example of such a statement is creating
a database table:
DB::unprepared('create table a (col varchar(1) null)');
Please refer to the MySQL manual for a list of all statements that trigger implicit commits.
Using Multiple Database Connections
If your application defines multiple connections in your
config/database.php
configuration file, you may access each
connection via the connection
method provided by the
DB
facade. The connection name passed to the
connection
method should correspond to one of the
connections listed in your config/database.php
configuration file or configured at runtime using the
config
helper:
use Illuminate\Support\Facades\DB;
$users = DB::connection('sqlite')->select(...);
You may access the raw, underlying PDO instance of a connection using
the getPdo
method on a connection instance:
$pdo = DB::connection()->getPdo();
Listening For Query Events
If you would like to specify a closure that is invoked for each SQL
query executed by your application, you may use the DB
facade's listen
method. This method can be useful for
logging queries or debugging. You may register your query listener
closure in the boot
method of a service provider:
<?php
namespace App\Providers;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;
class AppServiceProvider extends ServiceProvider
{
/**
* Register any application services.
*
* @return void
*/
public function register()
{
//
}
/**
* Bootstrap any application services.
*
* @return void
*/
public function boot()
{
DB::listen(function ($query) {
// $query->sql;
// $query->bindings;
// $query->time;
});
}
}
Database Transactions
You may use the transaction
method provided by 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 and the exception is
re-thrown. 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:
use Illuminate\Support\Facades\DB;
DB::transaction(function () {
DB::update('update users set votes = 1');
DB::delete('delete from posts');
});
Handling Deadlocks
The transaction
method accepts an optional second
argument which defines the number of times a transaction should be
retried when a deadlock occurs. Once these attempts have been exhausted,
an exception will be thrown:
use Illuminate\Support\Facades\DB;
DB::transaction(function () {
DB::update('update users set votes = 1');
DB::delete('delete from posts');
}, 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 provided by the DB
facade:
use Illuminate\Support\Facades\DB;
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!! The
DB
facade's transaction methods control the transactions for both the query builder and Eloquent ORM.
Connecting To The Database CLI
If you would like to connect to your database's CLI, you may use the
db
Artisan command:
php artisan db
If needed, you may specify a database connection name to connect to a database connection that is not the default connection:
php artisan db mysql