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 theowner
andname
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 starsrepositories_weekly_data
: Stores weekly activity information for each repositoryrepositories_historic_data
: Stores aggregated commit and star information per monthrepositories_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