Optimize database queries in the perimeter of your Laravel apps

I care a lot about application performance, and in particular, how you can make better use of your database layer to improve the performance of your applications. Today I want to share a rule that I've found to be helpful while building my Laravel apps:

As much as possible, optimize your database queries in the perimeter of your applications.

What do I mean by this? In the simplest terms, it means designing your app in such a way that you can eager load and filter model relationships in your controllers.

Over and over I have seen developers run into performance issues in their Laravel applications because they run database queries in their models, resources, views and service classes. However, when you do this, you strip away the ability for your controllers to optimize the queries for those requests.

Let's look at an example.

A simple example app

Let's say we have a simple home library app, which keeps track of the books someone owns. Our models might look like this:

class User extends Model
{
    public function books()
    {
        return $this->belongsToMany(Book::class)
            ->withPivot('favourite');
    }
}

class Book extends Model
{
    public function authors()
    {
        return $this->belongsToMany(Author::class);
    }
}

class Author extends Model
{
    public function books()
    {
        return $this->belongsToMany(Book::class);
    }
}

Now, imagine if we wanted to create a page in this app that showed the "favourite books" for a specific user. We might create the following controller:

class FavouriteBooksController extends Controller
{
    public function index(User $user)
    {
        return View::make('favourites', ['user' => $user]);
    }
}

Since we only want to show the favourite books, we'll need some way to filter a user's books to only include their favourites. A common approach would be to add a new favouriteBooks() method to our User model:

class User extends Model
{
    public function books()
    {
        return $this->belongsToMany(Book::class)
            ->withPivot('favourite');
    }

    public function favouriteBooks()
    {
        return $this->books()
            ->where('favourite', true)
            ->with('authors')
            ->get();
    }
}

We're being smart and eager loading the authors, because we don't want to introduce an N+1 issue, since we know we want our template to show the author names for each book.

Finally, let's create a template to display all of the user's favourite books.

<h1>{{ $user->name }}’s Favourite Books</h1>

@foreach($user->favouriteBooks() as $book)
    <h2>{{ $book->title }}</h2>
    <div>Authors: {{ $book->authors->implode('name', ', ') }}</div>
@endforeach

Beautiful. The controller, template and models are all clear and concise. And, we even have a new favouriteBooks() method that we can reuse elsewhere in our app.

Not so fast.

Subtle performance issues

We've just introduced two subtle performance problems into our application.

First, we're now forcing our application to eager load all the authors every single time we call the favouriteBooks() method. This could easily mean unnecessary database queries in other parts of our app that don't require the authors. And this problem will only get worse if we ever need more book information. For example, maybe we add publisher information as another relationship.

Second, we've made it impossible for us to eager load all the favourite books for a collection of users, since the favouriteBooks() method will run a full database query for every single user. This isn't an issue on our page which only shows one user, but on a page that displays many users, this will result in an N+1 issue.

What we've really done here is create a helper method that's optimized for one very specific use-case in our application. However, it has the illusion of being reusable. This is almost guaranteed to lead to future performance issues.

Moving optimizations to the perimeter

The main issue with running database queries directly in our models is that it's impossible for the model to know the context it's being used in, and therefore it cannot make the necessary optimizations. And this doesn't only apply to models. It also goes for resources, views, service classes, and really any deeper nested layer of your application.

Controllers, on the other hand, are fully aware of the context and know exactly what data is required for each specific endpoint. This puts them in the best position to make any necessary database query optimizations.

Let's refactor our example app above to push these optimizations to the controller.

First, let's update our favouriteBooks() method to operate on a books in-memory collection, not the query builder, to avoid making any database queries within the User model.

class User extends Model
{
    public function books()
    {
        return $this->belongsToMany(Book::class);
    }

    public function favouriteBooks()
    {
        // return $this->books()->where('favourite', true)->with('authors')->get();
        return $this->books->where('pivot.favourite', true);
    }
}

You're probably already thinking, "But won't this still make a database query, since Laravel will lazy-load the books relationship?" And you'd be correct, if we just left it at this. However, there's a very important difference here. By referencing the books in-memory collection instead of the query builder, we are able to optimize how and when this database query is run.

Let's make those optimizations next.

In our controller we'll use the load() method on the $user model to eager load only the user's favourite books, as well as the authors for those books.

class FavouriteBooksController extends Controller
{
    public function index(User $user)
    {
        $user->load(['books' => function ($query) {
            $query->where('favourite', true)->with('authors');
        }]);

        return View::make('favourites', ['user' => $user]);
    }
}

And that's it! We've now moved the database query optimizations for this page to the controller, allowing us to optimize it perfectly for this endpoint. Further, our model is no longer running any database queries or worrying about eager loading the authors relationship.

Awesome.

Optimizing another page

Let's see how this would play out in another endpoint. Let's pretend like the users of this application have friends, and they can see all their friends listed on a page with their favourite books. We can use the exact same technique to eager load all the friend's favourite books.

class FriendsController extends Controller
{
    public function index(User $user)
    {
        $user->load(['friends.books' => function ($query) {
            $query->where('favourite', true);
        }]);

        return View::make('friends', ['user' => $user]);
    }
}

We can reuse the favouriteBooks() method when displaying the friends in this page's template. And, since we've refactored this method to no longer run a database query directly, we are able to eager load all the friends books in our controller and avoid an N+1 issue for each friend.

Further, since we're not showing the authors on this page, we can simply omit the authors eager load and avoid having to unnecessarily load that data.

<h1>{{ $user->name }}’s Friends</h1>

@foreach($user->friends as $friend)
    <h2>{{ $friend->name }}</h2>
    <ul class="list-disc pl-10">
        @foreach($friend->favouriteBooks() as $book)
            <li>{{ $book->title }}</li>
        @endforeach
    </ul>
@endforeach

Let's compare the results between our original solution (which was only optimized for our first page), with our refactored version. We'll assume that we have 10 friends, who each have 5 favourite books, and each book has 1 author.

As you can see, there are some pretty big performance wins here. This refactor resulted in 19 fewer database queries and 50 less hydrated models. And to be honest, this is a pretty tame example. I've seen much worse in production applications where issues like this have resulted in endpoints running well over 1,000 database queries!

A general rule

As a general rule, I find it helpful to write my models as if all relationship data has already been eager loaded. This means not running database queries directly in my models, but instead working with the in-memory relationship data.

By doing this, you'll keep your models simpler, since they no longer need to be concerned with performance issues. Even more important, you'll be building your app in such a way that it empowers your controllers to make any necessary database query optimizations.

But my controllers are now ugly

Now, you might be thinking, "Okay, that's great Jonathan, but my controllers now look messier". You're right, they do. That's a trade off I am okay with. And, I'll often clean things up a little by using scopes for common queries. For example, I'd probably add a favourite() scope to the Book model.

class FriendsController extends Controller
{
    public function index(User $user)
    {
        $user->load(['friends.books' => function ($query) {
            $query->favourite();
        }]);

        return View::make('friends', ['user' => $user]);
    }
}

And PHP 7.4 makes this even prettier. 😍

class FriendsController extends Controller
{
    public function index(User $user)
    {
        $user->load(['friends.books' => fn ($query) => $query->favourite()]);

        return View::make('friends', ['user' => $user]);
    }
}

Aren't you duplicating work?

You might also be wondering if this approach means always duplicating the work you're doing in your model helper methods and in your controller optimizations. For example, aren't both the favouriteBooks() method and the controller filtering the books to only include the favourites?

Yes and no.

In some situations you are duplicating this logic. However, I see them as two different things. In our example, the favouriteBooks() method is ultimately responsible for ensuring that only the favourite books are returned. This method ensures that the application works the correct way. On the other hand, the controller optimizations are not responsible for ensuring that the app functions correctly, but rather that it performs adequately. Think of it as a progressive enhancement. The app works either way, but it's better with them.

In (many) other situations, it's not this simple, and the model method will do much more work than the controller optimizations. This can include transformations, calculations, and other common tasks that you'll find in your applications.

For example, in my SaaS app, I have a Family model that has a name() method on it. This method generates a family name (eg. Jonathan & Amy Reinink) based on the primary family member's first and last name as well as their spouse's first and last name. It uses the primary family member's marriage records to determine who the current spouse is. From there it checks if the last names match (ignoring characters that don't matter such as spaces), and if they match, only includes the last name once. For this method, it's important that the controller properly eager loads the correct user and marriage relationship data, but that's it. The rest of the hard work is left to the name() method.

Can't you just use a scoped relationship?

If you've been wondering the entire time if you could simply use a scoped relationship in this situation, the answer is yes. Here's how:

class User extends Model
{
    public function books()
    {
        return $this->belongsToMany(Book::class);
    }

    /**
     * @return \Illuminate\Database\Eloquent\Relations\BelongsToMany
     */
    public function favouriteBooks()
    {
        // Notice how we don't call get() at the end
        return $this->books()->where('favourite', true);
    }
}

From here you can interact with the favouriteBooks relationship just like the books relationship. You can eager load it, along with any child relationships, right from the controller. 👍

I chose to use this example regardless because it was simple, and easy to grok. However, there are many situations where scoped relationships won't work (such as the family name example above), and that's where the techniques in this article are extremely useful.

That's all I've got. I hope you've found this article helpful! Go forth and make fast apps! 🤟