Raw SQL Queries: Laravel interact with databases by the variety of database back-ends with raw SQL, the fluent query builder, and the Eloquent ORM.
Laravelsupports four types of databases:
- MySQL
- PostgreSQL
- SQLite
- SQL Server
Configuration
The database configuration of our application is located at config/database.php.
In the above location, we define all of our database connections,as we define a connection that should be used by default.
Environment configuration is ready to use which Laravel Homestead,it is a convenient virtual machine for Laravel development on our localmachine.
We are free to modify this configuration, which is needed for ourlocal database.
SQLite Configuration
We can configure our environment variables to the newly createddatabase by using the database`s absolute path:
DB_CONNECTION=sqliteDB_DATABASE=/absolute/path/to/database.sqlite
For enabling the foreign keyconstraints in the SQLite connections, we add theforeign_key_constraintsoption to yourconfig/database.phpconfiguration file:
'sqlite' => [ // ... 'foreign_key_constraints' => true, ],
Configuration using URLs
Database connections are configured using variety of configurationvalues like host, database, username, password, etc.
These configuration values have their environment variable. It means that when we are configuring ourdatabase connection information on a production server, we need to manage someenvironment variables.
Some of the managed database providers like Heroku provide asingle database ”URL”, which contains all of the connection information for thedatabase in a single string.
mysql://root:[emailprotected]/forge?charset=UTF-8
These URLs follow astandard schema accordingly:
driver://username:password@host:port/database?options
Laravel supports these URLs to configuring our database as analternate with multiple configuration options.
The URL configuration option is present, and it will be extractthe database connection and credential information.
Read & Write Connections
Laravel makes this an easy, and the proper connections will alwaysbe used whether we are using raw queries, the query builder, or the EloquentORM.
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' => '', ],
In the above example, the read/write connections should beconfigured like this.
The three keys that have been added to the configuration arrayare:
Read, write, and sticky.
The read and writekeys contains a single key of array values that is a host.
The rest of the database options of the read and write connectionswill be merged from the main mysql array.
We need to place items in the read and write arrays, to override the values from the main array.
192.168.1.1 used as the host to the “read” connection.
192.168.1.3 used for the “write” connection.
Both connections will share the Database prefix, character set,and all other options in the main mysql array.
The sticky Option
The sticky option is a non-required value that is used to allowthe reading of records, which is written to the database during the currentrequest cycle.
The sticky option is enabled and “write” operation performedagainst the database during the current request cycle, “read” operations willuse the “write” connection.
It ensures that any data which is written during the request cyclecan be immediately read back from the database during that same request.
It is up to the user to decide the desired behavior for the application.
Multiple Database Connections
The connectionmethod on the DBfaçade.
The namepassed to the connectionmethod correspond to one of the connections listed in our config/database.php configuration file:
$users=DB::connection('foo')->select(...);
We also access the raw, the getPdo method underlying PDO instance on a connection instance:
$pdo = DB::connection()->getPdo();
Running Raw SQL
To run the queries using the DB façade. The DB façade provides methods for each type of query like, select, update, insert, delete, and statement.
A SelectQuery
To run a query, we use the select method on the DB façade.
Example:
$employee]);}}
The raw SQL query is passed to the select method as the first argument, andthe second argument is any parameter that needs to be bound to the query.
Parameter binding provides protection against SQL injection.
The selectmethod will return the results of an array.
The result within the array will be a PHP stdClasss object, allowing us to accessthe values of the results
foreach ($users as $user) { echo $user->name; }
Named Bindings
We execute a query using named bindings:
$results = DB::select(‘select * from table_name where id = :id’, [‘id’ => 1]);
An Insert Statement
We use the insert method to execute an insert statement on the DBfaçade.
It takes the raw SQL query as the first argument and bindings asthe second argument:
DB::insert('insert into table_name (id, name) values (?, ?)',[5,'Rafia']);
An Update Statement
The updatemethod is used to update existing records in the database.
The number of rows which is affected by the statement will bereturned:
$affected = DB::update(‘update table_name set votes = 100 where name =?’ , [‘Rafia’]);
A Delete Statement
The delete method is used to delete records from the database.
$deleted=
DB::delete('delete from table_name');
A General Statement
Someof the database statements do not return any value.
For these types of operations, we use thestatementmethod on theDBfacade:
DB::statement('drop table table_name');
Query Events
To receive each SQL query executed by our application, we use the listen method.
This method is used for logging queries or debugging.
We register our query listener in a service provider.
sql// $query->bindings // $query->time});}}

Database Transactions
The transaction method used on the DB façade that runs a set ofoperations within a database transaction.
An exception is thrown within the transaction Closure then thetransaction will be automatically rolled back. And if the Closure executessuccessfully then the transaction will be automatically committed.
DB::transaction(function () { DB::table('users')->update(['votes' => 1]); DB::table('posts')->delete(); });