Raw SQL Queries

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.

Laravel supports four types of databases:

1. MySQL
2. PostgreSQL
3. SQLite
4. 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 local machine.

We are free to modify this configuration, which is needed for our local database.  

SQLite Configuration

We can configure our environment variables to the newly created database by using the database`s absolute path:

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

For enabling the foreign key constraints in the SQLite connections, we add the foreign_key_constraints option to your config/database.php configuration file:

'sqlite' => [
  // ...
  'foreign_key_constraints' => true,
 ], 

Configuration using URLs

Database connections are configured using variety of configuration values like host, database, username, password, etc.

These configuration values have their environment variable.  It means that when we are configuring our database connection information on a production server, we need to manage some environment variables.

Some of the managed database providers like Heroku provide a single database ”URL”, which contains all of the connection information for the database in a single string.

mysql://root:[email protected]/forge?charset=UTF-8

 These URLs follow a standard schema accordingly:

driver://username:password@host:port/database?options

Laravel supports these URLs to configuring our database as an alternate with multiple configuration options.

The URL configuration option is present, and it will be extract the database connection and credential information.

Read & Write Connections

Laravel makes this an easy, and the proper connections will always be used whether we are using raw queries, the query builder, or the Eloquent ORM.

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 be configured like this.

The three keys that have been added to the configuration array are:

Read, write, and sticky.

The read and write keys contains a single key of array values that is a host.

The rest of the database options of the read and write connections will 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 allow the reading of records, which is written to the database during the current request cycle.

The sticky option is enabled and “write” operation performed against the database during the current request cycle, “read” operations will use the “write” connection.

It ensures that any data which is written during the request cycle can 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 connection method on the DB façade.

The name passed to the connection method 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 Select Query

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, and the second argument is any parameter that needs to be bound to the query.

Parameter binding provides protection against SQL injection.

The select method will return the results of an array.

The result within the array will be a PHP stdClasss object, allowing us to access the 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 DB façade.

It takes the raw SQL query as the first argument and bindings as the second argument:

DB::insert('insert into table_name (id, name) values (?, ?)',[5,'Rafia']);

An Update Statement

The update method is used to update existing records in the database.

The number of rows which is affected by the statement will be returned:

$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

Some of the database statements do not return any value.

For these types of operations, we use the statement method on the DB facade:

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 of operations within a database transaction.

An exception is thrown within the transaction Closure then the transaction will be automatically rolled back. And if the Closure executes successfully then the transaction will be automatically committed.

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