Skip to content

Latest commit

 

History

History
503 lines (365 loc) · 26.3 KB

part15.md

File metadata and controls

503 lines (365 loc) · 26.3 KB
Raw

Добавляем таблицу users с ограничениями по уникальности и внешним ключом в PostgreSQL

Оригинал

Привет, ребята, рад вас снова видеть на мастер-класс по бекенду!

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

Однако до сих пор отсутствует одна очень важная функция: аутентификация и авторизация пользователей. Наша банковская система не может быть завершена, пока эта функция не будет реализована.

Итак, сегодня мы сделаем первый шаг к его реализации, а именно добавим новую таблицу пользователей в базу данных и свяжем ее с существующей таблицей accounts через некоторые ограничения БД.

Ниже:

Добавляем таблицу users

Итак, это текущая схема нашей простой банковской базы данных, написанная с использованием dbdiagram.io.

Table accounts as A {
  id bigserial [pk]
  owner varchar [not null]
  balance bigint [not null]
  currency varchar [not null]
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    owner
  }
}

Table entries {
  id bigserial [pk]
  account_id bigint [ref: > A.id, not null]
  amount bigint [not null, note: 'can be negative or positive']
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    account_id
  }
}

Table transfers {
  id bigserial [pk]
  from_account_id bigint [ref: > A.id, not null]
  to_account_id bigint [ref: > A.id, not null]
  amount bigint [not null, note: 'must be positive']
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    from_account_id
    to_account_id
    (from_account_id, to_account_id)
  }
}

На данный момент в таблице accounts есть поле owner, указывающее кому принадлежит этот счёт. Таким образом, мы можем использовать это поле в качестве внешнего ключа для ссылки на новую таблицу users, которую мы собираемся создать.

Давайте определим таблицу users с псевдонимом U.

Первое поле этой таблицы — это имя пользователя (usersname) типа varchar. У каждого пользователя должно быть уникальное имя пользователя, поэтому это поле может служить первичным ключом этой таблицы.

Далее у нас есть поле hashed_password для хранения хеш-значения пароля пользователя. Его тип также varchar, и он не должен быть равен null.

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

Мы узнаем больше о том, как хранить и проверять пароль в другой лекции. А пока давайте просто сосредоточимся на добавлении этой новой таблицы users.

Table users as U {
  username varchar [pk]
  hashed_password varchar [not null]
  full_name varchar [not null]
  email varchar [unique, not null]
  password_changed_at timestamptz [not null, default: '0001-01-01 00:00:00Z']
  created_at timestamptz [not null, default: `now()`]
}

Хорошо, в следующем поле будет храниться полное имя (full_name) пользователя, поэтому оно также имеет тип varchar и не должно быть null.

Еще одно важное поле, которое должно быть в таблице пользователей, — электронная почта (email). Мы будем использовать его позже для связи с пользователями, например, когда они забудут свой пароль и захотят его сбросить. Так что тип этого поля тоже должен быть varchar. И обратите внимание, что оно должен быть unique и не null, так как мы не хотим иметь в базе двух пользователей с одним и тем же адресом электронной почты.

Затем, как и в других таблицах, у нас будет поле created_at для хранения времени создания этого пользователя. Это поле не пустое и имеет значение по умолчанию now(), поэтому Postgres автоматически заполнит текущую метку времени при вставке новой записи пользователя.

Кроме того, из соображений безопасности рекомендуется часто просить пользователей менять пароль, например, раз в месяц. Поэтому нам понадобится поле: password_changed_at, чтобы узнать, когда пользователь в последний раз менял свой пароль. Его тип должен быть timestamp with timezone, а также не должен быть равен null.

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

Если пароль никогда не менялся, мы просто будем использовать значение по умолчанию, которое давно в прошлом. Поскольку мы используем Golang, здесь я буду использовать временную метку Go с нулевым значением. Таким образом, это должен быть год 1, месяц 1, дата 1, 0 часов, 0 минут, 0 секунд, а часовой пояс — UTC: '0001-01-01 00:00:00Z'. Буква Z здесь означает нулевой часовой пояс.

Итак, вот как мы определим новую таблицу users.

Добавляем ограничение внешнего ключа

Теперь имейте в виду, что мы хотим разрешить одному пользователю иметь несколько счетов с разными валютами, поэтому я собираюсь связать поле owner таблицы accounts с полем username таблицы users. Это сделает поле owner внешним ключом.

Здесь, в определении owner, я добавлю тег ref, указывающий на U.username. Напомню, что U — это просто псевдоним таблицы users.

Table accounts as A {
  id bigserial [pk]
  owner varchar [ref: > U.username, not null]
  balance bigint [not null]
  currency varchar [not null]
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    owner
  }
}

Теперь на диаграмме мы видим новую связь между полем username таблицы users и полем owner таблицы accounts.

Число 1 и символ * на концах этой ссылки говорят нам, что это отношение «1 ко многим», то есть один пользователь может иметь несколько банковских счетов, но один конкретный банковский счёт может принадлежать только одному пользователю.

Добавляем ограничение по уникальности

Здесь мы должны обратить внимание на следующее. Мы разрешаем одному пользователю иметь несколько банковских счетов, но эти счета должны иметь разные валюты. Например, у вас может быть один счет в USD и один счет в EUR, но явно не должно быть двух разных счетов в USD.

Один из способов задать это ограничение на уровне базы данных — добавить составной уникальный индекс (composite unique index) в таблицу accounts. Этот индекс состоит из двух полей: owner и currency. Вот почему он называется составным индексом — индексом, который включает более одного поля.

Table accounts as A {
  id bigserial [pk]
  owner varchar [ref: > U.username, not null]
  balance bigint [not null]
  currency varchar [not null]
  created_at timestamptz [not null, default: `now()`]

  Indexes {
    owner
    (owner, currency) [unique]
  }
}

Экспорт в PostgreSQL

Хорошо, теперь наша новая схема готова. Давайте экспортируем его в PostgreSQL.

Здесь приведён код для создания таблицы users:

CREATE TABLE "users" (
  "username" varchar PRIMARY KEY,
  "hashed_password" varchar NOT NULL,
  "full_name" varchar NOT NULL,
  "email" varchar UNIQUE NOT NULL,
  "password_changed_at" timestamptz NOT NULL DEFAULT '0001-01-01 00:00:00Z',
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

Затем команда alter table добавляет ограничение внешнего ключа для поля owner:

ALTER TABLE "accounts" ADD FOREIGN KEY ("owner") REFERENCES "users" ("username");

И составной уникальный индекс для owner и currency.

CREATE UNIQUE INDEX ON "accounts" ("owner", "currency");

Добавляем новое изменение схемы БД в наш проект

Затем мы должны добавить эти новые изменения в наше простое банковское приложение.

Один из способов сделать это — заменить все содержимое файла миграции init_schema новым, затем удалить содержимое базы данных и повторно запустить команду migrate up.

Однако в реальном проекте это сделать невозможно. Почему?

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

Таким образом, корректный способ добавить новое изменение в схему БД — создать новую версию миграции. Давайте откроем терминал, чтобы создать новую миграцию.

Мы узнали, как это сделать, в третьей лекции этого курса. Но если вы не помните команду, просто запустите:

migrate -help

Чтобы создать новую миграцию, мы запускаем эту команду:

migrate create -ext sql -dir db/migration -seq add_users

Мы используем определенные ключи, чтобы указать команде migrate поменять расширение выходного файла на sql, каталог, где будет храниться миграция, на db/migration, использовать порядковый номер в качестве префикса имени файла и add_users - в качестве названия миграции.

Как видно на рисунке, внутри папки db/migration было сгенерировано два файла миграции:

Давайте реализуем их в Visual Studio Code.

Реализуем миграцию up

Я начну с миграции up.

Во-первых, нам нужно создать таблицу users. Итак, давайте скопируем SQL запрос, сгенерированный для нас dbdiagram, и вставим его в файл миграции 000002_add_users.up.sql.

Далее я скопирую запрос, который добавляет новое ограничение внешнего ключа в поле owner таблицы accounts. И, наконец, запрос на создание уникального составного индекса для owner и currency.

CREATE TABLE "users" (
  "username" varchar PRIMARY KEY,
  "hashed_password" varchar NOT NULL,
  "full_name" varchar NOT NULL,
  "email" varchar UNIQUE NOT NULL,
  "password_changed_at" timestamptz NOT NULL DEFAULT('0001-01-01 00:00:00Z'),  
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

ALTER TABLE "accounts" ADD FOREIGN KEY ("owner") REFERENCES "users" ("username");

CREATE UNIQUE INDEX ON "accounts" ("owner", "currency");

Этого будет достаточно для нашего нового сценария миграции up. Однако я покажу вам еще один способ гарантировать, что у каждого владельца есть не более одного счёта для определенной валюты.

Вместо использования непосредственно уникального индекса, мы можем добавить уникальное ограничение для пары owner и currency в таблице accounts. Оно будет очень похоже на команду для добавления ограничения внешнего ключа, приведенную выше:

-- CREATE UNIQUE INDEX ON "accounts" ("owner", "currency");
ALTER TABLE "accounts" ADD CONSTRAINT "owner_currency_key" UNIQUE ("owner", "currency");

По сути, под капотом добавление этого уникального ограничения автоматически создаст тот же уникальный составной индекс для owner и currency, что и команда, которую мы написали выше.

Postgres понадобится этот индекс, чтобы быстрее проверять и применять ограничение уникальности. Таким образом, вы можете выбрать любую из этих двух команд, в зависимости от того какая вам больше нравится.

Запускаем миграцию up

Хорошо, теперь давайте откроем терминал и запустим make migrateup, чтобы применить эту новую миграцию.

Ой, возникла ошибка. И причина в том, что ограничение внешнего ключа нарушено. Почему?

Это происходит из-за того, что у нас уже есть несколько строк в таблице accounts, но в них поле owner совершенно случайное и не связано ни с одним из существующих пользователей. Конечно, поскольку таблицы users до сих пор не существует.

Так что в нашем случае мы должны удалить все существующие данные перед запуском migrate up. Это возможно, потому что наша существующая система еще не готова к развертыванию в продакшен среде.

Но обратите внимание, что, поскольку предыдущая миграция завершилась с ошибкой, текущая миграция схемы будет изменена на version 2, но в состоянии dirty.

Так что теперь, если мы запустим make migratedown с целью удаления данных, мы получим ошибку из-за наличия не до конца выполненной миграции.

Чтобы исправить её, я вручную изменю значение этого поля dirty на false, сохраню его и вернусь в терминал, чтобы запустить make migratedown.

На этот раз миграция прошла успешно, и все таблицы в нашей базе данных исчезли. Теперь мы можем снова запустить make migrateup, чтобы вернуть их.

Хорошо, миграция успешно выполнена. Вернувшись к TablePlus, мы увидим новую таблицу users. Давайте нажмем кнопку Structure, чтобы увидеть ее схему.

Она содержит все поля, объявленные в коде: username, hashed_password, full_name, email.

Поле password_changed_at имеет правильное значение по умолчанию и как и поле created_at.

Обратите внимание на 2 BTREE индекса внизу:

  • Один для первичного ключа username, который должен быть уникальным.
  • А другой — для поля email, которое также является уникальным (unique) индексом.

Давайте посмотрим на таблицу accounts.

Здесь, в поле owner, мы видим, что теперь у него есть ограничение внешнего ключа, которое ссылается на поле username в таблице users.

А внизу новый уникальный (unique) индекс для пары owner и currency.

Так что наш скрипт миграции up работает отлично! Теперь вернемся к коду и напишем миграцию down.

Реализация миграции down

При написании миграции down мы должны откатить всё, что было сделано при миграции up.

Итак, сначала мы должны удалить уникальное (unique) ограничение для пары owner и currency в таблице accounts. Команда:

ALTER TABLE IF EXISTS "accounts" DROP CONSTRAINT IF EXISTS "owner_currency_key";

Затем мы должны аналогичным образом удалить ограничение внешнего ключа (foreign key) для поля owner. Но как теперь узнать название этого ограничения внешнего ключа?

На самом деле очень просто! Вернемся к Table Plus и нажмем кнопку Info в нижней части окна.

Здесь, в определении таблицы, мы видим название ограничения внешнего ключа: account_owner_fkey.

Давайте скопируем его и вставим в эту команду:

ALTER TABLE IF EXISTS "accounts" DROP CONSTRAINT IF EXISTS "accounts_owner_fkey";

Последний шаг, который мы должны сделать, это удалить таблицу users. Итак, давайте добавим эту команду в файл:

DROP TABLE IF EXISTS "users";

На этом всё! Файл 000002_add_users.down.sql готов. Как мы можем проверить правильность его работы?

Тестируем миграции up и down

На данный момент в Makefile у нас есть только одна команда make migratedown для запуска всех версий миграции down. Но в этом случае мы хотим запустить только одну последнюю версию миграции down.

Итак, давайте добавим для этой цели новую команду make. Я назову его migratedown1. Команда миграции будет такой же, как и раньше, но в конце нам нужно добавить еще один аргумент.

migratedown1:
  migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose down 1

Цифра 1 здесь означает, что мы хотим откатить только одну последнюю миграцию, или, точнее, просто запустить последнюю версию миграции down, которая была применена ранее.

По аналогии я скопирую команду migrateup и добавлю новую команду migrateup1, которая применит только версию миграции на одну выше текущей:

migrateup1:
  migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose up 1

Хорошо, теперь давайте добавим эти две новые команды в список PHONY:

.PHONY: postgres createdb dropdb migrateup migratedown migrateup1 migratedown1 sqlc test server mock

Вернёмся в терминал и запустим:

make migratedown1

Миграция прошла успешно. Теперь в TablePlus мы видим, что текущая версия изменена на 1.

Таблица users исчезла, а в таблице accounts больше нет ограничения внешнего ключа для столбца owner, а также ограничения уникальности (unique) для owner и currency.

Так что скрипт миграции down сработал!

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

make migrateup1

Как видите, новые ограничения опять были добавлены в таблицу accounts.

Текущая версия миграции — 2, и появилась новая таблица users, как и ожидалось.

Итак, сегодня мы успешно добавили новую таблицу users в базу данных нашего простого банковского приложения.

При этом мы также узнали, как добавить некоторые ограничения внешнего ключа (foreign key) и уникальности (unique), чтобы создать связь между таблицами и обеспечить согласованность данных.

В следующих лекциях мы модифицируем наш код на Golang для работы с этой новой таблицей, а затем добавим дополнительные функции для аутентификации и авторизации пользователей.

Спасибо за время, потраченное на чтение, и до скорой встречи на следующей лекции!