We are thrilled to announce that Hasura now supports Remote Joins across all supported data sources, including PostgreSQL, Snowflake, MySQL, SQL Server, BigQuery, Oracle, Athena, and Remote Schemas!

In the world of modern application development, building complex data relationships across various sources has become a common requirement. As databases and data sources grow in complexity, developers often find themselves facing the challenge of integrating data from different databases, APIs, or remote GraphQL endpoints.

Thankfully, Hasura’s GraphQL engine provides a powerful solution through its remote relationships, also known as “Remote Joins.”

In this blog, we will explore the concept of remote relationships and how they can be leveraged to join data across tables and remote data sources, empowering developers to build sophisticated and data-rich applications.

Understanding remote relationships

At its core, a remote relationship in GraphQL allows you to connect data across different tables and remote data sources. These sources can include:

1. Database to database relationships: This type of remote relationship enables you to join data between two different database sources. For instance, you can link order information stored in one PostgreSQL database with user information stored in another PostgreSQL or even a SQL Server database. GraphQL acts as the glue that seamlessly integrates the data from both sources.

2. Database to GraphQL services: Here, you can merge data across tables with remote GraphQL APIs that we call Remote Schemas. For example, you might combine customer data from your database with account data from external services like Stripe, Spotify, or Auth0. This integration allows you to consolidate information from various sources into a single, cohesive GraphQL query.

3. GraphQL services to database relationships: In this scenario, you can connect data from Remote Schemas (representing services like Stripe, Spotify, or Auth0) to customer data from your database. This bidirectional relationship unlocks a plethora of possibilities for building feature-rich applications that rely on data spanning across multiple services.

4. Custom business logic to database relationships: Actions in GraphQL often correspond to REST APIs. With this type of remote relationship, you can join data across tables and Actions, such as fetching user data from your database and combining it with the response from a createUser action using the id field.

How Hasura performs Remote Joins?

When performing a RemoteJoin between “table A” and “table B,” Hasura will first query “table A” for its rows. From these rows Hasura will collect all the IDs involved in the join to “table B.” It will then query “table B” as a separate query, filtering the rows by the IDs collected from “table A.” Hasura will then stitch the rows returned from “table B” into the results from “table A” to provide the final response to the original GraphQL query.

This approach avoids the usual N+1 query anti-pattern that can occur with naive implementations that join between two tables in different databases. The N+1 query anti-pattern is where you query “table B” for every row returned by the query to “table A,” which is inefficient.

In Hasura, join queries are optimized to query each table once, limiting the number of separate queries issued to the smallest possible number.

How do Remote Joins enhance application development?

Businesses today are not confined to a single database. Data exists in a multitude of sources — on-premises, in the cloud, or distributed across multiple systems. To maintain a competitive edge, organizations must harness the power of these disparate data sources and seamlessly combine them to drive meaningful use cases and insights.

In a matter of minutes, developers can create a single GraphQL API using Hasura that accesses data from multiple sources, including GraphQL APIs (Remote Schemas) and databases, in real time, without the need for complex data integration processes. This means developers can create powerful applications that access data from different sources, without worrying about data silos.

In traditional SQL, joining tables from different databases requires maintaining a duplicate copy of the data from one database in the other database that is needed to perform the join. This process was not only time-consuming but also required a lot of resources. With Hasura’s remote joins, developers can perform joins across data sources without copying any data. This allows organizations to federate their data into a single self-service data access layer.

By providing a unified view of data, Hasura’s remote joins can significantly reduce the time it takes to develop data intensive applications. Developers can quickly prototype applications, test different use cases, and get feedback from stakeholders. This enables enterprises to bring applications and data products to market faster, giving them a competitive edge.

Simplified data management

Handling and managing data across multiple databases is a significant challenge for developers. It often involves writing complex queries and spending countless hours on data management tasks. However, with Hasura’s Remote Joins, these challenges become a thing of the past. Remote Joins provide a way to access and unify data across various databases as if it were coming from a single data source.

Boosts development speed

By offering a unified API across different databases, Hasura saves developers from the tiresome task of orchestrating data from various sources. This allows developers to focus more on the core functionality of the application, ultimately boosting the development speed.

Streamlined workflow

A GraphQL API that bridges different data sources simplifies your workflow. It not only helps you fetch data from multiple sources in a single request but also keeps the interface clean and organized, significantly streamlining the development process.

Getting started with Remote Joins for databases

Check out my colleague Vaishnavi’s demo from her talk at HasuraCon 2023 on joining data from Postgres to MySQL.

Step 1: Add two database sources. ​

Add a source database as described here and track the required tables. Then, repeat the process to add your target database.

Step 2: Define and create the relationship. ​

A remote database relationship is defined alongside the source database table (that is, the source side of the join).

The following fields can be defined for a Remote Schema relationship:

• Relationship type: Either object or array - similar to normal relationships. Hasura supports both many-to-one (object) and one-to-many (array) relationships.

• Relationship name: A name for the relationship.

• Reference source: The name of the target database (that is, the target side of the join).

• Reference table: The table in the target database source that should be joined with the source table.

• Field mapping: A mapping between fields in the source table and their corresponding fields in the target table, just as a foreign key relationship would be defined by such mapping within a single database.

For example, say we have a table Album(AlbumId int) in the source database and a table Track(id int, name text) in the target database.

We can create an array remote database relationship between the AlbumId joining the Album table to the Track table using the Album.AlbumId and Track.AlbumId fields.

Conclusion

When it comes to generating APIs for multiple data sources, Hasura offers a powerful and developer-friendly solution. Its ease of use, automation, and feature-rich capabilities provide a significant advantage over the DIY approach, which requires significant time and effort to design and implement custom solutions.

With Hasura, developers can create a single GraphQL API that can access data from multiple sources allowing developers to focus more on building innovative applications that leverage diverse data sources, without getting bogged down by the complexities of API generation.

If you’re looking to simplify the process of joining data from multiple sources, Hasura is the way to go.

📚 Documentation and resources

To help you get started, we’ve prepared detailed documentation, guides, and examples:

🚀 Get started today!

We can’t wait to see the amazing applications you’ll build using Hasura and Native Queries. Get started today by signing up for Hasura Cloud and connecting to one of the supported databases.

If you have any questions or need assistance, feel free to reach out to our team on Discord or GitHub.

Originally published at hasura.io on August 1, 2023.