remote relationships on a database

remote-relationships-on-a-database.md

Remote relationships to a database

Cross database remote relationships help fetch related data stored in a different database. For example, such a query is possible with this feature:

{
  # a table on sql server
  product {
    name

    # yearly_data is an array remote relationship to a postgres table
    # 'product_yearly_data' with the join condition
    # product.domain_id = product_yearly_data.domain_id AND
    # product.sku = product_yearly_data.sku
    yearly_data (limit: 10) {
      year
      conversion_rate
    }

  }
}

The execution of this query is as follows:

1. Execute this query on SQL Server:

   product {
     name
     # extra join columns, determined from the relationship's definition
     domain_id
     sku
   }

2. Collect all the join column values from the previous step and construct a temporary table on the target with the following schema (we don't actually do this but simplifies the explanation):

   create table temp_table (
     # a unique identifier for (domain_id, sku)
     temp_table_pk int
     domain_id int
     sku int
   )

3. Execute a query of this form on the target database:

   {
     temp_table {
       temp_table_pk
   
       # yearly_data is now a *local* array relationship from
       # temp_table to product_yearly_data table with the join condition
       # temp_table.domain_id = product_yearly_data.domain_id AND
       # temp_table.sku = product_yearly_data.sku
       yearly_data (limit: 10) {
         year
         conversion_rate
       }
     }
   }

4. Using the temp_table_pk, we join the data in step 1 with the data from step 3 on the haskell layer and return the final response.

Like mentioned above, we don't actually create a temporary table but we delegate steps 2 and 3 to the backend using mkDBRemoteRelationshipPlan function on BackendExecute typeclass whose signature is as follows:

  mkDBRemoteRelationshipPlan ::
    forall m.
    ( MonadError QErr m,
      MonadQueryTags m
    ) =>
    UserInfo ->
    SourceName ->
    SourceConfig b ->

    -- | Rows in the temporary table encoded as json objects
    NonEmpty J.Object ->

    -- | The schema of the temporary table
    HashMap FieldName (Column b, ScalarType b) ->

    -- | This is the name of the primary key column in the temporary table
    -- which *must* be present in the output response
    FieldName ->

    -- | 1. remote relationship's alias
    --   2. selection set of the relationship along with the join condition
    (FieldName, SourceRelationshipSelection b (Const Void) UnpreparedValue) ->
    m (DBStepInfo b)

See the implementation for Postgres on main and for SQL Server in this commit.

For testing, see the configuration posted here: hasura/graphql-engine#7106 (comment)