How to Build Your Backend with Hasura and PostgreSQL

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.

Why PostgreSQL

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:

• you can model data using table inheritance
• it has better concurrency control for multi-user environments (you can write massive amounts of data more efficiently)
• it’s fault tolerant and is less prone to data corruption
• it supports a number unique data types such as JSON and Spatial, which is useful for finance and research-related applications

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:

• Views, a stored query that can help simplify complex queries
• Functions & Operators, such as date formatting, pattern matching, arithmetic operations
• Extensions, addons that extends PostgreSQL’s capabilities (such as PostGIS)
• Procedural Languages, programming languages for writing user-defined functions, stored procedures, triggers and extending the standard SQL (such as PL/pgSQL)

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:

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:

EXAMPLE 1

You can retrieve a list of online users that are currently active using a view:

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)); 

EXAMPLE 2

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:

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

In the next section, we’ll focus on Hasura’s features.

What is Hasura?

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:

• 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

Hasura doesn’t support user authentication, so you’ll need to integrate Hasura and your frontend application with a provider such as:

• Clerk
• Auth0
• AuthGuardian
• Firebase
• Magic

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.

Launching Hasura

There are a couple of ways you can quickly launch and run a Hasura instance:

1. Docker

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:

# 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 

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.

2. Cloud

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.

Hasura Cloud comes with several new features not available in the open-source version, which include:

• Monitoring dashboard for errors, connections, subscriptions, slow queries and other operations
• GraphQL caching for improved server and client data fetching performance
• Rate limiting for preventing malicious users and DDoS attacks from compromising your API
• Regression testing for running test suites such as checking changes in your dev instance against your production instance

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:

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

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:

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

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.

Hasura Features

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.

Data Manager

Hasura comes with a visual designer for modeling your data layer. This allows you to:

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

When it comes to columns, Hasura supports a rich set of data types which include:

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

You can also add custom types using the CREATE TYPE SQL command. Next, we’ll look at how data is authorized in Hasura.

Continue reading How to Build Your Backend with Hasura and PostgreSQL on SitePoint.