How to Execute SQL Queries in Laravel

Introduction

Laravel, a popular PHP framework for web application development, simplifies the process of interacting with databases through its elegant and expressive syntax. Whether you are a seasoned developer or a beginner, Laravel’s ORM Eloquent and the database query builder offer a powerful and flexible way to handle SQL queries. In this tutorial, we will explore different methods to execute SQL queries within Laravel.

Prerequisites

• A basic understanding of PHP and Laravel.
• Laravel application setup on your system. If you don’t have it yet, check this guide: How to install Laravel on Windows and Mac.
• Database connection configured in your .env file.

Setting Up the Environment

Before executing SQL queries, ensure your Laravel application is connected to a database. This involves setting the appropriate database connection parameters within the .env file of your Laravel project.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=homestead
DB_USERNAME=homestead
DB_PASSWORD=secret

Using Eloquent Models

Eloquent is Laravel’s default ORM. It favors Active Record implementation, making it possible to interact with the database objects and relationships using expressive PHP syntax.

Retrieving All Records

$users = App\Models\User::all();

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

Inserting a New Record

$user = new App\Models\User;
$user->name = 'John Doe';
$user->email = '[email protected]';
$user->save();

Using the Query Builder

Laravel’s query builder provides a convenient, fluent interface to create and run database queries. It is database agnostic, meaning it can build queries for MySQL, Postgres, SQL Server, and SQLite.

Selecting Data

$users = DB::table('users')->get();

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

Inserting Data

DB::table('users')->insert([
    'name' => 'Jane Doe',
    'email' => '[email protected]',
    'password' => bcrypt('123456')
]);

Raw SQL Queries

While Eloquent and the query builder abstract away most of the direct database access, sometimes you might need to write a raw SQL query. Laravel makes this possible through a handful of methods.

Running a Select Query

$users = DB::select('SELECT * FROM users WHERE active = ?', [1]);

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

Running an Insert Query

$success = DB::insert('INSERT INTO users (name, email, password) VALUES (?, ?, ?)', ['Jane Doe', '[email protected]', bcrypt('123456')]);

Handling Transactions

Transactions are crucial when you need to perform multiple database operations as a single unit of work. Laravel provides several methods for transaction management.

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

Listening To Query Events

Debugging your SQL queries can be made easier by listening to query events that Laravel fires for every query executed.

DB::listen(function ($query) {
    Log::info($query->sql, $query->bindings, $query->time);
});

Conclusion

In this tutorial, you’ve learned various ways to execute SQL queries using Laravel’s Eloquent ORM, query builder, and raw SQL statements. Additionally, we’ve touched on handling database transactions and listening for query events for debugging purposes. With these tools, you’re well-equipped to tackle any database challenges in your Laravel applications.