RDS PostgreSQL で注文を保存してクエリする

はじめに

注文アプリケーションにはデータベース接続がありますが、注文テーブルはまだありません。小さなスキーマを定義し、顧客の注文を 3 件保存して、アプリケーションが読み取るデータをクエリします。

先に前のデータベース接続の実習を完了してください。この実習は独立して開始し、PostgreSQL インスタンス、アプリケーション、設定済みの AWS CLI が提供されます。結果をテストした後、練習用データベースを削除します。

認定試験との関連

この実習では、次の試験トピックに関する入門的な操作を練習します。

Cloud Practitioner (CLF-C02) · タスク 3.4：構造化されたアプリケーションデータに対するリレーショナルデータベースの用途を理解します。
Solutions Architect – Associate (SAA-C03) · タスク 3.3：リレーショナルエンジンとアプリケーションのデータアクセスパターンを理解します。

注文テーブルを定義する

このステップでは、各注文に一意の識別子、顧客名、合計金額を持たせるスキーマを作成します。

テーブルは、定義済みの列を持つ行を保存します。SQL は、その行を定義してクエリする言語です。RDS API はデータベースインスタンスを管理し、SQL は PostgreSQL エンジン内部のデータを操作します。

用意された SQL 接続設定を読み込み、提供されたインスタンスを確認します。

cd /home/labex/project
source database.env

aws rds \
  describe-db-instances \
  --db-instance-identifier orders-db \
  --query 'DBInstances[0].{Database:DBName,Endpoint:Endpoint,Status:DBInstanceStatus}'

インスタンスは利用可能で、データベース orders が含まれます。psql 接続は service=orders-db として設定されています。

ヒアドキュメントでテーブルを作成します。2 行の SQL の間のテキストが SQL 文として psql に渡されます。

psql \
  "service=orders-db" \
  --set=ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE orders (
  order_id integer PRIMARY KEY,
  customer text NOT NULL,
  total numeric(8,2) NOT NULL CHECK (total > 0)
);
SQL

主キーは、2 行が同じ注文 ID を持つことを防ぎます。NOT NULL は値を必須にします。numeric(8,2) は小数点以下 2 桁の数値を保存し、CHECK は 0 以下の合計金額を拒否します。これらの制約によって、データベースが不正なデータを拒否します。

テーブルを確認します。

psql \
  "service=orders-db" \
  --command '\d orders'

3 列、主キー、合計金額が正であることのチェックが表示されます。テーブルにはまだ行がありません。

顧客の注文を 3 件保存する

このステップでは、小さなデータセットを挿入し、すぐに読み戻します。

INSERT は行を書き込みます。列名を指定すると、各値と列の対応が明確になります。

psql \
  "service=orders-db" \
  --set=ON_ERROR_STOP=1 <<'SQL'
INSERT INTO orders (order_id, customer, total)
VALUES
  (101, 'Maya', 49.90),
  (102, 'Owen', 18.50),
  (103, 'Nina', 25.25);

SELECT order_id, customer, total
FROM orders
ORDER BY order_id;
SQL

上記の値を持つ 3 行が表示されます。SELECT は保存済みのデータを読み取り、ORDER BY は表示順を一定にします。並べ替え句がなければ、データベースは特定の行順を保証しません。

AWS View にも同じ 3 件の注文が表示されます。アプリケーションはリクエストを受け取ると PostgreSQL を読み取るため、テーブルと行を追加すると、利用できなかった注文一覧にアプリケーションのデータが表示されます。

注文をクエリしてアプリケーションを確認する

このステップでは、業務上の質問に答え、SQL データとアプリケーションの応答を比較します。

チームは 25.00 以上の注文を求めています。WHERE は結果に含める前に行を絞り込みます。

psql \
  "service=orders-db" \
  --command 'SELECT order_id, customer, total FROM orders WHERE total >= 25.00 ORDER BY order_id;'

注文 101 と 103 が表示されます。フィルターは注文 102 を削除せず、返す行を選択するだけです。

集約は複数行の値をまとめます。すべての注文数と合計金額を計算します。

psql \
  "service=orders-db" \
  --command 'SELECT count(*) AS order_count, sum(total) AS order_total FROM orders;'

注文数は 3、合計金額は 93.65 になります。

アプリケーションの注文一覧を取得します。

curl \
  --silent \
  --show-error \
  --fail \
  http://127.0.0.1:8080/application/orders | jq

3 件すべての注文、データベース orders、ユーザー orders_admin、read_only: false が表示されます。SQL とアプリケーションは同じ保存データを読み取っています。アプリケーションは一覧全体を返し、先ほどの SQL クエリは指定したフィルターを適用しました。

データベースのマスターユーザーは、このスキーマの作成に役立ちます。次の実習ではアプリケーションに権限を絞ったデータベースのユーザーを割り当て、日常的なアクセスに管理権限が不要になるようにします。アプリケーションが PostgreSQL プライマリに保存された 3 件の注文を読み取っています。

練習用データベースを削除する

このステップでは、この実習のデータベースと注文データを削除します。

提供されたインスタンスはこの練習用です。サンプルの注文や最終スナップショットを保持する必要はありません。

aws rds \
  delete-db-instance \
  --db-instance-identifier orders-db \
  --skip-final-snapshot \
  --query 'DBInstance.DBInstanceIdentifier'

aws rds \
  wait db-instance-deleted \
  --db-instance-identifier orders-db

aws rds \
  describe-db-instances \
  --query 'DBInstances[].DBInstanceIdentifier'

[] が表示されます。インスタンスの削除は、テーブルを含むデータベースを削除します。既存のテーブルの選択した行だけを削除する DELETE より広い操作です。用意されたアプリケーションとネットワークリソースは残してください。

まとめ

制約付きの注文スキーマを定義し、3 件のレコードを挿入して、SQL のフィルターと集約でデータに関する質問に答えました。アプリケーションは PostgreSQL から同じ行を読み取りました。その後、提供されたアプリケーションとネットワークを残して練習用データベースを削除しました。

次の実習では、ネットワークアクセスとアプリケーションのデータベース権限の両方を制限します。

顧客の注文を保存してクエリする