iT邦幫忙

2023 iThome 鐵人賽
0
自我挑戰組

Techschool Goalng Backend Master Class 的學習記錄系列 第 27 篇

[Day 27] Add users table with unique & foreign key constraints in PostgreSQL

15th鐵人賽 golang sql database
307 瀏覽

  • 分享至 

  • xImage
    •  

Intro

目前，我們已為簡易銀行系統實現了多項功能，包括創建、更新、檢索或轉賬等，然而，還缺少一個非常重要的功能：user authentication 和 authorization，在這項功能被實現之前，我們的銀行系統無法完全完成。

因此，今天我們將採取實現它的第一步，即向資料庫中新增一個User Table，並通過一些資料庫constraints將其與現有的帳戶表Link。

Add table users

Before:

https://ithelp.ithome.com.tw/upload/images/20231016/20121746PqPqFOJz21.png

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
    (owner, currency) [unique]
  }
}

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)
  }
}
  • 目前的Account Table有一個「owner」欄位，可以用來識別該帳戶的所有者。
  • 我們可以利用「owner」欄位作為外鍵，與新建的用戶表進行關聯。

After:

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()`]

}
  • 計劃創建一個別名為「U」的User Table。
  • 新表的第一個欄位是「username」，類型為 varchar，每個用戶必須有一個唯一的用戶名，可以作為這個表的主鍵。
  • 下一個欄位是「hashed_password」，也是 varchar 類型，用來儲存用戶密碼的哈希值，且不能為空。
    • 使用「hashed_password」而非「password」是因為我們不應將裸露的密碼存儲在資料庫中，避免因資料庫被訪問而導致的嚴重安全問題。
  • 下一個欄位是用來儲存用戶的「full_name」，類型為 varchar，且不能為空。
  • 用戶表格中還有一個重要的欄位是「email」，將來用來與用戶溝通，例如在他們忘記密碼並想重設時。此欄位的類型應為 varchar，需設定為唯一且非空，以避免有兩個用戶使用相同的電子郵件。
  • 出於安全考慮，我們通常建議用戶定期更改密碼，例如每月一次。因此，我們需要一個「password_changed_at」欄位來記錄用戶最後一次更改密碼的時間。此欄位的類型應為帶時區的時間戳，並且不能為空，之所以希望每個欄位都不是空值，是因為這樣可以使開發人員的工作更簡單，無需處理空指針問題。
    • 如果密碼從未更改過，我們將使用一個很久以前的默認值。由於使用的是golang，將使用Go的零值時間戳，即 UTC時間區的 '0001-01-01 00:00:00Z'，其中的 "Z" 代表零時區。
  • 與其他表格一樣，將有一個「created_at」欄位來儲存用戶創建的時間。這個欄位不能為空，並具有默認值 now()，因此當插入新的用戶記錄時，Postgres將自動填充當前的時間戳。

Add foreign key constraint

https://ithelp.ithome.com.tw/upload/images/20231016/20121746Maq3J83IBf.png

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()`]

}

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
  }
}
  • 允許一名user擁有多個具有不同貨幣的accounts。
  • 透過將account中的「owner」欄位連結到用戶表格中的「username」欄位來實現這一點，使得「owner」欄位成為外部鍵。
  • 在定義「owner」時，將增加一個參照標籤，指向「U.username」。需要注意，U只是用戶表的別名。
  • 這個連結末端的 "1" 和 "*" 符號表示這是一對多（1-to-many）的關係，意指一個 "user" 可以擁有多個 "accounts"，但一個 "account" 只能屬於一個 "user"。

Add unique constraint

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]
  }
}
  • 一個使用者可以擁有多個賬戶，但這些賬戶應該擁有不同的貨幣類型（例如，可以擁有一個 USD 賬戶和一個 EUR 賬戶，但不能擁有兩個不同的 USD 賬戶）。
  • 為了在數據庫層面設置此約束，我們可以在 "accounts" 表中添加一個組合唯一索引（composite unique index）。
  • 這個組合索引由兩個字段組成： "owner" 和 "currency"，因此稱為組合索引，因為它涉及多於一個字段。

Export to PostgreSQL

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())
);

CREATE TABLE "accounts" (
  "id" bigserial PRIMARY KEY,
  "owner" varchar NOT NULL,
  "balance" bigint NOT NULL,
  "currency" varchar NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

CREATE TABLE "entries" (
  "id" bigserial PRIMARY KEY,
  "account_id" bigint NOT NULL,
  "amount" bigint NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

CREATE TABLE "transfers" (
  "id" bigserial PRIMARY KEY,
  "from_account_id" bigint NOT NULL,
  "to_account_id" bigint NOT NULL,
  "amount" bigint NOT NULL,
  "created_at" timestamptz NOT NULL DEFAULT (now())
);

CREATE INDEX ON "accounts" ("owner");

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

CREATE INDEX ON "entries" ("account_id");

CREATE INDEX ON "transfers" ("from_account_id");

CREATE INDEX ON "transfers" ("to_account_id");

CREATE INDEX ON "transfers" ("from_account_id", "to_account_id");

COMMENT ON COLUMN "entries"."amount" IS 'can be negative or positive';

COMMENT ON COLUMN "transfers"."amount" IS 'must be positive';

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

ALTER TABLE "entries" ADD FOREIGN KEY ("account_id") REFERENCES "accounts" ("id");

ALTER TABLE "transfers" ADD FOREIGN KEY ("from_account_id") REFERENCES "accounts" ("id");

ALTER TABLE "transfers" ADD FOREIGN KEY ("to_account_id") REFERENCES "accounts" ("id");

Then the alter table command to add foreign key constraint to the owner field:

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

And a composite unique index for the owner and currency.

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

Add new schema change to our project

  • 需要將新的變更添加到simple bank中。

  • 一種方法是替換 init_schema 遷移文件的全部內容，然後重置數據庫並重新運行 migrate up 命令，但這在Production環境中並不是正確的方法。

  • 為什麽直接替換並重新migrattion不是正確的方法?

    • 因為需求經常變更，且可能會在系統的第一版部署到生產環境後發生，一旦生產數據庫中有數據，就不能重置它來重新運行舊的遷移。

  • 正確的方法是創建一個新的遷移版本。我們可以打開終端機來生成一個新的遷移。

  • 要創建一個新的遷移，我們運行以下命令：

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

  • 使用一些參數來告訴 migrate 設置輸出文件的擴展名為 sql，輸出目錄為 db/migration，使用順序號作為文件名前綴，並將遷移名稱設置為 add_users。

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

  • 可以看到，在 db/migration 文件夾中生成了兩個遷移文件。

    ❯ git status
On branch feat-building-restful-http-json-api-gin
Untracked files:
  (use "git add <file>..." to include in what will be committed)
	db/migration/000002_add_users.down.sql
	db/migration/000002_add_users.up.sql

nothing added to commit but untracked files present (use "git add" to track)

Implement the up migration

  • 創建 users 表格，可以復制 dbdiagram 為我們生成的 SQL 查詢並將其粘貼到遷移文件 000002_add_users.up.sql 中。

  • 接著，將複製 accounts 的 owner 字段的新外鍵約束的查詢。

  • 最後，還要複製 owner 和 currency 的UNIQUE復合索引的查詢，具體 SQL 語句如下：

    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");

  • 上述的SQL已經足夠，但有另一種確保每個 owner 對特定 currency 最多只有一個賬戶的方法，可以添加一個對 accounts 表的 owner 和 currency pair 的唯一約束，而不是直接使用這樣的唯一索引，該命令與上面添加外鍵約束的命令非常相似，SQL 語句如下：

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

  • 實際上，添加這樣的唯一約束會自動創建與我們上面寫的命令相同的用於 owner 和 currency 的唯一復合索引。

Q & A

  1. UNIQUE INDEX vs CONSTRAINT ?
    1. CREATE UNIQUE INDEX ON "accounts" ("owner", "currency");
      • 此命令直接創建一個唯一的復合索引在 "owner" 和 "currency" 字段上。
      • 它主要用來加速查詢速度，因為它允許數據庫更快地查找特定的 owner 和 currency 組合。
      • 當插入或更新記錄時，數據庫會檢查是否違反了唯一索引，如果是，則會拒絕操作。
    2. ALTER TABLE "accounts" ADD CONSTRAINT "owner_currency_key" UNIQUE ("owner", "currency");
      • 此命令不僅創建了一個唯一索引，還將其作為一個約束添加到表中，並賦予它一個名稱（在這種情況下是 "owner_currency_key"）。
      • 除了提供唯一索引的所有優點外，它還允許你在稍後的時間點輕鬆刪除或修改這個約束，因為它有一個名字可以參照。
      • 能夠命名約束也使得錯誤消息更清晰，因為它們會指出是哪個約束被違反了。

Run the migration up

make migrateup

migrate -path db/migration -database "postgresql://root:secret@localhost:5432/simple_bank?sslmode=disable" -verbose up
2023/09/19 12:13:10 Start buffering 2/u add_users
2023/09/19 12:13:10 Read and execute 2/u add_users
2023/09/19 12:13:10 error: migration failed: insert or update on table "accounts" violates foreign key constraint "accounts_owner_fkey", Key (owner)=(pvtpxw) is not present in table "users". in line 0: 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");
ALTER TABLE "accounts" ADD CONSTRAINT "owner_currency_key" UNIQUE ("owner", "currency");

 (details: pq: insert or update on table "accounts" violates foreign key constraint "accounts_owner_fkey")
make: *** [migrateup] Error 1

  • 運行 make migrateup 指令來應用新的遷移。

    • 遇到錯誤：外鍵約束被違反（foreign key constraint violated）
    • 原因：accounts 表中已有的行的 owner 字段包含隨機值，並未鏈接到任何存在的用戶。

  • 需要清除所有現有數據再運行 migrate up 指令。

    • 當前的系統尚未準備好投入生產，所以可以清理所有現有數據。

  • 由於之前的 migrate up 執行失敗，當前的模式遷移已變為“dirty state”版本 2。

  • 嘗試運行 make migratedown 來清理數據會導致錯誤，因為目前處於 dirty version。

    • 解決方法：手動將 dirty 字段的值更新為 false，保存後重新運行 make migratedown 指令。

  • 運行 make migratedown 指令成功，數據庫中的所有表都被刪除。

    • 可以再次運行 make migrateup 指令來恢復它們。

  • 運行 make migrateup 指令成功，可以在 TablePlus 中看到新的 users 表。

    • users 表的結構包括我們在代碼中聲明的所有字段：username, hashed_password, full_name, email。

      https://ithelp.ithome.com.tw/upload/images/20231016/20121746NN9yLq9LiI.png

    • password_changed_at 和 created_at 字段都有正確的默認值。

    • 底部有兩個 BTREE 索引：一個用於主鍵 username（必須是唯一的），另一個用於 email（也是唯一索引）。

  • 檢查 accounts 表：

    https://ithelp.ithome.com.tw/upload/images/20231016/20121746uaPMzcd9HW.png

    • owner 字段現在包含一個外鍵約束，鏈接到 users 表的 username 字段。
    • 底部新增了一個用於 owner 和 currency 對的唯一索引。

  • Makefile 新增migrateup1 ( -verbose up 1)

    migrateup1:
					migrate -path db/migration -database "$(DB_URL)" -verbose up 1

.PHONY: network postgres createdb dropdb migrateup migratedown sqlc test clean server air mock migrateup1

Implement the migration down

  • 我們需要migration down 與migration up來完成migration的步驟。

  • 首先，我們需要刪除“accounts”表中“owner”和“currency”配對的唯一約束，使用的命令如下：

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

  • 接著，我們需要刪除“owner”字段的外鍵約束，但首先我們需要知道這個外鍵約束的名字。為了找到它，我們可以返回Table Plus並點擊窗口底部的Info按鈕來查看表定義。

    https://ithelp.ithome.com.tw/upload/images/20231016/201217469dg5esfFNt.png

  • 在表定義中，我們可以找到外鍵約束的名稱：accounts_owner_fkey。我們需要將其複製並粘貼到以下命令中：

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

  • 最後一步是刪除“users”表，命令如下：

    DROP TABLE IF EXISTS "users";

  • Makefile 新增migratedown1 ( -verbose down 1)

    migratedown1:
					migrate -path db/migration -database "$(DB_URL)" -verbose down 1

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

Test the up and down migrations

  • make migratedown1

    • 確保User Table已經消失了，在帳戶表格中，owner列的外鍵約束以及owner和currency配對的唯一約束也已不復存在。

    https://ithelp.ithome.com.tw/upload/images/20231016/201217463GjpHgyaeM.png

  • make migrateup1

    • 確保User Table重新建立，在帳戶表格中，owner列的外鍵約束以及owner和currency配對的唯一約束也重新建立。

    https://ithelp.ithome.com.tw/upload/images/20231016/20121746deMURu1Dfi.png

        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())
    );

    CREATE TABLE "accounts" (
      "id" bigserial PRIMARY KEY,
      "owner" varchar NOT NULL,
      "balance" bigint NOT NULL,
      "currency" varchar NOT NULL,
      "created_at" timestamptz NOT NULL DEFAULT (now())
    );

    CREATE TABLE "entries" (
      "id" bigserial PRIMARY KEY,
      "account_id" bigint NOT NULL,
      "amount" bigint NOT NULL,
      "created_at" timestamptz NOT NULL DEFAULT (now())
    );

    CREATE TABLE "transfers" (
      "id" bigserial PRIMARY KEY,
      "from_account_id" bigint NOT NULL,
      "to_account_id" bigint NOT NULL,
      "amount" bigint NOT NULL,
      "created_at" timestamptz NOT NULL DEFAULT (now())
    );

    CREATE INDEX ON "accounts" ("owner");

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

    CREATE INDEX ON "entries" ("account_id");

    CREATE INDEX ON "transfers" ("from_account_id");

    CREATE INDEX ON "transfers" ("to_account_id");

    CREATE INDEX ON "transfers" ("from_account_id", "to_account_id");

    COMMENT ON COLUMN "entries"."amount" IS 'can be negative or positive';

    COMMENT ON COLUMN "transfers"."amount" IS 'must be positive';

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

    ALTER TABLE "entries" ADD FOREIGN KEY ("account_id") REFERENCES "accounts" ("id");

    ALTER TABLE "transfers" ADD FOREIGN KEY ("from_account_id") REFERENCES "accounts" ("id");

    ALTER TABLE "transfers" ADD FOREIGN KEY ("to_account_id") REFERENCES "accounts" ("id");

上一篇
[Day 26] Implement transfer money API with a custom params validator in Go
下一篇
[Day 28] How to handle DB errors in Golang correctly
系列文
Techschool Goalng Backend Master Class 的學習記錄 共 31 篇
  1. 27
    [Day 27] Add users table with unique & foreign key constraints in PostgreSQL
  2. 28
    [Day 28] How to handle DB errors in Golang correctly
  3. 29
    [Day 29] How to securely store passwords?
  4. 30
    [Day 30] How to write stronger unit tests with a custom go-mock matcher
  5. 31
    [Day 31] Why PASETO is better than JWT for token-based authentication?
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙