How to Build Your Backend with Hasura and PostgreSQL – SitePoint

Backend development often requires writing many lines of code that handle CRUD, authorization and business logic. All this code needs to be tested, debugged and maintained during the entire lifetime of the project. This takes a lot of time that developers can be using to develop new features. In this article, you’ll learn how Hasura and PostgreSQL can help you speed up app development and launch backends quickly with minimal effort.
Hasura is an open-source GraphQL engine that generates GraphQL and REST API endpoints using your database schema. It supports data modeling, real-time querying, event programming, role-based authorization and actions for running custom business logic over GraphQL.
PostgreSQL is a popular and powerful open-source, object-oriented relational database that offers enterprise-class features on a similar level with Oracle Database and Microsoft SQL Server. PostgreSQL is used by large international companies such as Netflix, Instagram, Uber and Spotify, according to StackShare.
In this article, we’ll go over the main features Hasura provides that makes it suitable for developing and launching scalable backends, for both web and mobile applications. We’ll also look at how you can leverage PostgreSQL’s features to perform various computation and analytical tasks using just SQL to deliver the right data to your frontend without writing custom code.
Do note that Hasura does support other databases as well, such as Microsoft SQL Server, Amazon Aurora and Google BigQuery. We’ll focus on PostgreSQL, since it’s free and accessible for the majority of businesses and organizations. Support for MySQL is coming soon, in case you’re interested.
If you’re new to GraphQL, please check out our tutorial to learn more. Let’s start with why PostgreSQL can be a perfect choice for your project.
Relational databases have many distinct advantages over other types of databases, in that you can model your data using strict schemas and table relationships. The ability to perform JOINs and execute ACID transactions is a major requirement for many applications in most industries. These features are essential to promoting integrity and consistency of business data at any given time.
PostgreSQL also has additional advantages over other relational databases such as MySQL, in that:
PostgreSQL isn’t just a data storage service. It’s also a server capable of running custom functions and triggers to perform various computation and analytical tasks. Running logic on PostgreSQL is more efficient, as it negates the need to write custom server code. You can define logic in PostgreSQL using:
When you implement logic in PostgreSQL, Hasura is able to expose them to frontend applications via GraphQL queries and mutations. Here’s a top-level view of a PostgreSQL server, as seen via the pgAdmin interface:
The pgAdmin interface
Learning to take advantage of PostgreSQL features can help you solve complex problems easily without writing server code. Here are a few examples of what you can do with PostgreSQL:
You can retrieve a list of online users that are currently active using a view:
Using a PostGIS function, you can list all stores that are located within a 1,000 meter radius. See this geolocation tutorial for a detailed explanation:

In the next section, we’ll focus on Hasura’s features.
Hasura is an open-source, real-time GraphQL engine that generates GraphQL and REST API endpoints for your database. It comes with a web console that allows you to:
Hasura API dashboard
Hasura doesn’t support user authentication, so you’ll need to integrate Hasura and your frontend application with a provider such as:
There’s also no file storage service, you’ll need to integrate your app with a third-party storage provider. If you’d prefer a more out-of-the-box integrated experience with Hasura, you can check out NHost, which we’ll discuss later in the deployment section.
In the next section, we’ll look at how we can run Hasura locally and in the cloud.
There are a couple of ways you can quickly launch and run a Hasura instance:
Using Docker to run Hasura in your local machine is the recommended way for setting up a development environment. With this setup, there’s no rate limiting for API requests and there’s no internet connection throttling your experience when you interact with Hasura’s web console. Any work you do locally can easily be migrated to staging and production environments. We’ll discuss how this is done later in the “Migrations and Environments” section.
Assuming you already have Docker and Docker Compose already installed on your machine, you can follow the instructions provided by this guide to run Hasura on your machine:
You can confirm your Hasura and PostgreSQL container instances are running by executing the command docker ps. You should be able to access your local Hasura instance via your browser at http://localhost:8080/console. To complete the setup, you’ll need to connect to the PostgreSQL database, which is running as a container alongside Hasura’s.
With the database connected, you’ll be able to use the web console to create tables, define relationships and perform CRUD operations on your data. Do note that your data is public when using the default Docker setup. You can secure it by simply uncommenting the line that starts with HASURA_GRAPHQL_ADMIN_SECRET in your docker-compose.yml file and restarting your Hasura container.
A much easier way of getting started is via Hasura Cloud. This is a re-engineered version of the open-source Hasura designed for scalability, availability, security
and global distribution.
The Hasura Cloud dashboard

Hasura Cloud comes with several new features not available in the open-source version, which include:
To get started with Hasura Cloud, you’ll need to sign up for a free account. Do note the free account has a rate limit of 60 requests per minute. After creating an account, you’ll need to:
For convenience, Hasura Cloud provides a one-click installation and connection to a free Heroku Cloud database instance. You can also connect to any other PostgreSQL database that’s accessible over the Internet. There are many PostgreSQL providers you can use. These include cloud services such as:
You can follow this guide if you need more clarity with the steps above. By default, Hasura Cloud restricts data access from the public using the admin secret key. We’ll discuss more about this in the upcoming sections.
In this section, I’ll give you a high-level overview of the features that Hasura offers for building a custom backend without writing code.
Hasura comes with a visual designer for modeling your data layer. This allows you to:
Hasura create table
When it comes to columns, Hasura supports a rich set of data types which include:
You can also add custom types using the CREATE TYPE SQL command. Next, we’ll look at how data is authorized in Hasura.
Access control in Hasura is role based. By default, there’s a role called admin that has full CRUD access to your data. To restrict access to other users, you’ll need to create additional roles such as user, public or accountant.
For each role you create, you need to define an access policy for the following actions:
There are three types of policies you can define:
The following example demonstrates a select authorization policy for the user role. The rules defined below are set to ensure that a user can only query either their own records or those marked as public:

Hasura authorization example
After designing your schema and defining permissions, you can test your GraphQL queries right within Hasura’s API dashboard. Every table, view and relationship that you’ve defined can be queried, provided tracking was enabled.
The types of queries you can execute include:
Hasura GraphQL query
GraphQL query results can be sorted, grouped and paginated. Cursor-based pagination is also supported. Basically, any SQL statement that can be run on PostgreSQL can be exposed via a GraphQL query. In the next section, we’ll look at mutations.
GraphQL mutations are statements that are used to modify data. Below are the types of mutations you can perform:
Hasura’s subscriptions are live queries that execute over a WebSocket protocol. This is a protocol used for fetching real-time data from a database. Any GraphQL query can be turned into a subscription by simply replacing the keyword query with subscription. By default, such queries run an SQL statement every one second. This is a configurable setting that can be tuned to 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 following example demonstrates a GraphQL subscription that tracks the location of a vehicle:
Other use cases of using subscriptions include:
Often when building a modern application, you’ll need to integrate with third-party APIs that:
These third-party APIs are usually accessed directly from client apps. With Hasura, you can merge these APIs with your database to create a unified GraphQL API. This creates new opportunities where you can implement authorization and define remote relationships between your tables/view and the remote API. You can also create Actions that perform a certain task based on this type of relationship.
Having a unified GraphQL API makes it easier for frontend developers to build the app. In the next section, we’ll look at what Hasura Actions are.
Custom business logic in Hasura is handled externally on microservices or serverless functions via webhooks. This means you can write custom logic using any language your prefer, such as Node.js, Python, Go or Ruby on Rails. An Action in Hasura is simply a custom GraphQL query or mutation that maps to a webhook where the input and output interfaces are defined.

An Action definition is made up of:
Let’s look at a simple “Hello, World!” example. First, we define an Action query as follows:
The Action above defines a function called hello that accepts a string variable called name as input. The function returns a HelloResponse object, which is a custom type that’s defined as follows:
With the interface defined, all that’s left is the implementing the webhook service and a URL that’s accessible to your Hasura instance. Here’s an implementation written for Express.js that has been created with the help of Hasura’s CodeGen assistant:
In Hasura, you can build apps using a software architecture known as event-driven programming. This is a design pattern that decouples complex state management from a monolithic backend and defers it to individual microservices or serverless functions that execute business logic. This allows for the building of apps that are highly robust and scalable.
With event programming, you can easily handle many use cases, such as:
In PostgreSQL, you can create triggers that executes SQL statements or stored procedures when an event such as INSERT, UPDATE or DELETE occurs. A trigger is associated with a table or view and can fire before or after an event.
In Hasura, event triggers are similarly defined and are used for invoking web hooks when an event is fired. A web hook in this context is quite similar to the ones we just discussed in the “Actions” section. Event triggers can also be invoked manually via the console or API.
Below is an example of an event trigger definition in YAML format:
An Event trigger can be defined with headers (for authentication purposes) and usually contains a payload that’s sent to the webhook service for data processing. The only response expected is 200 status. The actual results, if needed, are posted back through a different route — such as posting a GraphQL mutation, or sending an email notification, and so on.
Frontend apps can receive the results by querying through GraphQL subscription. This type of architecture makes it easy to build non-blocking interactive real-time apps that have great user experience. The codebase is also easier to maintain, test and scale for developers.
Scheduled Triggers are time-based events that execute custom business logic via webhooks. There are two types that you can define:
Scheduled triggers can be created either via the web console or via the metadata API. The screenshot below shows the web console example.

Web console cron trigger
The metadata API allows creation of scheduled events using application code. The example below shows how you would structure the creation of a one-time event using the API:
Hasura provides a number of fault-tolerance mechanisms that ensure scheduled triggers run successfully. For example, let’s say an event that was scheduled for 2:00pm wasn’t processed because Hasura was down at 1:59pm. When Hasura is brought back online, it will rehydrate events and re-run missed scheduled triggers based on a customizable tolerance configuration. The default setting is six hours.
Hasura also provides a flexible retry configuration in case of HTTP failures. You can configure the number of retries and the timeout between each retry. Every trigger invocation is logged — which you can access later for inspection.
Use cases for scheduled triggers include:
The ideal development workflow for any software project is to set up multiple environments for development, staging and production. This workflow simplifies testing and ensures developers don’t accidentally delete or corrupt production data when deploying new versions of their software.
In order to transfer the state of a development environment to staging and production, migration files are required. With Hasura, you’ll need:
Hasura metadata is made up of a snapshot of configuration files that keeps track of:
You’ll need to install Hasura CLI to be able to create and apply these migration files to a different Hasura instance. These migration files can be versioned and updated incrementally as the schema changes during development.
Creating and applying SQL migration files is done using the hasura migrate command, while the metadata migration files are handled using hasura metadata command. The CLI command also enables you to:
Hasura has a special Docker cli-migration image that automatically applies migrations when the server starts. This image also contains the Hasura CLI tool, which can be used to run CI/CD scripts in your workflow.
The easiest way to deploy Hasura for production is by using Hasura Cloud. This is the recommended option, since you get enterprise features not available on the open-source version. Fortunately, Hasura doesn’t lock you in to their platform, unlike most backend-as-a-service providers (BaaS). There are One-click Deploy service providers that you can take advantage of in platforms such as:
You can also deploy Hasura on any Kubernetes platform such as Google Cloud. AWS is also supported, but you have to go through a number of steps to get it working. Hosting externally does give you freedom and more pricing options. However, all the above options require you to set up additional services for:
In the next section, we’ll briefly look at how you can use NHost to further simplify development of Hasura backend applications.
NHost is an open-source BaaS provider that aims to compete with platforms such as Firebase. Their backend stack consists of:
The platform comes with a client library called nhost-js-sdk which is used for frontend authentication and file management. The storage service supports image optimization, which saves us the hassle of integrating another service for images.
At the the time of writing, NHost currently provides a 14-day trial for its service. A free tier will be launched soon. There’s a Docker version called Hasura Backend Plus which you can deploy locally on your machine.
To conclude, Hasura GraphQL Engine and PostgreSQL database is quite revolutionary in terms of how fast you can build backends for both mobile and web applications, without writing lines of code. Most of the computation and analytical logic can be handled by PostgreSQL, while the rest of the custom business logic can be implemented using microservices or serverless functions.
Adopting Hasura means you’ll be able to launch faster and build a product that’s highly performant, secure, fault tolerant, scalable and easy to maintain. Having no infrastructure hassles will also reduce your operating costs and allow you to focus on frontend development and the user-facing part of your app.
Although Hasura isn’t as feature rich as other open-source alternatives such as Parse platform, it supports versioning and environment staging, which is a crucial requirement for CI/CD development workflows. Because it’s open source, you’re protected from risks posed by vendor lock-ins.
I write clean, readable and modular code. I love learning new technologies that bring efficiencies and increased productivity to my workflow.
Learn the basics of programming with the web’s most popular language – JavaScript
A practical guide to leading radical innovation and growth.
© 2000 – 2021 SitePoint Pty. Ltd.
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

source