How to Execute Raw Queries in Laravel 8

  10795 views   8 months ago Laravel

Hello guys,

Sometimes you have raw SQL query which you want to execute in your Laravel application. In this article, I will show you how you can execute raw SQL query in Laravel application.

Laravel DB facade provides select, insert, update, delete and statement methods for running each type of query. We will discuss all methods with example.

select() method

Laravel's select method allows you to execute SELECT method.

/**
 * get all users
 *
 * @return \Illuminate\Http\Response
 */
public function index()
{
    $users = \DB::select('SELECT * from users');

    dd($user); // 1
}

If you are injecting data into query, you can pass in second parameter in array.

/**
 * get user by email
 *
 * @return void
 */
public function index(Request $request)
{
    $users = \DB::select('SELECT * from users WHERE email = ?', [$request->email]);

    dd($users);
}

array:1 [▼
  0 => {#282 ▼
    +"id": 1
    +"name": "Harsukh"
    +"email": "[email protected]"
    +"email_verified_at": null
    +"password": "$2y$10$XwA4wnJ/2O"
    +"remember_token": null
    +"created_at": "2020-08-11 12:40:50"
    +"updated_at": "2020-08-11 12:40:50"
  }
]

The select() method always return array of records.

insert() method

Laravel's DB::insert() method allows you to SQL insert query. You can define insert data in second parameter.

/**
 * insert new user
 *
 * @return void
 */
public function store(Request $request)
{
    $users = \DB::insert('INSERT into users (name, email) VALUES (?, ?)', [$request->name, $request->email]);

    dd($users); // true
}

update() method

To use SQL UPDATE method, Laravel provides DB facades update method.

/**
 * update user
 *
 * @return void
 */
public function update(Request $request)
{
    $users = \DB::update('UPDATE users set name = ? WHERE id = ?', ['manish', '1']);

    dd($users); // true
}

delete() method

If you want to run SQL DELETE statement, you can use DB::delete() method.

/**
 * delete user
 *
 * @return void
 */
public function delete(Request $request)
{
    \DB::delete('DELETE from users WHERE id = ?', ['2']);
}

statement() method

This method is used to run general query which doesn't return any value.

/**
 * destroy user
 *
 * @return void
 */
public function destroy(Request $request)
{
    \DB::statement('DROP table users');
}

unprepared() method

These are all prepared statement to prevent SQL injection into database. However you might needed to run query without binding value. To run query, use unprepared() method.

/**
 * view user
 *
 * @return void
 */
public function view(Request $request)
{
    \DB::unprepared('INSERT into users (name, email) VALUES ("Harsukh", "[email protected]")');
}

Note: Avoid run unprepared() method on user input value, this can be risk for SQL injection.

This way, you can run SQL query into Laravel application.

Author : Harsukh Makwana
Harsukh Makwana

Hi, My name is Harsukh Makwana. i have been work with many programming language like php, python, javascript, node, react, anguler, etc.. since last 5 year. if you have any issue or want me hire then contact me on [email protected]