Exploring Hasura - Schema setup and queries
Tags: hasura, graphql, android, mobile
Published: 2021-05-11 07:40:39

This is second article in the series: [Android app with Hasura backend - #2]


Now we have a database and a working graphQL engine for us to consume. The missing part is a table for us to insert data with. Let's create one. 


I assume you familiarized yourself with the website hierarchy. Below is an approximate site map of Hasura.



![image-20210511112858088](https://i.imgur.com/j1no1BG.png)



For this post, we're interested in the data manager where we create/edit tables. Navigate to Project ➝ Data and observe the side bar. You should see a SQL section there click on it.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z4y5mzueubzax9osb2fv.png)


Create a sequence which we'll use for auto-increment primary key. Type the below statement and click Run.

```sql
CREATE SEQUENCE expense_id_seq;
```



Now click on the public schema under database in the sidebar. You should see the below screen where all the tables listed. For now it's just empty.



![image-20210511114312945](https://i.imgur.com/gWKfVLw.png)



Click on *Create Table* and input table name and few columns as below.

![image-20210511114859000](https://i.imgur.com/qDzSyGY.png)



We have data related to an expense here. But we might need few more just to collect meta data on the expense row. Let's add ` created_at & updated_at` columns. Conveniently they're present in Frequently used columns.



![image-20210511115255820](https://i.imgur.com/OQgDSvA.png)



I've added one more column called `Category`, we might use it to group expenses in later stages of the project. So, pretty much the column section is over. Let's define primary key and complete the setup. Select id as primary key and unique key.



![image-20210511115721838](https://i.imgur.com/E7Gx2X2.png)



That's it. Click on `Add Table` button below. And now we have a working setup for our project. Look at the DataManager for an overview.



![image-20210511120122416](https://i.imgur.com/E8kJEJ0.png)



---

## Verifying the table & GraphQL engine

Goto Insert Row tab and create an expense. In browse rows tab you should see it.

![image-20210511120416498](https://i.imgur.com/quyZEIv.png)



Let's see how this data looks in GraphQL response. Beforehand add few more columns to the table. Now head over to the API section and check the pane on the left called **Explorer**. Pick few columns in there and run.



![image-20210511121031777](https://i.imgur.com/6Q59iWn.png)



You should get response like this.

```json
{
  "data": {
    "expenses": [
      {
        "id": 1,
        "amount": "$200.00",
        "category": "Books",
        "created_at": "2021-05-11T06:35:43.25801+00:00"
      },
      {
        "id": 2,
        "amount": "$300.00",
        "category": "Apparels",
        "created_at": "2021-05-11T06:35:01.168+00:00"
      },
      {
        "id": 3,
        "amount": "$100,000.00",
        "category": "Salary",
        "created_at": "2021-05-11T06:35:01.168+00:00"
      }
    ]
  }
}
```



This is called `Query` in graphQL. To insert a row / rows in the table, we'll use `mutation`. In the explorer click on `Mutation` .

![image-20210511121746997](https://i.imgur.com/Wji8o7S.png) 



```json
mutation MyMutation {
  insert_expenses_one(object: {amount: "$16", category: "Food", is_income: false, remarks: "Juice", spent_on: "2021-05-10T11:10:43.878066+00:00"}) {
    id
  }
}

```



For the above input, you should see, response closer to below one. 

```json
{
  "data": {
    "insert_expenses_one": {
      "id": 4
    }
  }
}
```



---

### Endnote:

So far, we have a working GraphQL backend that is waiting for a client. In the next posts of the series I will integrate it to an Android app. For a head start, install this wonderful [plugin](https://plugins.jetbrains.com/plugin/8097-js-graphql) in Intellij/Android Studio to start building queries.



Also play with the GraphQL explorer in Hasura to find solution for below requirements here.

1. Last expense that I created
2. Expenses that is `spent_on` this week
3. Find the maximum sum I spent so far
4. Maximum income that I earned
5. How much total I spent on a particular category?