Go教程：由PostgreSQL支持的REST API

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

我想要解決的問題的介紹
現有的數據庫結構
簡單的HTTP響應處理程序
連接到PostgreSQL
將處理程序移至它們自己的文件
實現/api/index端點
實現/api/repo/端點
這是正確的JSON嗎？
總結

正文

我想要解決的問題的介紹

我將在瀏覽器中使用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個路由：

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代碼。

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

$ export DBHOST=localhost
$ export DBPORT=5432
$ export DBUSER=you
$ export DBPASS=pass
$ export DBNAME=dbname

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

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

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

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請求處理程序移至它們自己的文件：

//...
import "github.com/flaviocopes/gitometer/api/handlers"
//...
http.HandleFunc("/api/index", handlers.Index)
http.HandleFunc("/api/repo/", handlers.Repo)
//...

package handlers

import (
	"net/http"
)

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

package handlers

import (
	"net/http"
)

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

实现`/api/index`端点

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

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

名稱
擁有者
GitHub上的星數

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

下面是代碼：

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
}

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

实现`/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()函数中完成：

// 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)。

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

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的输出：

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

这是正确的JSON吗？

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

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

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

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将会根据您的需要更改：

总结

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

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

接下来应该从哪里开始？

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

正文#

我想要解決的問題的介紹#

現有的數據庫結構#

簡單的HTTP響應處理程序#

連接到PostgreSQL#

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

实现/api/index端点#

实现/api/repo/端点#

这是正确的JSON吗？#

总结#

正文