Building a Database-Backed REST API

In this lesson, explore building a database-backed rest api through one small Go program, and connect its output to the language rules behind it.

Put HTTP parsing and response logic in handlers, business rules in a service, and persistent operations behind a repository interface. This makes handlers testable with an in-memory fake before connecting a real SQL implementation.

The runnable example uses a map-based repository. It is not database-backed yet; the additional snippet shows how to substitute SQL code for the repository behavior.

Follow this diagram before executing the example. Check where a value or decision changes what runs next.

SQL repository replacement

type SQLStore struct { DB *sql.DB }
func (s SQLStore) Find(ctx context.Context, id int) (Task, error) {
    var t Task
    err := s.DB.QueryRowContext(ctx, "SELECT id, title FROM tasks WHERE id = ?", id).Scan(&t.ID, &t.Title)
    return t, err
}

When adding SQLStore, change the repository contract to accept context and errors; handle sql.ErrNoRows as 404 and unexpected failures as 500. This fragment needs a database driver, configured handle and matching schema. It is not an executable replacement without integration work.

Reference: Accessing relational databases.

This example is a complete program. Save it as main.go or paste it into the Go Playground.

Go code

package main

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

type Task struct {
	ID    int    `json:"id"`
	Title string `json:"title"`
}
type Store interface{ Find(int) (Task, bool) }
type MemoryStore map[int]Task

func (m MemoryStore) Find(id int) (Task, bool) { t, ok := m[id]; return t, ok }
func taskHandler(store Store) http.HandlerFunc {
	return func(w http.ResponseWriter, r *http.Request) {
		t, ok := store.Find(1)
		if !ok {
			http.NotFound(w, r)
			return
		}
		w.Header().Set("Content-Type", "application/json")
		json.NewEncoder(w).Encode(t)
	}
}
func main() {
	store := MemoryStore{1: {ID: 1, Title: "Learn Go"}}
	rec := httptest.NewRecorder()
	taskHandler(store)(rec, httptest.NewRequest("GET", "/tasks/1", nil))
	fmt.Println(rec.Code)
	fmt.Print(rec.Body.String())
}

Output

200
{"id":1,"title":"Learn Go"}

The HTTP handler asks Store for a record, independent of whether it is backed by memory or SQL. The in-memory fake returns a Task and the handler serializes it as JSON.

• In-memory test doubles do not prove real SQL constraints or transaction behavior.
• A real database-backed API needs validation, ownership checks, cancellation, and integration tests.

Create a SQLStore type using a *sql.DB, implement Find with QueryRowContext, and test it with a real driver and database.

Tip: Edit the example above, predict the output, and run the modified code to test your reasoning.

You have practiced Building a Database-Backed REST API in a small, self-contained program. The important habit is to predict what runs, inspect the actual output and distinguish the language rule from the particular example.

Before continuing, make sure you can explain the program to someone who has not seen it, including the line that changes its result.