How to execute raw SQL queries in 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": "Jitesh"
    +"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 ("Jitesh", "[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.

Tags:

Was this article helpful?

0 out of 0 person found this article helpful.

Leave a comment

Or

No Comment