Building a Store Catalogue Management System using Appsmith and GraphQL

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.

Sign in to Appsmith Account
Click Create New to create a new application and name it GraphQL Store Manager. This takes you to an empty workspace with a sidebar.
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:

Make a connection to a database by clicking the plus icon near APIs on the sidebar under a page.
Now, rename your API to FetchGames
We’ll write a POST request and use GraphQL query to list down all the games in the database.
Next, use the following URL as the data source to access the mock database hosted on GraphQL: https://mock-as.hasura.app/v1/graphql
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
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.

Navigate to Page1 and click on the plus icon next to the widgets.
Now drag and drop a table widget onto the canvas; we'll see a new table with some pre-populated data.
Next, we’ll have to open the table’s property pane to update the table data by clicking on the cog icon.
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.

Table Gif.gif

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.

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.
We’ll also have to name the input fields so that we’ll have to reference them while posting values from the GraphQL query.
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.

Create a new API by clicking on the plus icon next to the APIs, rename the API to InsertGame.
Set the content-type andx-hasura-admin-secretheader to access the database on Hasura from the previousFetchGames` API.
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.

GIF Two.gif

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.

Open the Table Property Pane, and click on the Add New Column option.
Now set the column type to Button and the label to Edit.
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.
Now add necessary inputs widgets and text widgets to edit the store item.
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.

Create a new API by clicking on the plus icon next to the APIs, rename the API to EditGame.
Set the content-type andx-hasura-admin-secretheader to access the database on Hasura from the previousFetchGames` API.

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.

Create a new API by clicking on the plus icon next to the APIs, rename the API to DeleteGame.
Set the content-type andx-hasura-admin-secretheader to access the database on Hasura from the previousFetchGames` API.
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