Building an Order Tracking Demo App using AdonisJS Materialize and Redpanda

Introduction

❗️ This demo includes examples for an unsupported version of Materialize (0.26.x) ❗️

This is a self-contained demo using Materialize to process orders and display the delivery status and coordinates in real-time.

Prerequisites

Before you get started, you need to make sure that you have Docker and Docker Compose installed.

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

• Install Docker
• Install Docker Compose

Diagram

Running the Demo

Clone the repository and run the following command:

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

Then access the mz-order-tracking-dashboard directory and run the following command:

cd materialize-tutorials
git checkout lts
cd mz-order-tracking-dashboard

Then start the demo:

docker-compose up

Give it a few seconds to start up.

🤩 Our Amazing Sponsors 👇

View Website

DigitalOcean offers a simple and reliable cloud hosting solution that enables developers to get their website or application up and running quickly.

View Website

Laravel News keeps you up to date with everything Laravel. Everything from framework news to new community packages, Laravel tutorials, and more.

View Website

A Laravel Starter Kit that includes Authentication, User Dashboard, Edit Profile, and a set of UI Components. Learn more about the DevDojo sponsorship program and see your logo here to get your brand in front of thousands of developers.

Create a Redpanda topic

Once all services are running, you can create a Redpanda topic to receive order delivery coordinates.

docker-compose exec redpanda rpk topic create coordinates

Access Materialize

First access the Materialize instance by running the following command:

docker-compose run mzcli

Note: if you have psql installed, you could use it instead of mzcli: psql -U materialize -h localhost -p 6875

Create the Materialize Postgres Source

All orders are stored in a orders table in the Postgres container.

By using the Direct Postgres source you can connect your Postgres source directly to Materialize.

To create the Postgres source, run the following command:

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

Next, create views for all the tables in the source:

CREATE VIEWS FROM SOURCE mz_source (users, orders, coordinates);

After that let's create a view, that will store only the latest order for our user:

CREATE VIEW last_order AS SELECT * FROM orders ORDER BY id DESC LIMIT 1;

Create the Redpanda/Kafka Source

The demo app has a mock function that will simulate the delivery of orders. It will send the coordinates to the Redpanda topic every second. That way we can use Materialize to display the coordinates in real-time by processing the Redpanda topic.

To create the Kafka source execute the following statement:

CREATE SOURCE coordinates_source
  FROM KAFKA BROKER 'redpanda:9092' TOPIC 'coordinates'
  FORMAT BYTES;

We can use TAIL to quickly check the structure of the data:

COPY (
    TAIL (
        SELECT
            (data->>'latitude')::FLOAT AS latitude,
            (data->>'longitude')::FLOAT AS longitude,
            (data->>'user_id')::INT AS user_id,
            (data->>'order_id')::INT AS order_id,
            (data->>'distance')::FLOAT AS distance,
            data->>'timestamp' AS timestamp
        FROM (
            SELECT CAST(data AS jsonb) AS data
                FROM (
                    SELECT * FROM (
                        SELECT convert_from(data, 'utf8') AS data FROM coordinates_source
                    )
                )
            )
        )
    )
TO STDOUT;

Next, we will create a NON-materialized View, which you can think of as kind of a reusable template to be used in other materialized view:

CREATE VIEW coordinates_view AS
    SELECT
        (data->>'latitude')::FLOAT AS latitude,
        (data->>'longitude')::FLOAT AS longitude,
        (data->>'user_id')::INT AS user_id,
        (data->>'order_id')::INT AS order_id,
        (data->>'distance')::FLOAT AS distance,
        data->>'timestamp' AS timestamp
    FROM (
        SELECT CAST(data AS jsonb) AS data
            FROM (
                SELECT * FROM (
                    SELECT convert_from(data, 'utf8') AS data FROM coordinates_source
                )
            )
        )
    ;

After that the materialized view to get the last coordinates of each order:

CREATE MATERIALIZED VIEW coordinates_mv AS
    SELECT DISTINCT ON (order_id)
        order_id,
        user_id,
        latitude,
        longitude,
        distance,
        timestamp
    FROM coordinates_view
    WHERE
        order_id IS NOT NULL
    GROUP BY
        order_id,
        user_id,
        latitude,
        longitude,
        distance,
        timestamp
    ORDER BY
        order_id,
        timestamp DESC
    ;

Lastly, let's join the last order that we get from Postgres with the coordinates from Kafka:

CREATE VIEW last_order_with_coordinates AS
    SELECT
        o.id AS order_id,
        o.user_id AS user_id,
        o.status AS status,
        o.created_at AS created_at,
        o.updated_at AS updated_at,
        c.latitude AS latitude,
        c.longitude AS longitude,
        c.distance AS distance,
        c.timestamp AS timestamp
    FROM last_order o
    LEFT JOIN coordinates_mv c ON o.id = c.order_id
    ;

We are going to use the above view to display the last coordinates on the real-time dashboard.

View the dashboard

Now that we have all the materialized views, we can access the dashboard via your browser:

http://localhost:3333

You will see the following dashboard:

To place an order, click on the Add New Order button. This will trigger the following workflow:

• The order will be created in the Postgres database
• The order tracking coordinates will be sent to the Redpanda topic every second
• Materialize will then join the order with the coordinates and the order details
• Over SSE using TAIL we will be able to see the coordinates in real-time on the dashboard

Stopping the Demo

To stop all of the services run the following command:

docker-compose down

Helpful resources:

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

Community

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