/

Go教程：由PostgreSQL支持的REST API

Go教程：由PostgreSQL支持的REST API

在本教程中，我將說明如何使用Go來提供一個JSON API。我將為Vue.js應用程序創建一個基本的後端，該後端將提供PostgreSQL數據庫中已有的數據。

正文

我想要解決的問題的介紹

我將在瀏覽器中使用Vue編寫一個單頁應用程序。這個應用程序將列出幾個Git存儲庫，並在點擊其中一個存儲庫時顯示我在其他地方擴充的一些詳細信息。

本文中要構建的API是只讀的（不會有POST請求）。

它將有2個端點：

  • /api/index 將列出所有存儲庫
  • /api/repo/:owner/:name 將顯示由owner和name標識的存儲庫的詳細信息。

您可以將擁有者和名稱視為通常的github.comURL結構：github.com/owner/name。

我將把Go連接到一個現有的PostgreSQL數據庫，並根據請求中傳遞的參數提供響應。

現有的數據庫結構

該應用程序依賴於許多表。在此部分，我們將從數據庫中提取數據，並與以下

  • repositories：列出包含一些絕對數字（例如總點贊數）的存儲庫信息
  • repositories_weekly_data：以單週為單位存儲存儲庫中發生的情況，并聚合數字
  • repositories_historic_data：按月份聚合的提交和點贊信息
  • repositories_timelines：存儲與每個存儲庫相關的重要事件，例如達到 1 萬個點贊或首次創建的時間

簡單的HTTP響應處理程序

讓我們首先編寫一個簡單的HTTP服務器處理程序，用於處理2個路由：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package main

import (
	"fmt"
	"log"
	"net/http"
)

func main() {
	http.HandleFunc("/api/index", indexHandler)
	http.HandleFunc("/api/repo/", repoHandler)
	log.Fatal(http.ListenAndServe("localhost:8000", nil))
}

func indexHandler(w http.ResponseWriter, r *http.Request) {
	//...
}

func repoHandler(w http.ResponseWriter, r *http.Request) {
	//...
}

此代碼已經完成了對請求的並行處理。

連接到PostgreSQL

讓我們添加一個PostgreSQL連接。如果您對這些概念不熟悉，請查看這篇文章以了解如何在Go中使用SQL數據庫。

我不會使用任何ORM或外部庫，只使用純粹的database/sql代碼。

該連接使用環境變量來獲取憑據：

1
2
3
4
5
$ export DBHOST=localhost
$ export DBPORT=5432
$ export DBUSER=you
$ export DBPASS=pass
$ export DBNAME=dbname

(提示：如果您的密碼是空密碼，請使用export DBPASS="\"\"")

我引入了initDb()函數，它檢查這些必填的環境變量是否已設置，然後打開到數據庫的連接，如果出現錯誤會拋出異常。

db package 变量包含了数据库连接，并且在程序退出之前一直保持打开状态。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
package main

import (
	"database/sql"
	"fmt"
	"log"
	"net/http"
	"os"

	_ "github.com/lib/pq"
)

var db *sql.DB

const (
	dbhost = "DBHOST"
	dbport = "DBPORT"
	dbuser = "DBUSER"
	dbpass = "DBPASS"
	dbname = "DBNAME"
)

func main() {
	initDb()
	defer db.Close()
	http.HandleFunc("/api/index", indexHandler)
	http.HandleFunc("/api/repo/", repoHandler)
	log.Fatal(http.ListenAndServe("localhost:8000", nil))
}

func indexHandler(w http.ResponseWriter, r *http.Request) {
	//...
}

func repoHandler(w http.ResponseWriter, r *http.Request) {
	//...
}

func initDb() {
	config := dbConfig()
	var err error
	psqlInfo := fmt.Sprintf("host=%s port=%s user=%s "+
		"password=%s dbname=%s sslmode=disable",
		config[dbhost], config[dbport],
		config[dbuser], config[dbpass], config[dbname])

	db, err = sql.Open("postgres", psqlInfo)
	if err != nil {
		panic(err)
	}
	err = db.Ping()
	if err != nil {
		panic(err)
	}
	fmt.Println("Successfully connected!")
}

func dbConfig() map[string]string {
	conf := make(map[string]string)
	host, ok := os.LookupEnv(dbhost)
	if !ok {
		panic("DBHOST environment variable required but not set")
	}
	port, ok := os.LookupEnv(dbport)
	if !ok {
		panic("DBPORT environment variable required but not set")
	}
	user, ok := os.LookupEnv(dbuser)
	if !ok {
		panic("DBUSER environment variable required but not set")
	}
	password, ok := os.LookupEnv(dbpass)
	if !ok {
		panic("DBPASS environment variable required but not set")
	}
	name, ok := os.LookupEnv(dbname)
	if !ok {
		panic("DBNAME environment variable required but not set")
	}
	conf[dbhost] = host
	conf[dbport] = port
	conf[dbuser] = user
	conf[dbpass] = password
	conf[dbname] = name
	return conf
}

將處理程序移至它們自己的文件

由於代碼很快就會變得復雜，我想將HTTP請求處理程序移至它們自己的文件：

1
2
3
4
5
6
//...
import "github.com/flaviocopes/gitometer/api/handlers"
//...
http.HandleFunc("/api/index", handlers.Index)
http.HandleFunc("/api/repo/", handlers.Repo)
//...
1
2
3
4
5
6
7
8
9
package handlers

import (
	"net/http"
)

func indexHandler(w http.ResponseWriter, r *http.Request) {
	//....
}
1
2
3
4
5
6
7
8
9
package handlers

import (
	"net/http"
)

func repoHandler(w http.ResponseWriter, r *http.Request) {
	//....
}

实现/api/index端点

/api/index端点列出了我們在數據庫中擁有的所有存儲庫。沒有分頁。

對於每個存儲庫，它會返回我們將用於打印存儲庫索引的數據，用來查看詳細信息：

  • 名稱
  • 擁有者
  • GitHub上的星數

我們需要對repositories表執行一個查詢，該表包含我們需要的所有數據，並將數據添加到repositories結構中，然後將其編組為JSON並返回給客戶端。

下面是代碼：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
package main

import (
	"encoding/json"
	"fmt"
	"net/http"
)

// repository contains the details of a repository
type repositorySummary struct {
	ID          int
	Name        string
	Owner       string
	TotalStars  int
}

type repositories struct {
	Repositories []repositorySummary
}

// indexHandler calls `queryRepos()` and marshals the result as JSON
func indexHandler(w http.ResponseWriter, req *http.Request) {
	repos := repositories{}

	err := queryRepos(&repos)
	if err != nil {
		http.Error(w, err.Error(), 500)
		return
	}

	out, err := json.Marshal(repos)
	if err != nil {
		http.Error(w, err.Error(), 500)
		return
	}

	fmt.Fprintf(w, string(out))
}

// queryRepos first fetches the repositories data from the db
func queryRepos(repos *repositories) error {
	rows, err := db.Query(`
	SELECT
		id,
		repository_owner,
		repository_name,
		total_stars
	FROM repositories
	ORDER BY total_stars DESC`)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		repo := repositorySummary{}
		err = rows.Scan(
			&repo.ID,
			&repo.Owner,
			&repo.Name,
			&repo.TotalStars,
		)
		if err != nil {
			return err
		}
		repos.Repositories = append(repos.Repositories, repo)
	}
	err = rows.Err()
	if err != nil {
		return err
	}
	return nil
}

我可以在浏览器中调用该端点，将得到以下响应：

image

实现/api/repo/端点

除了/api/index之外，该应用程序还响应/api/repo/:owner/:name请求。这意味着它丢弃了其他格式的URL请求，比如/api/repo或/api/repo/:owner或/api/repo/1/2/3。我只想要2个令牌，owner和name。

通过在http.HandleFunc()调用的第一个参数中以/结束，将会调用处理程序来处理以/api/repo/开头的URL。

这个检查在parseParams()函数中完成：

1
2
3
4
5
6
7
8
9
10
// parseParams accepts a req and returns the `num` path tokens found after the `prefix`.
// returns an error if the number of tokens are less or more than expected
func parseParams(req *http.Request, prefix string, num int) ([]string, error) {
	url := strings.TrimPrefix(req.URL.Path, prefix)
	params := strings.Split(url, "/")
	if len(params) != num || len(params[0]) == 0 || len(params[1]) == 0 {
		return nil, fmt.Errorf("Bad format. Expecting exactly %d params", num)
	}
	return params, nil
}

示例用法：params, err := parseParams(req, "/api/repo/", 2)。

单个存储库端点处理程序的完整代码如下。大部分代码都是构建将承载数据的结构，并使用数据库调用填充它们：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
package main

import (
	"database/sql"
	"encoding/json"
	"fmt"
	"net/http"
	"strings"
)

// week represents the summary of a week of activity
// on a repository
type week struct {
	ID               int
	RepositoryID     int
	WeekNumber       int
	Year             int
	CreatedOn        string
	IssuesClosed     int
	IssuesOpened     int
	Stars            int
	Commits          int
	WeekStart        string
	WeekEnd          string
	PrOpened         int
	PrMerged         int
	PrClosed         int
}

// timeline represents important events happened on a
// repository, which will be displayed on the repo timeline
type timeline struct {
	ID           int
	RepositoryID int
	Title        string
	Description  string
	Emoji        string
	Date         string
}

// repository contains the details of a repository
type repository struct {
	ID         int
	Name       string
	Owner      string
	RepoAge    int
	Initialized bool
	CommitsPerMonth string
	StarsPerMonth string
	TotalStars int
}

// owner contains the details of an owner or a repo
type owner struct {
	ID                    int
	Name                  string
	Description           string
	Avatar                string
	GitHubID              string
	AddedBy               string
	Enabled               bool
	InstallationID        string
	RepositorySelection   string
}

// repoData contains the aggregate repository data returned
// by the API call
type repoData struct {
	MonthlyData monthlyData
	WeeklyData  []week
	Years       map[int]bool
	Timeline    []timeline
	Repository  repository
	Owner       owner
}

// monthlyData contains the monthly activity of a repo
type monthlyData struct {
	CommitsPerMonth string
	StarsPerMonth   string
}

// Error handling types

type errRepoNotInitialized string

func (e errRepoNotInitialized) Error() string {
	return string(e)
}

type errRepoNotFound string

func (e errRepoNotFound) Error() string {
	return string(e)
}

// parseParams accepts a req and returns the `num` path tokens found after the `prefix`.
// returns an error if the number of tokens are less or more than expected
func parseParams(req *http.Request, prefix string, num int) ([]string, error) {
	url := strings.TrimPrefix(req.URL.Path, prefix)
	params := strings.Split(url, "/")
	if len(params) != num || len(params[0]) == 0 || len(params[1]) == 0 {
		return nil, fmt.Errorf("Bad format. Expecting exactly %d params", num)
	}
	return params, nil
}

// repoHandler processes the response by parsing the params, then calling
// `query()`, and marshaling the result in JSON format, sending it to
// `http.ResponseWriter`.
func repoHandler(w http.ResponseWriter, req *http.Request) {
	repo := repository{}
	params, err := parseParams(req, "/api/repo/", 2)
	if err != nil {
		http.Error(w, err.Error(), http.StatusUnauthorized)
		return
	}
	repo.Owner = params[0]
	repo.Name = params[1]

	data, err := queryRepo(&repo)
	if err != nil {
		switch err.(type) {
		case errRepoNotFound:
			http.Error(w, err.Error(), 404)
		case errRepoNotInitialized:
			http.Error(w, err.Error(), 401)
		default:
			http.Error(w, err.Error(), 500)
		}
		return
	}

	out, err := json.Marshal(data)
	if err != nil {
		http.Error(w, err.Error(), 500)
		return
	}

	fmt.Fprintf(w, string(out))
}

// queryRepo first fetches the repository, and if nothing is wrong
// it returns the result of fetchData()
func queryRepo(repo *repository) (*repoData, error) {
	err := fetchRepo(repo)
	if err != nil {
		return nil, err
	}

	return fetchData(repo)
}

// fetchData calls utility functions to collect data from
// the database, builds and returns the `RepoData` value
func fetchData(repo *repository) (*repoData, error) {
	data := repoData{}
	err := fetchMonthlyData(repo, &data)
	if err != nil {
		return nil, err
	}
	err = fetchWeeklyData(repo, &data)
	if err != nil {
		return nil, err
	}
	err = fetchYearlyData(repo, &data)
	if err != nil {
		return nil, err
	}
	err = fetchTimelineData(repo, &data)
	if err != nil {
		return nil, err
	}
	err = fetchOwnerData(repo, &data)
	if err != nil {
		return nil, err
	}
	return &data, nil
}

// fetchRepo given a Repository value with name and owner of the repo
// fetches more details from the database and fills the value with more
// data
func fetchRepo(repo *repository) error {
	if len(repo.Name) == 0 {
		return fmt.Errorf("Repository name not correctly set")
	}
	if len(repo.Owner) == 0 {
		return fmt.Errorf("Repository owner not correctly set")
	}
	sqlStatement := `
	SELECT
		id,
		initialized,
		repository_created_months_ago
	FROM repositories
	WHERE repository_owner=$1 and repository_name=$2
	LIMIT 1;`
	row := db.QueryRow(sqlStatement, repo.Owner, repo.Name)
	err := row.Scan(&repo.ID, &repo.Initialized, &repo.RepoAge)
	if err != nil {
		switch err {
		case sql.ErrNoRows:
			//locally handle SQL error, abstract for caller
			return errRepoNotFound("Repository not found")
		default:
			return err
		}
	}
	if !repo.Initialized {
		return errRepoNotInitialized("Repository not initialized")
	}
	if repo.RepoAge < 3 {
		return errRepoNotInitialized("Repository not initialized")
	}
	return nil
}

// fetchOwnerData given a Repository object with the `Owner` value
// it fetches information about it from the database
func fetchOwnerData(repo *repository, data *repoData) error {
	if len(repo.Owner) == 0 {
		return fmt.Errorf("Repository owner not correctly set")
	}
	sqlStatement := `
	SELECT
		id,
		name,
		COALESCE(description, ''),
		COALESCE(avatar_url, ''),
		COALESCE(github_id, ''),
		added_by,
		enabled,
		COALESCE(installation_id, ''),
		repository_selection
	FROM organizations
	WHERE name=$1
	ORDER BY id DESC LIMIT 1;`
	row := db.QueryRow(sqlStatement, repo.Owner)
	err := row.Scan(&data.Owner.ID,
		&data.Owner.Name,
		&data.Owner.Description,
		&data.Owner.Avatar,
		&data.Owner.GitHubID,
		&data.Owner.AddedBy,
		&data.Owner.Enabled,
		&data.Owner.InstallationID,
		&data.Owner.RepositorySelection)
	if err != nil {
		return err
	}
	return nil
}

// fetchMonthlyData given a repository ID, it fetches the monthly
// data information
func fetchMonthlyData(repo *repository, data *repoData) error {
	if repo.ID == 0 {
		return fmt.Errorf("Repository ID not correctly set")
	}
	data.MonthlyData = monthlyData{}
	sqlStatement := `
	SELECT
		commits_per_month,
		stars_per_month
	FROM repositories_historic_data
	WHERE repository_id=$1
	ORDER BY id DESC LIMIT 1;`
	row := db.QueryRow(sqlStatement, repo.ID)
	err := row.Scan(
		&data.MonthlyData.CommitsPerMonth,
		&data.MonthlyData.StarsPerMonth)
	if err != nil {
		return err
	}

	return nil
}

// fetchWeeklyData given a repository ID, it fetches the weekly
// data information
func fetchWeeklyData(repo *repository, data *repoData) error {
	if repo.ID == 0 {
		return fmt.Errorf("Repository ID not correctly set")
	}
	rows, err := db.Query(`
	SELECT
		id,
		repository_id,
		week_number,
		year,
		created_on,
		issues_closed,
		issues_opened,
		stars,
		commits,
		week_start,
		week_end,
		pr_opened,
		pr_merged,
		pr_closed
	FROM repositories_weekly_data
	WHERE repository_id=$1
	ORDER BY id ASC`, repo.ID)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		week := week{}
		err = rows.Scan(
			&week.ID,
			&week.RepositoryID,
			&week.WeekNumber,
			&week.Year,
			&week.CreatedOn,
			&week.IssuesClosed,
			&week.IssuesOpened,
			&week.Stars,
			&week.Commits,
			&week.WeekStart,
			&week.WeekEnd,
			&week.PrOpened,
			&week.PrMerged,
			&week.PrClosed)
		if err != nil {
			return err
		}
		data.WeeklyData = append(data.WeeklyData, week)
	}
	err = rows.Err()
	if err != nil {
		return err
	}
	return nil
}

// fetchYearlyData returns the list of years for which we have weekly data
// available
func fetchYearlyData(repo *repository, data *repoData) error {
	if data.WeeklyData == nil {
		return fmt.Errorf("Repository weekly data not correctly set")
	}
	data.Years = make(map[int]bool)
	for i := 0; i < len(data.WeeklyData); i++ {
		year := data.WeeklyData[i].Year
		data.Years[year] = true
	}
	return nil
}

// fetchTimelineData returns all the timeline data we have in the db about
// the repo
func fetchTimelineData(repo *repository, data *repoData) error {
	if repo.ID == 0 {
		return fmt.Errorf("Repository ID not correctly set")
	}
	rows, err := db.Query(`
	SELECT
		id,
		repository_id,
		title,
		description,
		emoji,
		date
	FROM repositories_timelines
	WHERE repository_id=$1
	ORDER BY date ASC`, repo.ID)
	if err != nil {
		return err
	}
	defer rows.Close()
	for rows.Next() {
		timeline := timeline{}
		err = rows.Scan(
			&timeline.ID,
			&timeline.RepositoryID,
			&timeline.Title,
			&timeline.Description,
			&timeline.Emoji,
			&timeline.Date)
		if err != nil {
			return err
		}
		data.Timeline = append(data.Timeline, timeline)
	}
	err = rows.Err()
	if err != nil {
		return err
	}
	return nil
}

以下是调用/api/repo/dariubs/GoBooks的输出：

image

weeks元素包含了很多我们将在前端中使用的数据：

image

这是正确的JSON吗？

在JSON中生成大写属性看起来并不像“正确的JSON”。但是它是完全有效的。在JSON中没有标准的键名命名，而我们的格式被称为UpperCamelCase（也称为PascalCase）。

但是，许多样式指南要求使用以小写字母开头的camelCase，或者可以使用snake_case。

在这种情况下，您可能想使用struct tags（这里是一个详细的Go tags介绍）：

1
2
3
4
5
6
7
8
type repoData struct {
	MonthlyData monthlyData `json:"monthlyData"`
	WeeklyData  []week `json:"weeklyData"`
	Years       map[int]bool `json:"years"`
	Timeline    []timeline `json:"timeline"`
	Repository  repository `json:"repository"`
	Owner       owner `json:"owner"`
}

并且JSON将会根据您的需要更改：

image

总结

基本的API客户端已准备就绪。可以使用go build; ./api运行它。

当然，这只是一个起点，我们还有很多事情要做，才能使其成为一个合适的API端点，但我们正处在一个良好的轨道上。

接下来应该从哪里开始？

  • 测试API
  • 添加身份验证
  • 添加速率限制
  • 添加API版本控制