Strategies to Build Your Backend with Hasura and PostgreSQL

You've come to the right place if you are looking for a way or a well-structured guide to building your backend with Hasura and PostgreSQL. This guide will assist you in setting up your backend development environment, connecting to your database, and creating your API endpoint.

In this article, we’ll go over the key features that Hasura offers that make it suitable for creating and launching scalable backends for web and mobile applications. To deliver the appropriate data to your front end without writing custom code, we’ll also examine how you can use PostgreSQL’s features to perform various computation and analysis tasks using just SQL.

However, be aware that Hasura supports other databases, including GoogleBigQuery, Amazon Aurora, and Microsoft SQL Server. Since PostgreSQL is available and cost-free for most businesses and organizations, we’ll concentrate on it.

Before proceeding further on backend development with Hasura and PostgreSQL, let’s briefly overview Hasura and PostgreSQL.

PostgreSQL

PostgreSQL is a well-liked and potent open-source, object-oriented relational database comparable to Microsoft SQL Server and Oracle database in terms of enterprise-class features. According to StackShare, major international businesses like Netflix, Instagram, Spotify, and Uber use PostgreSQL.

Have you wondered why PostgreSQL is for your backend mobile and web app projects? Let’s begin with why PostgreSQL and how it can be a perfect choice for your mobile and web app projects.

Why Use PostgreSQL for Backend Development?

PostgreSQL is a relational database in which you can model your data using rigid and table relationships. A relational database has many clear advantages over other databases. For many applications in most industries, the capacity to conduct JOINs and carry out ACID transactions is crucial. These characteristics are essential for ensuring business data consistency and integrity.

Compared to relational databases like MySQL, PostgreSQL has additional benefits:

• You can use table inheritance to model data.
• It has better concurrency control for environments with multiple users (you can write enormous amounts of data more effectively)
• It is less likely to corrupt data and be fault-tolerant.
• It supports several distinctive data types, including JSON and Spatial, which is helpful for applications in the financial and scientific fields.

PostgreSQL is more than just a tool for storing data. Additionally, it is a server that can execute customized triggers and functions to perform various computational and analytical tasks. Since PostgreSQL eliminates the need for writing unique server code, running logic on it is more efficient. In PostgreSQL, you can define logic by using the following:-

• Using views, a stored query that can make complex queries simpler.
• Using Operators and Functions, date formatting, arithmetic operations, and pattern matching.
• Add-ons and extensions that increase PostgreSQL’s functionality, such as PostGIS.
• Programming languages known as procedural languages, such as PL/pgSQL, are used to create user-defined functions, stored procedures, triggers, and extensions for standard SQL.

Hasura can expose logic implemented in PostgreSQL to frontend applications via GraphQL mutations and queries. Here is a screenshot of the pgAdmin interface showing the top-level view of a PostgreSQL server:

You can quickly solve complex problems without writing server code using PostgreSQL features. Examples of what you can do with PostgreSQL are-
Examples 1-
Using a view, you can get a list of online and active users right now:

CREATE OR REPLACE VIEW "public"."online_users" AS
 SELECT users.id,
    users.last_seen
   FROM users
  WHERE (users.last_seen >= (now() - '00:00:30'::interval));

Examples 2-
Using a PostGIS function, you can get a list of all the stores within a 2,000-meter radius.

SELECT id, name, address, geom
FROM Seattle_Starbucks
WHERE ST_DWithin(geom, ST_MakePoint(-122.325959,47.625138)::geography, 2000);

Until now, you are aware of PostgreSQL; therefore, in the below section, let's understand what Hasura is and its features that help your backend development.

What is Hasura?

Hasura is an open-source GraphQL engine that uses your database schema to produce GaphQL and REST API endpoints. It supports data modeling, event programming,in-the-moment or real-time querying, role-based authorization, and actions for implementing unique business logic over GraphQL.

Hasura is a real-time, open-source GraphQL engine that creates your database’s REST API and GraphQL endpoints. It includes a web console that lets you:-

• model your database schema.
• view, insert, update and delete data.
• implement role-based access control policies.
• run GraphQL queries and mutations.
• create REST endpoints.
• run SQL code.
• define actions and triggers.

Since Hasura doesn’t support user authentication, you must incorporate Hasura into your frontend application using a service like-

• AuthGuardian
• Firebase
• Clerk
• Auth0
• Magic

Additionally, there is no file storage service; you must integrate your app with a third-party storage supplier. Check out NHost, which we'll cover later in the deployment section if you'd prefer a more out-of-the-box integrated experience with Hasura.

The following section will examine how to run Hasura locally and in the cloud.

Additionally, there is no file storage service; you must integrate your app with a third-party storage provider.
The below section will help you run Hasura locally and in the cloud.

Launching Hasura

You can quickly start and run a Hasura instance in one of the following ways:

1. Cloud

Hasura Cloud is a much simpler way to get started. This open-source Hasura version has been redesigned for global scalability, availability, security, and distribution.

However, Hasura Cloud has different other features that open-source doesn’t have. Such as

• Monitoring dashboard errors, slow queries, connections, subscriptions, and other operations.
• Improved server and client data fetching performance with GraphQL Coaching.
• Rate limiting to protect your API from DDoS attacks and malicious users.
• Regression testing involves comparing changes in your development instance to your production instance when running test suites.

You must register for a free account to use Hasura Cloud. Be aware that there is a 60-request-per-minute rate limit on the free account. After making an account, you must-

• Create a project (a Hasura instance)
• Connect to a PostgreSQL database

Hasura Cloud offers a one-click connection and installation to a free Heroku Cloud database instance. You can connect to any other PostgreSQL database online. Various PostgreSQL service providers are available.

• AWS
• Azure
• Digital Ocean
• TimescaleDB Cloud
• YugabyteDB

You can refer to this guide if you need more clarification on the steps above. Using the secret admin key, Hasura Cloud restricts public access to data by default.

2. Docker

It is advised to use Docker to run Hasura on your local computer. No internet connection throttling and rate-limiting of API requests affect your experience. Any work you do locally can easily be transferred to the environment for staging and production.

You can use the instructions in this guide to run Hasura on your computer, assuming that Docker and Docker Compose are already installed on your computer or system.

# create new directory
mkdir my-hasura
cd my-hasura

# download docker-compose.yml
curl https://raw.githubusercontent.com/hasura/graphql-engine/stable/install-manifests/docker-compose/docker-compose.yaml -o docker-compose.yml

# start hasura and postgresql container instances
docker-compose up -d

By running the docker ps command, you can verify that your Hasura and PostgreSQL container instances are active. You ought to be able to use your browser to access your local Hasura instance at http://localhost:8080/console. Connecting to the PostgreSQL database, which also runs as a container alongside Hasura’s, is required to finish the setup.

The web console can create tables, define relationships, and perform CRUD operations on your data with the database connection. Remember that your data is public when using the default Docker setup. You can secure it by simply uncommenting the line in your docker-compose.yml file that begins with HASURA_GRAPHQL_ADMIN_SECRET and restarting your Hasura container.

Features of Hasura?

In the below section, I’ll give a high-level overview of Hasura features to see how you can create a custom backend development environment without writing any code.

Data Manager

A visual designer for modeling your data layer is included with Hasura. This allows you to

• define relationships (one-to-one, one-to-many, many-to-many)
• perform CRUD operations
• create tables
• create views
• implement data validation using PostgreSQL’s DDL constraints
• define triggers
• run any SQL statement

Hasura supports a wide range of data types for columns, including

• Boolean
• serials and UUID
• integers, numerics, and floats
• characters and text
• date and time
• geometric — such as line, box, path, polygon, and circle
• JSON

Authorization

Role-based access control is used in Hasura. An admin role automatically has complete CRUD access to your data. You must create different roles, like the user, public, or accountant, to restrict access to other users.

You must specify an access policy for each role you create for the following actions:

• insert
• select
• update
• delete

You can define three different categories of policies:

• Full access, with no restriction
• Partial access, with conditionally—based restrictions
• No access

Queries

After creating your schema and setting permissions, you can test your GraphQL queries directly from Hasura’s API dashboard. You can query any defined table, view, and relationship as long as tracking is enabled. You can run different queries, including, Simple queries, Nested queries, Aggregation queries, and Filter/Search queries.

Results from GraphQL queries can be paginated, sorted, and grouped. A GraphQL query can access any SQL statement executed on PostgreSQL.

Mutations

Data can be modified using GraphQL mutations, which are statements. The various mutations you can make are listed below-
Insert: create one or more rows of data-

mutation insert_single_article {
    insert_article_one(
      object: {
        title: "Article 1"
        content: "Sample article content"
        author_id: 3
      }
    ) {
      id
      title
    }
  }

Upsert: create and update on conflict. In the below example, the column value has a unique constraint-

mutation upsert_single_tag {
    insert_tags(
      objects: { value: "Java" }
      on_conflict: { constraint: tags_value_key, update_columns: value }
    ) {
      returning {
        id
        value
      }
    }
  }

Update: One or more rows of data should be updated.

Additionally, there are specific operators for handling int and jsonb data types. The below example updates every article with a rating of two or less. In addition to listing the affected rows as an array of objects, the mutations will also return the number of affected rows-

mutation update_article {
    update_article(
      where: { rating: { _lte: 2 } }
      _set: { rating: 1, is_published: false }
    ) {
      affected_rows
      returning {
        id
        title
        content
        rating
        is_published
      }
    }
  }

Delete: to delete one or more rows of data.

mutation delete_an_object {
    delete_article_by_pk(id: 1) {
      id
      title
      user_id
    }
  }

Transaction: perform multiple mutations in one mutation block. All previously executed mutations within that block will be rolled back if one fails. The following example first deletes all articles belonging to an author. In the second mutation, the author’s name is updated:

mutation reset_author {
    delete_article(where: { author_id: { _eq: 6 } }) {
      affected_rows
    }
    update_author(where: { id: { _eq: 6 } }, _set: { name: "Cory" }) {
      returning {
        id
        name
        articles {
          id
          title
        }
      }
    }
  }

Subscription

Live queries are executed over a WebSocket protocol by Hasura’s subscription. This protocol is used to retrieve current data from a database. Any GraphQL query can be converted into a subscription by substituting a subscription for the keyword query.

These queries typically execute SQL statements once every second. This configurable setting can provide a suitable balance between database load and latency. If there’s a change in the underlying data, the new value is pushed to the client.

The below example demonstrates a GraphQL subscription that tracks the location of a vehicle-

# $vehicleId = 3
subscription getLocation($vehicleId: Int!) {
  vehicle(where: { id: { _eq: $vehicleId } }) {
    id
    vehicle_number
    locations(order_by: { timestamp: desc }, limit: 1) {
      location
      timestamp
    }
  }
}

However, there are other use cases available where you can use the subscription feature of Hasura like-

• online polls
• food delivery tracking
• chat messaging

Remote Schemas

Building a modern application requires integrating third-party APIs that-

• Give data that isn’t already in your database, like stock prices or real-time sports scores.
• Provide business logic, such as flight booking or payment processing.

The majority of the time, client apps directly access these third-party APIs. You can combine these APIs with your database using Hasura to produce a single GraphQL API. This opens up new possibilities for implementing authorization and setting up remote connections between your tables and views and the remote API.

Based on this kind of relationship, you can also create Actions that carry out specific tasks. A unified GraphQL API makes the front-end app’s development easier for front-end developers.

Deployment

Hasura Cloud is the most straightforward method of deploying Hasura for production. Given that you receive enterprise features not present in the open-source version, this is the option that is advised. Fortunately, unlike most backend-as-a-service (BaaS) providers, Hasura doesn’t lock you into their platform. You can benefit from One-click deployment service providers on platforms like-

• Digital Ocean
• Heroku
• Render
• Azure

Hasura can be set up on any Kubernetes platform, including Google Cloud. AWS is also supported, but setting it up requires several steps. You have more freedom and cost options if you host elsewhere. Each of the above choices calls for you to set up extra services for

• Authentication
• Storage
• Custom business logic

Deploying with NHost

In order to compete with services like Firebase, NHost is an open-source BaaS provider. They use the following components in their backend development stack.

• Hasura GraphQL engine
• An Authentication Service
• PostgreSQL Database
• MinIO, an S3-compatible object storage service
• Serverless functions (currently in beta)

The platform includes a client library called nhost-js-sdk, used for file management and frontend authentication. We don’t have to deal with the hassle of integrating another service for images because the storage service supports image optimization.

NHost currently offers a 14-day trial of its service as of the time of writing. Soon, a free tier will go live. You can install the Hasura Backend Plus version locally on your computer for backend development.

Final Thought

Finally, Hasura’s GraphQL Engine and PostgreSQL database represent a significant advance in the speed of backend development for mobile and web applications without writing a single line of code. PostgreSQL can handle most of the computation and analytical logic; the remaining custom business logic can be implemented using microservices or serverless functions.

Using Hasura, you can launch your web and mobile apps more quickly and create something highly effective, secure, fault-tolerant, scalable, and simple to maintain. You can concentrate on front-end development and the user-facing aspect of your app when there is no infrastructure hassle, reducing your operating costs.

Hasura supports versioning and environment staging, essential CI/CD development workflows, despite not having as many features as other open-source alternatives like the Parse platform. You are safe from the dangers of vendor lock-ins because it is open source.