Laravel Database: Getting Started


Laravel Database: Getting Started – The Laravel Database is used to interacting with database.


Laravel Database: Getting Started.

Let us understand how to use laravel Database.

Function:-

There are some followings function available in laravel Database.

  • 1. Introduction.
  • 2. Running Raw SQL Queries.
  • 3. Database Transactions.

1. Introduction.

Laravel makes interacting with databases extremly simple across a variety of database backend using either raw SQL. Laravel supports Four databases.

MySql

Postgres

SQLite

SQL Server

Configuration

The database configuration for your application is placed at config/database.php. In this file you can define all of your database connection and specify which connection should be used by default.

SQLite Configuration

After creating a new SQLite database using a command as touch database/database.sqlite,
you may configure your enviroment variables to point to this created database.

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

SQL Server Configuration

It supports SQL Server out of the box, 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 want to use one database connection for select statement and another for INSERT, UPDATE and DELETE statement.

Lets see how read/write connection should be configured.

'mysql' => [
    'read' => [
        'host' => '192.168.1.1',
    ],
    'write' => [
        'host' => '196.168.1.2'
    ],
    'driver'    => 'mysql',
    'database'  => 'database',
    'username'  => 'root',
    'password'  => '',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix'    => '',
],

Using Multiple Database Connections

When using multiple connection, you can access each connection via the connection method on the DB facade. The name passed to the connection method should correspond to one of the connection listed in your config/database.php file.

$users = DB::connection('foo')->select(...);

You can also access the raw using like this:-

$pdo = DB::connection()->getPdo();

2. Running Raw SQL Queries.

You have configured your database connection, you can run queries using the DB facade. This facade provides methods for each type of query such as:select, update insert delete and statement.

Running A Select Query

To run a basic query, you can use the select method on the DB facade.

Let’s look at a simple example.

<?php
namespace App\Http\Controllers;
use Illuminate\Support\Facades\DB;
use App\Http\Controllers\Controller;

class UserController extends Controller
{
  
    public function index()
    {
        $users = DB::select('select * from users where active = ?', [1]);

        return view('user.index', ['users' => $users]);
    }
}

The select method will always return an array of results.

foreach ($users as $user) 
{
    echo $user->name;
}

Using Named Bindings

Instead of using ? to represent your parameter bindings, you can 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, we can use insert method on the DB
facade.

DB::insert('insert into users (id, name) values (?, ?)', [1, 'Tutorialsplane.com']);

Running An Update Statement

The update method is used to update record in the database.

$affected = DB::update('update users set votes = 100 where name = ?', ['SolidCoupon']);

Running A Delete Statement

The delete method is used to delete record from the database.

$deleted = DB::delete('delete from users');

Running A General Statement

Some database statements do not return any value, then you can use statement method.

DB::statement('drop table users');

Listening For Query Events

If you want to recieve each SQL query executed by your application, you can use listen
method.

Let’s look at a simple example.

<?php
namespace App\Providers;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\ServiceProvider;

class AppServiceProvider extends ServiceProvider
{
    public function boot()
    {
        DB::listen(function ($query) {
            // $query->sql
            // $query->bindings
            // $query->time
        });
    }

    public function register()
    {
        //
    }
}

3. Database Transactions.

You can use the transaction method on the 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();
});

Manually Using Transactions

If you want to start a transaction manually and have complete control over rollbacks and commits, you can use the beginTransaction method.

DB::beginTransaction();

You can rollback the transaction via the rollback method.

DB::rollBack();

Finally, you can commit a transaction via the commit method.

DB::commit();

Advertisements

Add Comment

📖 Read More