A Simple Bank

Using PostgreSQL、Golang、Docker to implement simple bank API.

Features:

• Create and mange account:
  • Owner 、balance、currency
• Record all balance change(記錄帳戶所有的變動)
  • Create an account entry for each change
• Money transfer transaction (確保帳戶的一致性: 成功 or 失敗):
  • Perform money transfer between 2 accounts consistently within a transaction.

Database Schema Design

• Design DB schema using dbdiagram.io
• Save & share DB diagram.
• Generate SQL code.

Account Table:

• bigserial : a big auto increment integers(8-byte/64-bit)
• now() : PosgresSQL default function to get current time
• In PostgreSQL, text and varchar are both used to store strings of variable length. The main difference is that varchar has a limit on the maximum length of the string that can be stored, whereas text does not have a limit. text is generally used when the length of the string is unknown or could be very long, while varchar is used when the length of the string is expected to be relatively short and can be specified in advance.
• Indexes: use owner name to find account

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
  }
}

Entry Table:

• Account → Entry is 1-to-many
• Record all changes to the account
• amount: 會根據匯入/匯出 而有正負值
• Indexes: List all entries of specific account

// one-to-many: each account can have many entries
Table entries {
  id bigserial [pk] // auto-increment
  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
  }
}

Transfer Table

• Indexes 提供三種使用
  1. 找尋匯出的account
  2. 找尋匯入的account
  3. 找尋所有的交易記錄

// one-to-many
Table transfers {
  id bigserial [pk] // auto-increment
  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)
  }
}

SQL

以下為完整的Postgresql Simple_Back_diagram.sql

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

Reference

• Database Transaction & ACID