Хранение и запросы заказов в RDS PostgreSQL

Введение

У приложения заказов уже есть подключение к базе, но ещё нет таблицы заказов. Вы определите небольшую схему, сохраните три заказа клиентов и запросите данные, которые читает приложение.

Сначала завершите предыдущую лабораторную работу о подключении. Эта работа начинается независимо: предоставлены экземпляр PostgreSQL, приложение и настроенный AWS CLI. После проверки результатов вы удалите учебную базу.

Связь с сертификацией

Эта работа даёт начальную практику по следующим темам экзаменов.

Cloud Practitioner (CLF-C02) · Задача 3.4: Распознать применение реляционной базы для структурированных данных приложения.
Solutions Architect – Associate (SAA-C03) · Задача 3.3: Понять реляционные движки и шаблоны доступа приложений к данным.

Определение таблицы заказов

На этом этапе вы создадите схему с уникальным идентификатором, именем клиента и суммой для каждого заказа.

Таблица хранит строки с определёнными столбцами. SQL — язык определения и запросов этих строк. API RDS управляет экземпляром базы, а 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.

Создайте таблицу с помощью heredoc. Текст между двумя строками SQL передаётся в psql как SQL-операторы:

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

Первичный ключ не позволяет двум строкам иметь одинаковый ID заказа. NOT NULL требует значение. numeric(8,2) хранит десятичное число с двумя знаками после запятой, а CHECK отклоняет нулевую или отрицательную сумму. Эти ограничения позволяют базе отвергать неверные данные.

Проверьте таблицу:

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

Должны быть видны три столбца, первичный ключ и проверка положительной суммы. Строк в таблице пока нет.

Сохранение трёх заказов клиентов

На этом этапе вы добавите небольшой набор данных и сразу прочитаете его.

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

Ожидаются три строки с указанными выше значениями. SELECT читает сохранённые данные; ORDER BY делает порядок вывода предсказуемым. Без сортировки база не гарантирует конкретный порядок строк.

Теперь AWS View должен показывать те же три заказа. Приложение читает 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

Ожидаются все три заказа, база orders, пользователь orders_admin и read_only: false. SQL и приложение читают одни и те же сохранённые данные. Приложение возвращает полный список, а предыдущий SQL-запрос применял ваш фильтр.

Главный пользователь базы удобен для создания этой схемы. Следующая работа предоставит приложению отдельную учётную запись с меньшими правами, чтобы обычный доступ не требовал административных привилегий. Приложение читает три заказа, сохранённые в основном PostgreSQL.

Удаление учебной базы

На этом этапе вы удалите базу этой работы и её данные заказов.

Предоставленный экземпляр относится к этому упражнению. Сохранять примерные заказы или финальный снимок не нужно:

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, удаляющий выбранные строки существующей таблицы. Сохраните подготовленное приложение и сетевые ресурсы.

Итоги

Вы определили схему заказов с ограничениями, добавили три записи и ответили на вопросы о данных с помощью фильтров и агрегаций SQL. Приложение прочитало те же строки из PostgreSQL. Затем вы удалили учебную базу, сохранив предоставленное приложение и сеть.

Следующая работа ограничит и сетевой доступ, и права приложения в базе данных.

Хранение и запросы заказов клиентов