Introduction

Any application you use these days often interacts with a database. A database is at the core of a large-scale application. To store large amounts of data and retrieve them efficiently, speed matters, especially in these times when things have to be done quickly.

Laravel makes interacting with databases extremely simple across a variety of supported databases using raw SQL, a fluent query builder, and the Eloquent ORM. Currently, Laravel provides first-party support for five databases:

• MariaDB 10.10+ (Version Policy)
• MySQL 5.7+ (Version Policy)
• PostgreSQL 11.0+ (Version Policy)
• SQLite 3.8.8+
• SQL Server 2017+ (Version Policy)

Raw SQL, Query Builders and ORMs are the three common approaches for accessing data from a database. This blog will cover the three approaches along with the advantages and disadvantages of each approach. Here is a tabular view of the strengths and weaknesses of each approach.

Why three approaches?

The more advanced a query, the better it is to use fewer abstractions, with SQL having no abstraction, Query Builder having some level of abstraction and ORMs having the highest level of abstraction.

Raw SQL

As the name suggests, we directly interact with the database using SQL queries. Let's take a look at the advantages and disadvantages:

Advantages

• Control: When you write raw SQL queries, you have full control over the queries that are executed against the database. You can craft queries that are precisely tailored to your requirements, which can lead to more predictable and efficient database operations.
• Performance: Writing SQL queries directly allows you to optimize them for performance. Abstraction layers might generate suboptimal SQL in some cases, whereas with raw SQL, you can fine-tune performance-critical queries.
• Flexibility: When you write SQL directly, you can leverage the full capabilities of your database system without being limited by the abstractions imposed by higher-level tools.
• Debugging: Debugging SQL queries can be more straightforward when you write them directly.
• Complex Queries: Some database operations may require complex and custom SQL queries that are challenging to express using high-level abstractions. By writing raw SQL, you can handle even the most intricate database operations.

Let's check out some queries using raw SQL.

Queries

Select query

Select statements are used to select/retrieve data from the database. Let's take an example where we have the following tables - orders,order_details,products,and customers and perform some queries on them.

CREATE TABLE order_details (
    order_detail_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (order_id) REFERENCES orders(order_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    unit_price DECIMAL(10, 2),
    stock_quantity INT
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(255),
);

Let's consider an example:

We need to get a list of products along with their total sales, the total sales being in descending order. We have tables containing the data of orders with the name order_details and tables containing products with the name products.

Let's break it into smaller parts. Then this would be the list of tasks we need to perform in order:

1. Calculate the total sales from the order_details table. We will be using GROUP BY clause for this.
2. Once we know the data about the Sales, find the details of the product from the products table.
3. We use join to join the products table with the Sales on the product_id column to associate each product with its total sales.
4. The result set includes two columns: product_name from the products table and total_sales from the Sales.
5. Then we can use ORDER BY clause orders the result set by total_sales in descending order, so products with the highest total sales will appear first in the result.

WITH Sales AS (
    SELECT product_id, SUM(quantity) AS total_sales
    FROM order_details
    GROUP BY product_id
)
SELECT products.product_name, Sales.total_sales
FROM products
JOIN Sales ON products.product_id = Sales.product_id
ORDER BY Sales.total_sales DESC;

Let's see another query:

We need to retrieve a list of orders along with the names of the customers who placed them and the names of the products that were ordered. The orders should have an order date after January 1, 2022.

Again let's break it into smaller parts. Then this would be the list of tasks we need to perform in order:

1. Let's focus on the first part of the query which is to retrieve a list of orders along with the names of the customers who placed them and the names of the products that were ordered. So, we join the tables to get the data.
2. So, join orders table with customers,order_details,products.
3. orders JOIN customers ON orders.customer_id = customers.customer_id : This is the first JOIN clause we will use. It combines data from the orders table and the customers table using the customer_id column.
4. JOIN order_details ON orders.order_id = order_details.order_id : This is the second JOIN clause. It joins the order_details table with the result set obtained from the previous JOIN.
5. JOIN products ON order_details.product_id = products.product_id: This is the third JOIN clause. It further extends the JOIN by bringing in product information from the products table.
6. Now, let's select the required information from the result we get by joining the tables.

 SELECT
     orders.order_id,
     customers.customer_name,
     products.product_name
 FROM 
     orders
 JOIN customers ON orders.customer_id = customers.customer_id
 JOIN order_details ON orders.order_id = order_details.order_id
 JOIN products ON order_details.product_id = products.product_id

Now add the where condition to get the orders that have an order date after January 1, 2022.

SELECT
    orders.order_id,
    customers.customer_name,
    products.product_name
FROM
    orders
JOIN customers ON orders.customer_id = customers.customer_id
JOIN order_details ON orders.order_id = order_details.order_id
JOIN products ON order_details.product_id = products.product_id
WHERE
    orders.order_date > '2022-01-01';

Inserting Queries

These queries are used to insert the data into the table. The insert queries can be combined with the select queries to select data from another table and insert it into the table required.

Let's consider a situation where you have a users table, but you want to create another table for premium users in the users table called premium_users and insert the premium users' records into that table.

INSERT INTO premium_users(user_id,first_name,last_name)
SELECT user_id,first_name,last_name
FROM users
WHERE users.status = 'premium';

Let's look at another example:

We need to insert data into the clients table based on data from the suppliers table and ensure that only new clients (those that don't already exist in the clients table) are inserted.

We need to select the data from the suppliers table and then apply an additional condition that prevents from selecting the clients that already exist, for this we will use WHERE NOT EXISTS clause.

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id AS client_id, 
supplier_name AS client_name, 
'advertising' AS client_type
FROM suppliers
WHERE NOT EXISTS (SELECT * FROM clients
WHERE clients.client_id = suppliers.supplier_id);

Disadvantages

• Portability: Raw SQL queries may be tied to a specific database system, making it harder to switch to a different database engine.
• Readability: As queries become more complex, managing them in code can become challenging, and it may require careful documentation to ensure that future developers can understand and maintain the queries.
• Security: Writing raw SQL queries without proper input validation and parameterization can expose your application to SQL injection vulnerabilities. You must take care to sanitize and validate user inputs.

Query Builder

In case you are wondering what a query builder is, it is an interface that allows developers to construct database queries more abstractly instead of using raw SQL statements. This makes it more readable and easy to interact with the database. Let's take a look at the documentation:

Laravel's database query builder provides a convenient, fluent interface for creating and running database queries. It can be used to perform most database operations in your application and works perfectly with all of Laravel's supported database systems.

It means that Laravel provides a fluent way to construct and execute database queries. It allows you to chain methods, and execute complex methods with ease and makes your code more readable and maintainable. One of the advantages of using Laravel's query builder is protection against SQL injections. For this Laravel uses the PDO parameter and it doesn't support binding column names, which means that you should never take column names as input from the user.

Advantages

• Fluent Query Building: Query builders are libraries or components that provide a fluent, code-based interface for constructing and executing SQL queries. They are typically used to build dynamic queries without writing raw SQL.
• Fine-Grained Control: Query builders offer fine-grained control over SQL queries, allowing you to specify select statements, conditions, joins, and other SQL components programmatically.
• Efficiency: Query builders can be more efficient in certain situations, especially when dealing with complex queries or when performance optimization is critical. They allow you to optimize SQL queries manually.
• Raw SQL Integration: Query builders often provide ways to incorporate raw SQL when necessary, providing flexibility.
• Lightweight: Query builders can be lightweight and offer a simpler way to interact with databases for developers who are comfortable with SQL.

Queries

Select Query

Let's consider an example:

The tables we have are orders,customers and products whose schema is given below.

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
);

CREATE TABLE customers (
    id INT PRIMARY KEY,
    name VARCHAR(255),
);

CREATE TABLE order_items (
    id INT PRIMARY KEY,
    order_id INT,
    total DECIMAL(10, 2),
);

Let's consider the following query:

We need to retrieve information about the total sales for each customer.

Let's break it into smaller parts.

• Getting the information about the sales grouped by customer's name
  • We need to connect orders table with customers table first using the join method with customer_id as the common column. This gives the information about customers who have orders.
  • Now we can use the join method for the result of the above with the order_items with order_id as the common column. The resultant gives information about the items ordered by the customer along with the sales of each item.
  • Now that we need to use groupBy method to get information about items purchased by each customer by name.
• Finding the total sales
  • From the result of the first part, we select the customer name and the sum of the total items' cost.
  • To get the sum of the total items' cost, we can use the DB::raw method in Laravel query builder. It is used to execute raw SQL queries.(Remember, Laravel can not guarantee that any query using raw expressions is protected against SQL injection vulnerabilities.)

$orders = DB::table('orders')
    ->join('customers', 'orders.customer_id', '=', 'customers.id')
    ->join('order_items', 'orders.id', '=', 'order_items.order_id')
    ->groupBy('customers.name')
    ->select('customers.name', 
       DB::raw('SUM(order_items.total) as total_sales'))
    ->get();

Insert Statements

Laravel's Query Builder provides an insert method that can be used to insert records into the table. The insertOrIgnore method will ignore errors while inserting records into the database. The insertUsing method will insert new records into the table while using a subquery to determine the data that should be inserted.

Let's take an example

Let's insert data from users table into another table called pruned_users given that the users updated_at column is less than or equal to one month ago.

We need to insert the values of id,name,email into the pruned_users from the users table. We will be using the insert_using method since we need to apply a condition to determine which data should be inserted into pruned_users table. This will be the subquery.

In the subquery, we need to select id,name,email from the users table with the condition that updated_at is less than a month, for which we will use where method.

DB::table('pruned_users')->insertUsing(
    ['id', 'name', 'email'],
    DB::table('users')->select(
    'id', 'name', 'email'
)->where('updated_at', '<=', now()->subMonth()));

Disadvantages

• Complex Queries: Query Builder can handle most SQL queries, but complex and nested queries might become harder to read and maintain compared to writing raw SQL for such scenarios.
• Performance Overhead: In some cases, Query Builder can introduce a slight performance overhead compared to writing raw SQL, especially for very complex queries, because it generates SQL dynamically.
• Less Control: Query Builder abstracts some low-level details, which can be an advantage for most tasks. However, in some situations, you may need fine-grained control over the SQL query, which is easier to achieve with raw SQL.
• Maintenance: As applications grow and evolve, maintaining and debugging complex queries built with Query Builder can become challenging, especially if they involve numerous joins and subqueries.
• Not Suitable for All Database Tasks: While Query Builder is excellent for typical CRUD (Create, Read, Update, Delete) operations, some advanced database tasks, such as database migrations or defining complex constraints, might require manual SQL.

ORM

Laravel includes Eloquent, an object-relational mapper (ORM) that makes it enjoyable to interact with your database. When using Eloquent, each database table has a corresponding "Model" that is used to interact with that table. In addition to retrieving records from the database table, Eloquent models allow you to insert, update, and delete records from the table as well.

Advantages

1. Abstraction of Database Operations: ORM is a technique that abstracts the database by representing database tables as classes (models) and individual rows as objects. This allows you to interact with the database using object-oriented principles.
2. Simplifies Database Interaction: ORM simplifies database interactions by providing a higher level of abstraction. You can perform CRUD (Create, Read, Update, Delete) operations on database records using object-oriented code, which can be more intuitive and maintainable.
3. Relationship Management: ORMs often provide built-in support for managing relationships between database tables, such as one-to-many and many-to-many relationships. This simplifies complex data retrieval and manipulation.
4. Data Validation: Many ORMs offer features for data validation and business logic enforcement, ensuring data integrity.
5. Code Reusability: ORM models can promote code reusability since you can work with data objects consistently throughout your application.
6. Portability: ORM models are often database-agnostic, allowing you to switch between different database systems more easily.

Queries

Complex Relationships

Complex relationships in Eloquent ORM can involve multiple levels of hasMany and belongsTo relationships between models.

Let's consider an example involving three related models: Author, Book, and Chapter. Each author can have multiple books, and each book can have multiple chapters. We'll set up these relationships to demonstrate complex relationships in Eloquent.

Author Model

namespace App;

use Illuminate\Database\Eloquent\Model;

class Author extends Model
{
    protected $table = 'authors';

    public function books()
    {
        return $this->hasMany(Book::class);
    }
}

Book Model

namespace App;

use Illuminate\Database\Eloquent\Model;

class Book extends Model
{
    protected $table = 'books';

    public function author()
    {
        return $this->belongsTo(Author::class);
    }

    public function chapters()
    {
        return $this->hasMany(Chapter::class);
    }
}

Chapter Model

namespace App;

use Illuminate\Database\Eloquent\Model;

class Chapter extends Model
{
    protected $table = 'chapters';

    public function book()
    {
        return $this->belongsTo(Book::class);
    }
}

With these models, we have set up the following relationships:

• An Author can have multiple Book models, so we establish a hasMany relationship between Author and Book.
• Each Book belongs to an Author, so we establish a belongsTo relationship between Book and Author.
• A Book can have multiple Chapter models, so we establish a hasMany relationship between Book and Chapter.
• Each Chapter belongs to a Book, so we establish a belongsTo relationship between Chapter and Book.

Select Queries

Let's consider the following example:

We need to fetch the list of users along with their associated posts, including columns like name, email, title, and created_at.

Let's checkout User Model and Post Model. The User model should have hasMany relationship to show that a user has multiple posts.

namespace App;

use Illuminate\Database\Eloquent\Model;

class User extends Model
{
  
    public function posts()
    {
        return $this->hasMany(Post::class);
    }
}

The Post Model should have a belongsTo relationship to associate each post with a user.

namespace App;

use Illuminate\Database\Eloquent\Model;

class Post extends Model
{
    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

Now, we need to select the user and post details after joining them on id column.

$usersWithPosts = User::select('users.id', 'users.email', 'posts.title', 'posts.created_at')
    ->join('posts', 'users.id', '=', 'posts.user_id')
    ->get();

Insert Queries

If we want to create a new user, then the create method automatically creates a new record in the users table with the specified data and returns the created user model instance, which is then assigned to the variable $user.

$user = User::create([
    'name' => 'Alice',
    'email' => 'alice@example.com',
]);

Disadvantages

1. Performance Overhead: ORMs can introduce a performance overhead compared to writing raw SQL queries, especially for complex queries and high-traffic applications. ORM-generated queries may not be as optimized as hand-written SQL.
2. Complex Queries: Complex database queries may be challenging to express using an ORM's query builder, leading to less efficient or less readable code compared to raw SQL.
3. Limited Database Control: ORMs abstract the database layer, which means you have less direct control over database-specific features and optimizations. In some cases, you may need to write raw SQL for database-specific functionality.
4. Overhead in Memory Usage: ORM models often load entire database rows into memory as objects. This can lead to higher memory consumption, especially for large datasets.
5. Complex Migrations: Managing database schema changes and migrations can be more complex with an ORM, as it often involves creating migration files to update the schema based on changes to models.

References

https://laravel.com/docs