How to Create a Comments Database Table and Set Permissions

[00:00 - 00:07] Hi, welcome to the ferret lesson. In the previous lessons, we covered GraphQL basics and we are in table with Has ura.

[00:08 - 00:15] We also created a new Hasura project and played a bit with its functionalities. In this lesson, we are going back to implementing the commenting system.

[00:16 - 00:28] We will use the project created in the previous class and create the comments table there. This is an example comment form to help us visualize what data we need to store in the database.

[00:29 - 00:39] It has two inputs to get information from a user and a submit button. Let's look at the form and list down what columns we need in the comments table .

[00:40 - 00:44] The first one is "outer". Comments "outer" identifier.

[00:45 - 01:00] It could be a name, email or however a user would like to introduce themselves. We could go more granular here and create separate columns like names, surname and email, but the single column with the outer name is good enough for now.

[01:01 - 01:12] Then the second field is "content" and that will be the comments message. These two columns are the data from the form and that could be enough in our database.

[01:13 - 01:18] However, we may need a bit more. For example, "created at column".

[01:19 - 01:25] Date at which the comment was added. It will be important for us to store the comments by the posting date.

[01:26 - 01:37] The value can be sent from the front end application or generated by the database. Topic "undentifier of the post" or another content that was commented on.

[01:38 - 01:46] Imagine that you have a blog and users can add comments to each blog post. You need a way to identify which comments belong to a particular post.

[01:47 - 01:53] By storing the topic ID, the comment is assigned with a particular resource. For example, "blog post".

[01:54 - 02:01] An example of a topic could be "my post about cats". Now let's go back to our hosteler project and go to the "create table" page.

[02:02 - 02:08] We're going to fill the form of the information about columns. We need to provide column names and column types.

[02:09 - 02:22] For columns, "outer", "content" and "topic" will go with type text, which allows storing strings of any length. The "created at column" can be created by using frequently used types dropdown.

[02:23 - 02:34] We can also manually provide the type as "time stamp" and the default function as "now". That will tell Postgres to insert a current timestamp for every new row.

[02:35 - 02:43] This way, the database will handle "date" in this column for us. And we won't have to send it from the front-end application.

[02:44 - 03:01] To create a table in Hasura, we also need a primary key, and a unique identifier of every row. We could possibly use a multi-column key consisting of "created at and outer", but there would be a possibility of duplicates because we don't ensure unique outer names.

[03:02 - 03:15] We don't have their IDs, we only have pseudonyms, and two people with the same pseudonym could insert a comment at the same time. Let the database handle it for us and generate unique IDs for each row.

[03:16 - 03:28] We'll use "outer" and "comment" integer type by selecting it from the frequently used types dropdown. Now let's click the "Add Table" button and our table will be created in a few seconds.

[03:29 - 03:35] Let's go to the "Permissions" tab. Access to the Hasura instance and underlying database is protected.

[03:36 - 03:45] It requires an admin secret, and Hasura will reject requests without it. However, on our website, we want authorized users to add comments.

[03:46 - 03:54] We also want them to be able to see the comments. Hence, we need a way to be able to retrieve and modify data without authentication.

[03:55 - 04:01] For that, we have to do two things. We have to create a new row with specific permissions.

[04:02 - 04:09] We did something similar in the previous lesson. We need that row to access comments and insert new comments.

[04:10 - 04:21] The second thing is that we have to do is we need to tell Hasura that this is a special role that doesn't require authentication. Let's start with the first item.

[04:22 - 04:35] We need to go to the "Table's permissions" page and configure, select and insert access. For "Select", we'll choose "No checks" option for row-based authentication and allow access to all the columns.

[04:36 - 04:50] We also need access to make aggregate queries, for example, "count" to show the number of all the comments. This will let the front-end application perform "count" operations to retrieve the number of all the comments.

[04:51 - 04:57] This information will be useful when implementing pagination. The second thing is insertion permissions.

[04:58 - 05:23] Say as for "Select", we'll go with "No checks" for row access and for column access, we'll select only "Outer, Content and Topic" since the database handles ID and created it for us. Now what we need to do is go to Hasura Cloud Dashboard to set a new environmental variable and let Hasura know that this role doesn't require authentication.

[05:24 - 05:37] In the project "Table's" view, we need to go to the "End-Verse tab" and then click the "New End-Verse" button. The variable that we have to set is called Hasura Graphical and Outrise role.

[05:38 - 05:45] We can select it from the drop-down. And, of course, we're setting it to the previously created role anonymous.

[05:46 - 06:00] We are all set "Great Job", the packing part of our walk is done. We're moving to the front-end part in the following module and we'll be integrating to our Hasura instance who will be implementing the UI and the custom React hook.

[06:01 - 06:02] See you in the next module.