Dynamic Database Connection Routing

Available on: Cloud Professional, Cloud Enterprise, Self-hosted Enterprise

Introduction

Hasura Cloud and Enterprise customers can leverage dynamic routing to implement different kinds of database topology patterns with Hasura. You can group different databases using a new Metadata configuration object called 'connection-set' and leverage the Kriti template to define custom 'connection templates' to route GraphQL requests based on different request parameters such as session variables, headers and tenant IDs.

Caution

• Dynamic Database Connection Routing is available only for Postgres backends.
• The routing is applicable only for non-admin GraphQL requests.

Following are some of the top use cases for this feature:

Connect with different database credentials

If you are integrating with a vendor IAM or wish to use database RLS, you will want to use different connection credentials per request. For example, in Hasura, you may use a session variable like x-hasura-role and use a specific connection for that role.

No-stale reads when read replicas are configured

If read replicas are configured for a given source, then all query operations are routed to the read replicas, which sometimes leads to stale reads because of replication lag. You can force certain query operations to the primary connection by using some operation context variable like an operation name or a special request header.

Route to a specific shard or node group in a distributed database

In a distributed database system like YugabyteDB or CockroachDB, sometimes, you may want to route the request to a specific node. You can achieve this in Hasura using a connection template.

There are two configurable parameters in the source configuration useful for dynamic routing:

1. Connection set
2. Connection template

Connection set

You can define a set of connections that are available for routing. You can refer the members of this connection set in the connection template by their unique name using the variable $.connection_set.<member's name>.

Connection template

The logic for connection routing is defined as a Kriti template. This can resolve to the following:

1. A member of the connection set

   The connection will be routed to a member of the connection set. The request will fail if the name is not defined in the connection set. To use this, the template should resolve to the following variable:

   $.connection_set.<name of a member of the connection set>

2. Predefined connections

   The query can also be routed to connections that are already defined:

   1. Primary source

   The query will be routed to the connection specified under connection_info. To use this, the template should resolve to the following variable: $.primary

   2. Read replicas

   The query will be routed to a randomly chosen member from the set of read replicas. Please note that for mutations, this will fail. To use this, the template should resolve to the following variable: $.read_replicas

   3. Default

   The connection template resolving to the $.default variable directs the query to follow the default behavior. E.g., if read replicas are configured, then all queries and subscriptions are routed to read replicas and mutations are routed to the primary connection. Otherwise, Hasura will execute all GraphQL queries on the primary connection.

The context for the connection template contains the request variables ($.request). The request variable includes the following:

1. HTTP headers

   These are client headers associated with the request. Users can refer to the headers in the template using the variable $.request.headers. Header names are case insensitive; hence their names are provided in lowercase in the template context. An example template using HTTP headers:

   {{ if ($.request.headers?[no-stale-read] == "true")}}
     {{$.primary}}
   {{ else }}
     {{$.read_replicas}}

   For this example, if the header no-stale-read is set to true, then Hasura will route the requests to the primary source. Otherwise, it will use the read_replicas.

2. Session variables

   These are the key-value pairs returned from your authentication service. They can be referred to in the template using the variable $.request.session. The session variable keys always contain x-hasura-* as the prefix. An example template using session variables:

   {{ if $.request.session?["x-hasura-role"] == "dev" }}
     {{ $.​connection_set.dev_db }}
   {{ else }}
     {​{ if ​$.connection_set?[$.request.session.x-hasura-tenant-id] != null }}
       {{ ​$.connection_set[$.request.session.x-hasura-tenant-id] }}
     {{ else }}
       {{ $.default }}
     {{ end }}
   {{ end }}

   For this example, if the session variable x-hasura-role is set to dev, then Hasura will route the requests to the dev_db in connection_set. Otherwise, Hasura will look up if there is a connection member with the name of x-hasura-tenant-id and route to that member if it exists. If not, then it will fall back to the default behavior.

3. Graphql query parameters

   These are the operation type and operation name for the GraphQL query. They can be referred to in the template using the variable $.request.query. This variable can only have two keys: operation type and operation name (optional). An example template using the query parameters:

   {{ if ($.request.query.operation_type == "mutation") }}
       {{$.primary}}
   {{ else }}
       {{$.read_replicas}}
   {{ end }}

   For this example, Hasura will route to the primary database for mutations and read_replicas for everything else.

An example of the request context:

{
  "headers": {
    "x-hasura-role": "user",
    ...
  },
  "session": {
    "x-hasura-role": "user",
    "x-hasura-org": "hasura",
    ...
  },
  "query": {
    "operation_type": "query",
    "operation_name": "MyQuery"
  }
}

You can build your connection template using these variables. An example of a connection template is given below:

Example:

{{ if ($.request.query.operation_type == "query") || ($.request.query.operation_type == "subscription") }}
  {{ if $.request.session?["x-hasura-role"] == "developer" }}
      {{if $.request.headers?["route-to-read-replicas"] == "true"}}
        {{$.read_replicas}}
      {{else}}
        {{$.connection_set.dev_db}}
      {{end}}
  {{else}}
      {{$.default}}
  {{ end }}
{{else}}
    {{ if $.request.session?["x-hasura-role"] == "priority-user" }}
        {{$.connection_set.fast_db}}
    {{else}}
        {{$.primary}}
    {{ end }}
{{ end }}

Explanation:

• For query/subscription GraphQL operations, if the x-hasura-role is developer, then use read_replicas if the header route-to-read-replicas is set to true. Otherwise, use the dev_db from the connection set.
• For query/subscription GraphQL operations, if the x-hasura-role is not developer, then route using the default behavior.
• For mutations, if the x-hasura-role is set to priority-user, then use the fast_db from the connection set; else, use the primary connection.

Setting up connection set and connection template

Console

To access Dynamic DB Routing, navigate to the Dynamic DB Routing tab in the Edit Data Source page.

Select the template you want to use from the list of available templates, or click on Custom Template to create your own template.

To add a new database connection, click on the Add Connection button. Enter the necessary connection details in the modal that opens up.

You can also edit or delete an existing connection by clicking on the Edit Connection or Remove button next to the connection.

Adjust the connection template settings to suit your needs, then click Update Connection Template to save the changes.

CLI

You can add connection set and connection template for a database by adding their config to the /metadata/databases/database.yaml file:

- name: <db-name>
  kind: postgres
  configuration:
    connection_info:
      database_url:
        from_env: <DATABASE_URL_ENV>
      pool_settings:
        idle_timeout: 180
        max_connections: 50
        retries: 1
    connection_template:
      template: |
        {{ if $.request.session?["x-hasura-role"] == "user" }}
          {{$.primary}}
        {{else}}
          {{$.connection_set.db_1}}
        {{ end }}
    connection_set:
      - name: db_1
        connection_info:
          database_url:
            from_env: <DATABASE_URL_ENV>

Apply the Metadata by running:

hasura metadata apply

API

The connection set and connection template can be configured via the pg_add_source Metadata API.

POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "pg_add_source",
  "args": {
    "name": "db",
    "configuration": {
      "connection_info": {
        "database_url": {
          "from_env": "<DATABASE_URL_ENV>"
        },
        "pool_settings": {
          "idle_timeout": 180,
          "max_connections": 50,
          "retries": 1
        }
      },
      "connection_template": {
        "template": "{{ if $.request.session?[\"x-hasura-role\"] == \"user\" }} {{$.primary}} {{else}} {{$.connection_set.db_1}} {{ end }}"
      },
      "connection_set": [
        {
          "name": "db_1",
          "connection_info": {
            "database_url": {
              "from_env": "<DATABASE_URL_ENV>"
            }
          }
        }
      ]
    }
  }
}

Testing connection template

Hasura also provides a Metadata API (pg_test_connection_template) for testing the connection template for a source. It simulates an actual GraphQL request without hitting the database.

An example call to the pg_test_connection_template API is given below:

Request:

POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "pg_test_connection_template",
  "args": {
    "source": "source_name",
    "request_context": {
      "headers": {
        "header_name": "header_value"
      },
      "session": {
        "session_var": "session_var_value"
      },
      "query": {
        "operation_type": "query",
        "operation_name": "op_name"
      }
    }
  }
}

Success Response:

{
  "result": {
    "routing_to": "connection_set",
    "value": "connection_set_member_name"
  }
}

Limitations

Postgres schema of connection set

Hasura derives the GraphQL schema based on the primary connection only (i.e., connection_info). The Postgres schema of all members of connection_set should be identical to that of the primary connection. Hasura does not make any checks to ensure the Postgres schema consistency, and users should guarantee the same. Also, you can only configure/update the primary database on the Hasura Console. Other databases are not accessible via the Console.

Caution

A GraphQL request may result in a runtime exception when it is being executed on a member of the connection set that differs in the Postgres schema from the primary connection.

Event Triggers

Hasura Event Triggers are triggered only for mutations executed on the primary connection. Mutations routed to the members of the connection set will not trigger Event Triggers.

Migrations

Hasura CLI Migrations cannot be applied on a connection set member.