By using PostgreSQL as our backing database engine, we get to leverage all the upsides already included in its vast ecosystem.

Unless otherwise stated, the below queries should be ran using the OrbisDB SQL Editor.

Joins

Relational data model, as the name suggests, often has data that relates to one another. This is the core concept of relational databases and is an inevitable part of using a relational database - if used correctly.

OrbisDB supprots joins in Raw SQL and GraphQL query modes. The built-in query builder does not support joins, yet.

Joins are often used with foreign keys and indexes.

Here’s a great visual guide to PostgreSQL joins.

LEFT JOIN example

This is a Raw SQL left join example. GraphQL handles joins implicitly.

SELECT
    table_a.column_a,
    table_b.column_b
FROM
    tableA
LEFT JOIN table_b
    ON table_a.column = table_b.column;

Relations

One-to-One

One-to-Many

Many-to-Many

Foreign keys

OrbisDB relations

Views

There are some queries that are ran often and may include things such as JOIN, filters, etc. In order to avoid writing them over and over again, PostgreSQL comes with built-in Views.

You can think of Views as virtual tables that don’t actually exist, but can be queried as such. Views are queries that get executed every time you use it.

They are useful when complex relations and aggregations needs to be used.