E-commerce Order Management Dashboard with Hasura and GraphQL
25
January
2022
Tutorial

E-commerce Order Management Dashboard with Hasura and GraphQL

E-commerce Order Management Dashboard with Hasura and GraphQL
Vihar Kurama
0
 minutes ↗
#
dashboard
#
graphql
#
open-source
#
developer-tools
Tutorial

GraphQL is a query language for APIs and a server-side runtime for executing queries using the system you define for your data. It provides a complete and understandable description of the data in your API, gives clients the power to ask for exactly what they need and nothing more, makes it easier to evolve APIs over time, and enables powerful developer tools.

Today, developers spend a lot of time building internal applications, dashboards, and admin panels with GraphQL APIs as backend. But guess what? It’s now possible for you to write customizable applications with the ability to quickly prototype and deploy in a few minutes. With Appsmith, a low code app builder, you can build entirely customizable internal tools without spending too much time or effort.

In this blog, I am going to be building an e-commerce order management dashboard.

To build this app, we will connect Appsmith with a GraphQL API and build an interface that will fetch information from a database. We will use Hasura to create, manage, and customize GraphQL APIs.

Hasura is an open-source service that gives you production-grade GraphQL & REST APIs on your data instantly. It allows you to develop and deploy GraphQL APIs in minutes. At the core, Hasura helps you build APIs with a simple to use GraphQL composer and a GraphQL server that is stateless and blazing fast.

What exactly are we building?

By the end of the tutorial, we will have a fully customizable e-commerce order management dashboard! Typically, such dashboards enable the app users to:

  • Manage the entire product catalogue
  • View sales, track all details of total and recent orders
  • Send email to customers

Note: We will be using data from Hasura Super App, which can be found here. You can directly migrate the data here to your Hasura instance in just a few steps listed here: Moving from local development to staging and going production with Hasura.

Alternatively, you can connect to the instance we created for you to test and play with GraphQL APIs on Appsmith. These details of the instance are disclosed in the next section :)

All right, let's dive in!

Connect to Hasura on Appsmith with GraphQL

We will be connecting to the Hasura instance via the REST API datasource on Appsmith. For this, we will need the GraphQL API end-point and Admin Secret key from Hasura Dashboard. If you're using your instance, this is how your dashboard looks like, where you can copy the mentioned details.

hasura_dashboard.png
If you want to know more about the data schema and customize the relations between tables, you can always use Hasura cloud explorer.

Now, let's quickly take a look at all the tables that are available on Hasura Super App Dashboard in brief:

  • user: This table contains a list of all the customers who signed up for the Hasura super app.
  • address: The address tables contain all the user delivery addresses that will be further linked to the order.
  • admin: Details of all the super-admin who have access to all the dashboards
  • order: The order table contains all the order information a user places
  • order_product: This table contains a list of products that are included in the order
  • order_status: Contains details of order delivery status
  • product: Details of all the products on the e-commerce store
  • product_review: Reviews of products that the customers gave

Awesome, now that we had a glance at our tables, let's build the app! First up, we'll need an account on Appsmith Cloud. You can create it here for free if you don't have one. After we log in, we will be redirected to our dashboard to make a new Appsmith; this can be under any organization of your choice.

Now, to connect Hasura APIs, follow the below steps:

  • Click on the + icon next to datasources and choose Create new API under the Create New tab.
  • Rename the query to getProducts by double-clicking on the existing one.
  • Set the query type to POST, and paste the following URL:

https://subtle-platypus-36.hasura.app/v1/graphql

  • Under the headers pane, add the following key-values:

content-type: application/jso
x-hasura-admin-secret: <YOUR_SECRET_KEY>


  • Lastly, navigate to the Body property under the API pane, set the body type to JSON, and paste the following:
{{
JSON.stringify({
    variables: null,
    query: `
    query {
        product(limit: 300) {
            id
            brand
            category_display_name
            created_at
            description
            name
            image_urls
        }
}
`})
}}


Now, we can test this GraphQL query by clicking on the RUN button to the top-right of the application; with this, we should see all the products list from the backend in the response body.

Following is the screenshot:

GraphQL.png

Here, inside the body pane, we write the GraphQL query to fetch all the products; this is similar to how we do it on a regular GraphiQL playground.

Now let's bind this data onto the table widget.

  • For this, click on the + icon next to widgets and drag and drop a table widget onto the canvas.
  • Select the table on the canvas; this will open the property pane on the right sidebar; now, under table data, we bind the query using the moustache syntax:

{{ getproducts.data.data.product }}

  • And just like that, we should see all the data from the query binded onto the table.
  • Note that all the columns are not required for us. Hence, we can hide them by clicking on the eye-icon on the column name under the columns property.

Adding a New Product to Database

Doing a simple READ operation using Hasura's GraphQL API is easy now; let's look at how we can do a WRITE operation by building a form on Appsmith. Follow the below steps:

  • First, let's drag and drop a new button widget on the canvas; the idea is to open a modal and show a form to create a new product on the e-com store.
  • Next, add a text widget and a few form widgets on the modal by dragging and dropping them onto the modal from the widgets section.
  • We can also set the form's label by updating the label property from the form's property pane.

Following is a screenshot of how the form should look like:

form.png

Now, update the form names to the following to access them in the query:

You can update the widget names from the property pane by double-clicking on the existing one.
  • Name: getName
  • Description: getDescription
  • Category: getCategory
  • Brand: getBrand
  • Price: getPrice
  • ProductID: getID

Now, add a new button, update its label to Add Product, and set its onClick property to "Run a query" and choose Create a new API. This will create a new API datasource under the datasources section. Now, follow the below steps to create a new query that lets us post new data onto PostgresDB on Hasura.

  • Rename the query to addProduct
  • Update the URL to:

https://subtle-platypus-36.hasura.app/v1/graphql


  • Set the method type to POST, and copy the headers x-hasura-admin-secret, content-type from getProducts query to here.
  • Update the query body to the following:

{
    "query": {{JSON.stringify(`
    mutation ($object: insert_product_insert_input!) {
        insert_product_one(object: $object) {
    id
            brand
            category_display_name
            description
            price
            name
        }
    }
    `)}},
    "variables": {
        "object": {{
          JSON.stringify({
    id: getID.text,
            brand: getBrand.text,
            category_display_name: getCategory.text,
            description: getDescription.text,
            price: getPrice.text,
            name: getName.text,
        })}}
    }
}


Here, we have an object consisting of a query key that holds the GraphQL query and a variables key with all the variables that need to be posted to the database.

In the query, we have a mutation that lets us modify the server-side data. We'll send the object that references the data querying inside the mutation. The insert_product_one allows us to post one object to the database. Inside this query, we can add the attributes we need to post. Next, we've associated the variables to the object from the created UI input widgets. The .text on the input widget name helps us get the text written inside the input.

That's all, and our query is now ready! We can test this out by opening the form and creating a new product on our e-commerce store.

Add Stats on Dashboard

In this section, we will be showing some statistics on our dashboard; for this, let's drag and drop three stat box widgets onto the canvas. Following is a screenshot of how it looks like:

CleanShot 2022-01-17 at 10.28.11@2x.png

As we can see, these stat box widgets compute the total products, total categories, and unique brands. The data here is computed directly from the Hasura instance, using JavaScript. Following is how it works:

On the first stat box widget, select the text-widget and paste the following in the Text property:

{{getproducts.data.data.product.length}}

This small JS code will return the array's length from the getproducts query, displaying the total products.

Similarly, update the other two stat box widgets with the following JS code:

To compute Total Categories, we use an IIFE function and filter the getproducts query using its category_display_name attribute :

{{
function(){
    const filterProducts = getproducts.data.data.product.map(item => item.category_display_name)
  .filter((value, index, self) => self.indexOf(value) === index)
    return filterProducts.length
}()
}}


To compute Unique Brands, we use an IIFE function and filter the getproducts query using its brand attribute :


{{
function(){
    const filterProducts = getproducts.data.data.product.map(item => item.brand)
  .filter((value, index, self) => self.indexOf(value) === index)
    return filterProducts.length
}()
}}


There we go; we now have our dashboard up and running! Not just that, Appsmith allows us to create charts on top of Hasura data. Let's learn more about it in the next section.

Charting on Appsmith on Hasura Data

To get started, let's rename the page as Recent Orders; the idea is to create a chart that shows daily sales. Drag and drop a new chart widget onto the page from the widgets section.

This will show you a column chart with some pre-defined data; let's update this by creating our new query that fetches all the sales data:

  • Click on the + icon next to datasources and choose Create a new API.
  • Set the query type to POST, and update the URL to [https://subtle-platypus-36.hasura.app/v1/graphql](https://subtle-platypus-36.hasura.app/v1/graphql)
  • Under the headers pane, add the following key-values:
content-type: application/json
x-hasura-admin-secret: YOUR_SECRET_KEY


  • Lastly, navigate to the Body property under the API pane, set the body type to JSON, and paste the following:


This query will return a bit complex JSON; we will see something like:

{ "data": { "order": [ { "order_products": [ { "product": { "order_products": [ { "quantity": 2 } ] }, "created_at": "2021-02-22T18:16:17.776779+00:00" } ] }, … }

Here, order_products are nested in an array; hence, to process this and put it on a chart widget, we create a new JS object, allowing us to write full code on Appsmith and utilize its widgets. Confused? Follow the below steps:

  • Click on the + icon next to JS Objects, or use the JSObject1
  • Update myFunc1 to the following:

myFun1: () => {

  const data = _.compact(salesGraph.data.data.order.map((row) => 
  {
        const quantity = _.get(row, 'order_products[0].product.order_products[0].quantity');
        const created_at = moment(_.get(row, 'order_products[0].created_at')).format("LL");

        // if any of those in undefined
        if (!quantity || !created_at) return undefined;

        return {
      x: created_at,
             y: quantity,
    }
  }))
    return data.slice(1, 20)
    },

Here, we do a simple ordering using the _.compact method from lodash to aggregate the total number of sales done on a particular date. We return these in a special {x: value, y: value} format, which is accepted by the chart widget.

Now, let's head back to the chart widget and use this JS Object:

Update the series data property with the following:

{{JSObject1.myFun1()}}

Boom! There you go, we have all our sales aggregated based on the date on a beautiful chart widget. Following is how it looks like:

CleanShot 2022-01-17 at 11.17.25@2x.png

We also extended this app to show all the sales and orders on stat boxes and list widgets. Here's the link to the complete application!

If you liked this tutorial, and are planning to build this, let me know. I’d love to help you make it as complex as you’d like.

Write to me at vihar@appsmith.com.


Building a Store Catalogue Management System using Appsmith and GraphQL
8
April
2021
Tutorial

Building a Store Catalogue Management System using Appsmith and GraphQL

Building a Store Catalogue Management System using Appsmith and GraphQL
Vihar Kurama
0
 minutes ↗
#
javascript
#
graphql
#
web-development
#
ecommerce
Tutorial

Catalogue management is hard. But building a catalogue management app for your company is harder. Not only does the application have to connect to multiple tables/API, and pull data on inventory, price and even offers, but the application also needs a robust UI that lets the user manage this catalogue easily.

Not only does this take a lot of time to build and maintain, but direct access to the database or an API endpoint that could return private information could also prove to be a problem. GraphQL helps solve these problems inherently in the way it returns only what it is queried for.

Because of these benefits, GraphQL is a great interface to develop an internal application on and to make development and maintenance easier, a low-code tool like appsmith can be very useful and will help speed up the process of development.

In this tutorial, we'll learn to create an application to manage a games store's catalogue running a GraphQL backend. We'll be using appsmith to build the application and implement CRUD operations on the database. Here's the link to the final application.

Game Catalogue App Screenshot
Appsmith is an open-source framework that lets developers build dashboards, workflows, and CRUD apps with only the necessary code. You can connect to any API or databases like MongoDB, PostgreSQL, or MYSQL and get access to multiple widgets including charts, tables and forms for building a UI really fast.

Initial Setup

To build this application, first, we’ve collected a dataset from Kaggle that has a list of games, their prices, rating, average playtime and genre they’ve belonged to. We’ve created a mock database out of this, hosted it on Hasura and exported them as GraphQL APIs. Below is an image showing some of the attributes of the dataset.

Mock Data hosted on Hasura

Here, as we can see, the response consists of data of games in a JSON format. Next, we’ll use Appsmith to utilise this database and build a Store Manager by writing different GraphQL queries.

Getting started with Appsmith

To begin, we'll need to sign up for Appsmith (it's free!), if we don't already have an account. We can also deploy a Docker image on a server. For this tutorial, we're going to create an application on Appsmith cloud.

  1. Sign in to Appsmith Account
  2. Click Create New to create a new application and name it GraphQL Store Manager. This takes you to an empty workspace with a sidebar.
  3. On the sidebar, you should see items like Pages, API, and DB Queries in a folder-like structure. Under Pages, a new page, Page1, already exists. Rename it as Store Dashboard.

Below is a screenshot of the application:

Initial App Setup

Connecting to the Database and Creating the first UI View

To work with Appsmith, you need a database. This can be in the form of an API or a database. Appsmith supports MongoDB, MySQL, and PostgreSQL, among others. In this case, we’ll be connecting to Hasura, as it'll be our GraphQL backend server. To establish a new connection, follow these steps:

  1. Make a connection to a database by clicking the plus icon near APIs on the sidebar under a page.
  2. Now, rename your API to FetchGames
  3. We’ll write a POST request and use GraphQL query to list down all the games in the database.
  4. Next, use the following URL as the data source to access the mock database hosted on GraphQL: https://mock-as.hasura.app/v1/graphql
  5. We’ll have to set headers in order to access the database. Set content-type to application/json and x-hasura-admin-secret to qpN7KbsgIoKcuuC1sL3xiaIN2eegukvPPweg6MW1RkM8KUkaHAi85U3FOVj0Zkxm
  6. Next, we’ll have to write a GraphQL query in the request body to list all the database games. This will require an object with query as key and the GraphQL query value associated with it to fetch the games.
{
  "query": {{JSON.stringify(`
    query {
        steam_games(limit: 100, order_by: {appid: asc}) {
            name
            appid
            average_playtime
            genres
            price
        }
    }
  `)}}
}

Here, we have the query stringified with moustache syntax to ignore all the line breaks. Inside the query, we have the table name we want to query steam_games with a limit parameter set to 100 and an ordey_by parameter to order all the games by the appid ascending order using the asc keyword. Inside the query, we’ll have to use the fields we want to fetch. Now hit Run to run the query. You should see a list of games in a JSON field in the response tab.

FetchGames API Response

Awesome! You now have your store catalogue with all the games and their prices. Let’s now build a UI to display all these games on a beautiful table.

  1. Navigate to Page1 and click on the plus icon next to the widgets.
  2. Now drag and drop a table widget onto the canvas; we'll see a new table with some pre-populated data.
  3. Next, we’ll have to open the table’s property pane to update the table data by clicking on the cog icon.
  4. Inside the property pane, we’ll find a Table Data property, and here we’ll have to bind the FetchGames API. Use the moustache syntax and add the below code snippet:
{
{FetchGames.data.data.steam_games}
}

This will pull data from the FetchGames API we’ve set up in the previous section.

Beautiful! We now have our catalogue displayed on a table. Next, we’ll create a form where we can add new games to the database.

Implementing Create Operation on Database

In this section, let’s look at how we can add new values to the database by building a simple UI. For this, let’s create a new Form on Appsmith that takes in all necessary fields to post in our game store database.

  1. Now, go back to the page, and drag and drop a form widget. Inside the form widget, we’ll add some Input widgets that accept necessary fields to add a new game to the database.
  2. We’ll also have to name the input fields so that we’ll have to reference them while posting values from the GraphQL query.
  3. Additionally, we’ll also have some text widgets that’ll look like labels for the Input widgets. Below is how we’ll have to name the text widgets and associated input widgets.

Name : nameInput

AppId : appIdInput

Average Play Time : avgPlayTimeInput

Genres : genresInput

Price : priceInput

You can simply do this by going to the input widget’s property pane and double-clicking on the existing widget name.

Below is a screenshot how the form should look like,

Game Post Form

Perfect, we now have our UI ready; let’s create a GraphQl API to post data into the Database.

  1. Create a new API by clicking on the plus icon next to the APIs, rename the API to InsertGame.
  2. Set the content-type andx-hasura-admin-secretheader to access the database on Hasura from the previousFetchGames` API.
  3. Now we’ll have to write a GraphQL query inside the request body, paste the below code snippet inside the Body:
{
    "query": {{JSON.stringify(`
    mutation ($object: steam_games_insert_input!) {
        insert_steam_games_one(object: $object) {
            appid
            name
            average_playtime
            price
            genres
        }
    }
    `)}},
    "variables": {
        "object": {{JSON.stringify({
            appid: appIdInput.text,
            name: nameInput.text,
            average_playtime: avgPlayTimeInput.text,
            price: priceInput.text,
            genres: genresInput.text,
        })}}
    }
}

Here, we have an object consisting of a query key that holds the GraphQL query and a variables key with all the variables that need to be posted to the database.

Now, in the query, we have a mutation that lets us modify the server-side data. Inside the mutation, we’ll send the object that references the data we will be querying. The insert_steam_games_one allows us to post one object to the database. Inside this query, we can add the attributes we need to post. Next, we’ve associated the variables to the object from the created UI input widgets. The .text on the input widget name helps us get the text written inside the input.

One last thing now that you’re API is ready, we’ll have to call the API when we submit values from the Form. Now go back to the form, open the property pane of the submit button and set the onclick action to call an API. You’ll now find the list of APIs we’ve created, select the InsertGame API. Below is the GIF explaining the same.

Awesome, now that we have our POST request ready, we can try adding a new game to the store using the form we’ve created.

Updating the Store Item on Database

In this section, let’s look at how we can update the database’s values by building a simple Modal. For this, let’s create a new column consisting of a button in the table; once the button is clicked, we should see the modal with existing values and modify it after submitting it.

  1. Open the Table Property Pane, and click on the Add New Column option.
  2. Now set the column type to Button and the label to Edit.
  3. We’ll have to open a new modal when the Edit button is clicked, hence under the actions, select Open a New Modal and create a new modal and name it to editItemModal.
  4. Now add necessary inputs widgets and text widgets to edit the store item.
  5. Name the input widgets and their default text to the following:

Name Input: nameEditInput

Default Text: {{Table1.selectedRow.name}}

Average Playtime Input : avgPlayTimeEditInput

Default Text: {{Table1.selectedRow.average_playtime}}

Genre Input : genresEditInput

Default Text: {{Table1.selectedRow.genres}}

Price Input: priceEditInput

Default Text: {{Table1.selectedRow.price}}

The default text will allow us to display the selected row data on the table.

Now, let’s create an API that performs update operations on the data.

  1. Create a new API by clicking on the plus icon next to the APIs, rename the API to EditGame.
  2. Set the content-type andx-hasura-admin-secretheader to access the database on Hasura from the previousFetchGames` API.
  3. Now we’ll have to write a GraphQL query inside the request body, paste the below code snippet inside the Body:
{
 "query": {{JSON.stringify(`
     mutation ($object: steam_games_set_input!) {
         update_steam_games(where: {appid: {_eq: "${Table1.selectedRow.appid}"} }, _set: $object) {
             affected_rows
         }
     }
 `)}},
 "variables": {
     "object": {{JSON.stringify({
         name: nameEditInput.text,
         average_playtime: avgPlayTimeEditInput.text,
         price: priceEditInput.text,
         genres: genresEditInput.text,
     })}}
 }
}

Here, we have an object consisting of a query key that holds the GraphQL query and a variables key with all the variables that need to be posted to the database.

Now, in the query, we have a mutation that lets us modify the server-side data. Inside the mutation, we’ll send the object that references the data we will be querying. The steam_games_set_input allows us to update an object in the database. Next, we’ve associated the variables to the object from the created UI input widgets from the Edit Modal. The .text on the input widget name helps us get the text written inside the input.

Now we’ll have to call the EditGame API after the modal is submitted, hence open the button’s property pane and add the following code snippet under the onclick action:

{{
    EditGame.run(() => {
        FetchGames.run();
        closeModal('EditModal');
    })
}}

This will update the item on the store and refresh the table, by re-running the FetchGames query.

Deleting the Store Item on Database

This section looks at how we can delete the database’s values by building a simple button on the table. For this, let’s create a new column consisting of a button in the table; if clicked, the selected item should be deleted.

Open the Table Property Pane, and click on the Add New Column option. Now set the column type to Button and the label to Delete.

Now let’s write a Delete API and link it to the created button.

  1. Create a new API by clicking on the plus icon next to the APIs, rename the API to DeleteGame.
  2. Set the content-type andx-hasura-admin-secretheader to access the database on Hasura from the previousFetchGames` API.
  3. Now we’ll have to write a GraphQL query inside the request body, paste the below code snippet inside the Body:
{
    "query": {{JSON.stringify(`
    mutation {
        delete_steam_games(where: {appid: {_eq: "${this.params.appid}"} }) {
            affected_rows
        }
    }
    `)}}
}

Now, in the query, we have a mutation that lets us modify the server-side data. The delete_steam_games allows us to delete an object in the database based on the selected appid. The appid from the table is referenced using this.params.appid.

Lastly, let’s call this API, after we click the delete button, go to the delete button property pane and select the Call an API action in the actions and select the DeleteGame API.

To refresh the table, after deleting an object, you can add custom JS in the button's onclick property:

{{
DeleteGame.run(() => FetchGames.run(), () => {}, {appid: currentRow.appid})
}}

This code snippet will first, delete the Game and then fetches the data again and updated it on the table.

Conclusion

You've seen how easy it is to build an application on Appsmith, specifically a catalogue dashboard. This guide covered how to create an application and connect it to a GraphQL, as well as how to create, read, update and delete data. You learned how to build interactive pages, work with widgets, and customize them for your purposes.

Building a dashboard with Appsmith is a fast way to build admin tools. Check it out if you’d like to save your developers time and get internal tools launched faster.

Cover Image: Photo by Charisse Kenion on Unsplash