How not to rely solely on Laravel’s ORM (Eloquent)

Photo by Nik Shuliahin 💛💙 on Unsplash

Let us delve into the depths of database operations beyond the familiar boundaries of Eloquent. Get ready to discover a wealth of valuable techniques that will enhance your Laravel applications.

While Eloquent has been a reliable companion for database interactions, it’s time to broaden our horizons and explore the untapped potential that lies ahead. In this journey, we’ll dive into the intricacies of crafting efficient SQL queries, enabling you to optimize performance and fine-tune your database interactions. We’ll also uncover the power of raw SQL statements, giving you the ability to handle complex scenarios with precision and control. By understanding the underlying SQL, you’ll gain insight into the inner workings of your database and expand your toolkit.

Then, we’ll explore the unique features offered by your chosen database system, empowering you to leverage its specific capabilities, master the art of transactions, ensuring the integrity and consistency of your data. Transactions provide a safety net for executing multiple operations as a single unit, safeguarding against data inconsistencies and errors.

So, get ready to expand your database expertise and take your Laravel applications to new heights and unleash the power of advanced database techniques and create applications that leave a lasting impression!

Understand the Underlying SQL

While Eloquent provides an elegant and convenient way to interact with databases, it’s essential to have a solid understanding of SQL. Don’t solely rely on Eloquent’s abstractions — take the time to learn SQL queries and database concepts. This knowledge allows you to write efficient queries, optimize performance, and handle complex scenarios that may not be easily achieved with Eloquent alone.

Eloquent abstracts away the complexities of SQL by providing a fluent and intuitive syntax for querying and manipulating data. However, there are cases where you may encounter situations that require fine-tuning or optimization beyond what Eloquent offers.

To dive deeper into SQL, you can start by exploring the raw SQL queries executed by Eloquent. Laravel’s query logging feature allows you to log and examine the SQL statements generated by Eloquent. Enable query logging in your Laravel application by setting the DB_CONNECTION variable to log in your .env file:

DB_CONNECTION=log

Once query logging is enabled, you can retrieve the executed SQL statements by accessing the queries array on the DB facade:

DB::connection()->enableQueryLog();

// Perform Eloquent queries here

$queries = DB::getQueryLog();

dd($queries);

By analyzing the logged SQL queries, you can gain insights into the underlying SQL generated by Eloquent for various operations.

Additionally, you can use Laravel’s DB facade to execute raw SQL queries directly. Here's an example of executing a raw SQL query:

$results = DB::select('SELECT * FROM users WHERE age > ?', [18]);

foreach ($results as $result) {
    // Process the retrieved data
}

In this example, we use the select method on the DB facade to execute a raw SQL query that retrieves all users with an age greater than 18. The results are returned as an array of objects that you can iterate over and process as needed.

By understanding the underlying SQL, you gain more control over your database interactions and can handle complex scenarios that may require fine-tuned queries. Balancing your knowledge of SQL with the convenience of Eloquent allows you to make informed decisions and optimize your database operations in Laravel applications.

Leverage Raw SQL Queries

In some cases, complex queries or performance optimizations may require the use of raw SQL queries. Laravel allows you to execute raw SQL statements using the DB facade or the query builder's selectRaw, join, or whereRaw methods. By leveraging raw SQL queries when necessary, you have more control over the query execution and can achieve specific requirements efficiently.

When using raw SQL queries, it’s important to remember the potential risks of SQL injection. Always use parameter binding or prepared statements to sanitize user input and prevent SQL injection vulnerabilities.

Let’s explore an example of leveraging raw SQL queries in Laravel

$minimumAge = 18;

$results = DB::select("SELECT * FROM users WHERE age > :age", ['age' => $minimumAge]);

foreach ($results as $result) {
    // Process the retrieved data
}

In this example, we use the select method on the DB facade to execute a raw SQL query with a parameter binding. The query selects all users with an age greater than the provided $minimumAge value. The :age placeholder is bound to the $minimumAge value through the second argument of the select method, ensuring the query is safe from SQL injection.

Laravel also provides the insert, update, and delete methods on the DB facade to perform raw SQL operations for data modification. Here's an example of using the insert method:

$values = [
    ['John Doe', 25],
    ['Jane Smith', 30],
    // more data
];

DB::insert('INSERT INTO users (name, age) VALUES (?, ?)', $values);

In this example, we use the insert method to perform a raw SQL insert operation. The values to be inserted are passed as an array of arrays, where each nested array represents a set of values for a single row.

By leveraging raw SQL queries when necessary, you can tap into the full power of your database system and optimize performance in specific scenarios. However, it’s important to strike a balance between using raw SQL and utilizing Laravel’s abstractions to maintain code readability and maintainability.

Explore Database-specific Features

Different database systems have unique features and functionalities. Don’t limit yourself to what Eloquent provides in terms of database interactions. Explore and utilize the specific features offered by your chosen database system. For example, if you’re using MySQL, you can benefit from stored procedures, triggers, or full-text search capabilities. By leveraging database-specific features, you can harness the full power of your database system.

Let’s consider an example where you want to use a database-specific feature like a stored procedure in MySQL. Here’s how you can execute a stored procedure using Laravel’s DB facade:

$results = DB::select('CALL sp_get_users(?, ?)', [$param1, $param2]);

foreach ($results as $result) {
    // Process the retrieved data
}

In this example, we use the select method on the DB facade to execute a stored procedure named sp_get_users. The parameters $param1 and $param2 are passed to the stored procedure as inputs. The results returned by the stored procedure are then processed in the foreach loop.

Another example is utilizing database-specific functions. Let’s say you’re using PostgreSQL, which provides a powerful full-text search functionality. You can leverage PostgreSQL’s tsvector and tsquery types along with the @@ operator for performing full-text searches. Here's an example:

$searchTerm = 'Laravel';

$results = DB::table('articles')
    ->select('title', 'content')
    ->whereRaw("to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', ?)", [$searchTerm])
    ->get();

foreach ($results as $result) {
    // Process the retrieved data
}

In this example, we use the whereRaw method to construct a raw SQL query that performs a full-text search on the title and content columns of the articles table. The to_tsvector function converts the columns' values into a vector, and the to_tsquery function converts the search term into a query. The @@ operator checks if the vector matches the query, allowing us to perform the full-text search.

By exploring and utilizing database-specific features, you can enhance your application’s functionality, optimize performance, and take advantage of advanced capabilities offered by your chosen database system. However, keep in mind that using database-specific features ties your code to a specific database, reducing portability. Consider the trade-offs and ensure that the benefits outweigh the drawbacks in your specific use cases.

Utilize Database Transactions

Transactions ensure the atomicity and consistency of database operations. While Eloquent handles transactions automatically in most cases, there might be situations where you need finer control. Use Laravel’s transaction methods (beginTransaction, commit, rollback) or database-specific transaction methods to wrap multiple operations in a single transaction. This approach helps maintain data integrity and handle complex business logic scenarios.

Let’s explore an example of utilizing database transactions in Laravel:

DB::beginTransaction();

try {
    // Perform multiple database operations using Eloquent or raw SQL queries

    DB::commit();
} catch (\Exception $e) {
    DB::rollback();
    // Handle the exception or log an error message
}

In this example, we use Laravel’s beginTransaction, commit, and rollback methods to control a database transaction. Within the try block, you can perform multiple database operations using Eloquent or raw SQL queries. If any operation encounters an exception, the catch block is executed, and the transaction is rolled back using DB::rollback(). Otherwise, if all operations are successful, the transaction is committed using DB::commit().

It’s important to note that when using Eloquent, simple CRUD operations (create, update, delete) are automatically wrapped in a transaction by default. However, in scenarios where you need to perform multiple related operations or handle custom logic, manually managing transactions provides more control.

By utilizing database transactions, you ensure that a group of operations is executed as a single unit, either succeeding entirely or rolling back if any operation fails. This helps maintain data integrity and consistency, especially in situations where multiple records or tables are involved.

Remember to handle exceptions properly within the transaction and take appropriate actions such as logging errors, displaying user-friendly error messages, or triggering any necessary compensating operations.

Utilizing database transactions offers you greater control over complex database interactions and ensures that your data remains consistent, even in the face of potential failures or exceptions.

Consider Using Query Builder

Laravel’s Query Builder provides a powerful alternative to Eloquent. It allows you to construct queries programmatically using a fluent and intuitive syntax. The Query Builder provides more flexibility and control over the query structure and can be used in conjunction with Eloquent models. Consider using the Query Builder when you need fine-grained control over your queries or when you need to interact with database tables that don’t have corresponding Eloquent models.

The Query Builder offers a variety of methods that allow you to construct complex SQL queries. Let’s explore an example of using the Query Builder to perform a more intricate query:

$users = DB::table('users')
    ->join('orders', 'users.id', '=', 'orders.user_id')
    ->where('users.active', true)
    ->whereIn('orders.status', ['pending', 'completed'])
    ->orderBy('users.created_at', 'desc')
    ->select('users.*', 'orders.total')
    ->get();

In this example, we use the table method on the DB facade to specify the "users" table as the starting point for our query. We then join the "orders" table based on the relationship between the "users" and "orders" tables. We apply various conditions using the where and whereIn methods, specifying criteria for the "users" and "orders" tables respectively. We also order the results by the "created_at" column of the "users" table and select specific columns from both tables.

The Query Builder allows you to construct complex queries by chaining methods together in a fluent manner. It provides methods for selecting columns, joining tables, applying conditions, ordering results, and more. By utilizing the Query Builder, you have greater control over the query structure and can handle scenarios that may require fine-grained control or involve tables that don’t have corresponding Eloquent models.

It’s worth noting that Eloquent models are built on top of the Query Builder, and you can seamlessly switch between the two as needed. When the structure of your data aligns with Eloquent models, using Eloquent provides a convenient way to interact with the database. However, when you need more flexibility or control over the query construction, the Query Builder becomes a powerful tool.

By considering the use of the Query Builder, you can take advantage of its flexible syntax and extensive functionality to construct complex queries and interact with your database tables effectively, even in scenarios where Eloquent models may not be the ideal choice.

Use Repository Pattern

The Repository pattern is a common design pattern that promotes separation of concerns and improves the maintainability of your Laravel application. By implementing the Repository pattern, you can reduce the reliance on Laravel’s ORM (Eloquent) and create a clear abstraction layer between your application’s business logic and the database operations.

The Repository pattern involves creating a repository class for each entity or resource in your application. The repository acts as an intermediary between your application’s business logic and the database, encapsulating all the database interactions within its methods.

Here’s an example of implementing the Repository pattern for a User entity in Laravel:

1. Create the UserRepository class:

namespace App\Repositories;

use App\Models\User;

class UserRepository
{
    protected $model;

    public function __construct(User $model)
    {
        $this->model = $model;
    }

    public function getById($id)
    {
        return $this->model->find($id);
    }

    public function create(array $data)
    {
        return $this->model->create($data);
    }

    // Additional methods for updating, deleting, and querying users
}

2. Inject the repository into your application:

namespace App\Services;

use App\Repositories\UserRepository;

class UserService
{
    protected $userRepository;

    public function __construct(UserRepository $userRepository)
    {
        $this->userRepository = $userRepository;
    }

    public function createUser(array $data)
    {
        // Perform business logic and validation

        // Delegate database operations to the repository
        return $this->userRepository->create($data);
    }

    // Additional methods for user-related business logic
}

By following the Repository pattern, you decouple your business logic from the underlying database implementation. Your service classes interact with the repository, which abstracts away the specific database operations performed by Eloquent or raw SQL queries.

This approach offers several benefits:

• Improved code organization and separation of concerns: The repository encapsulates the database operations, making it easier to manage and maintain your codebase.
• Testability: By using repositories, you can easily mock and test your business logic without touching the database.
• Flexibility: You can switch between different data sources or ORM implementations without affecting your application’s business logic.

While Eloquent provides a convenient way to interact with the database, embracing the Repository pattern allows you to have greater control over your data access layer and reduces the direct reliance on Eloquent. It promotes modular and reusable code, making your Laravel application more robust and maintainable.

Conclusion

We have seen that by understanding the underlying SQL, leveraging raw SQL queries when needed, exploring database-specific features, utilizing database transactions, considering the Query Builder, and embracing the Repository pattern, you can expand your capabilities beyond relying solely on Laravel’s ORM (Eloquent). These approaches provide you with more control, flexibility, and performance optimizations, empowering you to handle complex scenarios and tailor your database interactions to meet specific requirements. By diversifying your toolkit and taking advantage of the broader database ecosystem, you can elevate your Laravel application to new heights of efficiency and maintainability.

Stay tuned!!! I will be back with some more cool Laravel tutorials in the next article. I hope you liked the article. Don’t forget to follow me 😇 and give some clap 👏. And if you have any questions feel free to comment.

Thank you.

Thanks a lot for reading till end. Follow or contact me via:
Twitter: https://twitter.com/EjimaduPrevail
Email: prevailexcellent@gmail.com
Github: https://github.com/PrevailExcel
LinkedIn: https://www.linkedin.com/in/chimeremeze-prevail-ejimadu-3a3535219
BuyMeCoffee: https://www.buymeacoffee.com/prevail
Chimeremeze Prevail Ejimadu