Getting Started with Laravel : Raw Queries

Screenshot from the tutorial

Getting Started with Laravel: Raw Queries

Laravel is one of the most popular PHP frameworks for web application development. It provides a clean and elegant syntax that makes it easy to build modern applications. One of the powerful features of Laravel is its database access layer, which allows for both ORM-based queries and raw SQL queries. In this blog post, we will explore how to use raw queries in Laravel to interact with your database efficiently.

What Are Raw Queries?

Raw queries allow developers to write SQL statements directly in their code. This can be beneficial when you need to execute complex queries that are not easily achievable with Laravel's Eloquent ORM. While Eloquent provides a powerful abstraction for database interactions, there are scenarios where raw SQL is necessary for performance or specific SQL functions.

Setting Up Laravel

Before we dive into raw queries, ensure you have a Laravel project set up. If you haven't done so, you can create a new Laravel project by running the following command:

composer create-project --prefer-dist laravel/laravel my-project

Once your project is created, navigate into your project directory:

cd my-project

Connecting to the Database

To use raw queries, you need to configure your database connection. Open the .env file in the root of your Laravel project and set your database details:

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=your_database
DB_USERNAME=your_username
DB_PASSWORD=your_password

Make sure to replace the placeholders with your actual database credentials. After updating the .env file, run the following command to clear the configuration cache:

php artisan config:cache

Using Raw Queries in Laravel

Laravel provides several methods to execute raw SQL queries. The primary methods are DB::select, DB::insert, DB::update, and DB::delete.

Selecting Data

To retrieve data using a raw query, you can use the DB::select method. Here's an example:

use Illuminate\Support\Facades\DB;

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

In this example, we fetch all active users from the users table. The ? placeholder helps prevent SQL injection by binding parameters securely.

Inserting Data

To insert data, use the DB::insert method. Here's how you can add a new user:

DB::insert('INSERT INTO users (name, email) VALUES (?, ?)', ['John Doe', 'john@example.com']);

Updating Data

Updating records can be done using the DB::update method. For instance, if we want to update a user's email address, we can do the following:

DB::update('UPDATE users SET email = ? WHERE name = ?', ['john.doe@example.com', 'John Doe']);

Deleting Data

To delete records, use the DB::delete method. For example:

DB::delete('DELETE FROM users WHERE name = ?', ['John Doe']);

Handling Transactions

Laravel also allows you to manage database transactions when executing multiple queries. Use the DB::transaction method to wrap your queries. Here's an example:

DB::transaction(function () {
    DB::insert('INSERT INTO users (name, email) VALUES (?, ?)', ['Alice', 'alice@example.com']);
    DB::insert('INSERT INTO users (name, email) VALUES (?, ?)', ['Bob', 'bob@example.com']);
});

If any of the queries fail, the transaction will roll back, ensuring data integrity.

Conclusion

Raw queries in Laravel are a powerful way to execute SQL commands directly when necessary. While Eloquent provides a rich API for interacting with your database, raw queries can simplify complex SQL operations. Always remember to use parameter binding to protect against SQL injection attacks.

By following the examples provided in this tutorial, you should be well on your way to using raw queries effectively in your Laravel applications. For more advanced features and best practices, consider exploring the official Laravel documentation.

Happy coding!

Another view from the tutorial

---

Connect with SkillBakery Studios

Explore more tutorials, tools, and resources:

• skillbakery.com
• Facebook
• X (Twitter)
• YouTube Channel

Posted by SkillBakery Studios