Part III: Data modelling for the blog app

Our basic data requirements are as follows:

  • Storing the profile data of each user
  • Storing the article data and the author of each article
  • Storing the likes given by users to articles
  • Storing the comments written by users on articles

Video reference

Basic data modelling

The video shows you how to create data models using the project console. The document below however, is an API driven approach to creating data models. Don’t skip reading this guide if you need a solid developer intro to modelling, especially if you want to start versioning your schema!

The data service

Every Hasura project comes with a data service. The data service provides an HTTP API over PostgreSQL, an extremely versatile open source relational database. We create tables in Postgres and access the data using the APIs provided by the data service.

Any user with the admin role has full-access to the data service. All requests to the data service are POST requests to /v1/query endpoint. The body should have two keys type and args. For example,

{
    "type": "run_sql",
    "args": {
        "sql" : "some sql"
    }
}

Creating tables

As you would normally do with any relational database, data is modelled as tables.

Table Columns
author id, name
article id, title, author_id, rating
like user_id, article_id
comment id, user_id, article_id, comment

Check out this video to see how we can create tables and model foreign key constraints via console. You can skip to the next section if you use the console.

Let us define the structure of these tables in sql as follows:

CREATE TABLE author (
    -- hasura_id is used to link to the user generated on the auth service
    hasura_id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE article (
    id SERIAL NOT NULL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    is_published BOOLEAN NOT NULL DEFAULT false,
    author_id INTEGER NOT NULL REFERENCES author(hasura_id)
);

CREATE TABLE article_like (
    article_id INTEGER NOT NULL REFERENCES article(id),
    author_id INTEGER NOT NULL REFERENCES author(hasura_id),
    PRIMARY KEY (author_id, article_id)
);

CREATE TABLE comment (
    id SERIAL NOT NULL PRIMARY KEY,
    author_id INTEGER NOT NULL REFERENCES author(hasura_id),
    article_id INTEGER NOT NULL REFERENCES article(id),
    comment TEXT NOT NULL
);

CREATE TABLE category (
    id SERIAL NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE article_category (
    article_id INTEGER NOT NULL REFERENCES article(id),
    category_id INTEGER NOT NULL REFERENCES category(id),
    PRIMARY KEY (article_id, category_id)
);

Let’s execute this sql on the PostgreSQL database. The run_sql query type is only allowed for the admin role. Let’s execute the above sql to create the tables.

POST data.<project-name>.hasura-app.io/v1/query HTTP/1.1
Authorization: Bearer <admin-token>
Content-Type: application/json

{
    "type" : "run_sql",
    "args" : {
        "sql" : "the above sql statements"
    }
}

The response would be as follows:

HTTP/1.1 200 OK
Content-Type: application/json

{
    "message" : "success"
}