Sometimes we want to run the raw SQL query and don’t want to create Model. Sometimes SQL query can be big and we don’t want to use Query Builder and want to write query by ourself.

In this article I will show you how you can get the data in laravel using raw SQL query. I will try to keep the article as simple and as simple as I could.

I am starting from a very new project in case you are wondering I have pre-configured something in my laravel project.

YouTube Video

Database

Starting from new laravel project. First of all we will make some changes to the .env file.

Set the database setting to the .env file. So that laravel could make connection with your database.

Route

Let’s create a route on which we will read the raw sql. I am going to add one line of code to our web.php file.

Route::get('/sql', 'Products@index');

We are calling the index method of Product Controller but we don’t have product controller now. We will create the controller in next step.

Final web.php file

<?php

/*
|--------------------------------------------------------------------------
| Web Routes
|--------------------------------------------------------------------------
|
| Here is where you can register web routes for your application. These
| routes are loaded by the RouteServiceProvider within a group which
| contains the "web" middleware group. Now create something great!
|
*/

Route::get('/', function () {
    return view('welcome');
});

Route::get('/sql', 'Products@index');

Controller

Now, We will create a controller and we will make to the sql query request from there.

Run this command to generate the Controller

php artisan make:controller Products

In this Controller we will use Illuminate\Support\Facades\DB to make request to the database.

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class Products extends Controller
{
    function index(){
        $all = DB::select("select * from product");

        return $all;
    }
}

SQL query

I am requesting for response corresponding to this query. Update this query based on your table.

select * from product

Important: When you are using DB::select($query) then you can only send query starting with select.

Now If you will make request to the 127.0.0.01:8080/sql the you will find the response.

DB Methods

To send more raw queries like create, update, delete etc

• DB::select($query) : Using the select method, we can select the data from the table.
• DB::insert($query) : Using the select method, We can insert the data to the table.
• DB::update($query) : Using the select method, We can insert the data to the table.
• DB::delete($query) : Using the select method, We can insert the data to the table.

There are more method but these methods are used very frequently.

For production

I don’t recommend to use this for production because passing SQL query directly could be harmful for the database. When you use query build it care for the attacks but when we send the raw query it doesn’t check.

We can do some thing do secure the query or to make the query more safe. Query builder take care for the protection very much. When we write the query by ourself then we need to take care that we are compromising with the security.

Laravel uses PDO at the lower level which means we can bind the variable and user input to the query. It will make the query safe.

$result = DB::select("select * from products where name LIKE ?", array($value));

The above code will make the query secure a lot. Now it’s little difficult to do any SQL attack.

Remember : This is not considered as fully secured. I will discuss about the security in the some other article.

Purpose of this article was to teach you how you can send the raw query and get the response.

I hope you learn something new today and If you think you would like to thank me then you tweet about this.

Tweet #nitishk72_