Hasuraを使ってみた

# 概要
- GraphQLとHasuraの紹介
- Hasuraの導入方法
- SQLとGraphQLのクエリの違い

# GraphQLとは
- APIのための問い合わせ言語
- RESTに比べて自由度の高いリクエストが可能
- 強い型付け

# Hasura
- PostgreSQLを用いたGraphQLサーバー
- テーブル構造や外部キーから自動でSchemaを構築
- ハイパフォーマンス
- 使いやすいUI

# 環境構築
## Hasuraの導入

[Getting started](https://hasura.io/docs/latest/graphql/core/getting-started/index/)の[Using Docker](https://hasura.io/docs/latest/graphql/core/getting-started/docker-simple/)にしたがって環境構築します

### docker-compose.ymlの取得
```
wget https://raw.githubusercontent.com/hasura/graphql-engine/stable/install-manifests/docker-compose/docker-compose.yaml
```

### docker-compose.ymlの編集
ホストOSからPostgreSQLにアクセスできるように`postgres`の`5432`ポートを開ける

```
  postgres:
    image: postgres
    ports:
    - "5432:5432"
```

### 起動
```
$ docker-compose up -d
```

# サンプルデータベースの構築

今回は[PostgreSQL Tutorialのサンプルデータベース](https://www.postgresqltutorial.com/postgresql-sample-database/)を使用します

## サンプルデータベースをダウンロード

```
$ wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
$ unzip dvdrental.zip
```

## リストア

```
$ pg_restore -h localhost -U postgres -d postgres ./dvdrental.tar
```

# テーブル・外部キーのTrack

1. コンソールの`Data -> Schema`セクションに移動します
2. `Untracked foreign-key relations`の横にある`Track All`ボタンをクリックします

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/314315/6c153b01-4846-73dd-94be-93faadaaf290.png)

同様の手順を外部キーに対しても実行します。


# SQLとGraphQLのクエリの比較

## SELECT

```sql:SQL
SELECT
  first_name,
  last_name,
  email
FROM
  customer;
```

```graphql:GraphQL
query {
  customer {
    first_name
    last_name
    email
  }
}
```

## ORDER BY

```sql:SQL
SELECT
  first_name,
  last_name
FROM
  customer
ORDER BY
  first_name;
```

```graphql:GraphQL
query {
  customer(order_by: {first_name: asc}) {
    first_name
    last_name
  }
}
```

### DESC

```sql:SQL
SELECT
  first_name,
  last_name
FROM
  customer
ORDER BY
  last_name DESC;
```

```graphql:GraphQL
query {
  customer(order_by: {last_name: desc}) {
    first_name
    last_name
  }
}
```

## SELECT DISTINCT
Hasuraが生成するSchemaに`distinct`は存在しません。
`distinct_on`に必要なカラムを列挙することで実現します。

```sql:SQL
SELECT DISTINCT
  staff_id,
  customer_id
FROM
  payment;
```

```graphql:GraphQL
query {
  payment(distinct_on: [staff_id, customer_id]) {
    staff_id
    customer_id
  }
}
```

## WHERE

```sql:SQL
SELECT
  last_name,
  first_name
FROM
  customer
WHERE
  first_name = 'Jamie';
```

```graphql:GraphQL
query {
  customer(where: {first_name: {_eq: "Jamie"}}) {
    last_name
    first_name
  }
}
```

### AND

```sql:SQL
SELECT
  last_name,
  first_name
FROM
  customer
WHERE
  first_name = 'Jamie'
  AND last_name = 'Rice';
```

```graphql:GraphQL
query {
  customer(where: {_and: [
    {first_name: {_eq: "Jamie"}}, 
    {last_name: {_eq: "Rice"}}
  ]}) {
    last_name
    first_name
  }
}
```

### OR

```sql:SQL
SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  last_name = 'Rodriguez'
  OR first_name = 'Adam';
```

```graphql:GraphQL
query {
  customer(where: {_or: [
    {last_name: {_eq: "Rodriguez"}},
    {first_name: {_eq: "Adam"}}
  ]}) {
    last_name
    first_name
  }
}
```

### IN

```sql:SQL
SELECT
  first_name,
  last_name
FROM
  customer
WHERE 
  first_name IN ('Ann','Anne','Annie');
```

```graphql:GraphQL
query {
  customer(where: {first_name: {_in: ["Ann", "Anne", "Annie"]}}) {
    last_name
    first_name
  }
}
```

### LIKE

```sql:SQL
SELECT
  first_name,
  last_name
FROM
  customer
WHERE 
  first_name LIKE 'Ann%'
```

```graphql:GraphQL
query {
  customer(where: {first_name: {_like: "Ann%"}}) {
    last_name
    first_name
  }
}
```

## LIMIT

```sql:SQL
SELECT
  film_id,
  title,
  release_year
FROM
  film
ORDER BY
  film_id
LIMIT 5;
```

```graphql:GraphQL
query {
  film(order_by: {film_id: asc}, limit: 5) {
    film_id
    title
    release_year
  }
}
```

## OFFSET

```sql:SQL
SELECT
  film_id,
  title,
  release_year
FROM
  film
ORDER BY
  film_id
LIMIT 4 OFFSET 3;
```

```graphql:GraphQL
query {
  film(order_by: {film_id: asc}, limit: 5, offset: 3) {
    film_id
    title
    release_year
  }
}
```

## Joins
- Hasuraには直接JOINに代わるクエリは存在しない
- [Nested object queries](https://docs.hasura.io/1.0/graphql/manual/queries/nested-object-queries.html)で実現出来ない場合は個別にSchemaを作成する必要がある

```graphql:GraphQL
query {
  customer {
    customer_id
    first_name
    last_name
    email
    payments {
      amount
      payment_date
    }
  }
}
```

## GROUP BY
- 基本的に、GROUP BYを使用するにはViewを使用する必要がある
https://docs.hasura.io/1.0/graphql/manual/schema/views.html#example-group-by-and-then-aggregate

- しかしながら、一部デフォルトで対応可能なクエリも存在する

### GROUP BY with SUM

```sql:SQL
SELECT
   customer_id,
   SUM (amount)
FROM
   payment
GROUP BY
   customer_id;
```

```graphql:GraphQL
{
  customer {
    customer_id
    payments_aggregate {
      aggregate {
        sum {
          amount
        }
      }
    }
  }
}
```

### GROUP BY with COUNT

```sql:SQL
SELECT
   staff_id,
   COUNT (payment_id)
FROM
   payment
GROUP BY
   staff_id;
```

```graphql:GraphQL
query {
  staff {
    staff_id
    payments_aggregate {
      aggregate {
        count
      }
    }
  }
}
```

### パフォーマンス上の注意
- 上記のクエリは一見良さそうに見えるが、Joinが発生するのでパフォーマンスは落ちる
- 以下は`GROUP BY with COUNT`のクエリの実行計画

```text:SQL
HashAggregate  (cost=326.94..326.96 rows=2 width=10) (actual time=213.397..213.417 rows=2 loops=1)
  Group Key: staff_id
  ->  Seq Scan on payment  (cost=0.00..253.96 rows=14596 width=6) (actual time=0.047..103.755 rows=14596 loops=1)
Planning Time: 0.177 ms
Execution Time: 213.514 ms
```

```text:GraphQL
Aggregate  (cost=618.51..618.52 rows=1 width=32)
  ->  Nested Loop Left Join  (cost=308.70..618.48 rows=2 width=36)
        ->  Seq Scan on staff  (cost=0.00..1.02 rows=2 width=4)
        ->  Aggregate  (cost=308.70..308.71 rows=1 width=32)
              ->  Seq Scan on payment  (cost=0.00..290.45 rows=7298 width=0)
                    Filter: (staff.staff_id = staff_id)
  SubPlan 1
    ->  Result  (cost=0.00..0.01 rows=1 width=32)
```

## INSERT

```graphql:GraphQL
mutation {
  insert_category(
  	objects: {
      category_id: 17,
      name: "VR"
    }
  ) {
    returning {
      category_id
      name
      last_update
    }
  }
}
```

## UPDATE

```graphql:GraphQL
mutation {
  update_category(
    where: {
      category_id: {_eq: 17}
    },
    _set: {name: "AR"}
  ) {
    returning {
      category_id
      name
      last_update
    }
  }
}
```

## UPSERT

```graphql:GraphQL
mutation {
  insert_category(
    objects: {
      category_id: 17,
      name: "VR"
    },
    on_conflict: {
      constraint: category_pkey,
      update_columns: [name]
    }
  ) {
    returning {
      category_id
      name
      last_update
    }
  }
}
```

## DELETE

- `affected_rows`で削除したレコード数が取得できる

```graphql:GraphQL
mutation {
  delete_category(
    where: {
      category_id: {_eq: 17}
    }
  ) {
    affected_rows
  }
}
```

# まとめ

- Hasuraの導入方法・使用方法を紹介した

- 自動で作成されるSchemaで多くのデータ操作が可能

- データ設計だけでAPIが作れるのは管理するものが減って嬉しい