laravel-eventstreams

Real-time stock trades dashboard with Laravel and Materialize

This is a self contained example of a real-time stock trades dashboard with Laravel and Materialize.

The trades are generated by a mock service and stored in a PostgreSQL database. The data is then ingested into a live Materialize view which is kept up to date in real-time.

We then use Laravel to connect to Materialize and to create a dashboard that shows the current trades as they are generated.

The diagram below shows the structure of the project:

Architecture

This demo consists of the following components:

• A mock service to continually generate trades and users.
• Laravel API to store the trades and users in a PostgreSQL database.
• A Materialize instance that is connected to the PostgreSQL database directly.
• A simple frontend Laravel dashboard that shows the current trades as they are generated.

NOTE: For the sake of simplicity, the stocks and their prices have been extracted from the NASDAQ stock exchange and stored in a database statically for this tutorial, meaning that the stock prices will not change and is not acurate.

Prerequisites

To run this demo, you need to have Docker and Docker Compose installed.

You can follow the steps here on how to install Docker:

• Install Docker
• Install Docker Compose
• Install psql

Running the demo

To get started, clone the repository:

git clone git clone https://github.com/bobbyiliev/materialize-tutorials.git

Then you can access the directory:

cd materialize-tutorials/laravel-eventstreams

With that you can then build the images:

docker-compose build

And finally, you can run all the containers:

docker-compose up -d

It might take a couple of minutes to start the containers. After that, we are ready to complete the Laravel setup.

Laravel setup

In order to get the Laravel setup done, you need to run the following commands:

• Install the Laravel dependencies:

docker-compose run --rm composer install

• Run the database migrations:

docker-compose run --rm artisan migrate

• Run the database seeds:

docker-compose run --rm artisan db:seed

PostgreSQL setup

Before we get started with the Materialize setup, we need to prepare the PostgreSQL database as per the official documentation here:

• Materialize: Direct Postgres source

First, access the PostgreSQL container:

psql -U postgres -h localhost -p 5432 postgres

Use postgres as the password.

Set the replica identity to FULL for the tables that we will replicate:

ALTER TABLE materialize.users REPLICA IDENTITY FULL;
ALTER TABLE materialize.stocks REPLICA IDENTITY FULL;
ALTER TABLE materialize.trades REPLICA IDENTITY FULL;

Then create a publication for all the tables:

CREATE PUBLICATION mz_source FOR ALL TABLES;

With that exit the psql prompt and then we are ready to start the Materialize setup.

Materialize setup

As we now have the PostgreSQL database setup, we can create a PostgreSQL source in Materialize:

• First, launch an mzcli session:

docker-compose run mzcli

• Then, create the PostgreSQL source:

CREATE MATERIALIZED SOURCE "mz_source" FROM POSTGRES
CONNECTION 'user=postgres port=5432 host=postgres dbname=postgres password=postgres'
PUBLICATION 'mz_source';

• After that create the replication views:

CREATE VIEWS FROM SOURCE mz_source (users,stocks,trades);

Creating the views

Let's first create a standerd non-materialized view that we will use as an alias for our SELECT query:

CREATE VIEW "materialize_stream" AS
    SELECT
        users.id AS user_id,
        users.name AS user_name,
        stocks.id AS stock_id,
        stocks.symbol AS stock_symbol,
        stocks.price AS stock_price,
        trades.id AS trade_id,
        trades.volume AS trade_volume,
        trades.type AS trade_type,
        trades.created_at AS created_at,
        trades.updated_at AS updated_at
    FROM users JOIN trades ON users.id = trades.user_id JOIN stocks ON trades.stock_id = stocks.id;

Then finally, let's create a materialized view for all the trades that happened in the last minute:

CREATE MATERIALIZED VIEW "latest_trades" AS
    SELECT * FROM materialize_stream
        WHERE (mz_logical_timestamp() >= (extract('epoch' from created_at)*1000)::bigint
        AND mz_logical_timestamp() < (extract('epoch' from created_at)*1000)::bigint + 60000);

For more information, about the mz_logical_timestamp() function, see Materialize documentation.

Viewing the trades

To access the dashboard, you can visit http://localhost/ in your browser.

The dashboard will show the trades that happened in the last minute:

Helpful resources:

• CREATE SOURCE: PostgreSQL
• CREATE SOURCE
• CREATE VIEWS
• SELECT

Community

If you have any questions or comments, please join the Materialize Slack Community!