学习笔记 | Backend master class [Golang, PostgreSQL, Docker]
后端开发大师课 | Backend master class [Golang, PostgreSQL, Docker]
目录
- Lecture 1 | 设计数据库schema并用dbdiagram.io生成sql
- Lecture 2 | 安装并使用docker+postgres+tableplus创建数据库schema
- Lecture 3 | 在golang中编写和运行数据库迁移脚本
- Lecture 4 | 从sql生成CRUD的golang代码
- Lecture 4 | 为CURD的golang代码编写单元测试
Lecture 1 | 设计数据库schema并用dbdiagram.io生成sql
通过 dbdiagram.io 设计数据库的 schema 并生成 postgresql 的 sql 代码。
分享链接：https://dbdiagram.io/d/62ba96ad69be0b672c5496b8
// 账户表
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 postive']
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 postive']
created_at timestamptz [not null, default: `now()`]
indexes {
from_account_id
to_account_id
(from_account_id, to_account_id)
}
}
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 postive';
COMMENT ON COLUMN "transfers"."amount" IS 'must be postive';
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");
Lecture 2 | 安装并使用docker+postgres+tableplus创建数据库schema
- docker：用来安装软件，相当于一个小型虚拟机。
- postgres：数据库管理系统。
- tableplus：与DBMS交互的界面化软件。
docker 相关命令：
-
docker ps查看当前运行中的容器 -
docker images查看镜像列表 -
docker rm container-id删除指定 id 的容器 -
docker stop/start container-id停止/启动指定 id 的容器 -
docker rmi image-id删除指定 id 的镜像 -
docker volume ls查看 volume 列表 -
docker network ls查看网络列表 -
docker pull拉取镜像到本地 -
docker run使用本地镜像启动容器 -
docker exec在容器中执行命令 -
docker logs
拉取 postgres 镜像：
docker pull postgres:12-alpine
使用镜像启动 postgre 容器：
docker run --name postgres12 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=123456 -d postgres:12-alpine
在容器中执行 psql 命令：
docker exec -it postgres12 psql -U root
tableplus:
容器内：
➜ ~ docker exec -it postgres12 psql -U root
psql (12.11)
Type "help" for help.
root=# select now();
now
------------------------------
2022-06-28 07:03:29.51169+00
(1 row)
root=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+-------+----------+------------+------------+-------------------
postgres | root | UTF8 | en_US.utf8 | en_US.utf8 |
root | root | UTF8 | en_US.utf8 | en_US.utf8 |
template0 | root | UTF8 | en_US.utf8 | en_US.utf8 | =c/root +
| | | | | root=CTc/root
template1 | root | UTF8 | en_US.utf8 | en_US.utf8 | =c/root +
| | | | | root=CTc/root
(4 rows)
root=# lc root
root-# \d
List of relations
Schema | Name | Type | Owner
--------+------------------+----------+-------
public | accounts | table | root
public | accounts_id_seq | sequence | root
public | entries | table | root
public | entries_id_seq | sequence | root
public | transfers | table | root
public | transfers_id_seq | sequence | root
(6 rows)
root-# \d accounts
Table "public.accounts"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+--------------------------------------
id | bigint | | not null | nextval('accounts_id_seq'::regclass)
owner | character varying | | not null |
balance | bigint | | not null |
currency | character varying | | not null |
created_at | timestamp with time zone | | not null | now()
Indexes:
"accounts_pkey" PRIMARY KEY, btree (id)
"accounts_owner_idx" btree (owner)
Referenced by:
TABLE "entries" CONSTRAINT "entries_account_id_fkey" FOREIGN KEY (account_id) REFERENCES accounts(id)
TABLE "transfers" CONSTRAINT "transfers_from_account_id_fkey" FOREIGN KEY (from_account_id) REFERENCES accounts(id)
TABLE "transfers" CONSTRAINT "transfers_to_account_id_fkey" FOREIGN KEY (to_account_id) REFERENCES accounts(id)
root-# \d entries
Table "public.entries"
Column | Type | Collation | Nullable | Default
------------+--------------------------+-----------+----------+-------------------------------------
id | bigint | | not null | nextval('entries_id_seq'::regclass)
account_id | bigint | | |
amount | bigint | | not null |
created_at | timestamp with time zone | | not null | now()
Indexes:
"entries_pkey" PRIMARY KEY, btree (id)
"entries_account_id_idx" btree (account_id)
Foreign-key constraints:
"entries_account_id_fkey" FOREIGN KEY (account_id) REFERENCES accounts(id)
root-# \d transfers
Table "public.transfers"
Column | Type | Collation | Nullable | Default
-----------------+--------------------------+-----------+----------+---------------------------------------
id | bigint | | not null | nextval('transfers_id_seq'::regclass)
from_account_id | bigint | | |
to_account_id | bigint | | |
amount | bigint | | not null |
created_at | timestamp with time zone | | not null | now()
Indexes:
"transfers_pkey" PRIMARY KEY, btree (id)
"transfers_from_account_id_idx" btree (from_account_id)
"transfers_from_account_id_to_account_id_idx" btree (from_account_id, to_account_id)
"transfers_to_account_id_idx" btree (to_account_id)
Foreign-key constraints:
"transfers_from_account_id_fkey" FOREIGN KEY (from_account_id) REFERENCES accounts(id)
"transfers_to_account_id_fkey" FOREIGN KEY (to_account_id) REFERENCES accounts(id)
Lecture 3 | 在golang中编写和运行数据库迁移脚本
golang数据库迁移包：https://github.com/golang-migrate/migrate
安装：
brew install golang-migrate
命令（主要关注create、up、down这三个命令）：
➜ ~ migrate -help
Usage: migrate OPTIONS COMMAND [arg...]
migrate [ -version | -help ]
Options:
-source Location of the migrations (driver://url)
-path Shorthand for -source=file://path
-database Run migrations against this database (driver://url)
-prefetch N Number of migrations to load in advance before executing (default 10)
-lock-timeout N Allow N seconds to acquire database lock (default 15)
-verbose Print verbose logging
-version Print version
-help Print usage
Commands:
create [-ext E] [-dir D] [-seq] [-digits N] [-format] [-tz] NAME
Create a set of timestamped up/down migrations titled NAME, in directory D with extension E.
Use -seq option to generate sequential up/down migrations with N digits.
Use -format option to specify a Go time format string. Note: migrations with the same time cause "duplicate migration version" error.
Use -tz option to specify the timezone that will be used when generating non-sequential migrations (defaults: UTC).
goto V Migrate to version V
up [N] Apply all or N up migrations
down [N] [-all] Apply all or N down migrations
Use -all to apply all down migrations
drop [-f] Drop everything inside database
Use -f to bypass confirmation
force V Set version V but don't run migration (ignores dirty state)
version Print current migration version
Source drivers: s3, gitlab, go-bindata, file, bitbucket, github, github-ee, godoc-vfs, gcs
Database drivers: crdb-postgres, clickhouse, cockroach, cockroachdb, firebird, firebirdsql, mysql, postgres, postgresql, redshift, sqlserver, cassandra, mongodb, mongodb+srv, spanner, stub, neo4j, pgx
创建迁移文件（sql）：
➜ simplebank migrate create -ext sql -dir db/migration -seq init_schema
/Users/tangyiheng/Desktop/Backend master class [Golang, Postgres, Docker]/simplebank/db/migration/000001_init_schema.up.sql
/Users/tangyiheng/Desktop/Backend master class [Golang, Postgres, Docker]/simplebank/db/migration/000001_init_schema.down.sql
- 升级：
migrate up，将执行 up 脚本文件 - 降级：
migrate down，将执行 down 脚本文件
在 postgres 容器中创建数据库：
docker exec -it postgres12 createdb --username=root --owner=root simple_bank
进入容器中的 postgres 控制台：
docker exec -it postgres12 psql -U root simple_bank
使用 migrate 对数据库版本进行升级：
migrate -path db/migration -database "postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose up
同理使用 migrate 对数据库版本进行回退：
migrate -path db/migration -database "postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose down
编写 Makefile 方便配置环境：
postgres:
docker run --name postgres12 -p 5432:5432 -e POSTGRES_USER=root -e POSTGRES_PASSWORD=123456 -d postgres:12-alpine
createdb:
docker exec -it postgres12 createdb --username=root --owner=root simple_bank
dropdb:
docker exec -it postgres12 dropdb simple_bank
migrateup:
migrate -path db/migration -database "postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose up
migratedown:
migrate -path db/migration -database "postgresql://root:123456@localhost:5432/simple_bank?sslmode=disable" -verbose down
.PHONY: postgres createdb dropdb migrateup migratedown
Lecture 4 | 从sql生成CRUD的golang代码
CRUD：
- create
- read
- update
- delete
考虑以下几种sql生成方案：
- golang 自带的 database/sql：https://pkg.go.dev/database/sql
- gorm：https://gorm.io/docs/index.html
- sqlx：https://github.com/jmoiron/sqlx
- sqlc：https://github.com/kyleconroy/sqlc
更正：目前已经支持 MySQL。
安装 sqlc：
brew install sqlc
sqlc 相关命令：
➜ simplebank sqlc help
Usage:
sqlc [command]
Available Commands:
compile Statically check SQL for syntax and type errors
completion Generate the autocompletion script for the specified shell
generate Generate Go code from SQL
help Help about any command
init Create an empty sqlc.yaml settings file
version Print the sqlc version number
Flags:
-x, --experimental enable experimental features (default: false)
-f, --file string specify an alternate config file (default: sqlc.yaml)
-h, --help help for sqlc
Use "sqlc [command] --help" for more information about a command.
主要关注以下命令：
compile：检查 sql 语法错误。generate：从 sql 生成 golang 的 CRUD 代码。init：生成 sqlc 的 yaml 配置文件。
编写 CRUD 的 query：
-- name: CreateAccount :one
INSERT INTO accounts (
owner,
balance,
currency
) VALUES (
$1, $2, $3
)
RETURNING *;
-- name: GetAccount :one
SELECT * FROM accounts
WHERE id = $1 LIMIT 1;
-- name: ListAccounts :many
SELECT * FROM accounts
ORDER BY id
LIMIT $1
OFFSET $2;
-- name: UpdateAccount :one
UPDATE accounts
SET balance = $2
WHERE id = $1
RETURNING *;
-- name: DeleteAccount :exec
DELETE FROM accounts
WHERE id = $1;
生成以下三个 golang 文件：
XXX.sql.go：对应之前写的CRUD代码，它将入参单独封装成为了一个结构体。
// Code generated by sqlc. DO NOT EDIT.
// source: account.sql
package db
import (
"context"
)
const createAccount = `-- name: CreateAccount :one
INSERT INTO accounts (
owner,
balance,
currency
) VALUES (
$1, $2, $3
)
RETURNING id, owner, balance, currency, created_at
`
type CreateAccountParams struct {
Owner string `json:"owner"`
Balance int64 `json:"balance"`
Currency string `json:"currency"`
}
func (q *Queries) CreateAccount(ctx context.Context, arg CreateAccountParams) (Account, error) {
row := q.db.QueryRowContext(ctx, createAccount, arg.Owner, arg.Balance, arg.Currency)
var i Account
err := row.Scan(
&i.ID,
&i.Owner,
&i.Balance,
&i.Currency,
&i.CreatedAt,
)
return i, err
}
const deleteAccount = `-- name: DeleteAccount :exec
DELETE FROM accounts
WHERE id = $1
`
func (q *Queries) DeleteAccount(ctx context.Context, id int64) error {
_, err := q.db.ExecContext(ctx, deleteAccount, id)
return err
}
const getAccount = `-- name: GetAccount :one
SELECT id, owner, balance, currency, created_at FROM accounts
WHERE id = $1 LIMIT 1
`
func (q *Queries) GetAccount(ctx context.Context, id int64) (Account, error) {
row := q.db.QueryRowContext(ctx, getAccount, id)
var i Account
err := row.Scan(
&i.ID,
&i.Owner,
&i.Balance,
&i.Currency,
&i.CreatedAt,
)
return i, err
}
const listAccounts = `-- name: ListAccounts :many
SELECT id, owner, balance, currency, created_at FROM accounts
ORDER BY id
LIMIT $1
OFFSET $2
`
type ListAccountsParams struct {
Limit int32 `json:"limit"`
Offset int32 `json:"offset"`
}
func (q *Queries) ListAccounts(ctx context.Context, arg ListAccountsParams) ([]Account, error) {
rows, err := q.db.QueryContext(ctx, listAccounts, arg.Limit, arg.Offset)
if err != nil {
return nil, err
}
defer rows.Close()
var items []Account
for rows.Next() {
var i Account
if err := rows.Scan(
&i.ID,
&i.Owner,
&i.Balance,
&i.Currency,
&i.CreatedAt,
); err != nil {
return nil, err
}
items = append(items, i)
}
if err := rows.Close(); err != nil {
return nil, err
}
if err := rows.Err(); err != nil {
return nil, err
}
return items, nil
}
const updateAccount = `-- name: UpdateAccount :one
UPDATE accounts
SET balance = $2
WHERE id = $1
RETURNING id, owner, balance, currency, created_at
`
type UpdateAccountParams struct {
ID int64 `json:"id"`
Balance int64 `json:"balance"`
}
func (q *Queries) UpdateAccount(ctx context.Context, arg UpdateAccountParams) (Account, error) {
row := q.db.QueryRowContext(ctx, updateAccount, arg.ID, arg.Balance)
var i Account
err := row.Scan(
&i.ID,
&i.Owner,
&i.Balance,
&i.Currency,
&i.CreatedAt,
)
return i, err
}
db.go：便于我们使用数据库和事务。
package db
import (
"context"
"database/sql"
)
type DBTX interface {
ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
PrepareContext(context.Context, string) (*sql.Stmt, error)
QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}
func New(db DBTX) *Queries {
return &Queries{db: db}
}
type Queries struct {
db DBTX
}
func (q *Queries) WithTx(tx *sql.Tx) *Queries {
return &Queries{
db: tx,
}
}
models.go：定义了与数据库单表对应的结构体
package db
import (
"time"
)
type Account struct {
ID int64 `json:"id"`
Owner string `json:"owner"`
Balance int64 `json:"balance"`
Currency string `json:"currency"`
CreatedAt time.Time `json:"created_at"`
}
type Entry struct {
ID int64 `json:"id"`
AccountID int64 `json:"account_id"`
// can be negative or positive
Amount int64 `json:"amount"`
CreatedAt time.Time `json:"created_at"`
}
type Transfer struct {
ID int64 `json:"id"`
FromAccountID int64 `json:"from_account_id"`
ToAccountID int64 `json:"to_account_id"`
// must be positive
Amount int64 `json:"amount"`
CreatedAt time.Time `json:"created_at"`
}
Lecture 4 | 为CURD的golang代码编写单元测试
为 go 自带的 database/sql 安装 postgres 驱动 pq（https://github.com/lib/pq）。
go get github.com/lib/pq
查看14道真题和解析