How to Connect Appsmith to the RisingWave Streaming Database

Goal

Prerequisites

Overview

1. Step 1. Set Up RisingWave Database

   ‍First, ensure you have a running instance of the RisingWave streaming database. You may need to install it on your local machine or server. You can install it locally by pulling a RisingWave image and running it as a Docker container. See the getting started RisingWave in the Docker guide.

   docker run -it –pull=always -p 4566:4566 -p 5691:5691 ghcr.io/risingwavelabs/risingwave:latest playground

2. Step 2. Connect to RisingWave using psql

   Once RisingWave is up and running, we test the connection using PostgreSQL interactive terminal. Using psql commands, you can manage and query data. Open your terminal, run the following psql command:

   psql -h localhost -p 4566 -d dev -U root

3. Step 3. Create a Taxi trip table

   Now let's create our first table to store data about taxi trips. We can use psql command in the terminal to run create table SQL query to achieve this. Of course, you can also run the same query from the Appsmith interface, you will learn it later in this tutorial.

   CREATE TABLE taxi_trips (
       trip_id int, 
       taxi_id int, 
       completed_at timestamp, 
       distance double precision, 
       duration double precision);

4. Step 4. Install and Launch Appsmith

   Follow the instructions on the Appsmith documentation to install Appsmith using Docker. Once installed, you can access the Appsmith dashboard in your web browser. Open https://localhost and wait for the server to come up. In my case, I was using a Docker desktop for a Windows machine and initiated two containers (One for RisingWave, and another for Appsmith):

   

5. Step 5. Create a New App

   In Appsmith, create a new application. You will be presented with a blank canvas and a range of widgets, APIs, and DB queries to help you build your app. I am going to skip some trivial steps so you can also read the Build Your First App quickstart tutorial on the Appsmith website and learn how to connect a data source, fetch data and display data in a table view.

   

6. Step 6. Connect Appsmith to the RisingWave database

   Appsmith provides a built-in connector for PostgreSQL which means we can use the same capability for connecting the RisingWave streaming database to read and write data in our application. Follow the tutorial to connect PostgreSQL and you need only specify the host address, port, database name, username for RisingWave as shown below. Note that we use host.docker.internal for Host Address because we are running RisingWave and Appsmith in two different containers. In this way, two containers can communicate easily.

   

7. Step 7. Write a query to add some taxi trips

   As I mentioned earlier, you can run SQL queries directly from Appsmith UI. Now on the Explorer tab in Appsmith, navigate to the Queries/JS section and click the New Query + button to the right of the screen. Provide the query name Insert_taxi_trips and insert two taxi trips into our TAXI_TRIPS the table we created in Step 3.

   INSERT INTO taxi_trips VALUES 
   (1, 1001, '2022-07-01 22:00:00', 4, 6), 
   (2, 1002, '2022-07-01 22:01:00', 6, 9);
   

   Click the Run button to execute a query. It will add these trips to the RisingWave database. You'll see the results in the Response tab if the query succeeds.

   Note that we inserted records manually for the sake of the demo, but in reality, you should ingest streaming data from different sources such as from a real taxi driver app.

8. Step 8. Write a query to select taxi trips

   Similarly, we can add another query to fetch taxi trips from RisingWave. We add a new query with the name Select_taxi_trips:

   

9. Step 9. Design your app and display data

   Appsmith allows you to bind data from your queries to your widgets. Read more here. You can now use Appsmith's drag-and-drop interface to design your app's front end. You can use widgets like Table, Text, Button, Input, and more to interact with your data. We are going to show taxi trip data in the Table widget. To display data, click the Widgets tab, and drag and drop a Table widget on the canvas. Give a new table name and in the Table Data box, paste the below JS code to display the results from the Select_taxi_trips query on the table.      

   {{Select_taxi_trips.data}}

   

10. Step 10. Create a new page to update trips

    With our current integration, you can easily define what happens when users interact with your app. For example, you could create a form for updating existing taxi trip data. When the form is submitted with UI, it triggers a UPDATE taxi_trips query that adds a new task to the RisingWave streaming database. Or when the user double-clicks on a selected table row in the Taxi Trips table we built in the previous step, we display details on the form. Add a new page called Edit Trip Details in Appsmith and begin to add Form widget with necessary fields to the canvas. See below the typical form structure:

    

    
    Here is our Update query example which maps input fields from the current trip form to the query.

    

    With Appsmith, it is also possible to navigate between two pages of the app and share data across pages. We can pass the values of the selected row in the Taxi Trips table to the Edit Taxi Trip form.

11. Step 11. Create a materialized view

    Creating a materialized view for our taxi ride app can significantly enhance the application's performance, especially when dealing with complex queries on large datasets. Materialized views store the result of a query physically in RisingWave, and are refreshed immediately as data comes in, making it much faster to fetch data from them compared to running the actual query every time.

    For example, we create a materialized view to dynamically calculate the average speed of all rides. To do so, we add a query and create a Table view in Appsmith:

    

    
    As you can see,

    

Conclusion

Additional Resources