Query Real-time Data With GraphQL And Streaming Database
Tags: webdev, graphql, database, programming
Published: 2023-06-10 09:11:37

In modern application development, efficiently querying and retrieving real-time data is crucial to building robust and performant systems. Using [materialized views](https://en.wikipedia.org/wiki/Materialized_view) we can improve query performance. When it is combined with GraphQL and a steaming database, developers can define their queries to leverage these materialized views for the data that constantly changes, allowing them to build highly responsive and interactive applications. 

For example, social media platforms like Twitter produce a massive volume of data every second. This data is valuable for analyzing trends and user behavior. In this article, we will explore how integrating GraphQL, materialized views, and streaming databases such [RisingWave](https://www.risingwave.com/) can enable us to efficiently query tweets and [discover the hottest hashtags](https://www.risingwave.dev/docs/current/fast-twitter-events-processing/) in real-time.

Before diving into implementation, it is important to understand these 3 concepts (GraphQL, materialized view, and streaming database). I believe you are smart enough to use [Google](https://www.google.com/) or [ChatGPT](https://chat.openai.com/) to find out this information. However, I am going to explain shortly why this integration can be beneficial and the role of each in the next section.

## Materialized views as precomputed caches

With a **materialized view**, we can precompute and store the results of frequently executed SQL queries. It is a denormalized representation of the data, which means that complex joins and aggregations are already performed and stored in a database like [PostgreSQL](https://www.postgresql.org/). This simplifies the query logic required to retrieve data and eliminates the need for us to manually handle join operations. In Twitter's case, materialized views can be used to compute summaries of user activity, such as the number of followers, a hashtag used, likes, or comments.

## **GraphQL to retrieve exactly what data we need**

**GraphQL** allows us to specify exactly what data we need and receive it **in a single request** without using an additional programming-language-specific data-processing framework or defining a bunch of entity objects and endpoints in the case of REST(Representational State Transfer). Unlike REST, GraphQL significantly reduces the number of round trips, resulting in faster data fetching. With GraphQL, we can **directly access the materialized views** in the database through the defined schema, abstracting away the complexities of the underlying database structure. A social media analytics platform can leverage GraphQL to offer a flexible API for querying and analyzing user-generated content.

There are several popular GraphQL builders and frameworks available that can assist in building GraphQL APIs. You can connect to different data sources and integrate with popular databases like PostgreSQL, and MySQL. Here are some of the widely used ones:

- [Apollo Server](https://www.apollographql.com/)
- [GraphQL Yoga](https://the-guild.dev/graphql/yoga-server)
- [Hasura](https://hasura.io/)
- [Nexus](https://nexusjs.org/)
- [Cube](https://cube.dev/docs/backend/graphql)
- [Stepzen](https://stepzen.com/)

> [StepZen](https://stepzen.com/) is a platform to build and deploy GraphQL APIs that integrates and aggregate data from various sources. In the demo section, I will show how to build a GraphQL API in declarative code using StepZen.


## Streaming database to process real-time data

To leverage the full potential of **real-time data querying** with GraphQL, a streaming database can be utilized. Materialized views may not always contain the most up-to-date data since they depend on **when and how often the view is refreshed**. Traditional databases such as PostgreSQL support materialized views, but to see the query over time, you need to **rerun the same query – and again**. To get updated results, you need to **execute queries repeatedly**. While a streaming database like [RisingWave](https://www.risingwave.com/) does almost all of its work at write time. This [post](https://www.iambobur.com/post/how-streaming-database-differs-from-a-traditional-database) explains **how a streaming database differs from a traditional database**. When data flows into the streaming database, it’s processed and immediately used to update the existing materialized views. It can ingest data from various [data sources](https://www.risingwave.dev/docs/current/data-ingestion/) like [Kafka](https://www.risingwave.dev/docs/current/create-source-kafka/) or [Pulsar](https://www.risingwave.dev/docs/current/create-source-pulsar/). By combining GraphQL with a streaming database, we can continuously ingest incoming Tweet posts and update the materialized views in real-time and query changes in data instantly.

> RisingWave is wire-compatible with [PostgreSQL](https://www.postgresql.org/), you can use the existing StepZen support for a [Postgres database](https://stepzen.com/docs/quick-start/with-database-postgresql) as a data source for your GraphQL API. You can query [Source](https://www.risingwave.dev/docs/current/sql-create-source/), [Table](https://www.risingwave.dev/docs/current/sql-create-table/), and [Materialized View](https://www.risingwave.dev/docs/current/sql-create-mv/) from RisingWave.


![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5qivdj2bmf732tbi6tky.png)

## Query Twitter events demo

Once we understand the importance of GraphQL, materialized views, and a streaming database, we can use this combination to create a new way to access tweet data. By pre-calculating summaries of the data and exposing them through a GraphQL endpoint, we can quickly get valuable insights from the tweets.

### Before you begin

In the tutorial, we will leverage the **existing use-case demo of RisingWave** on the website called [fast Twitter events processing](https://www.risingwave.dev/docs/current/fast-twitter-events-processing/). Make sure you completed the tutorial by cloning, launching the demo project there using [Docker](https://docs.docker.com/get-docker/), connecting RisingWave to Kafka data streams, and defining a materialized view as the tutorial guides you. 

Other prerequisites to install are:

- [ngrok account](https://ngrok.com/)
- [ngrok installed (Mac/Windows)](https://ngrok.com/download)

### When you are ready

Now I assume that you configured RisingWave and we have a materialized view named `hot_hashtags` processed by the RisingWave that tracks **how often each hashtag is used daily on Twitter**. In the next steps, we [install and set up](https://stepzen.com/docs/quick-start/install-and-setup) StepZen, design the GraphQL schema, map GraphQL queries to the actual data in the materialized view, and finally, expose the GraphQL Endpoint.

### Step 1: Install StepZen CLI

Note that you can also follow the instructions on StepZen website to install and run it. The StepZen command-line interface (CLI) provides commands to set up and manage StepZen. Run the following command to install the StepZen CLI:

```bash
npm install -g stepzen
```

### Step 2: Run StepZen in Docker

Next we run StepZen service on your local machine using the StepZen CLI we installed in the previous step:

```bash
stepzen service start
```

To use the StepZen CLI for local development, you must log in by pointing the CLI to the local configuration. Simply run the following command after StepZen service started:

```bash
stepzen login --config ~/.stepzen/stepzen-config.local.yaml
```

### Step 3: Clone the **graphql-stepzen-risingwave** demo project

Find my repository called **[graphql-stepzen-risingwave-demo](https://github.com/Boburmirzo/graphql-stepzen-risingwave-demo)** on GitHub. [Git clone](https://www.atlassian.com/git/tutorials/setting-up-a-repository/git-clone) this repository onto your machine. This project already has everything you need. There is written schema code in a **[postgresql.graphql](https://github.com/Boburmirzo/graphql-stepzen-risingwave-demo/blob/main/postgresql/postgresql.graphql)**  GraphQL Schema Definition Language (SDL) file with types and queries defined for the materialized view `hot_hashtags` in the RisingWave database. It has also a [stepzen.config.json](https://github.com/Boburmirzo/graphql-stepzen-risingwave-demo/blob/main/stepzen.config.json) file with our GraphQL endpoint. 

```bash
git clone https://github.com/Boburmirzo/graphql-stepzen-risingwave-demo.git
cd graphql-stepzen-risingwave-demo
```

### Step 4: Create a ngrok account and install ngrok (Optional)

> Note that it is just an additional step if you are running StepZen and RisingWave **in your local environment**. In the case of [cloud or running instances in your server](https://www.risingwave.dev/docs/current/get-started/?method=cloud#run-risingwave) that are accessible via the internet, you can set a direct address of RisingWave in the StepZen database configuration. If you are running both StepZen and RisingWave within Docker containers, you can specify the address of `host.docker.internal`**.**

Ngrok is a tool that creates a secure tunnel between a public internet address and a local server running on your machine. Create an account for ngrok and make sure that you can access it by setting `authtoken`. To make the Risingwave database available to other services outside your private network, you need to create a TCP tunnel. For this ngrok will be used on the port where RisingWave is running `4566`:

```bash
ngrok tcp 4566
```

After you run the command, Ngrok will return the forwarding address for the local RisingWave database, which will look something like this: `0.tcp.eu.ngrok.io:14304`

You need to add this to the file `./config.yaml` in the demo project where you need to replace `{{ngrok_tunnel}}` with your address `0.tcp.ngrok.io:15650`:

```bash
configurationset:
  - configuration:
      name: postgresql_config
      uri: postgresql://root:@0.tcp.eu.ngrok.io:15650/dev
```

### Step 5: Run GraphQL Endpoint

By running the command `stepzen start`, you can deploy the GraphQL schema we have in **[postgresql.graphql](https://github.com/Boburmirzo/graphql-stepzen-risingwave-demo/blob/main/postgresql/postgresql.graphql)** file to StepZen. This instantly creates a GraphQL API in the localhost, accessible through the configured endpoint (`api/twitter`) in [stepzen.config.json](https://github.com/Boburmirzo/graphql-stepzen-risingwave-demo/blob/main/stepzen.config.json) file. If you navigate to http://localhost:5001/api/twitter, you will the StepZen dashboard explorer on your browser.

![StepZen dashboard explorer](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9uqq3f9t3nq9ib82dp55.png)

### Step 6: Query the materialized view

StepZen explorer shows available queries with their attributes. When you run the following query `getHotHashtags`, it will pull data from the RisingWave materialized view and shows returned data in the explorer.

```json
query MyQuery {
  getHotHashtags {
    hashtag
    hashtag_occurrences
    window_start
  }
}
```

See the output:

![Image description](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/cj5x89thegy74mfedk58.png)

If you open the **[postgresql.graphql](https://github.com/Boburmirzo/graphql-stepzen-risingwave-demo/blob/main/postgresql/postgresql.graphql)** file, you will see how I used the GraphQL directive **@dbquery** to connect the database and write an SQL query to select the TOP 10 popular hashtags.

```json
type Query {
  getHotHashtags: [hot_hashtags]
    @dbquery(
      type: "postgresql"
      query: """
        SELECT
            *
        FROM
            hot_hashtags
        ORDER BY
            hashtag_occurrences DESC
        LIMIT
            10
      """
      configuration: "postgresql_config"
    )  
}
```

### Next steps

So far, we have built and deployed a GraphQL API `api/twitter` with the database backend. Next, you can observe how to consume real-time data from [other sources](https://www.risingwave.dev/docs/current/rw-integration-summary/) than Kafka with RisingWave,  combine [multiple streams of data](https://www.iambobur.com/post/multi-stream-joins-with-sql), create materialized views on joined streams, and create [a sequence of queries](https://stepzen.com/docs/features/sequencing-queries) with StepZen.

## Conclusion

With StepZen GraphQL API we simplified data access without introducing a backend service to do this work and we combined it with the real-time updates provided by RisingWave. In summary, querying real-time data with GraphQL and the streaming database opens up new possibilities for creating highly responsive and interactive applications. 

### Related resources

- [Query Real-Time Data in Kafka Using SQL](https://www.iambobur.com/post/query-real-time-data-in-kafka-using-sql).
- [How Streaming database differs from a Traditional database?](https://www.iambobur.com/post/how-streaming-database-differs-from-a-traditional-database)
- [Querying Microservices in Real-Time with Materialized Views.](https://medium.com/data-engineer-things/querying-microservices-in-real-time-with-materialized-views-b876e0aa7115)

### Recommended content

- [Is RisingWave the Right Streaming Database?](https://www.risingwave-labs.com/blog/is-risingwave-the-right-streaming-database/#durability)

### Community

🙋 [Join the Risingwave Community](https://join.slack.com/t/risingwave-community/shared_invite/zt-120rft0mr-d8uGk3d~NZiZAQWPnElOfw)

### About the author

- Visit my personal blog: [www.iambobur.com](https://www.iambobur.com/)
- Follow me on Twitter: [BoburUmurzokov](https://twitter.com/BoburUmurzokov)