Introduction

[!WARNING]
This demo includes examples for an unsupported version of Materialize (0.26.x).

Materialize is a streaming database that takes data coming from different sources like Kafka, PostgreSQL, S3 buckets, and more and allows users to write views that aggregate/materialize that data and let you query those views using pure SQL with very low latency.

As Materialize is PostgreSQL-compatible, it can be used with Laravel and Eloquent. And in this tutorial, you will learn exactly that: How to connect Laravel to Materialize.

Prerequisites

Before you start, you need to have Laravel installed on your machine.

If you do not have that yet, you can follow the steps from this tutorial on how to do that:

• How to Install Laravel with 1-Click

Or you could use this awesome script to do the installation:

• LaraSail

You would also need to have Materialize installed on your machine. You can follow this tutorial to do that:

• Installing Materialize

What is Materialize?

Unlike traditional materialized views, Materialize is designed to maintain the data in a continuous state and keeps the views incrementally updated. This means that if you have a view that is constantly updating, you can query the data in real-time. A normal materialized view would do a full table scan every time it needs to be updated which can be very expensive.

For a more detailed explanation of Materialize, please see the Materialize Documentation.

You can also take a look at this quick video that explains what Materialize is:

Adding additional database connection

Once you have Laravel and Materialize installed, you need to add a new database connection to your config/database.php file.

Using your favorite text editor, open config/database.php and add the following lines inside the connections array:

• For a locally running Materialize instance:

        'materialize' => [
            'driver' => 'pgsql',
            'url' => env('DATABASE_URL'),
            'host' => env('MZ_HOST', '127.0.0.1'),
            'port' => env('MZ_PORT', '6875'),
            'database' => env('MZ_DATABASE', 'materialize'),
            'username' => env('MZ_USERNAME', 'materialize'),
            'password' => env('MZ_PASSWORD', 'materialize'),
            'prefix' => '',
            'prefix_indexes' => true,
            'sslmode' => 'prefer',
        ],

• For Materialize Cloud:

        'materialize' => [
            'driver' => 'pgsql',
            'url' => env('DATABASE_URL'),
            'host' => env('MZ_HOST', 'your-project-id.materialize.cloud'),
            'port' => env('MZ_PORT', '6875'),
            'database' => env('MZ_DATABASE', 'materialize'),
            'username' => env('MZ_USERNAME', 'materialize'),
            'password' => env('MZ_PASSWORD', 'materialize'),
            'prefix' => '',
            'prefix_indexes' => true,
            'sslmode' => 'verify-ca',
            'sslcert' => storage_path('materialize.crt'),
            'sslkey' => storage_path('materialize.key'),
            'sslrootcert' => storage_path('ca.crt'),
        ],

Note: make sure to first download your certificate files and place them in the /storage folder.

As you can see, the materialize connection is the same as the pgsql connection, it also uses the pgsql driver but it has a different port.

Creating a new model

To create a new model, you can use the artisan command:

php artisan make:model MaterializeStream

We will use this new model to connect Laravel to Materialize and query a specific materialized view.

To do that, we have to update the MaterializeStream model as follows:

<?php

namespace App\Models;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;

class MaterializeStream extends Model
{
    use HasFactory;

    protected $guarded = [];
    protected $connection = 'materialize';
    protected $table = 'materialize_stream';
    public $timestamps = false;

}

The $connection property is set to the name of the database connection that you want to use.

That way all the queries will be executed on the specified database, in our case, materialize just as a standard Eloquent model, for example:

// Get all entries from the materialized view
MaterializeStream::get();
// Get the latest entry from the materialized view
MaterializeStream::latest()->first();

Creating a controller

Once you have the model created, you can create a new controller for it. For example, if you want to create a controller that retrieves some data from the MaterializeStream model, you can use the following command:

php artisan make:controller MaterializeStreamController

This will create a new controller file at app/Http/Controllers/MaterializeStreamController.php.

Let's open that file and add the following method:

    /**
     * The stream source.
     *
     * @return \Illuminate\Http\Response
     */
    public function mzStream()
    {
        $trades = \App\Models\MaterializeStream::get();
        $latestTrades = \App\Models\MaterializeStream::latest()->first();

        return response()->json([
            'trades' => $trades,
            'latestTrades' => $latestTrades,
        ]);
    }

Alternatively, rather than using the Eloquent model, you can use the DB facade to query materialize directly. Let's have an example of how to use the DB facade together with TAIL.

Using TAIL

You can use the TAIL command to get the latest entry from the materialized view in a streaming fashion.

Here is an example of how to use TAIL:

    /**
     * The stream source.
     *
     * @return \Illuminate\Http\Response
     */
    public function tail()
    {

        return response()->stream(function () {
            DB::connection('materialize')->statement('BEGIN');
            DB::connection('materialize')->statement('DECLARE trades_c CURSOR FOR TAIL latest_trades');
            while (true) {
                echo "event: ping\n";
                $curDate = date(DATE_ISO8601);
                echo 'data: {"time": "' . $curDate . '"}';
                echo "\n\n";

                $trades = DB::connection('materialize')->select('FETCH ALL trades_c');

                foreach ($trades as $trade) {
                    echo 'data: {"id": "' . $trade->trade_id . '", "stock": "' . $trade->stock_symbol . '", "user": "' . $trade->user_name . '", "created_at": "' . $trade->created_at . '"}' . "\n\n";
                }

                ob_flush();
                flush();
                if (connection_aborted()) {break;}
            }
        }, 200, [
            'Cache-Control' => 'no-cache',
            'Content-Type' => 'text/event-stream',
        ]);
    }

The above example uses EventStream to send the data to the client. For more information about EventStream, please visit EventStream.

Demo project

To see all this in action, you can clone the repository containing the demo project:

Laravel and Materialize demo project

The structure of the project is as follows:

The simple dashboard that you will build would be powered by Materialize and will look like this:

Conclusion

This is more or less it. You will be able to build a live dashboard with Materialize.

One thing to keep in mind is that Materialize doesn’t yet support the full system catalog of PostgreSQL (we're working on it!), which means that ORMs like Eloquent might fail during some attempts to interact with Materialize. As we work to broaden pg_catalog coverage, the integration with these tools will gradually improve!

For more information about Materialize, please visit the Materialize Documentation.

For more useful demos, please visit the Materialize Demos.

Hope that this was helpful!