Video is https://studio.youtube.com/video/oSVbqJ9M8QU/edit
Thumbnail is at: https://drive.google.com/drive/folders/1O2j3RugO6XfaaSPANGCklxrRc8cqbgDX

One of the more challenging pieces of maintaining applications that uses an SQL database is the fact that queries only every get more complex as the application grows. It never ever gets simpler so you start having to copy and past the same complex join into multiple places and good luck if you have multiple microservices that access the same database. Duplicated queries are a breeding ground for bugs especially if you’re duplicating the setup inconsistently.

The good news is SQL databases have given us a better option which is to use views. Combined with materialized views for performance-critical scenarios, you can massively simplify your queries which will keep your business logic consistent and your codebase cleaner.

In this article, we’ll discuss why duplicating queries leads to bugs, how database views solve the problem, how to integrate views with a framework like Laravel, and when to use materialized views for better performance.

The Problem with Duplicated Queries

Let me show you why duplicating queries is bug prone. For a project I’m working on we’re created an internal leaderboard based on how much experiance someone has earned in a set period. To generate the leaderboard data we’ve using build the following code which has raw SQL query in it.

<?php

class LeaderBoardRepository
{
    public function getGlobalLeaderBoard(): array
    {
        return DB::select("
            SELECT users.name, users.id, SUM(user_experience_transactions.amount)
            FROM user_experience_transactions
            INNER JOIN users ON user_experience_transactions.user_id = users.id
            WHERE users.allow_login = true
            AND user_experience_transactions.created_at >= NOW() - INTERVAL '7 days'
            GROUP BY users.name, users.id
        ");
    }
}

Months later, a teammate needs the same data for a leadboard for teams instead of globally so they copy the query but make a small mistake:

<?php

class TeamRepository
{
    public function getTeamLeaderBoard(Team $team): array
    {
        return DB::select("
            SELECT users.name, users.id, SUM(user_experience_transactions.amount)
            FROM user_experience_transactions
            INNER JOIN users ON user_experience_transactions.user_id = users.id
            WHERE users.team_id = {$team->id}
            AND user_experience_transactions.created_at >= NOW() - INTERVAL '7 days'
            GROUP BY users.name, users.id
        ");
    }
}

Did you spot it?

They accidentially remove the users.allow_login = true conditional from the query which is going to cause different data to be displayed in both places. This is a sneaky bug that can easily slip through code reviews and cause real discrepancies although a minor one.

Database Views to the Rescue

At a very basic level database views are basically a saved query that acts like a virtual table. You define the view once, and then you can SELECT from it just like any other table. Here’s how to create a view that solves our duplication problem:

CREATE VIEW leaderboard AS
SELECT users.name, users.id, users.team_id, SUM(user_experience_transactions.amount)
FROM user_experience_transactions
INNER JOIN users ON user_experience_transactions.user_id = users.id
WHERE users.allow_login = true
AND user_experience_transactions.created_at >= NOW() - INTERVAL '7 days'
GROUP BY users.name, users.id, users.team_id;

Now both repositories can query the view.

<?php

class LeaderBoardRepository
{
    public function getGlobalLeaderBoard(): array
    {
        return DB::table("leaderboard")
            ->get()
            ->toArray();
    }
}

class TeamRepository
{
    public function getTeamLeaderBoard(Team $team): array
    {
        return DB::table("order_totals")
            ->where("team_id", $team->id)
            ->get()
            ->toArray();
    }
}

If you need to add logic that shows 0s for users who haven’t done anything in the last week, you update the view definition once, and every piece of code that uses it automatically gets updated. It’s one source of truth with minimal duplication.

Versioning Your Views

If you’re using modern development practices you should be versioning your database changes using some kind of tool. Your views are no exception because they change over time.

To version control your views, create them in migrations. The key is using raw SQL if (like Laravel’s schema builder) your solution doesn’t have native view support:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

return new class extends Migration
{
    public function up(): void
    {
        DB::statement("
            CREATE VIEW leaderboard AS...
        ");
    }

    public function down(): void
    {
        DB::statement("DROP VIEW IF EXISTS leaderboard");
    }
};

A common gotcha is migration ordering. Your view migration must run after the migrations that create the underlying tables. Generally this isn’t an issue with migration management tools but you may run into errors if you’re removing/altering the columns that the views use.

Materialized Views for Performance

A downside to regular views is that you’re execute the underlying query every time you SELECT from them. For complex queries with expensive JOINs or aggregations, this can be REALLY slow. Materialized views solve this by storing the query results physically on disk which can massively improve the performance.

Here’s how to create a materialized view in PostgreSQL:

CREATE MATERIALIZED VIEW leaderboard AS
SELECT users.name, users.id, users.team_id, SUM(user_experience_transactions.amount)
FROM user_experience_transactions
INNER JOIN users ON user_experience_transactions.user_id = users.id
WHERE users.allow_login = true
  AND user_experience_transactions.created_at >= NOW() - INTERVAL '7 days'
GROUP BY users.name, users.id, users.team_id;

The tradeoff is that materialized views can become stale and you’ll need to refresh them:

-- Refresh the materialized view (blocks reads during refresh)
REFRESH MATERIALIZED VIEW leaderboard;

-- Refresh concurrently (requires a unique index, but allows reads during refresh)
REFRESH MATERIALIZED VIEW CONCURRENTLY leaderboard;

I can honestly say that they’re totally worth it for views you’re querying repeatedly and are slow. The downside is that your users might be expecting the data to be real time and materialized views will get in the way of that.

Workaround for Materialized Views

Annoyingly, some database management systems like MySQL and MariaDB doesn’t support materialized views natively. You can achieve similar results with a regular table and the creating a cron job to drop and rebuild the table.

DROP TABLE IF EXISTS leaderboard_cached;
CREATE TABLE leaderboard_cached AS
SELECT users.name, users.id, users.team_id, SUM(user_experience_transactions.amount)
FROM user_experience_transactions
INNER JOIN users ON user_experience_transactions.user_id = users.id
WHERE users.allow_login = true
  AND user_experience_transactions.created_at >= NOW() - INTERVAL '7 days'
GROUP BY users.name, users.id, users.team_id;

What You Need To Know

1. Use database views to eliminate query duplication and maintain one source of truth.
2. Version control views using migrations with raw SQL statements.
3. Use materialized views when possible for expensive queries that don’t need real-time data.
4. You can simulate materialized views in MySQL using “cache” tables with scheduled refresh procedures.
5. Always consider staleness when using materialized views and communicate data freshness to users.