BigQuery: Data Validations

Introduction

Many times, we need to perform validations of input data before selecting, inserting or updating objects.

BigQuery does not natively support constraints, primary or foreign keys.

However, if you would like the validation logic to be at the GraphQL API layer, Hasura permissions can be used to add your validation.

These solutions are explained in some more detail below.

Using Hasura permissions

If the validation logic can be expressed declaratively using static values and data from the database, then you can use row level permissions to perform the validations. (Read more about Authorization).

Example 1: Validate that the editor role can only select articles with empty titles.

Suppose, we have the following tables in our schema:

authors (id int, name text)
articles (id int, title text, author_id int, body text)

Now, we can create a role editor and add a select validation rule as follows:

Console

CLI

You can add roles and permissions in the tables.yaml file inside the metadata directory:

table:
  dataset: bigquery
  name: articles
select_permissions:
  - role: editor
    permission:
      columns: '*'
      filter:
        title:
          _eq: ''

Apply the Metadata by running:

hasura metadata apply

API

You can add an select permission rule by using the bigquery_create_select_permission Metadata API:

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

{
  "type": "bigquery_create_select_permission",
  "args": {
    "source": "<db_name>",
    "table": {
      "name": "articles",
      "dataset": "bigquery"
    },
    "role": "editor",
    "permission": {
      "filter": {
        "title": {
          "_eq": ""
        }
      }
    }
  }
}

If we set the X-Hasura-Role to editor and try to select all articles, only those with empty string titles will be returned:

GraphiQL

Query Variables

Request Headers

Documentation Explorer

⚲

No Schema Available

Example 2: Validate that author roles can only select an article if the author_id matches X-Hasura-User-Id

Suppose, we have 2 tables:

authors (id int, name text)
articles (id int, title text, author_id int, body text)

Also, suppose there is an object relationship articles.author defined as:

articles.author_id -> authors.id

Now, we can create a role author and add an select validation rule as follows:

Console

CLI

You can add roles and permissions in the tables.yaml file inside the metadata directory:

table:
  dataset: bigquery
  name: articles
select_permissions:
  - role: author
    permission:
      columns: '*'
      filter:
        author_id:
          _eq: X-Hasura-User-Id

Apply the Metadata by running:

hasura metadata apply

API

You can add a select permission rule by using the bigquery_create_select_permission Metadata API:

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

{
  "type": "bigquery_create_select_permission",
  "args": {
    "source": "<db_name>",
    "table": {
      "name": "articles",
      "dataset": "bigquery"
    },
    "role": "author",
    "permission": {
      "filter": {
        "author_id": {
          "_eq": "X-Hasura-User-Id"
        }
      }
    }
  }
}

If we set the X-Hasura-Role and X-Hasura-User-Id headers, then try to select all articles, only those where the author_id matches the current X-Hasura-User-Id will be returned:

GraphiQL

Query Variables

Request Headers

Documentation Explorer

⚲

No Schema Available

Note

Permissions are scoped to a user's role. So, if a validation check needs to be global then you will have to define it for all roles which have insert/update permissions.