Go to Qiita Advent Calendar 2024 Top
LoginSignup
35
17

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@kyamamoto9120(Kazumasa Yamamoto)inユニークビジョン株式会社

Hasuraを使ってみた

Last updated at Posted at 2020-02-28

概要

  • GraphQLとHasuraの紹介
  • Hasuraの導入方法
  • SQLとGraphQLのクエリの違い

GraphQLとは

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

Hasura

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

環境構築

Hasuraの導入

Getting startedのUsing Dockerにしたがって環境構築します

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のサンプルデータベースを使用します

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

$ 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

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

SQLとGraphQLのクエリの比較

SELECT

SQL
SELECT
  first_name,
  last_name,
  email
FROM
  customer;
GraphQL
query {
  customer {
    first_name
    last_name
    email
  }
}

ORDER BY

SQL
SELECT
  first_name,
  last_name
FROM
  customer
ORDER BY
  first_name;
GraphQL
query {
  customer(order_by: {first_name: asc}) {
    first_name
    last_name
  }
}

DESC

SQL
SELECT
  first_name,
  last_name
FROM
  customer
ORDER BY
  last_name DESC;
GraphQL
query {
  customer(order_by: {last_name: desc}) {
    first_name
    last_name
  }
}

SELECT DISTINCT

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

SQL
SELECT DISTINCT
  staff_id,
  customer_id
FROM
  payment;
GraphQL
query {
  payment(distinct_on: [staff_id, customer_id]) {
    staff_id
    customer_id
  }
}

WHERE

SQL
SELECT
  last_name,
  first_name
FROM
  customer
WHERE
  first_name = 'Jamie';
GraphQL
query {
  customer(where: {first_name: {_eq: "Jamie"}}) {
    last_name
    first_name
  }
}

AND

SQL
SELECT
  last_name,
  first_name
FROM
  customer
WHERE
  first_name = 'Jamie'
  AND last_name = 'Rice';
GraphQL
query {
  customer(where: {_and: [
    {first_name: {_eq: "Jamie"}}, 
    {last_name: {_eq: "Rice"}}
  ]}) {
    last_name
    first_name
  }
}

OR

SQL
SELECT
  first_name,
  last_name
FROM
  customer
WHERE
  last_name = 'Rodriguez'
  OR first_name = 'Adam';
GraphQL
query {
  customer(where: {_or: [
    {last_name: {_eq: "Rodriguez"}},
    {first_name: {_eq: "Adam"}}
  ]}) {
    last_name
    first_name
  }
}

IN

SQL
SELECT
  first_name,
  last_name
FROM
  customer
WHERE 
  first_name IN ('Ann','Anne','Annie');
GraphQL
query {
  customer(where: {first_name: {_in: ["Ann", "Anne", "Annie"]}}) {
    last_name
    first_name
  }
}

LIKE

SQL
SELECT
  first_name,
  last_name
FROM
  customer
WHERE 
  first_name LIKE 'Ann%'
GraphQL
query {
  customer(where: {first_name: {_like: "Ann%"}}) {
    last_name
    first_name
  }
}

LIMIT

SQL
SELECT
  film_id,
  title,
  release_year
FROM
  film
ORDER BY
  film_id
LIMIT 5;
GraphQL
query {
  film(order_by: {film_id: asc}, limit: 5) {
    film_id
    title
    release_year
  }
}

OFFSET

SQL
SELECT
  film_id,
  title,
  release_year
FROM
  film
ORDER BY
  film_id
LIMIT 4 OFFSET 3;
GraphQL
query {
  film(order_by: {film_id: asc}, limit: 5, offset: 3) {
    film_id
    title
    release_year
  }
}

Joins

  • Hasuraには直接JOINに代わるクエリは存在しない
  • Nested object queriesで実現出来ない場合は個別にSchemaを作成する必要がある
GraphQL
query {
  customer {
    customer_id
    first_name
    last_name
    email
    payments {
      amount
      payment_date
    }
  }
}

GROUP BY

GROUP BY with SUM

SQL
SELECT
   customer_id,
   SUM (amount)
FROM
   payment
GROUP BY
   customer_id;
GraphQL
{
  customer {
    customer_id
    payments_aggregate {
      aggregate {
        sum {
          amount
        }
      }
    }
  }
}

GROUP BY with COUNT

SQL
SELECT
   staff_id,
   COUNT (payment_id)
FROM
   payment
GROUP BY
   staff_id;
GraphQL
query {
  staff {
    staff_id
    payments_aggregate {
      aggregate {
        count
      }
    }
  }
}

パフォーマンス上の注意

  • 上記のクエリは一見良さそうに見えるが、Joinが発生するのでパフォーマンスは落ちる
  • 以下はGROUP BY with COUNTのクエリの実行計画
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
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
mutation {
  insert_category(
  	objects: {
      category_id: 17,
      name: "VR"
    }
  ) {
    returning {
      category_id
      name
      last_update
    }
  }
}

UPDATE

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

UPSERT

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
mutation {
  delete_category(
    where: {
      category_id: {_eq: 17}
    }
  ) {
    affected_rows
  }
}

まとめ

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

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

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

35
17
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
35
17

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?