iT邦幫忙

2023 iThome 鐵人賽

DAY 2
0
自我挑戰組

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

[Day 02] Install & use Docker + Postgres + TablePlus to create DB schema

  • 分享至 

  • xImage
  •  

Download & Install Docker Desktop

開發環境是使用 Mac(Intel) + Orbtack來進行開發,亦可使用Docker Desktop來替代。

Orbtack Install:

brew install orbstack

Docker Desktop Install:

wget https://desktop.docker.com/mac/main/amd64/Docker.dmg?utm_source=docker&utm_medium=webreferral&utm_campaign=docs-driven-download-mac-amd64

PostgresSQL Docker Image

Staring a Postgres instance

docker run --name postgres -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=secret -d postgres:14-alpine

Exec and login Postgres Container

docker exec -it postgres psql -U root

Testing Postgres

root=# select now();
              now
-------------------------------
 2022-09-18 07:01:10.272148+00
(1 row)

Logging of Postgres

docker logs postgres

To interact with the Postgres by using TablePlus

Download and Install

wget https://tableplus.com/release/osx/tableplus_latest

Connect with Postgres

https://ithelp.ithome.com.tw/upload/images/20230917/201217461INv820YOc.png

Testting Postgres SQL

https://ithelp.ithome.com.tw/upload/images/20230917/20121746P265CrROzg.png

Create Simple Bank Tables

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

https://ithelp.ithome.com.tw/upload/images/20230917/20121746safQD1tzHO.png

Check Tables

https://ithelp.ithome.com.tw/upload/images/20230917/20121746AFGsLRzO3P.png

Delete Tables

https://ithelp.ithome.com.tw/upload/images/20230917/20121746OEzUIKsfRt.png


上一篇
[Day 01] Design DB schema and generate SQL
下一篇
[Day 03] How to write & run database migration in Golang
系列文
Techschool Goalng Backend Master Class 的學習記錄31
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言