Building an Order Tracking Demo App using AdonisJS Materialize and Redpanda

Created April 27, 2022

Introduction

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:

Diagram

Order Tracking Demo App - Materialize

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 mz-order-tracking-dashboard

Then start the demo:

docker-compose up

Give it a few seconds to start up.

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:

Order Tracking Demo App - Materialize dashboard

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

Order tracking demo dashboard - Materialize

Stopping the Demo

To stop all of the services run the following command:

docker-compose down

Helpful resources:

Community

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