Tutorial: Building a REST API with Go and PostgreSQL | Tech Wiki Online

Tutorial: Building a REST API with Go and PostgreSQL

In this tutorial, I will demonstrate how to create a JSON API using Go. The API will serve data from a PostgreSQL database for a Vue.js application.

Table of Contents

• Introduction to the problem
• The existing database structure
• Simple HTTP response handler
• Connecting to PostgreSQL
• Moving handlers to their own file
• Implementing the /api/index endpoint
• Implementing the /api/repo/ endpoint
• Is this proper JSON?
• Wrapping up

Introduction to the problem

The goal of this tutorial is to develop a single-page application using Vue.js. The application will display a list of Git repositories, and upon clicking on a repository, it will show additional details. The API we’ll create will be read-only and have two endpoints:

• /api/index: Lists all the repositories
• /api/repo/:owner/:name: Shows the details of a specific repository identified by the owner and name

It’s important to note that the owner and name parameters follow the structure of a typical GitHub URL (e.g., github.com/owner/name).

To achieve this, we will connect our Go application to an existing PostgreSQL database and serve responses based on the parameters passed in the request.

The existing database structure

The application relies on multiple tables in the PostgreSQL database. These tables include:

• repositories: Contains information about repositories, such as the number of total stars
• repositories_weekly_data: Stores weekly activity information for each repository
• repositories_historic_data: Stores aggregated commit and star information per month
• repositories_timelines: Stores relevant events associated with each repository, such as milestone achievements or creation dates

Simple HTTP response handler

Let’s start by writing a simple HTTP server that handles two routes:

package main

import (
	"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) {
	//...
}

This code sets up a simple server that can handle parallel processing of requests.

Connecting to PostgreSQL

Next, we need to connect our Go application to the PostgreSQL database. For this, we will use the database/sql package without any additional ORM or external libraries. The PostgreSQL connection details will be retrieved from environment variables.

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
}

This code connects to the PostgreSQL database using the provided environment variables for the connection details. It also checks if the necessary environment variables are set and panics if any are missing. The connection is opened and kept active until the program exits.

Moving handlers to their own file

To keep the code organized, we’ll move the HTTP request handlers to separate files. Here are the updated files:

api/api.go

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

api/index.go

package handlers

import (
	"net/http"
)

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

api/repo.go

package handlers

import (
	"net/http"
)

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

Implementing the /api/index endpoint

The /api/index endpoint will list all the repositories present in the database. The response will include the repository’s name, owner, and the number of stars it has on GitHub. To fetch this data, we will query the repositories table and marshal the result as JSON.

package main

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

type repositorySummary struct {
	ID         int
	Name       string
	Owner      string
	TotalStars int
}

type repositories struct {
	Repositories []repositorySummary
}

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

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
}

In this code, we define two structs to hold the repository data. The indexHandler function queries the repositories table, fills the repositories struct, and then marshals it as JSON to be sent as the API response.

Implementing the /api/repo/ endpoint

The /api/repo/:owner/:name endpoint will return the details of a specific repository specified by the owner and name parameters. Similar to the /api/index endpoint, we will query the database for the repository details and its associated data using the provided repository ID.

Here’s the complete code for the /api/repo/ endpoint:

package main

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

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
}

type timeline struct {
	ID            int
	RepositoryID  int
	Title         string
	Description   string
	Emoji         string
	Date          string
}

type repository struct {
	ID         int
	Name       string
	Owner      string
	RepoAge    int
	Initialized bool
	CommitsPerMonth string
	StarsPerMonth string
	TotalStars int
}

type owner struct {
	ID                   int
	Name                 string
	Description          string
	Avatar               string
	GitHubID             string
	AddedBy              string
	Enabled              bool
	InstallationID       string
	RepositorySelection  string
}

type repoData struct {
	MonthlyData  monthlyData
	WeeklyData   []week
	Years        map[int]bool
	Timeline     []timeline
	Repository   repository
	Owner        owner
}

type monthlyData struct {
	CommitsPerMonth  string
	StarsPerMonth    string
}

type errRepoNotInitialized string

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

type errRepoNotFound string

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

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
}

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

func queryRepo(repo *repository) (*repoData, error) {
	err := fetchRepo(repo)
	if err != nil {
		return nil, err
	}

	return fetchData(repo)
}

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
}

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

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
}

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
}

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
}

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
}

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
}

This endpoint fetches various data associated with the specified repository, such as weekly activity, monthly statistics, timeline events, owner information, and more. The fetched data is then merged into the repoData struct, which is then marshaled as JSON.

Is this proper JSON?

In the JSON responses we generate, the property names of the struct fields are in UpperCamelCase, which differs from the commonly used camelCase or snake_case conventions. However, this is still valid JSON, and there is no standard naming convention for JSON keys.

If you prefer different naming conventions, like camelCase or snake_case, you can use struct tags to customize the field names when marshaling JSON. Simply add the json:"proper_field_name" tag to each struct field. For example:

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

By using struct tags, you can customize the JSON field names to fit your preferred convention.

Wrapping up

In this tutorial, we covered the basics of creating a JSON API using Go and PostgreSQL. Although this is just a starting point, you now have the foundation to build upon and expand the functionality of your API. Some potential next steps include testing the API, adding authentication, implementing rate limiting, and introducing API version control.

Tags: Go, Golang, REST API, PostgreSQL