# How to connect Laravel to Materialize and build a live dashboard?

By Bobby Iliev, 2022-01-17. https://bobbyiliev.com/post/how-to-connect-laravel-to-materialize/

# Introduction

> ❗️ This demo includes examples for an unsupported version of [Materialize (0.26.x)](https://materialize.com/docs/) ❗️

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](/post/how-to-install-laravel-on-digitalocean-with-1-click/)

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

- [LaraSail](https://devdojo.com/episode/laravel-on-digital-ocean-with-larasail)

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

- [Installing Materialize](https://materialize.com/docs/install/)

# 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](https://materialize.com/docs/get-started).

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

<iframe width="100%" height="399" src="https://www.youtube.com/embed/WVORbibyFGA" frameborder="0" allow="accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>

# 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:

```php
        '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](https://materialize.com/cloud):

```php
        '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
<?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:

```php
// 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:

```php
    /**
     * 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`](https://materialize.com/docs/sql/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`:

```php

    /**
     * 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
";
                $curDate = date(DATE_ISO8601);
                echo 'data: {"time": "' . $curDate . '"}';
                echo "

";

                $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 . '"}' . "

";
                }

                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](/post/how-to-create-a-simple-event-streaming-in-laravel/).

# Demo project

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

[Laravel and Materialize demo project](https://github.com/bobbyiliev/materialize-tutorials/tree/lts/laravel-eventstreams)

The structure of the project is as follows:

![Laravel and Materialize](https://user-images.githubusercontent.com/21223421/149404230-e41c0dae-f3b0-45c8-a671-5783da8ccf62.png)

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

![Laravel Event Stream with Materialize](https://user-images.githubusercontent.com/21223421/149408173-7a76c837-314b-419a-a589-d6867fc55c99.gif)

# 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](https://github.com/MaterializeInc/materialize/issues/2157), the integration with these tools will gradually improve!

For more information about Materialize, please visit the [Materialize Documentation](https://materialize.com/docs/get-started).

For more useful demos, please visit the [Materialize Demos](https://github.com/MaterializeInc/demos).

Hope that this was helpful!